DB2 Version 10.1 for Linux, UNIX, and Windows

Loading data

The load utility efficiently moves large quantities of data into newly created tables, or into tables that already contain data.

Before you begin

Before invoking the load utility, you must be connected to (or be able to implicitly connect to) the database into which you want to load the data.

Since the utility issues a COMMIT statement, complete all transactions and release all locks by issuing either a COMMIT or a ROLLBACK statement before invoking the load utility.

Data is loaded in the sequence that appears in the input file, except when using multidimensional clustering (MDC) tables, partitioned tables, or the anyorder file type modifier. If you want a particular sequence, sort the data before attempting a load operation. If clustering is required, the data should be sorted on the clustering index before loading. When loading data into multidimensional clustered tables (MDC), sorting is not required before the load operation, and data is clustered according to the MDC table definition. When loading data into partitioned tables, sorting is not required before the load operation, and data is partitioned according to the table definition.

Restrictions

These are some of the restrictions that apply to the load utility:

Loading data into nicknames is not supported.
Loading data into typed tables, or tables with structured type columns, is not supported.
Loading data into declared temporary tables and created temporary tables is not supported.
XML data can only be read from the server side; if you want to have the XML files read from the client, use the import utility.
You cannot create or drop tables in a table space that is in Backup Pending state.
You cannot load data into a database accessed through DB2 Connect™ or a server level before DB2® Version 2. Options that are only available with the current cannot be used with a server from the previous release.
If an error occurs during a LOAD REPLACE operation, the original data in the table is lost. Retain a copy of the input data to allow the load operation to be restarted.
Triggers are not activated on newly loaded rows. Business rules associated with triggers are not enforced by the load utility.
Loading encrypted data is not supported.

These are some of the restrictions that apply to the load utility when loading into a partitioned table:

Consistency points are not supported when the number of partitioning agents is greater than one.
Loading data into a subset of data partitions while keeping the remaining data partitions fully online is not supported.
The exception table used by a load operation or a set integrity pending operation cannot be partitioned.
A unique index cannot be rebuilt when the load utility is running in insert mode or restart mode, and the load target table has any detached dependents.

Procedure

To invoke the load utility:

Issue a LOAD command in the command line processor (CLP).
Call the db2Load application programming interface (API) from a client application.
Open the task assistant in IBM® Data Studio for the LOAD command.

Example

The following is an example of a LOAD command issued through the CLP:

   db2 load from stafftab.ixf of ixf messages staff.msgs
      insert into userid.staff copy yes use tsm data buffer 4000

In this example:

Any warning or error messages are placed in the staff.msgs file.
A copy of the changes made is stored in Tivoli® Storage Manager (TSM).
4000 pages of buffer space are to be used during the load operation.

The following is another example of a LOAD command issued through the CLP:

   db2 load from stafftab.ixf of ixf messages staff.msgs
      tempfiles path /u/myuser replace into staff

In this example:

The table data is being replaced.
The TEMPFILES PATH parameter is used to specify /u/myuser as the server path into which temporary files are written.

Note: These examples use relative path names for the load input file. Relative path names are only allowed on calls from a client on the same database partition as the database. The use of fully qualified path names is recommended.

What to do next

After you invoke the load utility, you can use the LIST UTILITIES command to monitor the progress of the load operation. If a load operation is performed in either INSERT mode, REPLACE mode, or RESTART mode, detailed progress monitoring support is available. Issue the LIST UTILITIES command with the SHOW DETAILS parameter to view detailed information about the current load phase. Details are not available for a load operation performed in TERMINATE mode. The LIST UTILITIES command simply shows that a load terminate utility is currently running.

A load operation maintains unique constraints, range constraints for partitioned tables, generated columns, and LBAC security rules. For all other constraints, the table is placed in the Set Integrity Pending state at the beginning of a load operation. After the load operation is complete, the SET INTEGRITY statement must be used to take the table out of Set Integrity Pending state.