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.
Example
Given the staff_data.del input file with the following content:
|
| 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: |
| 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: |
| 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: |
| 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: |
| 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: |
| 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: |
| 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:20040611174124:
20040630000715, restore the database backup and rollforward to the end of logs as follows:
|
| 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 is20040613204955, restore a database backup as follows:
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:
相关 |