QUIESCE TABLESPACES FOR TABLE command

The QUIESCE TABLESPACES FOR TABLE command quiesces table spaces for a table. Placing table spaces in a quiesced state enables administrators to complete work that cannot be done safely under normal conditions.

There are three possible states resulting from the quiesce function:

Quiesced: SHARE
Quiesced: UPDATE
Quiesced: EXCLUSIVE

Scope

This command quiesces the table spaces used by a table in the desired state. 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 command is run. To quiesce the entire tablespace, this command must be run on all database partitions using db2_all or equivalent.

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.

Authorization

One of the following authorities:

Note: In Db2® 12.1.1 and later, if the table space is managed by automatic storage, then TBSPACEADM authority can be used on the storage group.

Required connection

Database

Command syntax

Command parameters

TABLE

tablename: Specifies the unqualified table name. The table cannot be a system catalog table.
schema.tablename: Specifies the qualified table name. If schema is not provided, the CURRENT SCHEMA will be used. The table cannot be a system catalog table.

SHARE

Specifies that the quiesce is to be in share mode.

When a "quiesce share" request is made, the transaction requests intent share (IS) locks for the table spaces and a share (S) 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.

An internal commit or rollback is issued to release locks on success or failure. On success, the table spaces for the table remain in QUIESCED SHARE state until the state is explicitly reset.

INTENT TO UPDATE

Specifies that the quiesce is to be in intent to update mode.

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. When the transaction obtains the locks, the state of the table spaces changes to QUIESCED INTENT TO UPDATE.

The table cannot be changed while its table spaces are in QUIESCED INTENT TO UPDATE state. Other share mode requests to the table and table spaces are allowed.

An internal commit or rollback is issued to release locks on success or failure. On success, the table spaces for the table remain in QUIESCED INTENT TO UPDATE state until the state is explicitly reset.

EXCLUSIVE

Specifies that the quiesce is to be in exclusive mode.

When a "quiesce exclusive" request is made, the transaction requests super exclusive (Z) locks on the table spaces, and a super exclusive (Z) 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.

An internal commit or rollback is issued to release locks on success or failure. On success, the table spaces for the table remain in QUIESCED EXCLUSIVE state until the state is explicitly reset.

RESET

Specifies that the quiesce state is to be reset. When a "quiesce reset" request is made, only the quiesce mode for that quiescer is reset. If there are multiple quiescers, then the state of the table space will appear unchanged. A quiesce state cannot be reset if the connection that issued the quiesce request is still active.

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.

Usage notes

This command is not supported in Db2 pureScale® environments. However, the db2dart tool can be used to quiesce a tablespace in SHARE mode, with restrictions.

The statement must be the first statement in a transaction.

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.

To remove a phantom quiesce:

Connect to the database with the same user ID used when the quiesce mode was set.
Use the LIST TABLESPACES command to determine which table space is quiesced.
Re-quiesce the table space using the current quiesce state. For example:
```
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 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.

When quiescing against a system-period temporal table, all the tables spaces associated with the system-period temporal table and the history table are quiesced. When quiescing against a history table, all the tables spaces associated with the history table, and the associated system-period temporal table are quiesced.

Examples

Putting a table (and tablespace) into read-only mode via QUIESCE SHARE.

The table (and related tablespaces) allow read-only access by any connection. This is commonly used to make a subset of a database read-only while allowing the remainder to be fully operational in read-write mode.

db2 quiesce tablespaces for table order_history share;
db2 quiesce tablespaces for table payment_history share;

Putting a table (and tablespace) into exclusive mode via QUIESCE EXCLUSIVE.

The table (and related tablespaces) allow exclusive access by this connection only. All other connections are blocked from any access to the table (and related tablespaces). This is commonly used in conjunction with third-party software to perform bulk load / unload operations.

db2 quiesce tablespaces for table orders exclusive;
// perform operation with third-party software
db2 quiesce tablespaces for table orders reset;