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.



   1145            Abacus                        15.50

   1167            Vacuum tube                   7.95

   260097          Analytic engine               9000.50

   .

   .

   .

   :wq 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 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. 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.

Unloading and Loading Tables

Technical documentation

» Table of Contents

» Index

Using the UNLOAD Command

External Format

Internal Format

Using the LOAD Command

External Format

Internal Format

Setting the AUTOCOMMIT Option