https://github.com/famousdraw

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.

Table 1. Supported table space states
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: 
11,"Melnyk",20,"Sales",10,70000,15000:
Load this data into the staff table specifying the copy no as follows: 
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;
list tablespaces; 
connect reset;
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: 
backup db sample online;
From another session, execute one of the following scripts while the backup operation is running: 
  • connect to sample;
list tablespaces show detail;
connect reset;
  • connect to sample;
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.

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: 
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 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: 
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;
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: 
connect to sample; 
create tablespace ts1 managed by automatic storage; 
list tablespaces show detail;
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: 
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. 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: 
connect to sample; 
quiesce tablespaces for table staff reset; 
quiesce tablespaces for table staff exclusive; 
connect reset;
From another session, execute the following script: 
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. 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: 
connect to sample; 
quiesce tablespaces for table staff reset; 
quiesce tablespaces for table staff share; 
connect reset;
From another session, execute the following script: 
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. 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: 
connect to sample; 
quiesce tablespaces for table staff reset; 
quiesce tablespaces for table staff intent to update; 
connect reset;
From another session, execute the following script: 
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.

Example

Reorganize the staff table as follows: 
connect to sample; 
reorg table staff; 
connect reset;
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: 
update db cfg for sample using logarchmeth1 logretain; 
backup db sample; 
backup db sample tablespace (userspace1);
Restore the USERSPACE1 table space backup assuming the timestamp for this backup image is 20040611174124: 
restore db sample tablespace (userspace1) online taken at 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: 
update db cfg for sample using logarchmeth1 logretain; 
backup db sample; 
connect to sample; 
create tablespace ts1 managed by automatic storage;
create table newstaff like staff in ts1; 
connect reset; 
backup db sample tablespace (ts1) online;
Assuming that the timestamp for the backup image is 20040630000715, restore the database backup and rollforward to the end of logs as follows: 
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;
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: 
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.

Example

Assuming that the timestamp for the backup image is 20040613204955, restore a database backup as follows: 
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.
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: 
connect to sample; 
create tablespace ts1 managed by automatic storage;
create tablespace ts2 managed by automatic storage;
create tablespace ts3 managed by automatic storage;
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:

connect to sample; 
create tablespace ts1 managed by automatic storage;
create tablespace ts2 managed by automatic storage; 
create tablespace ts3 managed by automatic storage; 
drop tablespaces ts1, ts2, ts3;
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.
Parent topic:
Was this topic helpful?
 
 
 
 
 
 
© Copyright IBM Corporation 2014
 
 
 
 
 
 

posted on 2022-06-22 13:47  红色MINI  阅读(24)  评论(0)  编辑  收藏  举报

导航