SET WRITE command

The SET WRITE command allows a user to suspend I/O write operations or to resume I/O write operations for a database. Typical use of this command is for splitting a mirrored database. This type of mirroring is achieved through a disk storage system.

This new state, SUSPEND_WRITE, is visible from the Snapshot Monitor. This state guarantees that the existing write operations are completed and no new write operations can be performed. All table spaces need not be in NORMAL state for the command to execute successfully.

Scope

This command only affects the database partition where the command is issued. In partitioned database environments, you must issue it on all the database partitions. In Db2® pureScale® environments, you can issue it from any member to suspend I/O write operations for all the members, or to resume I/O write operations for all the suspended members.

Authorization

The authorization of this command requires the issuer to have one of the following privileges:

SYSADM
SYSCTRL
SYSMAINT

Required Connection

Database

Command Syntax

Command Parameters

SUSPEND: Suspends the I/O write operations, such as writing to the logs, extending a table, and any subsequent I/O write actions/functions. All database operations, apart from online backup and restore, function normally while I/O write operations are suspended. However, some operations might wait while attempting to flush dirty pages from the buffer pool or log buffers to the logs. These operations continue after you resume the I/O write operations for the database.
RESUME: Resumes the I/O write operations. In Db2 pureScale environments, this parameter resumes the I/O write operations for all suspended members.
INCLUDE LOGS: Specifies that writes to the log files are not allowed when the database is in a write-suspended state. This is the default.
EXCLUDE LOGS: Specifies that writes to the log files (but not to log file header and mirror log file header files) can occur when the database is in a write-suspended state. This provides a window during which update transactions running against the database can still complete. This can help to reduce the impact on the workload that would normally occur while the database is write suspended. Any copies of the database that are taken while it is write suspended and the EXCLUDE LOGS option is specified must not include log files in the copy.
Note: There are some situations in which logged operations can still be blocked from proceeding. This can happen, for example, if the current active log file is full.

Usage notes

Starting in Db2 V10.1, certain commands like db2 list tablespaces show detail might hang during a set write suspend. This hang is an expected behavior, and is due to changes to the Db2 latching protocol between V9.7 and V10.1; which is used to get the most up to date used page data. Since the Db2 V9.7 db2 list tablespaces command is deprecated, it is suggested that you use the db2pd -d dbname -tablespaces command to retrieve the same information.

You can determine whether the I/O write operations are suspended by viewing the setting of the suspend_io database configuration parameter. To view database configuration information, you can use the GET DATABASE CONFIGURATION command, the DBCFG administrative view, the db2pd command with the -dbcfg parameter, or the db2CfgGet API.

You can use the FLUSH BUFFERPOOLS statement before using the SET WRITE command to minimize the recovery time of a split-mirror database. Issuing the statement can be useful if you plan on using a split-mirror database as a backup image or if you plan to create a backup image using the split-mirror database.

A connection attempt will fail if dirty pages must be flushed from the buffer pool to disk but it is impossible to resume I/O write operations by using the SET WRITE command. To resolve the connection failure, issue the RESTART DATABASE command with the WRITE RESUME parameter. In this scenario, the RESTART DATABASE command resumes write operations without performing crash recovery. The RESTART DATABASE command with the WRITE RESUME parameter performs crash recovery only when you use the command after a database crash.

The table spaces can be in transient states such as SQLB_MOVE_IN_PROGRESS or SQLB_BACKUP_IN_PROGRESS for this command to succeed.

In High Availability Disaster Recovery (HADR) environments, the SET WRITE SUSPEND operation is only supported on the HADR primary database. Attempting to perform a SET WRITE SUSPEND operation on a standby database will result in an SQL1550N error.

Note: Blocked write-to-table event monitors must be explicitly disabled before issuing the SET WRITE SUSPEND command, and re-enabled when write operations are resumed. If a blocked event monitor remains active while write operations are suspended, delays can occur because the event monitor cannot write records to disk. For example, a new connection attempt is delayed if a blocked write-to-table connection event monitor is left active under the following conditions:

While write operations are suspended.
While the volume of incoming connections is enough to fill all available space in the event monitor buffers.