DB2 Version 9.7 for Linux, UNIX, and Windows

Table space states

This topic provides information about the supported table space states.

There are currently at least 25 table or table space states supported by the IBM® DB2® database product. These states are used to control access to data under certain circumstances, or to elicit specific user actions, when required, to protect the integrity of the database. Most of them result from events related to the operation of one of the DB2 database utilities, such as the load utility, or the backup and restore utilities. The following table describes each of the supported table space states. The table also provides you with working examples that show you exactly how to interpret and respond to states that you might encounter while administering your database. The examples are taken from command scripts that were run on AIX®; you can copy, paste and run them yourself. If you are running the DB2 database product on a system that is not UNIX, ensure that any path names are in the correct format for your system. Most of the examples are based on tables in the SAMPLE database that comes with the DB2 database product. A few examples require scenarios that are not part of the SAMPLE database, but you can use a connection to the SAMPLE database as a starting point.

Table 1. Supported table space states
State	Hexadecimal state value	Description	Examples
Backup Pending	0x20	A table space is in this state after a point-in-time table space rollforward operation, or after a load operation (against a recoverable database) that specifies the COPY NO option. The table space (or, alternatively, the entire database) must be backed up before the table space can be used. If the table space is not backed up, tables within that table space can be queried, but not updated. Note: A database must also be backed up immediately after it is enabled for rollforward recovery. A database is recoverable if the logretain database configuration parameter is set to RECOVERY, or the userexit database configuration parameter is set to YES. You cannot activate or connect to such a database until it has been backed up, at which time the value of the backup_pending informational database configuration parameter is set to NO.	1. Given load input file staff_data.del with content: 11,"Melnyk",20,"Sales",10,70000,15000: `update db cfg for sample using logretain recovery; backup db sample; connect to sample; load from staff_data.del of del messages load.msg insert into staff copy no; update staff set salary = 69000 where id = 11;` 2. `update db cfg for sample using logretain recovery; connect to sample;`
Backup in Progress	0x800	This is a transient state that is only in effect during a backup operation.	Issue an online BACKUP DATABASE command: `backup db sample online;` While the backup operation is running, execute the following script from another session: `connect to sample;` 1. `list tablespaces show detail;` or 2. `get snapshot for tablespaces on sample; connect reset;` Information returned for USERSPACE1 shows that this table space is in Backup in Progress state.
DMS Rebalance in Progress	0x10000000	This is a transient state that is only in effect during a data rebalancing operation. When new containers are added to a table space that is defined as database managed space (DMS), or existing containers are extended, a rebalancing of the table space data might occur. Rebalancing is the process of moving table space extents from one location to another in an attempt to keep the data striped. An extent is a unit of container space (measured in pages), and a stripe is a layer of extents across the set of containers for a table space.	Given load input file staffdata.del with a substantial amount of data (for example, 20000 or more records): `connect to sample; create tablespace ts1 managed by database using (file '/home/melnyk/melnyk/NODE0000/SQL00001 /ts1c1' 1024); create table newstaff like staff in ts1; load from staffdata.del of del insert into newstaff nonrecoverable; alter tablespace ts1 add (file '/home/melnyk/melnyk /NODE0000/SQL00001/ts1c2' 1024); list tablespaces; connect reset;` Information returned for TS1 shows that this table space is in DMS Rebalance in Progress state.
Disable Pending	0x200	A table space may be in this state during a database rollforward operation and should no longer be in this state by the end of the rollforward operation. The state is triggered by conditions that result from a table space going offline and compensation log records for a transaction not being written. The appearance and subsequent disappearance of this table space state is transparent to users.	An example illustrating this table space state is beyond the scope of this document.
Drop Pending	0x8000	A table space is in this state if one or more of its containers is found to have a problem during a database restart operation. (A database must be restarted if the previous session with this database terminated abnormally, such as during a power failure, for example.) If a table space is in Drop Pending state, it will not be available, and can only be dropped.	An example illustrating this table space state is beyond the scope of this document.
Load in Progress	0x20000	This is a transient state that is only in effect during a load operation (against a recoverable database) that specifies the COPY NO option. See also Load in Progress table state.	Given load input file staffdata.del with a substantial amount of data (for example, 20000 or more records): `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;` While the load operation is running, execute the following script from another session: `connect to sample; list tablespaces; connect reset;` Information returned for USERSPACE1 shows that this table space is in Load in Progress (and Backup Pending) state.
Normal	0x0	A table space is in Normal state if it is not in any of the other (abnormal) table space states. Normal state is the initial state of a table space after it is created.	`connect to sample; create tablespace ts1 managed by database using (file '/home/melnyk/melnyk/NODE0000/SQL00001 /tsc1' 1024); list tablespaces show detail;`
Offline and Not Accessible	0x4000	A table space is in this state if there is a problem with one or more of its containers. A container might be inadvertently renamed, moved, or damaged. After the problem has been rectified, and the containers that are associated with the table space are accessible again, this abnormal state can be removed by disconnecting all applications from the database and then reconnecting to the database. Alternatively, you can issue an ALTER TABLESPACE statement, specifying the SWITCH ONLINE clause, to remove the Offline and Not Accessible state from the table space without disconnecting other applications from the database.	`connect to sample; create tablespace ts1 managed by database using (file '/home/melnyk/melnyk/NODE0000/SQL00001 /tsc1' 1024); alter tablespace ts1 add (file '/home/melnyk/melnyk /NODE0000/SQL00001/tsc2' 1024); export to st_data.del of del select * from staff; create table stafftemp like staff in ts1; import from st_data.del of del insert into stafftemp; connect reset;` Rename table space container tsc1 to tsc3 and then try to query the STAFFTEMP table: `connect to sample; select * from stafftemp;` The query returns SQL0290N (table space access is not allowed), and the LIST TABLESPACES command returns a state value of 0x4000 (Offline and Not Accessible) for TS1. Rename table space container tsc3 back to tsc1. This time the query runs successfully.
Quiesced Exclusive	0x4	A table space is in this state when the application that invokes the table space quiesce function has exclusive (read or write) access to the table space. You can put a table space in Quiesced Exclusive state explicitly by issuing the QUIESCE TABLESPACES FOR TABLE command.	Ensure that the table space state is Normal before setting it to Quiesced Exclusive. `connect to sample; quiesce tablespaces for table staff reset; quiesce tablespaces for table staff exclusive; connect reset;` Execute the following script from another session: `connect to sample; select * from staff where id=60; update staff set salary=50000 where id=60; list tablespaces; connect reset;` Information returned for USERSPACE1 shows that this table space is in Quiesced Exclusive state.
Quiesced Share	0x1	A table space is in this state when both the application that invokes the table space quiesce function and concurrent applications have read (but not write) access to the table space. You can put a table space in Quiesced Share state explicitly by issuing the QUIESCE TABLESPACES FOR TABLE command.	Ensure that the table space state is Normal before setting it to Quiesced Share. `connect to sample; quiesce tablespaces for table staff reset; quiesce tablespaces for table staff share; connect reset;` Execute the following script from another session: `connect to sample; select * from staff where id=40; update staff set salary=50000 where id=40; list tablespaces; connect reset;` Information returned for USERSPACE1 shows that this table space is in Quiesced Share state.
Quiesced Update	0x2	A table space is in this state when the application that invokes the table space quiesce function has exclusive write access to the table space. You can put a table space in Quiesced Update state explicitly by issuing the QUIESCE TABLESPACES FOR TABLE command.	Ensure that the table space state is Normal before setting it to Quiesced Update. `connect to sample; quiesce tablespaces for table staff reset; quiesce tablespaces for table staff intent to update; connect reset;` Execute the following script from another session: `connect to sample; select * from staff where id=50; update staff set salary=50000 where id=50; list tablespaces; connect reset;` Information returned for USERSPACE1 shows that this table space is in Quiesced Update state.
Reorg in Progress	0x400	This is a transient state that is only in effect during a reorg operation.	Issue a REORG TABLE command: `connect to sample; reorg table staff; connect reset;` While the reorg operation is running, execute the following script from another session: `connect to sample;` 1. `list tablespaces show detail;` or 2. `get snapshot for tablespaces on sample; connect reset;` Information returned for USERSPACE1 shows that this table space is in Reorg in Progress state. Note: Table reorganization operations involving the SAMPLE database are likely to complete in a short period of time and, as a result, it may be difficult to observe the Reorg in Progress state using this approach.
Restore Pending	0x100	Table spaces for a database are in this state after the first part of a redirected restore operation (that is, before the SET TABLESPACE CONTAINERS command is issued). The table space (or the entire database) must be restored before the table space can be used. You cannot connect to the database until the restore operation has been successfully completed, at which time the value of the restore_pending informational database configuration parameter is set to NO.	When the first part of the redirected restore operation in Storage May be Defined completes, all of the table spaces are in Restore Pending state.
Restore in Progress	0x2000	This is a transient state that is only in effect during a restore operation.	`update db cfg for sample using logretain recovery; backup db sample; backup db sample tablespace (userspace1);` The timestamp for this backup image is: 20040611174124 `restore db sample tablespace (userspace1) online taken at 20040611174124;` While the restore operation is running, execute the following script from another session: `connect to sample;` 1. `list tablespaces show detail;` or 2. `get snapshot for tablespaces on sample; connect reset;` Information returned for USERSPACE1 shows that this table space is in Restore in Progress state.
Roll Forward Pending	0x80	A table space is in this state after a restore operation against a recoverable database. The table space (or the entire database) must be rolled forward before the table space can be used. A database is recoverable if the logretain database configuration parameter is set to RECOVERY, or the userexit database configuration parameter is set to YES. You cannot activate or connect to the database until a rollforward operation has been successfully completed, at which time the value of the rollfwd_pending informational database configuration parameter is set to NO.	When the online table space restore operation in Restore in Progress completes, the table space USERSPACE1 is in Roll Forward Pending state.
Roll Forward in Progress	0x40	This is a transient state that is only in effect during a rollforward operation.	Given load input file staffdata.del with a substantial amount of data (for example, 20000 or more records): `update db cfg for sample using logretain recovery; backup db sample; connect to sample; create tablespace ts1 managed by database using (file '/home/melnyk/melnyk/NODE0000/SQL00001 /ts1c1' 1024); create table newstaff like staff in ts1; connect reset; backup db sample tablespace (ts1) online;` The timestamp for this backup image is: 20040630000715 `connect to sample; load from staffdata.del of del insert into newstaff copy yes to /home/melnyk/backups; connect reset; restore db sample tablespace (ts1) online taken at 20040630000715; rollforward db sample to end of logs and stop tablespace (ts1) online;` While the rollforward operation is running, execute the following script from another session: `connect to sample;` 1. `list tablespaces show detail;` or 2. `get snapshot for tablespaces on sample; connect reset;` Information returned for TS1 shows that this table space is in Roll Forward in Progress state.
Storage May be Defined	0x2000000	Table spaces for a database are in this state after the first part of a redirected restore operation (that is, before the SET TABLESPACE CONTAINERS command is issued). This allows you to redefine the containers, if you wish.	`backup db sample;` Assuming that the timestamp for this backup image is 20040613204955: `restore db sample taken at 20040613204955 redirect; list tablespaces;` Information returned by the LIST TABLESPACES command shows that all of the table spaces are in Storage May be Defined and Restore Pending state.
Storage Must be Defined	0x1000	Table spaces for a database are in this state during a redirected restore operation to a new database if the set table space containers phase is omitted or if, during the set table space containers phase, the specified containers cannot be acquired. The latter can occur if, for example, an invalid path name has been specified, or there is insufficient disk space.	`backup db sample;` Assuming that the timestamp for this backup image is 20040613204955: `restore db sample taken at 20040613204955 into mydb redirect; set tablespace containers for 2 using (path 'ts2c1'); list tablespaces;` Information returned by the LIST TABLESPACES command shows that table space SYSCATSPACE and table space TEMPSPACE1 are in Storage Must be Defined, Storage May be Defined, and Restore Pending state. Storage Must be Defined state takes precedence over Storage May be Defined state.
Suspend Write	0x10000	A table space is in this state after a write operation has been suspended.	An example illustrating this table space state is beyond the scope of this document.
Table Space Creation in Progress	0x40000000	This is a transient state that is only in effect during a create table space operation.	`connect to sample; create tablespace ts1 managed by database using (file '/home/melnyk/melnyk/NODE0000/SQL00001 /tsc1' 1024); create tablespace ts2 managed by database using (file '/home/melnyk/melnyk/NODE0000/SQL00001 /tsc2' 1024); create tablespace ts3 managed by database using (file '/home/melnyk/melnyk/NODE0000/SQL00001 /tsc3' 1024);` While the create table space operations are running, execute the following script from another session: `connect to sample;` 1. `list tablespaces show detail;` or 2. `get snapshot for tablespaces on sample; connect reset;` Information returned for TS1, TS2, and TS3 shows that these table spaces are in Table Space Creation in Progress state.
Table Space Deletion in Progress	0x20000000	This is a transient state that is only in effect during a delete table space operation.	`connect to sample; create tablespace ts1 managed by database using (file '/home/melnyk/melnyk/NODE0000/SQL00001 /tsc1' 1024); create tablespace ts2 managed by database using (file '/home/melnyk/melnyk/NODE0000/SQL00001 /tsc2' 1024); create tablespace ts3 managed by database using (file '/home/melnyk/melnyk/NODE0000/SQL00001 /tsc3' 1024); drop tablespace ts1; drop tablespace ts2; drop tablespace ts3;` While the drop table space operations are running, execute the following script from another session: `connect to sample;` 1. `list tablespaces show detail;` or 2. `get snapshot for tablespaces on sample; connect reset;` Information returned for TS1, TS2, and TS3 shows that these table spaces are in Table Space Deletion in Progress state.