DB2 Version 9.7 for Linux, UNIX, and Windows

Table space states during and after load operations

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

The load utility does not quiesce (put persistent locks on) the table spaces involved in the load operation and uses table space states only for load operations for which you specify the COPY NO parameter.

You can check table space states by using the LIST TABLESPACES command. Table spaces can be in multiple states simultaneously. The states returned by LIST TABLESPACES are as follows:

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

Load in Progress
The Load in Progress state indicates that there is a load in progress on the table space. This state prevents the backup of dependent tables during the load. The table space state is distinct from the Load in Progress table state (which is used in all load operations) because the load utility places table spaces in the Load in Progress state only when you specify the COPY NO parameter for a recoverable database. The table spaces remain in this state for the duration of the load operation.

Backup Pending
If you perform a load operation for a recoverable database and specify the COPY NO parameter, table spaces are placed in the Backup Pending table space state after the first commit. You cannot update a table space in the Backup Pending state. You can remove the table space from the Backup Pending state only by backing up the table space. Even if you cancel the load operation, the table space remains in the Backup Pending state because the table space state is changed at the beginning of the load operation and cannot be rolled back.

Restore Pending
If you perform a successful load operation with the COPY NO option, restore the database, and then rollforward through that operation, the associated table spaces are placed in the Restore Pending state. To remove the table spaces from the Restore Pending state, you must perform a restore operation.

Note: DB2® LOAD does not set the table space state to Load Pending or Delete Pending.

Example of a table space state

If you load an input file (staffdata.del) into a table NEWSTAFF, as follows:

update db cfg for sample using logretain recovery;
backup db sample;
connect to sample;
create table newstaff like staff;
load from staffdata.del of del insert into newstaff copy no;
connect reset;

and you open another session and issue the following commands,

connect to sample;
list tablespaces;
connect reset;

USERSPACE1 (the default table space for the sample database) is in the Load in Progress state and, after the first commit, the Backup Pending state as well. After the load operation finishes, the LIST TABLESPACES command reveals that USERSPACE1 is now in the Backup Pending state:

Tablespace ID                        = 2
Name                                 = USERSPACE1
Type                                 = Database managed space
Contents                             = All permanent data. Large table space.  
State                                = 0x0020    
		Detailed explanation:
		Backup pending