HP 3000 Manuals HP 3000 Manuals
Loading Tables
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" chapter 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.