HP 3000 Manuals HP 3000 Manuals
Unloading and Loading Tables
ISQL provides the following commands for loading and unloading data:
* UNLOAD copies data from tables into a file.
* LOAD provides higher-volume data loading than the INPUT or INSERT.
commands by providing bulk inserts using data stored in a file.
Like the other ISQL commands, these commands cannot be used from within
an application program.
Using the UNLOAD Command
You can unload all of a table to an output file by specifying a table
name, or you can unload selected data by specifying a SELECT statement
with the UNLOAD command. Also, the data can be unloaded in sorted order
if you use the GROUP BY or ORDER BY clause as part of the SELECT
statement. For complete information on the SELECT statement, refer to
the ALLBASE/SQL Reference Manual. You specify the format of the output
file as either external or internal.
External Format.
You unload a table to an external data file using the external format for
the following reasons:
* To use the data file in an application other than ALLBASE/SQL.
* To change column names, sizes, or data types when reloading.
* To edit the file.
* To do maintenance tasks, such as splitting columns.
When you use the EXTERNAL option of the UNLOAD command, ISQL creates an
external file and a description file. The external file contains the
data; the description file describes the location, length, and format of
the data in the external file. The external and description files can be
read by an editor, an application program, or ISQL (when you use the LOAD
command with the EXTERNAL option). Application programs and ISQL use the
description file to interpret the format of the data in the external data
file.
NOTE You can use the UNLOAD command with the EXTERNAL option to unload
LONG column values. For LONG columns, the data is placed in the
output file or device that was specified at the time the data was
inserted into the table (or at the time the column was last
updated). The output file or device name is placed in the external
file.
You can unload data from a table in the following ways:
* With the prompting mode of the UNLOAD command.
* Directly from the isql prompt.
* By using a command file.
When you are using prompting mode as shown below, ISQL displays the
attributes of each column you are unloading and prompts you to describe
how values from this column should appear in the external file.
isql=> UNLOAD;
Output file format (i[nternal] or e[xternal])> EXTERNAL;
Output file name> EParts;
TableName or "SelectStatement"> PurchDB.Parts;
Description file name> DParts;
Column PARTNUMBER, Char (16):
Output length> 16;
Column PARTNAME, Char (30):
Output length> 30;
Null indicator> ?;
Column SALESPRICE, Decimal (10,2):
Output length> 11;
Fraction length> 2;
Null indicator> ?;
Command in progress.
Number of rows processed is 21
isql=>
In the case of LONG columns, you should enter the length of the output
device name in response to the prompt for output length.
To unload the same file as in the example above, you can also enter the
necessary information directly on the command line:
isql=> UNLOAD TO EXTERNAL EParts FROM PurchDB.Parts
> DParts 16 30 ? 11 2 ?;
Note that the keywords TO and FROM are necessary in addition to the
responses given if you had been prompted.
Similarly, you could create a command file containing the following
lines:
unload to external
EParts from PurchDB.Parts
DParts 16 30 ? 11 2 ?
To execute the command file, use the START command. For example, if the
file is named MyFile, issue the following command:
isql=> START MyFile;
The description file is automatically created and contains a record for
each column unloaded. The record describes where and in what format the
data exists in the external file. You can use your editor to view this
file. The description file has seven columns containing the following
information about the external data file:
Characters 1 - 20: Column name
Character 25: Data type code
Characters 30 - 40: Output length
Characters 41 - 50: Fraction Length
Characters 51 - 60: Starting location of the data
Characters 61 - 70: Starting location of length of VARCHAR data
Character 75: Character chosen for the NULL indicator
You may unload the TID (tuple identifier) data by specifying the SELECT
statement:
isql=> UNLOAD;
Output file format (i[nternal] or e[xternal])> EXTERNAL;
Output file name> TidOut;
TableName or "SelectCommand"> "SELECT Tid() FROM PurchDB.Parts";
Description file name> TidDesc;
Column TID, Tuple ID:
Output length> 16;
Command in progress.
Number of rows processed is 22
isql=>
NOTE Although the UNLOAD command allows you to copy TID data into a
file, you may not use the LOAD command to load the TID data back
into a table. The TID is a unique identifier generated internally
by ALLBASE/SQL, and may not be assigned by user programs.
Internal Format.
The internal format specified in the following example is understandable
only by ALLBASE/SQL. It is faster than the EXTERNAL option, because the
data is not converted from internal to ASCII format. Files in internal
format are read by ISQL when you use the LOAD command with the INTERNAL
option.
isql=> UNLOAD;
Output file format (i[nternal] or e[xternal])> INTERNAL;
Output file name> IntFile;
TableName or "SelectCommand"> PurchDB.Vendors;
Command in progress.
Number of rows processed is 15
isql=>
You cannot use the INTERNAL option to unload LONG column data.
Using the LOAD Command
You load data stored in a file into a table by using the LOAD command.
The file is a data file in external or internal format.
External Format.
The EXTERNAL option of the LOAD command is used to load external data
files into a database table. The external data file can be one of the
following:
* The external file produced when the EXTERNAL option of the UNLOAD
command is used.
* A file created with an editor.
* The output of an application program.
* A file created on an IBM mainframe.
You can load data into a table in the following ways:
* Using the external data and description files produced with the
UNLOAD EXTERNAL command.
* Creating an external data file and using the prompting mode of the
LOAD command.
* Creating an external data file and entering the LOAD EXTERNAL
command from the isql prompt.
* Creating an external data file and using a command file.
To load the data, which was unloaded with the EXTERNAL option in the
previous example, you specify the external data file name, the table
where the data is to be loaded, and the description file name in the LOAD
FROM EXTERNAL command as follows:
isql=> LOAD FROM EXTERNAL EParts to Purchdb2.Parts USING DParts;
If you want to load data that was not produced with the EXTERNAL option
of the UNLOAD command, you need to create the external data file and
provide a description of the data to be loaded as part of the LOAD
command.
You can create the external data file with the editor as shown below:
isql=> EDIT;
The editor named in the EDITOR option of the SET command is invoked.
/ a
1 1145 Abacus 15.50
2 1167 Vacuum tube 7.95
3 260097 Analytic engine 9000.50
:
/k EParts2
isql=>
String data does not need to be enclosed in quotation marks. You do not
need to align numeric values within a column, and you can extend beyond
column 72.
To provide a description of the data file, you can use the LOAD command
in prompting mode to provide ISQL with the location and length of the
data, and the character used for the null indicator. To obtain this
information you can use the INFO command. For example:
isql=> LOAD;
File format (i[nternal] or e[xternal])> EXTERNAL;
Input file name> EParts2;
Table name> OldParts;
Enter information as requested for each column to be loaded.
Enter END for the column name when finished.
Column name> PartNumber;
Starting location> 1;
Column length> 16;
Column name> PartName;
Starting location> 17;
Column length> 30;
Null representation> ?;
Column name> SalesPrice;
Starting location> 47;
Column length> 10;
Null representation> ?;
Column name> END;
Load depending on value in input record (Y/N)> Y;
Starting location of select field> 1;
Select field pattern> 1;
Command in progress
Number of rows read is 21
Number of rows processed is 21
COMMIT WORK to save to DBEnvironment
isql=>
In the example above, you can only load data from records that contain a
specific character string (Select field pattern) beginning at a specific
location (Starting location of select field) in the input records. In
this example, only parts having numbers that start with 1 are loaded.
You can also specify only a range of records using the PARTIAL option.
Refer to the LOAD command in chapter 4, "ISQL Commands," for a
description of the PARTIAL option.
To load the above data into a table, you enter the LOAD FROM EXTERNAL
command and include the following information:
* External data file name
* Name of the target file
* Responses which you would give if you were in prompting mode
For example:
isql=> LOAD FROM EXTERNAL EParts to PurchDB.Parts
> PARTNUMBER 1 16
> PARTNAME 17 30 ?
> SALESPRICE 47 10 ?
> END
> Y 1 1;
The indicated column name, starting location, length, and the character
used as the null character are specified. The command is terminated by
the word END, a semicolon, and a Y or N. The N response to the question
indicates that you want to load all the rows in the external data file.
A Y response requires that you include the pattern location (the column
number where the pattern begins) and the pattern.
Similarly, if you want to use a command file, it would look like this:
LOAD FROM EXTERNAL EParts to PurchDB.Parts
PARTNUMBER 1 16
PARTNAME 17 30 ?
SALESPRICE 47 10 ?
END
Y 1 1;
To execute the command file use the START command as follows:
isql=> START MyFile;
Internal Format.
If you used the INTERNAL option of the the UNLOAD command, you specify it
again when loading the data. For example:
isql=> LOAD;
File format (i[nternal] or e[xternal])> INTERNAL;
Input file name> IntFile;
Table name> PurchDB.Vendors;
Number of rows read is 15
Number of rows processed is 15
COMMIT WORK to save to DBEnvironment.
isql=>
Setting the AUTOCOMMIT Option.
Modified or newly loaded data is written to the log buffer. When a
COMMIT WORK is processed, the data is moved from the log buffer to the
log file and the changes to the DBEnvironment are made permanent. When
nonarchive mode (the default) is set, the nonarchive log file space is
reclaimed.
As in the case of the INPUT command, ISQL processes the COMMIT WORK
statement based on the AUTOCOMMIT option value of the SET command. If
AUTOCOMMIT is ON, ISQL will continue to accept rows for input[REV BEG]
until the load buffer is filled, even if the number of rows specified in
the AUTOSAVE option has been exceeded. Once the load buffer is full, a
check is made to see if the number of rows specified in the AUTOSAVE
option has been reached or exceeded.[REV END] If this is the case ISQL
processes a COMMIT WORK statement.
If AUTOCOMMIT is OFF (the default setting), the rows are committed only
when you enter the COMMIT WORK statement. When using nonarchive mode,
use the AUTOCOMMIT command with the option value set to ON to ensure that
the nonarchive log file (a circular file) is not overwritten before you
process the COMMIT WORK statement.