The easiest way to insert data into a table is by loading data from
a file through ISQL using the LOAD command.
The file can be either an EXTERNAL
or an INTERNAL file:
An EXTERNAL file is a sequential, ASCII, fixed-format
file created either outside of ISQL or with the
EXTERNAL option of the ISQL UNLOAD command.
EXTERNAL files contain only data and are used when loading
initial data into tables, or when restructuring
tables by changing column names, column size, or data type.
An INTERNAL file is created with the INTERNAL option of
the ISQL UNLOAD command.
INTERNAL files store the data format in a header that
contains data and column descriptors used by
ALLBASE/SQL to load data more efficiently.
INTERNAL files are used to unload and load tables when restructuring
without changing columns. The
column definitions of the unloaded and loaded tables
do not need to match exactly, since
compatible data types are converted.
For details on table restructuring, refer to the "Maintenance"
SECT in this guide.
The syntax for the LOAD command is explained in detail in the
ISQL Reference Manual for ALLBASE/SQL and IMAGE/SQL.
Loading from an External File |
 |
If you are loading data from a non-ALLBASE/SQL file, you
must use the EXTERNAL option of the ISQL LOAD command.
To use the LOAD command, you must know the definition of the
table to be loaded. The INFO command shows table definitions.
The following example of the LOAD command loads data from
an MPE/iX file
to the PurchDB.OrderItems table:
isql=> LOAD FROM EXTERNAL ORDERITE
> TO PurchDB.OrderItems
> OrderNumber 1 6
> ItemNumber 9 2
> VendPartNumber 13 8 ?
> PurchasePrice 21 10
> OrderQty 31 4 ?
> ItemDueDate 37 8 ?
> ReceivedQty 47 2 ?
> END
|
The first number after the column name indicates where in the
file the data for that column starts. The second number tells
ISQL the length of the input. The last
character is a null indicator. You must supply a null indicator
if the column allows null values.
The null indicator is any single
character except a blank, a semicolon,
a single or double quote, or the current ISQL escape character.
Do not choose a null indicator that might also represent valid data.
For example if you are loading integers, do not
use zero as a null indicator.
When a null indicator is found, a null value is loaded, not the
character representing the null indicator.
See the "Data Types" chapter of the ALLBASE/SQL Reference Manual for more information on
null values.
You can also load from an external file using the description
file option, as in the following example:
isql=> LOAD FROM EXTERNAL ORDERITE
> TO PurchDB.OrderItems
> USING orderdescrip;
|
All PurchDB tables are listed in the "Sample DBEnvironment"
appendix in the ALLBASE/SQL Reference Manual.
An ISQL option exists for loading data from external files
that contain EBCDIC, packed decimal, and zoned decimal data.
Refer to the description of the LOAD command in the
"ISQL Commands" chapter of the ISQL Reference Manual for ALLBASE/SQL and IMAGE/SQL.
Loading from an Internal File |
|
You can
unload an existing table using the INTERNAL option of the ISQL UNLOAD
command, then load the file into the new table with the
INTERNAL option of the LOAD command. This method is faster
than loading an EXTERNAL file because the data is already
in the necessary format.
Use the INTERNAL option to move the data in a table in one
DBEnvironment to a table with identical name, columns, and data
types in another DBEnvironment.
Loading Tables with Constraints on Them |
|
If a table has already been built with a referential integrity constraint,
the LOAD command will exercise the constraint, testing each value
for compliance. In order to speed loading in non-archive mode,
you can use the following statements:
SET DML ATOMICITY AT ROW LEVEL
SET CONSTRAINTS DEFERRED
|
The SET DML ATOMICITY statement reduces logging overhead for
the load operation when the DBEnvironment is running
in non-archive mode to improve performance at load time.
The SET CONSTRAINTS DEFERRED statement suspends integrity
checking until a COMMIT WORK statement is issued, at which time
the integrity checking takes place for the entire table. This
approach is recommended.
Loading Tables with Rules Built on Them |
|
If a table has a rule defined on it for the INSERT statement
type, the rule will fire during load operations. If you are loading
a set of tables that are related by a set of rules, and you know that
the initial data already conforms to the conditions enforced by the
rules, you can use the following statements to improve the
performance of the initial table load:
SET DML ATOMICITY AT ROW LEVEL
DISABLE RULES
|
The SET DML ATOMICITY statement reduces logging overhead for
the load operation when the DBEnvironment is running in non-archive mode
to improve performance at load time. The DISABLE RULES statement
turns off rule checking. You should only use DISABLE RULES for loads
that take place in single-user mode, since the DISABLE RULES
statement affects all rules in the DBEnvironment. As soon as
the tables have been loaded, you should issue the following
statements:
ENABLE RULES
SET DML ATOMICITY AT STATEMENT LEVEL
|
As an alternative to disabling rules, you can load the tables first,
then create the rules that interrelate them. In any event, if
you load data without using the rules, it is your obligation to
make sure the data you are loading conforms to the rules. This
is because the operation of rules is not retroactive; they will
only fire on rows inserted after rule firing is enabled again.
Using Command Files for Loading |
|
Use ISQL command files to create and test load and unload commands.
Once the DBEnvironment is in operation, use the SQLGEN GENERATE
LOAD and GENERATE UNLOAD commands to create load and unload scripts
for you.
Refer to the "Maintenance" chapter in this guide for more information
and examples of using the UNLOAD command to restructure tables.
