Quiesces table spaces for a table. There are three valid quiesce modes: share, intent to update, and exclusive.
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 a partitioned database environment, this command acts locally on a database partition. It quiesces only that portion of table spaces belonging to the database partition on which the load operation is performed. For partitioned tables, all of the table spaces listed in SYSDATAPARTITIONS.TBSPACEID and SYSDATAPARTITIONS.LONG_TBSPACEID associated with a table and with a status of normal, attached or detached, (for example, SYSDATAPARTITIONS.STATUS of '"', 'A' or 'D') are quiesced.
Database
>>-QUIESCE TABLESPACES FOR TABLE--+-tablename--------+----------> '-schema.tablename-' >--+-SHARE------------+---------------------------------------->< +-INTENT TO UPDATE-+ +-EXCLUSIVE--------+ '-RESET------------'
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.
When a quiescer issues a reset, only the quiesce mode for that quiescer is reset. If there are multiple quiescers, then the state of the table space will appear unchanged.
When working with a system-period temporal table and its associated history table, the reset operation must be performed on the same table that was used to originally set the quiesce mode.
Quiesce the table spaces containing the staff table.
CALL SYSPROC.ADMIN_CMD( 'quiesce tablespaces for table staff share' )
This command is not supported in DB2® pureScale® environments.
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. 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 quiesce exclusive state. Upon database restart, this quiesce would be an unowned (or phantom) quiesce. The removal of a phantom quiesce requires a connection with the same user ID used when the quiesce mode was set.
CALL SYSPROC.ADMIN_CMD('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 alter 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.
Command execution status is returned in the SQLCA resulting from the CALL statement.
When quiescing against a system-period temporal table, all the tables paces associated with the system-period temporal table and the history table are quiesced. When quiescing against a history table, all the tables paces associated with the history table, and the associated system-period temporal table are quiesced.