HP 3000 Manuals HP 3000 Manuals
LOAD
The LOAD command inserts rows into a table from a file.
Scope
ISQL only.
ISQL Syntax
LO[AD] [P[ARTIAL]] FROM {E[XTERNAL]} InputFileName [AT StartingRow]
{I[NTERNAL]}
[FOR NumberOfRows] TO {[Owner.]TableName} [ExternalInputSpec ]
{[Owner.]ViewName } [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 XL
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:
[REV BEG]
{ColumnName StartingLocation Length [NullIndicator]}
{[FormatType] }
[...] E[ND]
[REV END]
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.
[REV BEG]
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.[REV
END]
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 |
| | DATE1 |
| | TIME1 |
| | DATETIME1 |
| | INTERVAL1 |
| | 3 = VARCHAR |
| | 4 = FLOAT |
| | 5 = DECIMAL |
| | 8 = NATIVE CHAR2 |
| | 9 = NATIVE VARCHAR2 |
| | 14 = VARBINARY |
| | 15 = LONG BINARY |
| | 16 = LONG VARBINARY |
| | |
| | |
| | 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. |
| | |
---------------------------------------------------------------------------------------------
| | |
| 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. |
| | |
---------------------------------------------------------------------------------------------
A description file is generated automatically
when using the UNLOAD EXTERNAL command, but can
be created with an editor.[REV BEG] When SET
CONVERT is ASCII or EBCDIC, a description file
cannot be specified.[REV END]
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.
[REV BEG]
* 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.[REV END] 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.
[REV BEG]
* To improve performance when running in non-archive mode, issue the
SET AUTOCOMMIT OFF and SET DML ATOMICITY AT ROW LEVEL commands
before the LOAD command. The SET AUTOCOMMIT OFF command is
required because a COMMIT WORK automatically sets the DML
ATOMICITY to STATEMENT. The SET DML ATOMICITY AT ROW LEVEL
statement reduces logging overhead, although logging is still
performed.
* To defer constraint error checking until the end of a transaction,
issue the SET AUTOCOMMIT OFF command and the SET CONSTRAINTS
statement with the DEFERRED option before the LOAD command. The
SET AUTOCOMMIT OFF command is required because a COMMIT WORK
automatically sets constraint checking to IMMEDIATE.[REV END]
* 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 [REV
BEG] numeric columns, and generates an error message for date/time
columns. [REV END]
* If a column is not null and the external data file contains
spaces, ISQL loads space(s) into character columns, loads zero(s)
[REV BEG] into numeric columns, and generates an error message for
date/time columns. [REV END]
* 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.[REV BEG]
* 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.[REV END]
[REV BEG]
* 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:
Valid IBM Mainframe Format Types
------------------------------------------------------------------------------------
| | | |
| Target ALLBASE/SQL Column | IBM Mainframe Format | ExternalInputSpec |
| | Type | FormatType |
| | | |
------------------------------------------------------------------------------------
| | | |
| INTEGER or SMALLINT | INTEGER | INTEGER |
| | PACKED DECIMAL | PACKED Scale |
| | ZONED DECIMAL1 | ZONED Scale |
| | ASCII | CHAR |
| | EBCDIC | CHAR |
| | | |
------------------------------------------------------------------------------------
| | | |
| DECIMAL | PACKED DECIMAL | PACKED Scale |
| | ZONED DECIMAL1 | ZONED Scale |
| | ASCII | CHAR |
| | EBCDIC | CHAR |
| | | |
------------------------------------------------------------------------------------
| | | |
| CHAR or VARCHAR | ASCII | none |
| | EBCDIC | 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.
[REV END]
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
[REV BEG]
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.[REV END]
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.
[REV BEG]
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.[REV END]
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=>
[REV BEG]
Starting with row 5, load 60 rows from the external file ExtParts into the
PurchDB.Parts table.[REV END]
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=>
[REV BEG]
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=>
To improve performance, the SET DML ATOMICITY AT ROW LEVEL statement is
issued prior to the LOAD command. Since a COMMIT WORK automatically sets
the DML ATOMICITY to STATEMENT, AUTOCOMMIT is set to OFF.
isql=> BEGIN WORK;
isql=> SET AUTOCOMMIT OFF;
isql=> SET DML ATOMICITY AT ROW LEVEL;
isql=> LOAD FROM EXTERNAL ExtParts
> TO PurchDB.Parts
> USING DesParts;
Load depending on value in input record (Y/N)> N;
Command in progress.
Number of rows read is 256
Number of rows processed is 256
Number of rows read is 308
Number of rows processed is 308
COMMIT WORK to save to DBEnvironment.
isql=> COMMIT WORK;
isql=>[REV END]