DB2 table and table space states use to control access to data, or help protect the integrity of the database. This article describes these conditions and to work examples, demonstrating the emergence of these states typically respond to their conditions and how. Currently, IBM? DB2? Universal Database? (DB2 UDB) support at least 25 kinds of table or table space state. In some cases, these states are used to control access to data, or if necessary, be used to lead to a specific user actions to protect the integrity of the database. With a majority of state resulting from the operation of DB2 utilities related events, such as load utility, or backup and restore utility.
This article describes each of the supported table or table space state (see Table 1; click the state name Skip to the description, the description of a table in the back). This article also provides some working examples to show exactly how to interpret and respond to management of the state of the database may encounter. These examples taken from AIX? Run command script; you can copy, paste and run them in person. If you are non-UNIX? The system running DB2 UDB, please ensure that all of the path name is correct format for the system. Most examples are based on tables in the SAMPLE database, based on the DB2 UDB database is included. There are a few SAMPLE database need not include the scene of the situation, however, you can connect to the SAMPLE database, used as a starting point.
Table 2 and Table 3 describes these states. Table 2 describes the supported table space state, while Table 3 describes the current state of support for the table.
Table 1. To version 8.1.4 until all supported versions of DB2 UDB tables and table spaces defined in state
State the scope of the scope of state
Backup Pending table space tablespace Quiesced Update
Backup in Progress table space Read Access Only Form
Check Pending Table Reorg in Progress table space
DMS Rebalance in Progress table space tablespace Restore Pending
Disable Pending Restore in Progress table space tablespace
Drop Pending table space tablespace Roll Forward Pending
Load Pending Table Roll Forward in Progress table space
Load in Progress table space or table space Storage May be Defined Table
Normal table space or table space Storage Must be Defined Table
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 tablespace
Table space state
db2tbst order to receive the status of the hexadecimal value, and returns the corresponding table space state (see Figure 1). For example, the command returns db2tbst 0x0008 State = Load Pending. The status of the hexadecimal value, in turn, is LIST TABLESPACES part of command output (see Figure 2).
Figure 1. Db2tbst order to receive hexadecimal table space state value and return its status.
Table space is by a single externally visible state of the hexadecimal value of the sum of the state posed. For example, if the table space state is the Backup Pending and Load in Progress, then the hexadecimal value returned is 0x20020 (0x00020 + 0x20000). In this example, the command db2tbst 0x20020 returned:
State = Backup Pending
+ Load in Progress
Figure 2. You can use the LIST TABLESPACES command connects to the database table space to determine the current state.
Table 2. Supported table space states
State of the sample described in hexadecimal status value
Backup Pending 0x20 in the specified time points (point-in-time) after the table space rollforward operation, or in the implementation of the COPY NO option specified LOAD operation (for the recoverability of the database), the table space is in this state . Using the table space, you must back up the table space (or the entire database). If you do not back up the table space, so the table can only contain a query can not update them. Note: Enable the database roll-forward recovery, you must also back up the database immediately. If logretain database configuration parameter is set to RECOVERY, or userexit database configuration parameter is set to YES, then the database is recoverable. Until such a database on a backup, you can connect to it. Backup, backup_pending database configuration parameter is set to NO. 1. Staff_data.del known to load the input file has the following contents: 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 backup operation only during the interim period before the effective state. Implementation of an online BACKUP DATABASE command:
backup db sample online;
In the implementation of the backup operation, through another session run the following script:
connect to sample;
1.list tablespaces show detail; or
2.get snapshot for tablespaces on sample;
connect reset;
USERSPACE1 return information display, the table space in the Backup in Progress state.
DMS Rebalance in Progress 0x10000000 This is a re-balancing only in the implementation of the data to be effective during the operation of a temporary state. When the space is defined as the database management (DMS) table space to add a new container, or when the expansion of existing container, the data on the possible realignment of the table space. Re-adjustment (Rebalancing) is the section of the table space (extent) from one location to another location, try to maintain the data into a single piece of process. A segment (extent) of space is a container unit (in page units), while a band (stripe) is used for table space level set of cross-section of the container. Known load with large data input files staffdata.del (for example, 20,000 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;
TS1 return information display, the table space in the DMS Rebalance in Progress state.
Disable Pending 0x200 roll in the implementation of the database during operation, the table space may be in this state, but when the end of the first rolling operation, it should not be in the state. When the tablespace offline and the transaction has not been written to the hard disk compensation log records, may trigger this state. The emergence of the table space state and the subsequent disappearance of the state is transparent to users. As the table shows an example of the state space, is beyond the scope of this article.
Drop Pending 0x8000 restart the database, if found in the database of one or more containers in question, then the table space will be in this state. (If the database of the previous session terminates abnormally, such as power outage, you must restart the database.) If the table space is in Drop Pending state, it is no longer available, can be deleted. As the table shows an example of the state space, is beyond the scope of this article.
Load in Progress 0x20000 This is an implementation of only the COPY NO option specified load operation (for recoverable database) when a valid temporary status. Known to load the input file staffdata.del have a lot of data (for example, 20,000 or more records):
update db cfg for sample using logretain recovery;
connect to sample;
load from staffdata.del of del insert into newstaff copy no;
In the implementation of the loading operation, the following script from another session:
connect to sample;
list tablespaces;
connect reset;
Information returned for USERSPACE1 return to the information display, the table space in the Load in Progress (and Backup Pending) state.
Normal 0x0 If the table space is not in any other (unusual) table space state, then the state space on the table for the Normal state. Normal state is the table space was created after the initial state. 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 table space if one or more containers a problem, then the table space is in such condition. Containers may occasionally be renamed, moved or damaged. The problem is corrected, and can be accessed again associated with the table space containers, you can disconnect the database and applications through the connection, then re-connect to the database to eliminate the abnormal state. Or, you can perform an ALTER TABLESPACE statement, specify the SWITCH ONLINE clause to eliminate the table spaces Offline and Not Accessible state, so no need to disconnect the other applications with 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 backup bin conf config data eshow_sitemap.html generate.sh log maint sitemap.html svn tmp from staff;
create table stafftemp like staff in ts1;
import from st_data.del of del insert into stafftemp;
connect reset;
Table space containers tsc1 renamed tsc3, and then try to check STAFFTEMP table:
connect to sample;
select backup bin conf config data eshow_sitemap.html generate.sh log maint sitemap.html svn tmp from stafftemp;
The query returns SQL0290N (not allowed to access the table space), and TS1 LIST TABLESPACES command returns the status value of 0x4000 (Offline and Not Accessible). Table space container tsc3 again named tsc1. This time, the query will run successfully.
Quiesced Exclusive 0x4 table space when you call silence (quiesce) exclusive application functionality (read or write) to access the table space, the table space is in such condition. You can perform a QUIESCE TABLESPACES FOR TABLE command, put Quiesced Exclusive table space state. In the table space is set to Quiesced Exclusive, make sure it is in Normal state.
connect to sample;
quiesce tablespaces for table staff reset;
quiesce tablespaces for table staff exclusive;
connect reset;
From another session run the following script:
connect to sample;
select backup bin conf config data eshow_sitemap.html generate.sh log maint sitemap.html svn tmp from staff where id = 60;
update staff set salary = 50000 where id = 60;
list tablespaces;
connect reset;
Information returned for USERSPACE1 return to the information display, the table space is Quiesced Exclusive state.
Quiesced Share 0x1 table space when you call silence (quiesce) function applications and concurrent applications can read (but not write) to access the table space, the table space is in such condition. You can perform a QUIESCE TABLESPACES FOR TABLE command, the table space explicitly placed Quiesced Share state. In the table space is set to Quiesced Share, make sure it is in Normal state.
connect to sample;
quiesce tablespaces for table staff reset;
quiesce tablespaces for table staff share;
connect reset;
From another session run the following script:
connect to sample;
select backup bin conf config data eshow_sitemap.html generate.sh log maint sitemap.html svn tmp from staff where id = 40;
update staff set salary = 50000 where id = 40;
list tablespaces;
connect reset;
USERSPACE1 return information display, the table space is Quiesced Share state.
Quiesced Update 0x2 table space when you call silence (quiesce) function in the application space for exclusive write access to the table, the table space is in such condition. You can perform a QUIESCE TABLESPACES FOR TABLE command, the table space explicitly placed Quiesced Update state. In the table space is set to Quiesced Update, make sure it is in Normal state.
connect to sample;
quiesce tablespaces for table staff reset;
quiesce tablespaces for table staff intent to update;
connect reset;
From another session run the following script:
connect to sample;
select backup bin conf config data eshow_sitemap.html generate.sh log maint sitemap.html svn tmp from staff where id = 50;
update staff set salary = 50000 where id = 50;
list tablespaces;
connect reset;
USERSPACE1 return information display, the table space is Quiesced Update state.
Reorg in Progress 0x400 This is a just course of reorganization (reorg) to be effective during the operation of a temporary state. Implementation of a REORG TABLE command:
connect to sample;
reorg table staff;
connect reset;
In the implementation of the restructuring operation, the following script from another session:
connect to sample;
1.list tablespaces show detail; or
2.get snapshot for tablespaces on sample;
connect reset;
USERSPACE1 return information display, the table space is Reorg in Progress state. NOTE: SAMPLE database tables involved in reorganization of operations is likely within a very short time to complete, so use this method to observe the Reorg in Progress state may be more difficult.
Restore Pending 0x100 in the implementation of the redirected restore operation after the first part (that is issued before the SET TABLESPACE CONTAINERS command), the database table space is in such condition. Before using the table space, table space must be restored (or the entire database). Until the successful completion of recovery operations, you can connect to the database at this time, restore_pending information database configuration parameter is set to NO. When in Storage May be Defined in the first part of the redirected restore operation is completed, all the table spaces will be in Restore Pending state.
Restore in Progress 0x2000 This is a recovery operation only during the implementation of the temporary status to be effective. update db cfg for sample using logretain recovery;
backup db sample;
backup db sample tablespace (userspace1);
Timestamp of the backup image is: 20040611174124
restore db sample tablespace (userspace1) online taken at 20040611174124;
In the implementation of the recovery operation, the following script from another session:
connect to sample;
1.list tablespaces show detail; or
2.get snapshot for tablespaces on sample;
connect reset;
USERSPACE1 return information display, the table space in the Restore in Progress state.
Roll Forward Pending 0x80 restore the database in the recovery operations after the implementation of the table space is in such condition. The use of table space, you must roll the table space (or the entire database). If logretain database configuration parameter is set to RECOVERY, or userexit database configuration parameter is set to YES, then the database is recoverable. Until the roll operation completed successfully, you can activate or connect to the database at this time, rollfwd_pending information database configuration parameter is set to NO. When in the Restore in Progress in the completion of the online table space restore operation, the table space USERSPACE1 in Roll Forward Pending state.
Roll Forward in Progress 0x40 This is a pre-roll only during the operation in the implementation of the temporary status to be effective. Known load with large data input files staffdata.del (for example, 20,000 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;
Timestamp of the 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;
When the implementation of the pre-roll operation, the following script from another session:
connect to sample;
1.list tablespaces show detail; or
2.get snapshot for tablespaces on sample;
connect reset;
TS1 return information display, the table space is in Roll Forward in Progress state.
Storage May be Defined 0x2000000 in the implementation of the redirected restore operation after the first part (that is issued before the SET TABLESPACE CONTAINERS command), the database table space is in such condition. backup db sample;
Assumes that the timestamp of the backup image is: 20040613204955
restore db sample taken at 20040613204955 redirect;
list tablespaces;
Information returned by the LIST TABLESPACES command returns the information display, all the table spaces are in Storage May be Defined and Restore Pending state.
Storage Must be Defined 0x1000 recovery operations in the period redirected to the new database, if the omitted set the stage table space containers, or, if you set the table space containers can not be specified during the stage of the container, then the database table space will in this state. After some time a situation occurs, for example, specify an invalid path name, or disk space. backup db sample;
Assumes that the timestamp of the backup image is: 20040613204955.
restore db sample taken at 20040613204955 into mydb redirect;
set tablespace containers for 2 using (path''ts2c1'');
list tablespaces;
LIST TABLESPACES command returns the information display, table space and table space TEMPSPACE1 SYSCATSPACE are in Storage Must be Defined, Storage May be Defined and Restore Pending state. Storage Must be Defined state than in Storage May be Defined state is more important.
Table Space Creation in Progress 0x40000000 This is only the implementation of the create table space to be effective during the operation of the temporary status. 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);
Create table space in the implementation of the operation, the following script from another session:
connect to sample;
1.list tablespaces show detail; or
2.get snapshot for tablespaces on sample;
connect reset;
TS1, TS2 and TS3 return information shown in these table spaces Table Space Creation in Progress state.
Table Space Deletion in Progress 0x20000000 This is a table space only in the implementation of the delete operation to be effective during the interim state. 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;
Remove the table space in the implementation of operation, the following script from another session:
connect to sample;
1.list tablespaces show detail; or
2.get snapshot for tablespaces on sample;
connect reset;
TS1, TS2 and TS3 return information shown in these table spaces Table Space Deletion in Progress state.
琛?state
DB2 UDB Version 8 load utility status by the table (and lock) to gain access to the table, and the implementation of the loading operation to maintain the database consistency. Even if the abnormal termination occurred loading operation, the table state will also be maintained. You can use the LOAD QUERY command (see Figure 3) to determine the status of a particular table. LOAD QUERY command in the run-time checking the status of load operation, and returns the table status. If the loading operation is completed (or abnormal termination), then the command only returns the table status.
Figure 3. You can use the LOAD QUERY command to determine the status of the specified table.
Although the loading operation before the table space is no longer dependent silent (quiesce is a persistent lock), but the Load in Progress table space state in the implementation of the loading operation to prevent backups on the slave table. Load in Progress table space state is different from the Load in Progress table space state: all the loading operations are using the Load in Progress table state, but the COPY NO option specified load operation (against the recoverability of the database) or use the Load in Progress table space state.
A table in several states simultaneously under. For example, if the data included in the definition of the table check constraint in the table, and specify ALLOW READ ACCESS option, then the implementation of the load during operation, the table on in the Check Pending, Load in Progress, and Read Access Only state.
Table 3. By the support of the state table
State description of the sample
Check Pending the definition of a table if the table check constraint, but not yet verify the new data with those already defined compatibility constraints, it is in this state on the table. For example, DB2 load utility is defined as beginning in the table table check constraints on implementation of the loading operation, the state will set the table for the Check Pending state. If you want to restore the Normal state of the table, you need to do a SET INTEGRITY statement. Known to load the input file staff_data.del have the following: 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;
LOAD QUERY command returns the information display, STAFF table is in Check Pending state.
Load Pending before the data can be submitted if the table is performed on abnormal loading operation was terminated, then the table on in this state. To make the table to resume Normal state, you need to call the load terminate, load restart or load replace operation. Known to load the input file staffdata.del with large data (eg, 20000 or more records), create a table that contains a small load operation target table space, create a new table named 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;
LOAD QUERY command returns the information display, NEWSTAFF table in the Load Pending state; in the implementation of the load terminate operation, the the table on the back in the Normal state.
Load in Progress This is a load operation only during the implementation of the temporary status to be effective. Known load with large data input files staffdata.del (for example, 20,000 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;
In the implementation of the loading operation, the following script from another session:
connect to sample;
load query table newstaff;
connect reset;
LOAD QUERY command returns the information display, NEWSTAFF table in the Load in Progress state.
Normal if the table is not in any other (unusual) form the state, then it is in Normal state. Normal state is created after the table is the initial state. connect to sample;
create table newstaff like staff;
load query table newstaff;
LOAD QUERY command returns the information display, NEWSTAFF table in the Normal state.
Not Load Restartable when the execution finished before the rolling operation, followed by the emergence of a failed load operation, and the operation has not been successfully restarted or terminated, the table on in this state. The table will also be in the Load Pending state. To make the table to resume Normal state, you need to execute a LOAD TERMINATE command. Known load with large data input files staffdata.del (for example, 20,000 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;
Timestamp of the 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;
LOAD QUERY command returns the information display, NEWSTAFF table is 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;
LOAD QUERY command returns the information display, NEWSTAFF expressed in the state in Normal.
Read Access Only in the implementation of the loading operation, if you specify the ALLOW READ ACCESS option, then the table will in this state. Read Access Only is a temporary state that allows other applications and utilities read access in the implementation of existence before loading the data operation. Known load with large data input files staffdata.del (for example, 20,000 or more records):
connect to sample;
export to st_data.del of del select backup bin conf config data eshow_sitemap.html generate.sh log maint sitemap.html svn tmp 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;
In the implementation of the loading operation, the following script from another session:
connect to sample;
load query table newstaff;
select backup bin conf config data eshow_sitemap.html generate.sh log maint sitemap.html svn tmp from newstaff;
connect reset;
LOAD QUERY command returns the information display, NEWSTAFF table in the Read Access Only and Load in Progress state. The query returns the contents of export STAFF table, and before the implementation of load operation, there is data in the table in NEWSTAFF.
Unavailable current roll of a load operation can not be restored, the table in this state; this table can only be removed, or from the backup image to restore it. Known to load the input file staff_data.del have the following: 11, "Melnyk", 20, "Sales", 10,70000,15000:
update db cfg for sample using logretain recovery;
backup db sample;
Timestamp of the 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;
LOAD QUERY command returns the information display, STAFF sheet is Unavailable state.
Conclusion
DB2 UDB table space with the state and the state table to control access to data, or in specific cases to help protect the integrity of the database. This paper shows examples of work may lead to a specific state of the more common conditions that you can use them to identify which state is effective, and how to properly respond, so you can continue to use the data, without any leakage beat (miss a beat ) phenomenon. You will be very easy to adapt to these examples, and they use their own data; soon, you will truly "perceived around the" various states, and can use them to better understand the behavior of the database.
相关链接:
Lenovo restart willow to break the current mix
Fax Tools Report
Qualcomm CEO: WiMax 3G will compete in the face of setbacks
What is digital TV renovation Project?
The biggest killer of retail sales
Interview Xoceco Xie Siyu: flat panel TV has been REVERSED in the Western pattern
WMV To MPEG
Close to the KM Role
Cursors And Fonts introduction
Source Editors Infomation
Infomation Computer Education
Shanghai real estate control POLICIES introduced Countdown
ASF to MPG
AVI to 3GP
Remodeling "value STREAM"
No comments:
Post a Comment