SET WRITE command

The SET WRITE command allows a user to suspend I/O writes or to resume I/O writes 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 on which it is executed.

Authorization

Required Connection

Database

Command Syntax

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

Command Parameters

SUSPEND

Suspending I/O writes will put all table spaces into a new state SUSPEND_WRITE state. Suspends 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, should function normally while database writes are suspended. However, some operations can wait while attempting to flush dirty pages from the buffer pool or log buffers to the logs. These operations will resume normally once the database writes are resumed.

RESUME

Resuming I/O writes will remove the SUSPEND_WRITE state from all of the table spaces and make the table spaces available for update.

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 9.7 Fix Pack 9 and later fix packs.

Usage notes

It is suggested that I/O writes be resumed from the same connection from which they were suspended. Ensuring that this connection is available to resume I/O writes involves not performing any operations from this connection until database writes are resumed. Otherwise, some operations can wait for I/O writes to be resumed if dirty pages must be flushed from the buffer pool or from log buffers to the logs. Furthermore, subsequent connection attempts might hang if they require flushing dirty pages from the buffer pool to disk. Subsequent connections will complete successfully once database I/O resumes. If your connection attempts are hanging, and it has become impossible to resume I/O from the connection that you used to suspend I/O, then you will have to run the RESTART DATABASE command with the WRITE RESUME option. When used in this circumstance, the RESTART DATABASE command will resume I/O writes without performing crash recovery. The RESTART DATABASE command with the WRITE RESUME option will only perform crash recovery when you use it 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.