How the LOAD utility loads DB2 tables

Use the LOAD utility to load one or more persistent tables of a table space, or one or more partitions of a table space. The LOAD utility operates on a table space, so you must have authority for all tables in the table space when you run LOAD.

The LOAD utility loads records into the tables and builds or extends any indexes defined on them. If the table space already contains data, you can choose whether you want to add the new data to the existing data or replace the existing data.

Additionally, you can use the LOAD utility to do the following:
  • Compress data and build a compression dictionary
  • Convert data between compatible data types and between encoding schemes
  • Load multiple tables in a single table space

Delimited input and output files

The LOAD and UNLOAD utilities can accept or produce a delimited file, which is a sequential BSAM file with row delimiters and column delimiters. You can unload data from other systems into one or more files that use a delimited file format and then use these delimited files as input for the LOAD utility. You can also unload DB2® data into delimited files by using the UNLOAD utility and then use these files as input into another DB2 database.

INCURSOR option

The INCURSOR option of the LOAD utility specifies a cursor for the input data set. Use the EXEC SQL utility control statement to declare the cursor before running the LOAD utility. You define the cursor so that it selects data from another DB2 table. The column names in the SELECT statement must be identical to the column names of the table that is being loaded. The INCURSOR option uses the DB2 cross-loader function.

CCSID option

You can load input data into ASCII, EBCDIC, or Unicode tables. The ASCII, EBCDIC, and UNICODE options on the LOAD utility statement let you specify whether the format of the data in the input file is ASCII, EBCDIC, or Unicode. The CCSID option of the LOAD utility statement lets you specify the CCSIDs of the data in the input file. If the CCSID of the input data does not match the CCSID of the table space, the input fields are converted to the CCSID of the table space before they are loaded.

Availability during load

For nonpartitioned table spaces, data for other tables in the table space that is not part of the table that is being loaded is unavailable to other application programs during the load operation with the exception of LOAD SHRLEVEL CHANGE. For partitioned table spaces, data that is in the table space that is being loaded is also unavailable to other application programs during the load operation with the exception of LOAD SHRLEVEL CHANGE. In addition, some SQL statements, such as CREATE, DROP, and ALTER, might experience contention when they run against another table space in the same DB2 database while the table is being loaded.

Default values for columns

When you load a table and do not supply a value for one or more of the columns, the action DB2 takes depends on the circumstances.

  • If the column is not a ROWID or identity column, DB2 loads the default value of the column, which is specified by the DEFAULT clause of the CREATE or ALTER TABLE statement.
  • If the column is a ROWID column that uses the GENERATED BY DEFAULT option, DB2 generates a unique value.
  • If the column is an identity column that uses the GENERATED BY DEFAULT option, DB2 provides a specified value.
  • With XML columns, if there is an implicitly created DOCID column in the table, it is created with the GENERATED ALWAYS attribute.

For ROWID or identity columns that use the GENERATED ALWAYS option, you cannot supply a value because this option means that DB2 always provides a value.

XML columns

You can load XML documents from input records if the total input record length is less than 32 KB. For input record length greater than 32 KB, you must load the data from a separate file. (You can also use a separate file if the input record length is less than 32 KB.)

When the XML data is to be loaded from the input record, specify XML as the input field type. The target column must be an XML column. The LOAD utility treats XML columns as varying-length data when loading XML directly from input records and expects a two-byte length field preceding the actual XML value.

The XML tables are loaded when the base table is loaded. You cannot specify the name of the auxiliary XML table to load.

XML documents must be well formed in order to be loaded.

LOB columns

The LOAD utility treats LOB columns as varying-length data. The length value for a LOB column must be 4 bytes. The LOAD utility can be used to load LOB data if the length of the row, including the length of the LOB data, does not exceed 32 KB. The auxiliary tables are loaded when the base table is loaded. You cannot specify the name of the auxiliary table to load.

Replacement or addition of data

You can use LOAD REPLACE to replace data in a single-table table space or in a multiple-table table space. You can replace all the data in a table space (using the REPLACE option), or you can load new records into a table space without destroying the rows that are already there (using the RESUME option).