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-partition environment, this command quiesces all table spaces involved in a load operation in exclusive mode for the duration of the load operation. 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 operation 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
When a "quiesce share" request is made, 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, along with the authorization ID and the database agent ID of the quiescer, are recorded in the table space table, 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 are 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 a "quiesce intent to 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 is recorded in the table space table.
When a "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) has exclusive access to the table and the table spaces.
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 it 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 is lost, the quiesce remains, but it has no owner, 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. 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, this quiesce would be an unowned (or phantom) quiesce.
To remove a phantom quiesce:
db2 quiesce tablespaces for table mytable exclusive
Once completed, the new connection owns the quiesce, and the load operation can be restarted.
There is a limit of five quiescers on a table space at any given time.
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.