LOAD

Use the LOAD online utility to load one or more tables of a table space. The LOAD utility loads records into the tables and builds or extends any indexes that are 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.

The loaded data is processed by any edit or validation routine that is associated with the table, and any field procedure that is associated with any column of the table. The LOAD utility ignores and does not enforce informational referential constraints.

Start of changeTo avoid the cost of running the RUNSTATS utility afterward, you can also specify the STATISTICS option collect inline statistics when you run the LOAD utility.End of change

Output

LOAD DATA generates one or more of the following forms of output:

  • A loaded table space or partition.
  • A discard file of rejected records.
  • A summary report of errors that were encountered during processing; this report is generated only if you specify ENFORCE CONSTRAINTS or if the LOAD utility involves unique indexes.

Authorization required

To execute this utility, you must use a privilege set that includes one of the following authorizations:

  • Ownership of the table
  • LOAD privilege for the database
  • STATS privilege for the database is required if STATISTICS keyword is specified
  • Start of changeDBADM or DBCTRL authority for the database. If the database is implicitly created, these privileges must be on the implicitly created database or on DSNDB04.End of change
  • Start of changeDATAACCESS authorityEnd of change
  • SYSCTRL or SYSADM authority

The LOAD utility operates on a table space level, so you must have authority for all tables in the table space when you perform LOAD.

To run LOAD STATISTICS, the privilege set must include STATS authority on the database. To run LOAD STATISTICS REPORT YES, the privilege set must also include the SELECT privilege on the tables required.

Start of changeIf either the FLASHCOPY YES or FLASHCOPY CONSISTENT option is specified, the user ID that invokes the LOAD utility must have the authority to execute the DFSMSdss COPY command.End of change

If you use RACF® access control with multilevel security and the LOAD utility is to process a table space that contains a table that has multilevel security with row-level granularity, you must be identified to RACF and have an accessible valid security label. You must also meet the following authorization requirements:

  • To replace an entire table space with LOAD REPLACE, you must have the write-down privilege unless write-down rules are not in effect.
  • You must have the write-down privilege to specify values for the security label columns, unless write-down rules are not in effect. If these rules are in effect and you do not have write-down privilege, DB2® assigns your security label as the value for the security label column for the rows that you are loading.

Execution phases of LOAD

The LOAD utility operates in the following phases:

UTILINIT
Performs initialization.
RELOAD
Loads record types and writes temporary file records for indexes and foreign keys. RELOAD makes one pass through the sequential input data set. Check constraints are checked for each row. Internal commits provide commit points at which to restart in case operation should halt in this phase.

RELOAD creates inline copies if you specified the COPYDDN or RECOVERYDDN keywords.

A subtask is started at the beginning of the RELOAD phase to sort the keys. The sort subtask initializes and waits for the main RELOAD phase to pass its keys to SORT. RELOAD loads the data, extracts the keys, and passes them in memory for sorting. At the end of the RELOAD phase, the last key is passed to SORT, and record sorting completes.

Note that load partition parallelism starts subtasks. PREFORMAT for table spaces occurs at the end of the RELOAD phase.

SORT
Sorts temporary file records before creating indexes or validating referential constraints, if indexes or foreign keys exist. The SORT phase is skipped if all the following conditions apply for the data that is processed during the RELOAD phase:
  • Each table has no more than one key.
  • All keys are the same type (index key only, indexed foreign key, or foreign key only).
  • The data that is being loaded or reloaded is in key order (if a key exists). If the key is an index key only and the index is a data-partitioned secondary index, the data is considered to be in order if the data is grouped by partition and ordered within partition by key value. If the key in question is an indexed foreign key and the index is a data-partitioned secondary index, the data is never considered to be in order.
  • The data that is being loaded or reloaded is grouped by table, and each input record is loaded into one table only.

SORT passes the sorted keys in memory to the BUILD phase, which builds the indexes.

BUILD
Creates indexes from temporary file records for all indexes that are defined on the loaded tables. Build also detects duplicate keys. PREFORMAT for indexes occurs at the end of the BUILD phase.
SORTBLD
Performs all activities that normally occur in both the SORT and BUILD phases, if you specify a parallel index build.
INDEXVAL
Corrects unique index violations or index evaluation errors from the information in SYSERR, if any exist.
ENFORCE
Checks referential constraints, except informational referential constraints, and corrects violations. Information about violations of referential constraints is stored in SYSERR.
DISCARD
Copies records that cause errors from the input data set to the discard data set.
REPORT
Generates a summary report, if you specified ENFORCE CONSTRAINT or if load index validation is performed. The report is sent to SYSPRINT.
Start of changeLOGAPPLYEnd of change
Start of changeIf LOAD SHRLEVEL CHANGE FLASHCOPY CONSISTENT is specified, log apply applies the updates to the FlashCopy® image copy to ensure that all activity is reflected up to the point of consistency.End of change
Start of changeLOGCSREnd of change
Start of changeIf LOAD SHRLEVEL CHANGE FLASHCOPY CONSISTENT is specified, the LOGCSR phase identifies any uncommitted work to back out from the FlashCopy image copy.End of change
Start of changeLOGUNDOEnd of change
Start of changeIf LOAD SHRLEVEL CHANGE FLASHCOPY CONSISTENT is specified, the LOGUNDO phase backs out uncommitted work from the FlashCopy image copy. End of change
UTILTERM
Performs cleanup.