DB2 Basics: Demystifying table and table space states

The DB2 database manager uses table and table space states to control access to data or to help protect the integrity of the database. This article describes these states and shows you, by way of working examples, typical conditions under which they appear, and how to respond to them.

Originally written in 2004, this article was updated in October, 2011, to include information from the most recent version of IBM® DB2 for Linux®, UNIX®, and Windows®.

Roman Melnyk (roman_b_melnyk@hotmail.com), DB2 Information Development, IBM Canada Ltd.

Roman Melnyk photo Roman B. Melnyk , Ph.D., is a senior member of the DB2 Information Development team. Roman co-authored 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).



21 October 2011 (First published 01 July 2004)

Also available in Chinese

There are currently at least 30 table or table space states supported by IBM DB2 Database for Linux, UNIX, and Windows (DB2 for LUW). 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 the DB2 server 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 the DB2 product. 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 for LUW through Version 9.7.4

StateScopeStateScope
Backup PendingTable spaceRedistribute PendingTable
Backup in ProgressTable spaceRedistribute in ProgressTable space
DMS Rebalance in ProgressTable spaceReorg in ProgressTable space
Delete PendingTable spaceRestore PendingTable space
Disable PendingTable spaceRestore in ProgressTable space
Drop PendingTable spaceRoll Forward PendingTable space
Load PendingTableRoll Forward in ProgressTable space
Load in ProgressTable space or tableSet Integrity PendingTable
Movement in ProgressTable spaceStorage May be DefinedTable space
NormalTable space or tableStorage Must be DefinedTable space
Not Load RestartableTableTable Space Creation in ProgressTable space
Offline and Not AccessibleTable spaceTable Space Deletion in ProgressTable space
Quiesced ExclusiveTable spaceType-1 IndexesTable
Quiesced ShareTable spaceUnavailableTable
Quiesced UpdateTable spaceWrite SuspendedTable space
Read Access OnlyTable

Table space states

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.

Figure 1. The db2tbst command accepts a hexadecimal table space state value and returns the state.
Figure 1

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

The table space state is part of the output from the MON_GET_TABLESPACE - Get table space metrics table function. For example:

connect to sample;
select
    varchar(tbsp_name, 30) as tbsp_name,
    varchar(tbsp_state, 40) as tbsp_state
  from table(mon_get_tablespace('',-2)) as t;

TBSP_NAME                      TBSP_STATE
------------------------------ ----------------------------------------
SYSCATSPACE                    NORMAL
TEMPSPACE1                     NORMAL
USERSPACE1                     NORMAL
IBMDB2SAMPLEREL                NORMAL
IBMDB2SAMPLEXML                NORMAL

Table 2. Supported table space states

StateHexadecimal state valueDescriptionExamples
Backup Pending0x20A 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 or logarchmeth2 database configuration parameter is set to a 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.1. Given load input file staff_data.del with content: 11,"Melnyk",20,"Sales",10,70000,15000:
update db cfg for sample using logarchmeth1 logretain;
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 logarchmeth1 logretain;
connect to sample;
Backup in Progress0x800This 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. select varchar(tbsp_name, 30) as tbsp_name, varchar(tbsp_state, 40) as tbsp_state from table(mon_get_tablespace('',-2)) as t; 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 Progress0x10000000This 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);
select varchar(tbsp_name, 30) as tbsp_name, varchar(tbsp_state, 40) as tbsp_state from table(mon_get_tablespace('',-2)) as t;
connect reset;
Information returned for TS1 shows that this table space is in DMS Rebalance in Progress state.
Delete Pending0x00000010If a load operation fails during the delete stage (while rows that violate unique constraints are being deleted), the table space is left in Delete Pending state. The state change object identification monitor element (tablespace_state_change_object_id) identifies the object that caused the table space state to be set to Delete Pending. If nonzero, the value of this element matches a value from the TABLEID column of the SYSCAT.TABLES catalog view. The state change table space identification monitor element (tablespace_state_change_ts_id) shows the table space ID of the object that caused this table space state to be set. If nonzero, the value of this element matches a value from the TABLESPACEID column of the SYSCAT.TABLES catalog view.An example illustrating this table space state is beyond the scope of this article.
Disable Pending0x200A table space might 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 Pending0x8000A 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 Progress0x20000This 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 logarchmeth1 logretain;
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;
select varchar(tbsp_name, 30) as tbsp_name, varchar(tbsp_state, 40) as tbsp_state from table(mon_get_tablespace('',-2)) as t;
connect reset;
Information returned for USERSPACE1 shows that this table space is in Load in Progress (and Backup Pending) state.
Movement in Progress0x00080000The Movement in Progress state indicates that a table space extent movement operation to reclaim unused storage is in progress.An example illustrating this table space state is beyond the scope of this article.
Normal0x0A 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);
select varchar(tbsp_name, 30) as tbsp_name, varchar(tbsp_state, 40) as tbsp_state from table(mon_get_tablespace('',-2)) as t;
Offline and Not Accessible0x4000A 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 MON_GET_TABLESPACE - Get table space metrics table function returns a state value of Offline and Not Accessible for TS1. Rename table space container tsc3 back to tsc1. This time the query runs successfully.
Quiesced Exclusive0x4A 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;
select varchar(tbsp_name, 30) as tbsp_name, varchar(tbsp_state, 40) as tbsp_state from table(mon_get_tablespace('',-2)) as t;
connect reset;
Information returned for USERSPACE1 shows that this table space is in Quiesced Exclusive state.
Quiesced Share0x1A 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;
select varchar(tbsp_name, 30) as tbsp_name, varchar(tbsp_state, 40) as tbsp_state from table(mon_get_tablespace('',-2)) as t;
connect reset;
Information returned for USERSPACE1 shows that this table space is in Quiesced Share state.
Quiesced Update0x2A 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;
select varchar(tbsp_name, 30) as tbsp_name, varchar(tbsp_state, 40) as tbsp_state from table(mon_get_tablespace('',-2)) as t;
connect reset;
Information returned for USERSPACE1 shows that this table space is in Quiesced Update state.
Redistribute in Progress0x40000The Redistribute in Progress table space state indicates that there is a data redistribution operation in progress. If the data redistribution operation fails or is interrupted, the table state will change to Redistribute Pending.An example illustrating this table space state is beyond the scope of this article.
Reorg in Progress0x400This 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. select varchar(tbsp_name, 30) as tbsp_name, varchar(tbsp_state, 40) as tbsp_state from table(mon_get_tablespace('',-2)) as t; 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 might be difficult to observe the Reorg in Progress state using this approach.
Restore Pending0x100Table 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 Progress0x2000This is a transient state that is only in effect during a restore operation.update db cfg for sample using logarchmeth1 logretain;
backup db sample;
backup db sample tablespace (userspace1);
The timestamp for this backup image is: 20110611174124
restore db sample tablespace (userspace1) online taken at 20110611174124;
While the restore operation is running, execute the following script from another session:
connect to sample;
1. select varchar(tbsp_name, 30) as tbsp_name, varchar(tbsp_state, 40) as tbsp_state from table(mon_get_tablespace('',-2)) as t; 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 Pending0x80A 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 or logarchmeth2 database configuration parameter is set to a 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.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 Progress0x40This 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 logarchmeth1 logretain;
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: 20110630000715
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 20110630000715;
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. select varchar(tbsp_name, 30) as tbsp_name, varchar(tbsp_state, 40) as tbsp_state from table(mon_get_tablespace('',-2)) as t; 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 Defined0x2000000Table 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 20110613204955:
restore db sample taken at 20110613204955 redirect;
select varchar(tbsp_name, 30) as tbsp_name, varchar(tbsp_state, 40) as tbsp_state from table(mon_get_tablespace('',-2)) as t;
Information returned by the MON_GET_TABLESPACE - Get table space metrics table function shows that all of the table spaces are in Storage May be Defined and Restore Pending state.
Storage Must be Defined0x1000Table 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 20110613204955:
restore db sample taken at 20110613204955 into mydb redirect;
set tablespace containers for 2 using (path 'ts2c1');
select varchar(tbsp_name, 30) as tbsp_name, varchar(tbsp_state, 40) as tbsp_state from table(mon_get_tablespace('',-2)) as t;
Information returned by the MON_GET_TABLESPACE - Get table space metrics table function 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 Progress0x40000000This 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. select varchar(tbsp_name, 30) as tbsp_name, varchar(tbsp_state, 40) as tbsp_state from table(mon_get_tablespace('',-2)) as t; 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 Progress0x20000000This 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. select varchar(tbsp_name, 30) as tbsp_name, varchar(tbsp_state, 40) as tbsp_state from table(mon_get_tablespace('',-2)) as t; 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.
Write Suspended0x00010000The Write Suspended table space state indicates that the SET WRITE command has been used to suspend I/O write operations against a database. I/O write operations to associated logs are also suspended by this command.connect to sample;
set write suspend for db;
select varchar(tbsp_name, 30) as tbsp_name, varchar(tbsp_state, 40) as tbsp_state from table(mon_get_tablespace('',-2)) as t;
connect reset;
Information returned by the MON_GET_TABLESPACE - Get table space metrics table function shows that all table spaces are in Write Suspended state.

Table states

The DB2 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 2) 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 9.7 for Linux, UNIX, and Windows Command Reference.

Figure 2. The LOAD QUERY command can be used to determine the state of a specified table.
Figure 2

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 Load in Progress, Read Access Only, and Set Integrity Pending state during the load operation.

Table 3. Supported table states

StateDescriptionExamples
Set Integrity Pending (formerly 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 Set Integrity 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 Set Integrity Pending state.
Load PendingA 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 ProgressThis 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 9.7 for Linux, UNIX, and Windows 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.
NormalA 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 RestartableA 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 logarchmeth1 logretain;
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: 20110629205935
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 20110629205935;
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 OnlyA 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.
Redistribute PendingThe Redistribute Pending table state indicates that a data redistribution operation failed or was interrupted. You can execute a REDISTRIBUTE DATABASE PARTITION GROUP command, specifying the CONTINUE or the ABORT option, to remove the Redistribute Pending state.An example illustrating this table state is beyond the scope of this article.
Type-1 IndexesThe Type-1 Indexes state indicates that the table is using type-1 indexes, which are no longer supported. You can convert type-1 indexes to type-2 indexes by using the CONVERT option of the REORG INDEXES/TABLE command or by using the db2IdentifyType1 utility.An example illustrating this table state is beyond the scope of this article.
UnavailableA 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 logarchmeth1 logretain;
backup db sample;
The timestamp for this backup image is: 20110629182012
connect to sample;
load from staff_data.del of del insert into staff nonrecoverable;
connect reset;
restore db sample taken at 20110629182012;
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.

Summary

Table and table space states are used by DB2 for LUW 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.

Resources

Learn

Get products and technologies

  • Download a free trial version of DB2 for Linux, UNIX, and Windows.
  • Now you can use DB2 for free. Download DB2 Express-C, a no-charge version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edition and provides a solid base to build and deploy applications.
  • Build your next development project with IBM trial software, available for download directly from developerWorks.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=16210
ArticleTitle=DB2 Basics: Demystifying table and table space states
publish-date=10212011