Table space states
Table space states
https://www.ibm.com/docs/en/db2/11.1?topic=spaces-table-space-states
Last Updated: 2022-05-20
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.
State
|
Hexadecimal state value
|
Description
|
---|---|---|
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 logarchmeth1 database configuration parameter is set to any value other than OFF. 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.
Given the staff_data.del input file with the following content:
Load this data into the staff table specifying the copy no as follows:
Information returned for USERSPACE1 shows that this table space is in Backup Pending state.
|
Backup in Progress | 0x800 | This is a transient state that is only in effect during a backup operation.
Example Perform an online backup as follows:
From another session, execute one of the following scripts while the backup operation is running:
|
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.
Example Given the staffdata.del input file with 20000 or more records, create the table newstaff, load it using this input file, and then add a new container to table space ts1:
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.
Example Given the staffdata.del input file with 20000 or more records, create the table newstaff and load it specifying COPY NO and this input file:
From another session, get information about table spaces while the load operation is running by executing one of the sample scripts shown in the Backup in Progress example.
Information returned for USERSPACE1 shows that this table space is in Load in Progress (and Backup Pending) state. |
Move in progress | 0x80000 | This is a transient state that is only in effect during an extent movement operation. This state indicates that the db2ExtMove is in the process of moving a table space's extents. |
Move has started | 0x100000 | This is a transient state that is only in effect during an extent movement operation. This state indicates that extent movement has been started on the table space and the db2ExtMov thread has been created. The process of moving extents may not have started yet. Example connect to sample; create tablespace TS1; alter tablespace TS1 lower high water mark; |
Move is terminating | 0x200000 | This is a transient state that is only in effect during an extent movement operation. This state indicates that extent movement is in the process of terminating on the table space. Example connect to sample; create tablespace TS1; alter tablespace TS1 reduce max; alter tablespace TS1 reduce stop; |
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.
Example Create a table space and then get information about that table space as follows:
Information returned for USERSPACE1 shows that this table space is in Normal state. |
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.
Example Create table space ts1 with containers tsc1 and tsc2, create table staffemp, and import data from the st_data.del file as follows:
Rename table space container tsc1 to tsc3 and then try to query the STAFFTEMP table:
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.
The query returns SQL0290N (table space access is not allowed), and the |
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. Use the QUIESCE TABLESPACES FOR TABLE command to explicitly set a table space to Quiesced Exclusive.
Example Set table spaces to Normal before setting them to Quiesced Exclusive as follows:
From another session, execute the following script:
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. Use the QUIESCE TABLESPACES FOR TABLE command to explicitly set a table space to Quiesced Share.
Example Set table spaces to Normal before setting them to Quiesced Share as follows:
From another session, execute the following script:
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. Use the QUIESCE TABLESPACES FOR TABLE command to explicitly set a table space to Quiesced Update state.
Example Set table spaces to Normal before setting them to Quiesced Update as follows:
From another session, execute the following script:
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.
Example Reorganize the staff table as follows:
sample scripts shown in the Backup in Progress example.
From another session, get information about table spaces while the reorg operation is running by executing one of the 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.
Example 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.
Example Enable the sample database for rollforward recovery then back up the sample database and the USERSPACE1 table space as follows:
Restore the USERSPACE1 table space backup assuming the timestamp for this backup image is
20040611174124 :
From another session, get information about table spaces while the restore operation is running by executing one of the sample scripts shown in the Backup in Progress example.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 logarchmeth1 database configuration parameter is set to any value other than OFF. 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.
Example 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.
Example Given the staffdata.del input file with 20000 or more record, create a table and tablespace followed by a database backup:
Assuming that the timestamp for the backup image is
20040630000715 , restore the database backup and rollforward to the end of logs as follows:
From another session, get information about table spaces while the rollforward operation is running by executing one of the sample scripts shown in the Backup in Progress example.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.
Example Assuming that the timestamp for the backup image is
20040613204955 , restore a database backup as follows:
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.
Example Assuming that the timestamp for the backup image is
20040613204955 , restore a database backup as follows:
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.
Example Create table spaces ts1, ts2, and ts3 as follows:
sample scripts shown in the Backup in Progress example.
From another session, get information about table spaces while the create table space operations are running by executing one of the 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.
Example Create table spaces ts1, ts2, and ts3 then drop them as follows: From another session, get information about table spaces while the drop table space operations are running by executing one of the sample scripts shown in the Backup in Progress example.Information returned for TS1, TS2, and TS3 shows that these table spaces are in Table Space Deletion in Progress state. |
![](https://cm.ipinyou.com/xcms/ibmdmp/s.gif?tid=oeu1650264295318r0.3609051207535896)