There are currently at least 25 table or table space states supported by IBM® DB2® Universal Database™ (DB2 UDB). 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 utilities, such as the load utility or the backup and restore utilities.
This article describes each of the supported table or table space states (see Table 1; click on the name of a state to jump to its description in one of the tables that follow). It 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 DB2 UDB on a system that is not UNIX®-based, 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 DB2 UDB. A few require scenarios that are not part of the SAMPLE database, but you can use a connection to the SAMPLE database as a starting point.
The states are described in Tables 2 and 3. Table 2 describes the supported table space states, and Table 3 describes the table states that are currently supported.
Table 1. Table and table space states that are defined in supported versions of DB2 UDB through Version 8.1.4
| State | Scope | State | Scope |
|---|---|---|---|
| Backup Pending | Table space | Quiesced Update | Table space |
| Backup in Progress | Table space | Read Access Only | Table |
| Check Pending | Table | Reorg in Progress | Table space |
| DMS Rebalance in Progress | Table space | Restore Pending | Table space |
| Disable Pending | Table space | Restore in Progress | Table space |
| Drop Pending | Table space | Roll Forward Pending | Table space |
| Load Pending | Table | Roll Forward in Progress | Table space |
| Load in Progress | Table space or table | Storage May be Defined | Table space |
| Normal | Table space or table | Storage Must be Defined | Table space |
| Not Load Restartable | Table | Table Space Creation in Progress | Table space |
| Offline and Not Accessible | Table space | Table Space Deletion in Progress | Table space |
| Quiesced Exclusive | Table space | Unavailable | Table |
| Quiesced Share | Table space |
The db2tbst command accepts a hexadecimal state value and returns the corresponding table space state (see Figure 1). For example, the command db2tbst 0x0008 returns State = Load Pending. The hexadecimal state value, in turn, is part of the output from the LIST TABLESPACES command (see Figure 2). For more information about these and other DB2 commands, see the IBM DB2 Universal Database Command Reference.
Figure 1. The db2tbst command accepts a hexadecimal table space state value and returns the state.
The externally visible state of a table space is composed of the hexadecimal sum of individual state values. For example, if the state of a table space is Backup Pending and Load in Progress, the returned hexadecimal value is 0x20020 (0x00020 + 0x20000). In this case, the command db2tbst 0x20020 returns:
State = Backup Pending
+ Load in Progress
|
Figure 2. The LIST TABLESPACES command can be used to determine the current state of the table spaces in a connected database.
Table 2. 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 article. |
| 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 article. |
| 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 (see Cloning DB2 Databases Using Redirected Restore). | 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. |
| 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. |
The DB2 UDB Version 8 load utility uses table states (as well as locks) to gain required access to a table and to preserve database consistency during a load operation. Table states persist even if a load operation aborts. You can use the LOAD QUERY command (see Figure 3) to determine the state of a particular table. The LOAD QUERY command checks the status of a load operation while it is running and returns the table state. If the load operation has completed (or aborted), this command returns only the table state. For more information about this and other DB2 commands, see the IBM DB2 Universal Database Command Reference.
Figure 3. The LOAD QUERY command can be used to determine the state of a specified table.
Although dependent table spaces are no longer quiesced (a quiesce is a persistent lock) prior to a load operation, the Load in Progress table space state prevents the backup of dependent tables during a load operation. The Load in Progress table space state is different from the Load in Progress table state: All load operations use the Load in Progress table state, but load operations (against a recoverable database) with the COPY NO option specified also use the Load in Progress table space state.
A table can be in several states simultaneously. For example, if data is loaded into a table that has table check constraints defined on it, and the ALLOW READ ACCESS option is specified, the table is in Check Pending, Load in Progress, and Read Access Only state during the load operation.
Table 3. Supported table states
| State | Description | Examples |
|---|---|---|
| Check Pending | A table is in this state if it has table check constraints (restrictions) defined on it, but the compliance of new data with those defined restrictions has not yet been verified. For example, the DB2 load utility sets the Check Pending state when it begins a load operation on a table that has table check constraints defined on it. To return the table to Normal state, issue the SET INTEGRITY statement. For more information, see Constraints. | Given load input file staff_data.del with content: 11,"Melnyk",20,"Sales",10,70000,15000: connect to sample; alter table staff add constraint max_salary check (100000 - salary > 0); load from staff_data.del of del insert into staff; load query table staff; Information returned by the LOAD QUERY command shows that the STAFF table is in Check Pending state. |
| Load Pending | A table is in this state if a load operation that has been active on the table is aborted before the data could be committed. To return the table to Normal state, invoke a load terminate, load restart, or load replace operation. | Given load input file staffdata.del with a substantial amount of data (for example, 20000 or more records), create a small table space that contains the target table of the load operation, a new table called NEWSTAFF: connect to sample; create tablespace ts1 managed by database using (file '/home/melnyk/melnyk/NODE0000/SQL00001/ts1c1' 256); create table newstaff like staff in ts1; load from staffdata.del of del insert into newstaff; load query table newstaff; load from staffdata.del of del terminate into newstaff; load query table newstaff; connect reset; Information returned by the LOAD QUERY command shows that the NEWSTAFF table is in Load Pending state; after a load terminate operation, the table is in Normal state. |
| Load in Progress | This is a transient state that is only in effect during a load operation. For information about bringing a table out of Load in Progress state if your load operation has failed or was interrupted, see the section on pending states after a load operation in the IBM DB2 Universal Database Data Movement Utilities Guide and Reference. See also Load in Progress table space state. | Given load input file staffdata.del with a substantial amount of data (for example, 20000 or more records): connect to sample; create table newstaff like staff; load from staffdata.del of del insert into newstaff; While the load operation is running, execute the following script from another session: connect to sample; load query table newstaff; connect reset; Information returned by the LOAD QUERY command shows that the NEWSTAFF table is in Load in Progress state. |
| Normal | A table is in Normal state if it is not in any of the other (abnormal) table states. Normal state is the initial state of a table after it is created. | connect to sample; create table newstaff like staff; load query table newstaff; Information returned by the LOAD QUERY command shows that the NEWSTAFF table is in Normal state. |
| Not Load Restartable | A table is in this state when a rollforward operation follows a failed load operation that was not successfully restarted or terminated. The table will also be in Load Pending state. To return the table to Normal state, issue the LOAD TERMINATE command. | 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' 256); create table newstaff like staff in ts1; connect reset; backup db sample; The timestamp for this backup image is: 20040629205935 connect to sample; load from staffdata.del of del insert into newstaff copy yes to /home/melnyk/backups; connect reset; restore db sample taken at 20040629205935; rollforward db sample to end of logs and stop; connect to sample; load query table newstaff; connect reset; Information returned by the LOAD QUERY command shows that the NEWSTAFF table is in Not Load Restartable and Load Pending state. connect to sample; load from staffdata.del of del terminate into newstaff copy yes to /home/melnyk/backups; load query table newstaff; connect reset; Information returned by the LOAD QUERY command shows that the NEWSTAFF table is now in Normal state. |
| Read Access Only | A table is in this state during a load operation if the ALLOW READ ACCESS option was specified. Read Access Only is a transient state that allows other applications and utilities to have read access to data that existed prior to the load operation. | Given load input file staffdata.del with a substantial amount of data (for example, 20000 or more records): connect to sample; export to st_data.del of del select * from staff; create table newstaff like staff; import from st_data.del of del insert into newstaff; load from staffdata.del of del insert into newstaff allow read access; While the load operation is running, execute the following script from another session: connect to sample; load query table newstaff; select * from newstaff; connect reset; Information returned by the LOAD QUERY command shows that the NEWSTAFF table is in Read Access Only and Load in Progress state. The query returns only the exported contents of the STAFF table, data that existed in the NEWSTAFF table prior to the load operation. |
| Unavailable | A table is in this state when a nonrecoverable load operation is rolled forward; such a table can only be dropped or restored from a backup image. | 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; The timestamp for this backup image is: 20040629182012 connect to sample; load from staff_data.del of del insert into staff nonrecoverable; connect reset; restore db sample taken at 20040629182012; rollforward db sample to end of logs and stop; connect to sample; load query table staff; connect reset; Information returned by the LOAD QUERY command shows that the STAFF table is in Unavailable state. |
Table and table space states are used by DB2 UDB to control access to data or to help protect the integrity of the database under specific circumstances. This article shows you, by way of working examples, some of the more common conditions that are likely to give rise to particular states, the commands that you can run to identify which states are in effect, and how to respond appropriately so that you can resume working with your data without missing a beat. You can easily adapt these examples and experiment with your own data; in no time at all, you will really "know your way around" the various states, and be able to use them to better understand the behavior of your database.
- DB2 Technical Support is the ideal place to locate resources such as the Version 8 Information Center and PDF product manuals.
- The IBM DB2 Universal Database Data Movement Utilities Guide and Reference provides detailed information about the DB2 data movement utilities.
- The IBM DB2 Universal Database Data Recovery and High Availability Guide and Reference provides detailed information about the DB2 backup and recovery utilities.
Roman B. Melnyk , Ph.D., is a senior member of the DB2 Information Development team, specializing in database administration, DB2 utilities, and SQL. During more than nine years at IBM, Roman has written numerous DB2 books, articles, and other related materials. Roman coauthored DB2 Version 8: The Official Guide (Prentice Hall Professional Technical Reference, 2003), DB2: The Complete Reference (Osborne/McGraw-Hill, 2001), DB2 Fundamentals Certification for Dummies (Hungry Minds, 2001), and DB2 for Dummies (IDG Books, 2000).
Comments (Undergoing maintenance)
Next steps from IBM
My developerWorks community
Tags
Use the search field to find all types of content in My developerWorks with that tag.
Use the slider bar to see more or fewer tags.
Popular tags shows the top tags for this particular content zone (for example, Java technology, Linux, WebSphere).
My tags shows your tags for this particular content zone (for example, Java technology, Linux, WebSphere).
Use the search field to find all types of content in My developerWorks with that tag. Popular tags shows the top tags for this particular content zone (for example, Java technology, Linux, WebSphere). My tags shows your tags for this particular content zone (for example, Java technology, Linux, WebSphere).
Popular article tags |
My article tagsSkip to tags list
Popular article tags |
My article tags
