SET WRITE command

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

Required Connection

Database

Command Syntax

                                            .-INCLUDE LOGS-.     
>>-SET WRITE--+-SUSPEND--FOR--+-DATABASE-+--+--------------+-+-><
              |               '-DB-------'  '-EXCLUDE LOGS-' |   
              '-RESUME--FOR--+-DATABASE-+--------------------'   
                             '-DB-------'                        

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.

The INCLUDE LOGS and EXCLUDE LOGS options are available in DB2 Version 10.1 Fix Pack 2 and later fix packs.

Usage notes

Starting 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.

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.