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.
ExampleGiven 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. |
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:
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. 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:
From another session, get information about
table spaces while the reorg 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 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:
From
another session, get information about table spaces while the create
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 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. |