DB2 Version 10.1 for Linux, UNIX, and Windows

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

Read syntax diagramSkip visual syntax diagram
                                            .-INCLUDE LOGS-.     
>>-SET WRITE--+-SUSPEND--FOR--+-DATABASE-+--+--------------+-+-><
              |               '-DB-------'  '-EXCLUDE LOGS-' |   
              '-RESUME--FOR--+-DATABASE-+--------------------'   
                             '-DB-------'                        

Command Parameters

SUSPEND
Suspends the I/O write operations, Start of changesuch as writing to the logs, extending a table, and any subsequent I/O write actions/functions.End of change 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.

Start of changeThe INCLUDE LOGS and EXCLUDE LOGS options are available in DB2 Version 10.1 Fix Pack 2 and later fix packs.End of change

Usage notes

Start of changeStarting in DB2 V10.1, certain commands like db2 list tablespaces show detail but 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.End of change

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. Note that REBAL_IN_PROGRESS is another state that snapshot monitor might report when database is suspended.