Phases of a load operation

A complete load operation consists of five distinct phases; they are as follows:

Analyze phase

Load phase

Build phase

Delete phase

Index copy phase

Analyze phase: This phase of the Load process is only utilized when a column-organized table is being loaded and the column compression dictionaries need to be built. This happens during a LOAD REPLACE operation, a LOAD REPLACE RESETDICTIONARY operation, a LOAD REPLACE RESETDICTIONARYONLY operation, or a LOAD INSERT operation (if the column-organized table is empty). For column-organized tables, this phase is followed by the load, build, and delete phases. The following diagram showcases the steps involved in the analyze phase. The analyze phase is invoked if you are loading data for the first time into a column-organized BLU acceleration table using LOAD INSERT, or running a LOAD REPLACE with RESETDICTIONARY or RESETDICTIONARYONLY clause.

Load phase: During this phase, data is loaded into the table, and index keys and table statistics are collected (if necessary). The save points, also called points of consistency, are established at intervals specified through the SAVECOUNT parameter in the LOAD command. The messages are generated, indicating how many input rows were successfully loaded at the time of the save point. You can use the LOAD QUERY TABLE command to monitor the progress of the load operation.

If you are loading data into a column-organized BLU Accelerated table, the load phase does the following:

 Compresses values

Builds data pages

Updates synopsis table

Builds keys for page map indexes and any UNIQUE indexes

Build phase: During this phase, indexes are created based on the index keys collected during the load phase. The index keys are sorted during the load phase, and index statistics are collected if the STATISTICS USE PROFILE option was specified, and profile indicates collecting index statistics.

Delete phase: During this phase, the rows that caused a unique or primary key violation are removed from the table and inserted into an exception table if specified.

Index copy phase: During this phase, the index data is copied from a system temporary table space to the original table space where the index data associated with the table that was loaded is to reside. This will only occur if a system temporary table space was specified for index creation during a load operation with the ALLOW READ ACCESS and USE [TablespaceName] options specified. This phase is applicable only to row-organized tables.

Monitoring a Load operation::

Like other DB2 database utilities, the Load utility can be monitored using two commands:

 LIST UTILITIES SHOW DETAIL

              LOAD QUERY TABLE <TableName>