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 before 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 before 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 since Version 9.7. You should convert them to type-2 indexes before upgrading to Version 10. Otherwise, the type-1 indexes are automatically rebuilt as type-2 indexes the first time a table is accessed.

For details on how to convert type-1 indexes before upgrading databases, 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.

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

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

Tablestate:
	Load in Progress
	Read Access Only