Quiesces table spaces for a table. There are three valid quiesce modes: share, intent to update, and exclusive. There are three possible states resulting from the quiesce function: QUIESCED SHARE, QUIESCED UPDATE, and QUIESCED EXCLUSIVE.
Scope
In a single-node environment, this command quiesces all table spaces involved in a load operation in exclusive mode for the duration of the load. In an MPP environment, this command acts locally on a node. It quiesces only that portion of table spaces belonging to the node on which the load is performed.
Authorization
One of the following:
Required Connection
Database
Command Syntax
>>-QUIESCE TABLESPACES FOR TABLE----+-tablename--------+-------->
'-schema.tablename-'
>-----+-SHARE------------+-------------------------------------><
+-INTENT TO UPDATE-+
+-EXCLUSIVE--------+
'-RESET------------'
|
Command Parameters
Examples
db2 quiesce tablespaces for table staff share db2 quiesce tablespaces for table boss.org intent to update
Usage Notes
A quiesce is a persistent lock. Its benefit is that is persists across transaction failures, connection failures, and even across system failures (such as power failure, or reboot).
A quiesce is owned by a connection. If the connection holding a table space quiesce is lost prior to the quiesce being removed, the quiesce remains on the table space, but it moves into a state of non-ownership, and is called a phantom quiesce. A phantom quiesce becomes "owned" by the next connection that issues the QUIESCE TABLESPACES FOR TABLE command against the same table spaces or table with the current quiesce mode. For example, if a power outage caused a load operation to be interrupted during the DELETE PHASE, the table spaces for the loaded table would be left in DELETE PENDING, QUIESCE EXCLUSIVE state. Upon database restart, the quiesces on the affected table space would be unowned (or phantom) quiesces. The user, wishing to restart the interrupted load operation, first issues a QUIESCE command for table <LOAD table name> EXCLUSIVE, establishing this connection as the owner of the quiesce. At this point, the load can be restarted.
When the quiesce share request is received, the transaction requests intent share locks for the table spaces and a share lock for the table. When the transaction obtains the locks, the state of the table spaces is changed to QUIESCED SHARE. The state is granted to the quiescer only if there is no conflicting state held by other users. The state of the table spaces is recorded in the table space table, along with the authorization ID and the database agent ID of the quiescer, so that the state is persistent.
The table cannot be changed while the table spaces for the table are in QUIESCED SHARE state. Other share mode requests to the table and table spaces will be allowed. When the transaction commits or rolls back, the locks are released, but the table spaces for the table remain in QUIESCED SHARE state until the state is explicitly reset.
When the quiesce exclusive request is made, the transaction requests super exclusive locks on the table spaces, and a super exclusive lock on the table. When the transaction obtains the locks, the state of the table spaces changes to QUIESCED EXCLUSIVE. The state of the table spaces, along with the authorization ID and the database agent ID of the quiescer, are recorded in the table space table. Since the table spaces are held in super exclusive mode, no other access to the table spaces is allowed. The user who invokes the quiesce function (the quiescer), however, has exclusive access to the table and the table spaces.
When a quiesce update request is made, the table spaces are locked in intent exclusive (IX) mode, and the table is locked in update (U) mode. The state of the table spaces with the quiescer is recorded in the table space table.
There is a limit of five quiescers on a table space at any given time. Since QUIESCED EXCLUSIVE is incompatible with any other state, and QUIESCED UPDATE is incompatible with another QUIESCED UPDATE, the five quiescer limit, if reached, must have at least four QUIESCED SHARE and at most one QUIESCED UPDATE.
A quiescer can upgrade the state of a table space from a less restrictive state to a more restrictive one (for example, S to U, or U to X). If a user requests a state lower than one that is already held, the original state is returned. States are not downgraded.
See Also
LOAD.