Data Movement Utilities Guide and Reference

LOAD Command

Command Syntax

                .-,-------------.
                V               |
>>-LOAD FROM------+-filename-+--+--OF--filetype----------------->
                  +-pipename-+
                  '-device---'
 
>-----+-----------------------------+--------------------------->
      |            .-,-----------.  |
      |            V             |  |
      '-LOBS FROM-----lob-path---+--'
 
>-----+-----------------------------------+--------------------->
      |              .-----------------.  |
      |              V                 |  |
      '-MODIFIED BY-----filetype-mod---+--'
 
>-----+-----------------------------------------------------------------------------------------------+>
      |                 .-,---------------------------.                                               |
      |                 V                             |                                               |
      '-METHOD--+-L--(-----column-start--column-end---+---)--+-----------------------------------+-+--'
                |                                            |                     .-,----.      | |
                |                                            |                     V      |      | |
                |                                            '-NULL INDICATORS--(-----n---+---)--' |
                |       .-,--------------.                                                         |
                |       V                |                                                         |
                +-N--(-----column-name---+---)-----------------------------------------------------+
                |       .-,------------------.                                                     |
                |       V                    |                                                     |
                '-P--(-----column-position---+---)-------------------------------------------------'
 
>-----+---------------+---+--------------+---------------------->
      '-SAVECOUNT--n--'   '-ROWCOUNT--n--'
 
>-----+------------------+---+-------------------------+-------->
      '-WARNINGCOUNT--n--'   '-MESSAGES--message-file--'
 
>-----+--------------------------------+---+-INSERT----+-------->
      '-TEMPFILES PATH--temp-pathname--'   +-REPLACE---+
                                           +-RESTART---+
                                           '-TERMINATE-'
 
>----INTO--table-name----+------------------------------+------->
                         |    .-,----------------.      |
                         |    V                  |      |
                         '-(-----insert-column---+---)--'
 
>-----+--------------------------------------------+------------>
      '-DATALINK SPECIFICATION--| datalink-spec |--'
 
>-----+----------------------------+---------------------------->
      '-FOR EXCEPTION--table-name--'
 
>-----+-------------------------------------------------------------------------------------------+>
      |               .-YES-.                                                                     |
      '-STATISTICS--+-+-----+--+-+---------------------------------------------------------+-+-+--'
                    |          | '-WITH DISTRIBUTION--+---------------------------------+--' | |
                    |          |                      '-AND--+----------+--INDEXES ALL--'    | |
                    |          |                             '-DETAILED-'                    | |
                    |          '-+---------------------------------------+-------------------' |
                    |            '--+-AND-+---+----------+--INDEXES ALL--'                     |
                    |               '-FOR-'   '-DETAILED-'                                     |
                    '-NO-----------------------------------------------------------------------'
 
>-----+------------------------------------------------------------------+>
      |       .-NO-----------------------------------------------------. |
      +-COPY--+-YES--+-USE ADSM--+---------------------------+-------+-+-+
      |              |           '-OPEN--num-sess--SESSIONS--'       |   |
      |              |     .-,-------------------.                   |   |
      |              |     V                     |                   |   |
      |              +-TO-----device/directory---+-------------------+   |
      |              '-LOAD--lib-name--+---------------------------+-'   |
      |                                '-OPEN--num-sess--SESSIONS--'     |
      '-NONRECOVERABLE---------------------------------------------------'
 
>-----+---------------+---+--------------------+---------------->
      '-HOLD QUIESCE--'   '-WITHOUT PROMPTING--'
 
>-----+---------------------------+---+---------------------+--->
      '-DATA BUFFER--buffer-size--'   '-CPU_PARALLELISM--n--'
 
>-----+----------------------+---------------------------------->
      '-DISK_PARALLELISM--n--'
 
>-----+---------------------------------+----------------------><
      '-INDEXING MODE--+-AUTOSELECT--+--'
                       +-REBUILD-----+
                       +-INCREMENTAL-+
                       '-DEFERRED----'
 
datalink-spec
 
    .-,--------------------------------------------------------------------------------------------------.
    V                                                                                                    |
|------(--+-----------------+--+----------------------------------+---+--------------------------+---)---+->
          '-DL_LINKTYPE URL-'  +-DL_URL_REPLACE_PREFIX--"prefix"--+   '-DL_URL_SUFFIX--"suffix"--'
                               '-DL_URL_DEFAULT_PREFIX--"prefix"--'
 
>---------------------------------------------------------------|

Command Parameters

COPY NO

Specifies that the table space in which the table resides will be placed in backup pending state if forward recovery is enabled (that is, logretain or userexit is on). The data will not be accessible until a table space backup or a full database backup is made.

COPY YES

Specifies that a copy of the loaded data will be saved. This option is invalid if forward recovery is disabled (both logretain and userexit are off). The option is not supported for tables with DATALINK columns.

USE ADSM: Specifies that the copy will be stored using ADSTAR Distributed Storage Manager (ADSM).
OPEN num-sess SESSIONS: The number of I/O sessions to be used with ADSM or the vendor product. The default value is 1.
TO device/directory: Specifies the device or directory on which the copy image will be created. Tape is not supported on OS/2; copy to tapes is not supported for DB2 servers running on SCO UnixWare 7.
LOAD lib-name: The name of the shared library (DLL on OS/2 or the Windows operating system) containing the vendor backup and restore I/O functions to be used. It may contain the full path. If the full path is not given, it will default to the path where the user exit programs reside.

CPU_PARALLELISM n

Specifies the number of processes or threads that the load utility will spawn for parsing, converting, and formatting records when building table objects. This parameter is designed to exploit intra-partition parallelism. It is particularly useful when loading presorted data, because record order in the source data is preserved. If the value of this parameter is zero, or has not been specified, the load utility uses an intelligent default value at run time.

Notes:

If this parameter is used with tables containing either LOB or LONG VARCHAR fields, its value becomes one, regardless of the number of system CPUs or the value specified by the user.
Specifying a small value for the SAVECOUNT parameter causes the loader to perform many more I/O operations to flush both data and table metadata. When CPU_PARALLELISM is greater than one, the flushing operations are asynchronous, permitting the loader to exploit the CPU. When CPU_PARALLELISM is set to one, the loader waits on I/O during consistency points. A load operation with CPU_PARALLELISM set to two, and SAVECOUNT set to 10 000, completes faster than the same operation with CPU_PARALLELISM set to one, even though there is only one CPU.

DATA BUFFER buffer-size

Specifies the number of 4KB pages (regardless of the degree of parallelism) to use as buffered space for transferring data within the utility. If the value specified is less than the algorithmic minimum, the minimum required resource is used, and no warning is returned.

This memory is allocated directly from the utility heap, whose size can be modified through the util_heap_sz database configuration parameter.

If a value is not specified, an intelligent default is calculated by the utility at run time. The default is based on a percentage of the free space available in the utility heap at the instantiation time of the loader, as well as some characteristics of the table.

DATALINK SPECIFICATION

For each DATALINK column, there can be one column specification enclosed by parentheses. Each column specification consists of one or more DL_LINKTYPE, prefix, and a DL_URL_SUFFIX specification. The prefix specification can be either DL_URL_REPLACE_PREFIX or DL_URL_DEFAULT_PREFIX.

There can be as many DATALINK column specifications as the number of DATALINK columns defined in the table. The order of specifications follows the order of DATALINK columns found within the insert-column list, or within the table definition (if an insert-column list is not specified).

DISK_PARALLELISM n

Specifies the number of processes or threads that the load utility will spawn for writing data to the table space containers. If a value is not specified, the utility selects an intelligent default based on the number of table space containers and the characteristics of the table.

DL_LINKTYPE

If specified, it should match the LINKTYPE of the column definition. Thus, DL_LINKTYPE URL is acceptable if the column definition specifies LINKTYPE URL.

DL_URL_DEFAULT_PREFIX "prefix"

If specified, it should act as the default prefix for all DATALINK values within the same column. In this context, prefix refers to the "scheme host port" part of the URL specification.

Examples of prefix are:

   "http://server"
   "file://server"
   "file:"
   "http://server:80"

If no prefix is found in the column data, and a default prefix is specified with DL_URL_DEFAULT_PREFIX, the default prefix is prefixed to the column value (if not NULL).

For example, if DL_URL_DEFAULT_PREFIX specifies the default prefix "http://toronto":

The column input value "/x/y/z" is stored as "http://toronto/x/y/z".
The column input value "http://coyote/a/b/c" is stored as "http://coyote/a/b/c".
The column input value NULL is stored as NULL.

DL_URL_REPLACE_PREFIX "prefix"

This clause is useful when loading or importing data previously generated by the export utility, if the user wants to globally replace the host name in the data with another host name. If specified, it becomes the prefix for all non-NULL column values. If a column value has a prefix, this will replace it. If a column value has no prefix, the prefix specified by DL_URL_REPLACE_PREFIX is prefixed to the column value.

For example, if DL_URL_REPLACE_PREFIX specifies the prefix "http://toronto":

The column input value "/x/y/z" is stored as "http://toronto/x/y/z".
The column input value "http://coyote/a/b/c" is stored as "http://toronto/a/b/c". Note that "toronto" replaces "coyote".
The column input value NULL is stored as NULL.

DL_URL_SUFFIX "suffix"

If specified, it is appended to every non-NULL column value for the column. It is, in fact, appended to the "path" component of the data location part of the DATALINK value.

FOR EXCEPTION table-name

Specifies the exception table into which rows in error will be copied. Any row that is in violation of a unique index or a primary key index is copied. DATALINK exceptions are also captured in the exception table.

Information that is written to the exception table is not written to the dump file (for a description of the dumpfile modifier, see Table 8). In a partitioned database environment, an exception table must be defined for those nodes on which the loading table is defined. The dump file, on the other hand, contains rows that cannot be loaded because they are invalid or have syntax errors. For more information, see Exception Table.

FROM filename/pipename/device

Specifies the file, pipe, or device that contains the data being loaded. This file, pipe, or device must reside on the node where the database resides. If several names are specified, they will be processed in sequence. If the last item specified is a tape device, the user is prompted for another tape. Valid response options are:

c: Continue. Continue using the device that generated the warning message (for example, when a new tape has been mounted).
d: Device terminate. Stop using the device that generated the warning message (for example, when there are no more tapes).
t: Terminate. Terminate all devices.

Notes:

Tape is not supported on OS/2.
It is recommended that the fully qualified file name be used. If the server is remote, the fully qualified file name must be used. If the database resides on the same node as the caller, relative paths may be used.
Loading data from multiple IXF files is supported if the files are physically separate, but logically one file. It is not supported if the files are both logically and physically separate.
If, when specifying pipename on OS/2, less than the expected amount of data is loaded, clean up system resources (IPL is recommended), and reissue the LOAD command.

HOLD QUIESCE

Specifies that the utility should leave the table in quiesced exclusive state after the load operation. To unquiesce the table spaces, issue:

   db2 quiesce tablespaces for table <tablename> reset

Note: Ensure that no phantom quiesces are created (see the Command Reference).

INDEXING MODE

Specifies whether the load utility is to rebuild indexes or to extend them incrementally. Valid values are:

AUTOSELECT

The load utility will automatically decide between REBUILD or INCREMENTAL mode.

REBUILD

All indexes will be rebuilt. The utility must have sufficient resources to sort all index key parts for both old and appended table data.

INCREMENTAL

Indexes will be extended with new data. This approach consumes index free space. It only requires enough sort space to append index keys for the inserted records. This method is only supported in cases where the index object is valid and accessible at the start of a load operation (it is, for example, not valid immediately following a load operation in which the DEFERRED mode was specified). If this mode is specified, but not supported due to the state of the index, a warning is returned, and the load operation continues in REBUILD mode. Similarly, if a load restart operation is begun in the load build phase, INCREMENTAL mode is not supported.

Incremental indexing is not supported when all of the following conditions is true:

The LOAD COPY option is specified (logretain or userexit is enabled).
The table resides in a DMS table space.
The index object resides in a table space that is shared by other table objects belonging to the table being loaded.

To bypass this restriction, it is recommended that indexes be placed in a separate table space.

DEFERRED

The load utility will not attempt index creation if this mode is specified. Indexes will be marked as needing a refresh. The first access to such indexes that is unrelated to a load operation may force a rebuild (for more information, see the Administration Guide), or indexes may be rebuilt when the database is restarted. This approach requires enough sort space for all key parts for the largest index. The total time subsequently taken for index construction is longer than that required in REBUILD mode. Therefore, when performing multiple load operations with deferred indexing, it is advisable (from a performance viewpoint) to let the last load operation in the sequence perform an index rebuild, rather than allow indexes to be rebuilt at first non-load access.

Deferred indexing is only supported for tables with non-unique indexes, so that duplicate keys inserted during the load phase are not persistent after the load operation.

INSERT

One of four modes under which the load utility can execute. Adds the loaded data to the table without changing the existing table data.

insert-column

Specifies the table column into which the data is to be inserted.

The load utility cannot parse columns whose names contain one or more spaces. For example,

   db2 load from delfile1 of del modified by noeofchar noheader 
      method P (1, 2, 3, 4, 5, 6, 7, 8, 9)
      insert into table1 (BLOB1, S2, I3, Int 4, I5, I6, DT7, I8, TM9)

will fail because of the Int 4 column. The solution is to enclose such column names with double quotation marks:

   db2 load from delfile1 of del modified by noeofchar noheader 
      method P (1, 2, 3, 4, 5, 6, 7, 8, 9)
      insert into table1 (BLOB1, S2, I3, "Int 4", I5, I6, DT7, I8, TM9)

INTO table-name

Specifies the database table into which the data is to be loaded. This table cannot be a system table. An alias, or the fully qualified or unqualified table name can be specified. A qualified table name is in the form schema.tablename. If an unqualified table name is specified, the table will be qualified with the current authorization ID.

LOBS FROM lob-path

The path to the data files containing LOB values to be loaded. The path must end with a slash (/). The names of the LOB data files are stored in the main data file (ASC, DEL, or IXF), in the column that will be loaded into the LOB column. This option is ignored if lobsinfile is not specified within the filetype-mod string (see Table 8).

MESSAGES message-file

Specifies the destination for warning and error messages that occur during the load operation. If a message file is not specified, messages are written to standard output. If the complete path to the file is not specified, the load utility uses the current directory and the default drive as the destination. If the name of a file that already exists is specified, the utility appends the information.

METHOD

L

Specifies the start and end column numbers from which to load data.

Note: This method can only be used with ASC files, and is the only valid option for that file type.

N

Specifies the names of the columns in the data file to be loaded. The case of these column names must match the case of the corresponding names in the system catalogs. Each column in the table that is not nullable should be included in this list. Specify only complete subsets of column names (for example, given file columns F1, F2, F3, F4, F5, and F6, and table columns C1 INT, C2 INT NOT NULL, C3 INT NOT NULL, and C4 INT, method N (F1,F2,F3,F4) insert into table_name (C1,C2,C3,C4) is a valid request, while method N (F1,F4) is not valid, since there will be no data to put into C3.

Note:

This method can only be used with IXF files.

P

Specifies the numbers of the columns to be loaded. Each column in the table that is not nullable should be included in this list. Specify only complete subsets of column numbers (for example, given file columns F1, F2, F3, F4, F5, and F6, and table columns C1 INT, C2 INT NOT NULL, C3 INT NOT NULL, and C4 INT, method P (1,2,3,4) is a valid request, while method P (1,4) is not valid.

Note:

This method can only be used with IXF or DEL files, and is the only valid option for the DEL file type.

MODIFIED BY filetype-mod

Specifies additional options (see Table 8).

NONRECOVERABLE

Specifies that the load transaction is to be marked as non-recoverable, and that it will not be possible to recover it by a subsequent roll forward action. The rollforward utility will skip the transaction, and will mark the table into which data was being loaded as "invalid". The utility will also ignore any subsequent transactions against that table. After the roll forward is completed, such a table can only be dropped.

With this option, table spaces are not put in backup pending state following the load operation, and a copy of the loaded data does not have to be made during the load operation.

This option should not be used when DATALINK columns with the FILE LINK CONTROL attribute are present in, or being added to, the table.

NULL INDICATORS n

Specifies a column (by number) to be used as a NULL indicator field. If this option is used, a NULL indicator column for each data column must also be specified. A value of zero indicates that the data column is not nullable, and that there will always be data in that column.

A value of Y in the NULL indicator column specifies that the column data is NULL. Any character other than Y in the NULL indicator column specifies that the column data is not NULL, and that column data specified by the METHOD L option will be loaded.

The NULL indicator character can be changed using the MODIFIED BY option (see the description of the nullindchar modifier in Table 8).

OF filetype

Specifies the format of the data in the input file:

ASC (non-delimited ASCII format)
DEL (delimited ASCII format)
IXF (integrated exchange format, PC version), exported from the same or from another DB2 table.

For more information about file formats, see Appendix C. Export/Import/Load Utility File Formats.

REPLACE

One of four modes under which the load utility can execute. Deletes all existing data from the table, and inserts the loaded data. The table definition and index definitions are not changed. If this option is used when moving data between hierarchies, only the data for an entire hierarchy, not individual subtables, can be replaced.

This option is not supported for tables with DATALINK columns.

RESTART

One of four modes under which the load utility can execute. Restarts a previously interrupted load operation. The load operation will automatically continue from the last consistency point in the load, build, or delete phase.

RESTARTCOUNT

Reserved.

ROWCOUNT n

Specifies the number of n physical records in the file to be loaded. Allows a user to load only the first n rows in a file.

SAVECOUNT n

Specifies that the load utility is to establish consistency points after every n rows. This value is converted to a page count, and rounded up to intervals of the extent size. Since a message is issued at each consistency point, this option should be selected if the load operation will be monitored using LOAD QUERY Command. If the value of n is not sufficiently high, the synchronization of activities performed at each consistency point will impact performance.

The default value is zero, meaning that no consistency points will be established, unless necessary.

SORT BUFFER buffer-size

Reserved.

STATISTICS NO

Specifies that no statistics are to be collected, and that the statistics in the catalogs are not to be altered. This is the default.

STATISTICS YES

Specifies that statistics are to be collected for the table and for any existing indexes. This option is supported only if the load operation is in REPLACE mode.

WITH DISTRIBUTION: Specifies that distribution statistics are to be collected.
AND INDEXES ALL: Specifies that both table and index statistics are to be collected.
FOR INDEXES ALL: Specifies that only index statistics are to be collected.
DETAILED: Specifies that extended index statistics are to be collected.

TEMPFILES PATH temp-pathname

Specifies the name of the path to be used when creating temporary files during a load operation, and should be fully qualified according to the server node.

Temporary files take up file system space. Sometimes, this space requirement is quite substantial. Following is an estimate of how much file system space should be allocated for all temporary files:

4 bytes for each duplicate or rejected row containing DATALINK values
136 bytes for each message that the load utility generates
15KB overhead if the data file contains long field data or LOBs. This quantity can grow significantly if the INSERT option is specified, and there is a large amount of long field or LOB data already in the table.

For more information about temporary files, see Load Temporary Files.

TERMINATE

One of four modes under which the load utility can execute. Terminates a previously interrupted load operation, and rolls back the operation to the point in time at which it started, even if consistency points were passed. The states of any table spaces involved in the operation return to normal, and all table objects are made consistent (index objects may be marked as invalid, in which case index rebuild will automatically take place at next access). If the load operation being terminated is a load REPLACE, the table will be truncated to an empty table after the load TERMINATE operation. If the load operation being terminated is a load INSERT, the table will retain all of its original records after the load TERMINATE operation.

If the table spaces in which the table resides are not in load pending state, this option does not affect the state of the table spaces.

The load terminate option will not remove a backup pending state from table spaces.

USING directory

Reserved.

WARNINGCOUNT n

Stops the load operation after n warnings. Set this parameter if no warnings are expected, but verification that the correct file and table are being used is desired. If n is zero, or this option is not specified, the load operation will continue regardless of the number of warnings issued. If the load operation is stopped because the threshold of warnings was encountered, another load operation can be started in RESTART mode. The load operation will automatically continue from the last consistency point. Alternatively, another load operation can be initiated in REPLACE mode, starting at the beginning of the input file.

WITHOUT PROMPTING

Specifies that the list of data files contains all the files that are to be loaded, and that the devices or directories listed are sufficient for the entire load operation. If a continuation input file is not found, or the copy targets are filled before the load operation finishes, the load operation will fail, and the table will remain in load pending state.

If this option is not specified, and the tape device encounters an end of tape for the copy image, or the last item listed is a tape device, the user is prompted for a new tape on that device. Tape is not supported on OS/2.

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]