LOAD

The LOAD command inserts rows into a table from a file.

Scope

ISQL only.

ISQL Syntax

LO [AD] [ P [ARTIAL]] FROM { E [XTERNAL] I [NTERNAL]} InputFileName [ AT StartingRow ] [ FOR NumberOfRows ] TO {[Owner.] TableName [Owner.] ViewName } [ ExternalInputSpec USING DescriptionFileName ] { Y [ES] PatternLocation Pattern N [O]}

Parameters

PARTIAL

indicates that you want to load only a range of rows from a file.

EXTERNAL

indicates the input file is a text file. The file can be created outside ISQL or with the EXTERNAL option of the ISQL UNLOAD command.

INTERNAL

indicates the input file is one that is created with the INTERNAL option of the ISQL UNLOAD command. The format of this file is recognizable only to ALLBASE/SQL, when executing the LOAD command with the INTERNAL option.

InputFileName

identifies the file containing the rows to be inserted. Name qualification follows MPE/iX conventions:

   FileName[/Lockword][.Group[.Account]]

StartingRow

identifies the first row to be read from an EXTERNAL file when the PARTIAL option is specified. The default is 1.

NumberOfRows

identifies the total number of rows, beginning with the starting row, to be read from a file. The default is the number of rows from StartingRow to the end of the file.

[Owner.]TableName

identifies the table to be loaded in the DBEnvironment you are using.

[Owner.]ViewName

identifies a view based on a single table. Refer to the CREATE VIEW command in the ALLBASE/SQL Reference Manual for restrictions governing insert operations on a view.

ExternalInputSpec

describes how ISQL should read an EXTERNAL file:

{ ColumnName StartingLocation Length [NullIndicator] [FormatType]} [...] E [ND]

ColumnName

identifies the column into which the data at StartingLocation is to be inserted. You must provide data for any column that is not null.

StartingLocation

is the column (byte position) in each external file line at which the data for ColumnName starts. The first column in a line is 1.

Length

is the number of input line columns (bytes) in the data field.

NullIndicator

identifies the character in an input data field that represents the value of null. You must supply a NullIndicator for any ColumnName that allows null values. The null indicator can be any one-byte character except a blank, a semicolon, a single or double quotation mark, a minus sign, or the current SET ESCAPE character. If you use a slash (/), precede it with the current SET ESCAPE character. In the data files, ensure the null indicator is at StartingLocation. Also ensure that any data having the null indicator as the first character does not start at StartingLocation in the data file.

FormatType

specifies the IBM data type of the column in the external file:

[ CHAR INTEGER PACKED Scale ZONED Scale ]

FormatType is required when the SET CONVERT option is ASCII or EBCDIC, unless the ALLBASE/SQL column being loaded is character or binary. The Scale parameter is an integer indicating the number of digits to the right of the decimal point.

END

indicates end of column descriptions.

DescriptionFileName

identifies a description file. Name qualification follows the same conventions used for the output file.

The description file contains one line for each column in the table. The first line describes the first column in the table, the second line describes the second column in the table, and so on. Each line contains seven fields. Data in all the fields except the column name field are right-justified; the column name field is left justified.

Column(s)	Contents
1-20	Column name.
25	LOAD/UNLOAD type code (see following list): 0 = SMALLINT & INTEGER 1 = BINARY 2 = CHAR DATE^[1] TIME^[1] DATETIME^[1] INTERVAL^[1] 3 = VARCHAR 4 = FLOAT 5 = DECIMAL 8 = NATIVE CHAR^[2] 9 = NATIVE VARCHAR^[2] 14 = VARBINARY 15 = LONG BINARY 16 = LONG VARBINARY
30-40	Output length.
41-50	Fraction length (number of decimal places).
51-60	Starting location of data.
61-70	Starting location of length of VARCHAR data.
75	Null indicator.
^[1]DATE, TIME, DATETIME and INTERVAL columns are known to the LOAD and UNLOAD commands as CHAR data. ^[2]NATIVE CHAR or VARCHAR is what SQLCore uses internally when a CHAR or VARCHAR column is defined with a LANG= clause.

A description file is generated automatically when using the UNLOAD EXTERNAL command, but can be created with an editor. When SET CONVERT is ASCII or EBCDIC, a description file cannot be specified.

YES

indicates that you want to load only rows that meet specific criteria.

PatternLocation

identifies the input line column at which a Pattern starts.

Pattern

is a character string that constitutes the criteria for loading. Only rows that have the Pattern starting at PatternLocation are loaded.

NO

indicates that you want to load all the rows in the external file.

Description

You must establish a DBE session with the CONNECT or the START DBE command before using the LOAD command.
If a tape is being used, mount the tape and issue the LOAD command at the ISQL prompt; otherwise just issue the LOAD command. When the end of the tape is reached, the ISQL prompt reappears. To continue loading data from a second tape, mount the tape and reissue the LOAD command. You can use the REDO command to enter the same syntax again.
When loading has begun, the following message is displayed.
Command in progress
During table loading from either external or internal files, ISQL displays the cumulative number of rows read and rows loaded after it processes a group of rows. For example:
Number of rows read is 12 Number of rows processed is 12 Number of rows read is 24 Number of rows processed is 24 . . .
The number of rows in each group inserted depends on the columns and their sizes.

If AUTOCOMMIT is off, the following message appears after loading is complete:

   Number of rows read is n

   Number of rows processed is n

   COMMIT WORK to save to DBEnvironment.

If AUTOCOMMIT is on, ISQL will continue to load rows until the 16K tuple buffer is filled, even if the number of rows specified in the AUTOSAVE option has been exceeded. Once the 16K tuple 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. For more information on the 16K tuple buffer, refer to the "Concurrency Control through Locks and Isolation Levels" chapter in the ALLBASE/SQL Reference Manual. A COMMIT WORK is also processed after the last row is loaded; then the following message appears:
Number of rows read is n Number of rows processed is n DBEnvironment has been updated.
To improve performance, issue the following SET commands prior to the load operation:
- SET LOAD_BUFFER to enlarge the load buffer beyond the default size of 16,384 bytes.
- SET AUTOLOCK ON to lock the table in exclusive mode when the load is performed.
- SET AUTOCOMMIT ON and SET AUTOSAVE to automatically commit the number of rows specified by autosave if the load buffer is full. Should the load operation subsequently fail, you can insert the remaining rows with the LOAD PARTIAL command.
- SET SESSION DML ATOMICITY AT ROW LEVEL to reduce logging overhead when running in non-archive mode.
- SET SESSION UNIQUE, REFERENTIAL, CHECK CONSTRAINTS DEFERRED to defer constraint checking until the end of the load operation.
For more hints on improving load performance, refer to the "Initial Table Loads" section in the ALLBASE/SQL Performance and Monitoring Guidelines
If the LOAD command is terminated due to an error, you can correct the problem, then insert the remaining rows by submitting a LOAD PARTIAL command after the last row read. However, if the error causes your transaction to be terminated, any rows loaded since the last COMMIT WORK are rolled back. In this case, submit a LOAD PARTIAL command after the last row read before the last COMMIT WORK.

In prompting mode, ISQL prompts you for input options as follows:

   isql=> LOAD PARTIAL;



   File format (i[nternal] or e[xternal])> INTERNAL;

   Input file name> InputFileName;

   Starting Row> StartingRow;

   Number of Rows> NumberOfRows;

   Table name> TableName;

You are prompted for StartingRow and NumberOfRows only if the PARTIAL option is specified.

When you are loading from an external file in prompting mode, ISQL prompts you for DescriptionFileName if you specified the keyword USING. If the keyword USING was not specified, ISQL prompts you for a description of each input column. For example:

   Enter information as requested for each column to be loaded.

   Enter END for the column name when finished.

   Column name> ColumnName;

   Starting location> StartingLocation;

   Column length> Length;

   Null representation> NullRepresentation;

   Column name>



       .

       .

       .

You are prompted for a NullIndicator only if the column you are describing allows null values.

When loading from an external file created by using the UNLOAD command, specify a StartingLocation after the 10-byte prefix if the file contains VARCHAR data. The 10-byte prefix, written by the UNLOAD command using the EXTERNAL option, contains the actual length of the VARCHAR data item.
If a column can contain null values but the null indicator you specify is not in the proper column in an external data file, ISQL loads space(s) into character columns, loads zero(s) into numeric columns, and generates an error message for date/time columns.
If a column is not null and the external data file contains spaces, ISQL loads space(s) into character columns, loads zero(s) into numeric columns, and generates an error message for date/time columns.
When loading a large number of rows, use the LOCK TABLE statement in EXCLUSIVE mode before using the LOAD command. This keeps the load process from obtaining a large number of page locks and thus depleting shared memory for the lock table. Alternatively, you can increase the number of control block pages by using the SQLUtil ALTDBE command.
If you create a description file with an editor, the length of a BINARY column is double the actual length of the column. This is caused by the description file being in ASCII format where one character is translated to a byte; however, in hexadecimal format two bytes make one character. So for ALLBASE/SQL to read the correct number of bytes, the actual length is doubled.
When loading a LONG column and you are prompted for the output length, respond with the length of the output file name, not the length of the data type.
In external files, DATE, TIME, DATETIME, and INTERVAL columns appear as characters. Internally, they are stored as binary values, although ISQL returns a code of 2 (CHAR) for them in creating description files.
If the SET CONVERT option is ASCII or EBCDIC, ISQL converts the IBM mainframe data from the InputFileName file during the LOAD operation. The supported conversions of IBM mainframe data when loading ALLBASE/SQL columns are as follows:

Table 4-2 Valid IBM Mainframe Format Types

Target ALLBASE/SQL Column	IBM Mainframe Format Type	ExternalInputSpec FormatType
INTEGER or SMALLINT	INTEGER PACKED DECIMAL ZONED DECIMAL^[1] ASCII EBCDIC	INTEGER PACKED Scale ZONED Scale CHAR CHAR
DECIMAL	PACKED DECIMAL ZONED DECIMAL^[1] ASCII EBCDIC	PACKED `Scale` ZONED `Scale` CHAR CHAR
CHAR or VARCHAR	ASCII EBCDIC	none none
BINARY or VARBINARY	BINARY	none
^[1]A ZONED DECIMAL number may have either a leading or trailing sign.

The EXTERNAL parameter must be specified. The DescriptionFileName parameter is not allowed. Since NULL values are not permitted, ISQL does not prompt for them. ISQL generates additional prompts for the format type of the InputFileName data only if the data type of the ALLBASE/SQL column is INTEGER, SMALLINT, or DECIMAL.

If the format type to be loaded is PACKED DECIMAL or ZONED DECIMAL, ISQL prompts for the Scale, which is the number of digits to the right of the decimal point. The location of the decimal point is determined by the response to the Scale prompt, not by the column definition. When loading an ALLBASE/SQL INTEGER or SMALLINT column with PACKED DECIMAL or ZONED DECIMAL data, the digits to the right of the decimal point are truncated. If an ALLBASE/SQL numeric column is not large enough to hold the incoming PACKED DECIMAL or ZONED DECIMAL data, an error occurs.

Authorization

You must be authorized to insert a row into the table named. Refer to the INSERT command in the ALLBASE/SQL Reference Manual.

Example

A COMMIT WORK is automatically performed when 40 rows have been loaded from the external Price file into the PurchDB.SupplyPrice table. ISQL prompts for the column name, starting location, column length, and null representation.

   isql=> SET AUTOCOMMIT ON;

   isql=> SET AUTOSAVE 40;

   isql=> LOAD FROM EXTERNAL Price TO PurchDB.SupplyPrice;



   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> VendorNumber;

   Starting location> 17;

   Column length> 4;

   Column name> VendPartNumber;

   Starting location> 24;

   Column length> 6;

   Column name> UnitPrice;

   Starting location> 33;

   Column length> 7;

   Null representation> ?;

   Column name> DeliveryDays;

   Starting location> 42;

   Column length> 3;

   Null representation> ?;

   Column name> END;

   Load depending on value in input record (Y/N)> Y;

   Starting location of select field> 17;

   Select field pattern> 9014;

   Command in progress

   Number of rows read is 25

   Number of rows processed is 15

   Number of rows read is 55

   Number of rows processed is 30

   Number of rows read is 75

   Number of rows processed is 42

   DBEnvironment has been updated.

A COMMIT WORK is automatically performed when 50 rows have been loaded from the external EParts file into the PurchDB.Parts table. ISQL does not prompt for the external input specification values, since they are included as parameters of the LOAD command.

   isql=> SET AUTOSAVE 50

   isql=> LOAD FROM EXTERNAL EParts

   > TO PurchDB.Parts

   > PartNumber 1    16

   > PartName   17   30    ?

   > SalesPrice 47    7    ?

   > END

   > N;

   >

   Command in progress

   Number of rows read is 25

   Number of rows processed is 25

   Number of rows read is 50

   Number of rows processed is 50

   DBEnvironment has been updated.

   Number of rows read is 75

   Number of rows processed is 75

   Number of rows read is 78

   Number of rows processed is 78

   DBEnvironment has been updated.



   isql=>

Starting with row 5, load 60 rows from the external file ExtParts into the PurchDB.Parts table.

   isql=> LOAD PARTIAL FROM EXTERNAL ExtParts

   > AT 5

   > FOR 60

   > TO PurchDB.Parts

   > USING DesParts;

   Command in progress

   Number of rows read is 25

   Number of rows processed is 25

   Number of rows read is 50

   Number of rows processed is 50

   DBEnvironment has been updated.

   Number of rows read is 60

   Number of rows processed is 60

   DBEnvironment has been updated.



   isql=>

Load from the external file Ecustomer, which was created on an IBM mainframe, into the Customer table. The character data in the external file is in EBCDIC format.



   isql=>INFO Customer;



Column Name     Data Type (length)   Nulls Allowed    Language

-----------------------------------------------------------------

CUSTOMERID      Char      (   10)       NO            n-computer

CREDITAMOUNT    Decimal   (   10,  4)   NO

QTYSOLD         SmallInt                NO

TESTDATA        Binary    (   20        NO



   isql=> SET CONVERT EBCDIC; 



   isql=> LOAD FROM EXTERNAL ECustomer TO Customer

   > CustomerId    1  8

   > CreditAmount  9  4 packed 2

   > QtySold      13  4 integer

   > TestData     17  8

   > END

   > N;

   >

   Command in progress.

   Number of rows read is 64

   Number of rows processed is 64

   COMMIT WORK to save to DBEnvironment.



   isql=>