- CLIENT
- Specifies that the data to be loaded resides on a remotely connected
client. This option is ignored if the load operation is not being
invoked from a remote client.
Notes:
- Loading data that resides on a remotely connected client is not supported
under the following conditions:
- The database that the client is connected to is in a DB2 Enterprise -
Extended Edition environment.
- The database that the client is connected to is cataloged against an
already cataloged database.
- The DUMPFILE and LOBSINFILE modifiers (see Table 8) refer to files on the server even when the CLIENT keyword
is specified.
- Code page conversion is not performed during a remote load
operation. If the code page of the data is different from that of the
server, the data code page should be specified using the CODEPAGE modifier
(see Table 8).
- When loading remote client data from files (but not named pipes), there is
an upper limit of 2GB per file.
In the following example, a data file (/u/user/data.del)
residing on a remotely connected client is to be loaded into MYTABLE on the
server database:
db2 load client from /u/user/data.del of del
modified by codepage=850 insert into mytable
- 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 in any
table in the table space cannot be updated or deleted until a table space
backup or a full database backup is made. However, it is possible to
access the data in any table by using the SELECT statement.
- 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 TSM
- Specifies that the copy will be stored using Tivoli Storage Manager
(TSM).
- OPEN num-sess SESSIONS
- The number of I/O sessions to be used with TSM 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 (usually based on the number of CPUs available) 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. (For Distributed File System,
or DFS, prefix refers to the "scheme cellname filespace-junction" part of the
URL specification.)
Examples of prefix are:
"http://server"
"file://server"
"file:"
"http://server:80"
"dfs://.../cellname/fs"
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 Distributed File System (DFS), prefix refers to the "scheme
cellname filespace-junction" part of the URL specification.
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. If an unqualified table name is specified, the table will be
qualified with the CURRENT SCHEMA.
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, unless the CLIENT option is specified. 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.
- If loading data that resides on a client machine, the data must be in the
form of either a fully qualified file or a named pipe.
- 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
are 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.
| Note: | Deferred indexing is not supported for tables that have DATALINK
columns.
|
- 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 or a declared temporary 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 SCHEMA.
- LOBS FROM lob-path
- The path to the data files containing LOB values to be loaded. The
path must end with a slash (/). If the CLIENT option is
specified, the path must be fully qualified. 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.
The message file is usually populated with messages at the end of the load
operation and, as such, is not suitable for monitoring the progress of the
operation. For real-time monitoring of a load operation, use the LOAD QUERY Command.
- METHOD
-
- L
- Specifies the start and end column numbers from which to load data.
A column number is a byte offset from the beginning of a row of data.
It is numbered starting from 1.
| 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 table column that is not nullable should
have a corresponding entry in the METHOD N list. For example, given
data fields 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 (F2, F1, F4, F3) is a
valid request, while method N (F2, F1) is not valid.
| Note: | This method can only be used with IXF files.
|
- P
- Specifies the indexes (numbered from 1) of the input data fields to be
loaded. Each table column that is not nullable should have a
corresponding entry in the METHOD P list. For example, given data
fields 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 (2, 1, 4, 3) is a valid
request, while method P (2, 1) 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 roll forward 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 operation is completed, such a table can
only be dropped or restored from a backup (full or table space) taken after a
commit point following the completion of the non-recoverable load
operation.
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 null-indicator-list
- This option can only be used when the METHOD L parameter is
specified; that is, the input file is an ASC file). The null
indicator list is a comma-separated list of positive integers specifying the
column number of each null indicator field. The column number is the
byte offset of the null indicator field from the beginning of a row of
data. There must be one entry in the null indicator list for each data
field defined in the METHOD L parameter. A column number of zero
indicates that the corresponding data field always contains data.
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.
The load terminate option will not remove a backup pending state from table
spaces.
| Note: | This option is not supported for tables with DATALINK columns.
|
- 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.