DB2 Version 9.7 for Linux, UNIX, and Windows

Table states during and after load operations

The load utility uses table states to preserve database consistency during a load operation. These states work by controlling access to data or eliciting user actions.

To determine the state of a table, issue the LOAD QUERY command, which also checks the status of a load operation. Tables can be in a number of states simultaneously. The states returned by LOAD QUERY are as follows:

Normal State
The Normal state is the initial state of a table after it is created, indicating that no (abnormal) states currently affect the table.

Read Access Only
If you specify the ALLOW READ ACCESS option, the table is in the Read Access Only state. The data in the table that existed prior to the invocation of the load command is available in read-only mode during the load operation. If you specify the ALLOW READ ACCESS option and the load operation fails, the data that existed in the table prior to the load operation continues to be available in read-only mode after the failure.

Load in Progress
The Load in Progress table state indicates that there is a load in progress on the table. The load utility removes this transient state after the load is successfully completed. However, if the load operation fails or is interrupted, the table state will change to Load Pending.

Redistribute in Progress
The Redistribute in Progress table state indicates that there is a redistribute in progress on the table. The redistribute utility removes this transient state after it has successfully completed processing the table. However, if the redistribute operation fails or is interrupted, the table state will change to Redistribute Pending.

Load Pending
The Load Pending table state indicates that a load operation failed or was interrupted. You can take one of the following steps to remove the Load Pending state:

Address the cause of the failure. For example, if the load utility ran out of disk space, add containers to the table space. Then, restart the load operation.
Terminate the load operation.
Run a load REPLACE operation against the same table on which the load operation failed.
Recover table spaces for the loading table by using the RESTORE DATABASE command with the most recent table space or database backup, then carry out further recovery actions.

Redistribute Pending
The Redistribute Pending table state indicates that a redistribute operation failed or was interrupted. You can perform a REDISTRIBUTE CONTINUE or REDISTRIBUTE ABORT operation to remove the Redistribute Pending state.

Not Load Restartable
In the Not Load Restartable state, a table is partially loaded and does not allow a load restart operation. There are two situations in which a table is placed in the Not Load Restartable state:

If you perform a rollforward operation after a failed load operation that you could not successfully restart or terminate
If you perform a restore operation from an online backup that you took while the table was in the Load in Progress or Load Pending state

The table is also in the Load Pending state. To remove the table from the Not Load Restartable state, issue the LOAD TERMINATE or the LOAD REPLACE command.

Set Integrity Pending
The Set Integrity Pending state indicates that the loaded table has constraints which have not yet been verified. The load utility places a table in this state when it begins a load operation on a table with constraints. Use the SET INTEGRITY statement to take the table out of Set Integrity Pending state.

Type-1 Indexes
The Type-1 Indexes state indicates that the table currently uses type-1 indexes. Type-1 indexes are no longer supported and must be converted to type-2 indexes. You can convert indexes to type-2 indexes using the CONVERT option of the REORG INDEXES/TABLE command or using the output of the db2IdentifyType1 command. The db2IdentifyType1 command generates the appropriate commands for the conversion of any type-1 indexes found in tables or schemas for a specified database. For more information, see the "Converting type-1 indexes to type-2 indexes" topic.

Unavailable
Rolling forward through an unrecoverable load operation places a table in the Unavailable state. In this state, the table is unavailable; you must drop it or restore it from a backup.

Example of a table in multiple states

If you load an input file (staffdata.del) with a substantial amount of data into a table NEWSTAFF, as follows:

connect to sample;
create table newstaff like staff;
load from staffdata.del of del insert into newstaff allow read access;
connect reset;

and you open another session and issue the following commands,

connect to sample;
load query table newstaff;
connect reset;

the LOAD QUERY command reveals that the NEWSTAFF table is in the Read Access Only and Load in Progress table states:

Tablestate:
	Load in Progress
	Read Access Only