DB2 10.5 for Linux, UNIX, and Windows

Replay-only window on the active standby database

When an HADR active standby database is replaying DDL log records or maintenance operations, the standby enters the replay-only window. When the standby is in the replay-only window, existing connections to the standby are terminated and new connections to the standby are blocked (SQL1224N). New connections are allowed on the standby after the replay of all active DDL or maintenance operations has completed.

The only user connections that can remain active on a standby in the replay-only window are connections that are executing DEACTIVATE DATABASE or TAKEOVER commands. When applications are forced off at the outset of the replay-only window, an error is returned (SQL1224N). Depending on the number of readers connected to the active standby, there may be a slight delay before the DDL log records or maintenance operations are replayed on the standby.

There are a number of DDL statements and maintenance operations that, when run on the HADR primary, will trigger a replay-only window on the standby. The following lists are not exhaustive.

DDL statements

CREATE, ALTER, or DROP TABLE (except DROP TABLE for DGTT)
CREATE GLOBAL TEMP TABLE
TRUNCATE TABLE
RENAME TABLE
RENAME TABLESPACE
CREATE, DROP, or ALTER INDEX
CREATE or DROP VIEW
CREATE, ALTER, or DROP TABLESPACE
CREATE, ALTER, or DROP BUFFER POOL
CREATE, ALTER, or DROP FUNCTION
CREATE, ALTER, or DROP PROCEDURE
CREATE or DROP TRIGGER
CREATE, ALTER, or DROP TYPE
CREATE, ALTER, or DROP ALIAS
CREATE or DROP SCHEMA
CREATE, ALTER, or DROP METHOD
CREATE, ALTER, or DROP MODULE
CREATE, ALTER, or DROP NICKNAME
CREATE, ALTER, or DROP SEQUENCE
CREATE, ALTER, or DROP WRAPPER
CREATE, ALTER, or DROP FUNCTION MAPPING
CREATE or DROP INDEX EXTENSION
CREATE or DROP INDEX FOR TEXT
CREATE or DROP EVENT MONITOR
CREATE, ALTER, or DROP SECURITY LABEL
CREATE, ALTER, or DROP SECURITY LABEL COMPONENT
CREATE, ALTER, or DROP SECURITY POLICY
CREATE or DROP TRANSFORM
CREATE, ALTER, or DROP TYPE MAPPING
CREATE, ALTER, or DROP USER MAPPING
CREATE or DROP VARIABLE
CREATE, ALTER, or DROP WORKLOAD
GRANT USAGE ON WORKLOAD
REVOKE USAGE ON WORKLOAD
CREATE, ALTER, or DROP SERVICE CLASS
CREATE, ALTER, or DROP WORK CLASS SET
CREATE, ALTER, or DROP WORK ACTION SET
CREATE, ALTER, or DROP THRESHOLD
CREATE, ALTER, or DROP HISTOGRAM TEMPLATE
AUDIT
CREATE, ALTER, or DROP AUDIT POLICY
CREATE or DROP ROLE
CREATE, ALTER, or DROP TRUSTED CONTEXT
REFRESH TABLE
SET INTEGRITY

Maintenance operations

Classic, or offline, reorg
Inplace, or online, reorg
Index reorg (indexes all, individual index)
MDC and ITC reclaim reorg
Load
Bind or rebind
db2rbind
Runstats
Table move
Auto statistics
Auto reorg
Real Time Statistics

Other operations or actions

Automatic Dictionary Creation for tables with COMPRESS YES attribute
Asynchronous Index Cleanup on detached table partition
Implicit rebind
Implicit index rebuild
Manual update of statistics.
Deferred MDC rollout
Asynchronous Index cleanup after MDC rollout
Reuse of a deleted MDC or ITC block on insert into MDC or ITC table
Asynchronous background processes updating catalog tables SYSJOBS and SYSTASKS for inserting, updating, and deleting tasks

Monitoring the replay-only window

You can monitor the replay-only window using the db2pd command with the -hadr option (on either the standby or the primary) or the MON_GET_HADR table function (from the primary). The standby's status, including information about the replay-only window, is sent to the primary on every heartbeat.

There are three pertinent elements to monitor:

STANDBY_REPLAY_ONLY_WINDOW_ACTIVE, which indicates whether DDL or maintenance-operation replay is in progress on the standby. Normally, the value is N, but when the replay-only window is active, the value is Y.
STANDBY_REPLAY_ONLY_WINDOW_START, which indicates the time at which the current replay-only window (if there is one) became active.
STANDBY_REPLAY_ONLY_WINDOW_TRAN_COUNT, which indicates the total number of existing uncommitted DDL or maintenance transactions executed so far in the current replay-only window (if there is one).

To use the table function, issue something similar to the following query on the primary:

select STANDBY_ID, STANDBY_REPLAY_ONLY_WINDOW_ACTIVE, STANDBY_REPLAY_ONLY_WINDOW_START,
	STANDBY_REPLAY_ONLY_WINDOW_TRAN_COUNT from table (mon_get_hadr(NULL))

Here is an example using the db2pd command on a standby that is currently in a replay-only window:

Database Member 0 -- Database HADRDB -- Active -- Up 0 days 00:23:17 -- Date 06/08/2011 13:57:23

                              HADR_ROLE = STANDBY
                            REPLAY_TYPE = PHYSICAL
                          HADR_SYNCMODE = NEARSYNC
                             STANDBY_ID = 1
                          LOG_STREAM_ID = 0
                             HADR_STATE = PEER
                             HADR_FLAGS = 
                    PRIMARY_MEMBER_HOST = hostP.ibm.com
                       PRIMARY_INSTANCE = db2inst
                         PRIMARY_MEMBER = 0
                    STANDBY_MEMBER_HOST = hostS1.ibm.com
                       STANDBY_INSTANCE = db2inst
                         STANDBY_MEMBER = 0
                    HADR_CONNECT_STATUS = CONNECTED
               HADR_CONNECT_STATUS_TIME = 06/08/2011 13:38:10.199479 (1307565490)
            HEARTBEAT_INTERVAL(seconds) = 25
                  HADR_TIMEOUT(seconds) = 120
          TIME_SINCE_LAST_RECV(seconds) = 3
               PEER_WAIT_LIMIT(seconds) = 0
             LOG_HADR_WAIT_CUR(seconds) = 0.000
      LOG_HADR_WAIT_RECENT_AVG(seconds) = 0.006298
     LOG_HADR_WAIT_ACCUMULATED(seconds) = 0.516
                    LOG_HADR_WAIT_COUNT = 82
  SOCK_SEND_BUF_REQUESTED,ACTUAL(bytes) = 0, 50772
  SOCK_RECV_BUF_REQUESTED,ACTUAL(bytes) = 0, 87616
              PRIMARY_LOG_FILE,PAGE,POS = S0000009.LOG, 1, 49262315
              STANDBY_LOG_FILE,PAGE,POS = S0000009.LOG, 1, 49262315
                    HADR_LOG_GAP(bytes) = 0
       STANDBY_REPLAY_LOG_FILE,PAGE,POS = S0000009.LOG, 1, 49262315
         STANDBY_RECV_REPLAY_GAP(bytes) = 0
                       PRIMARY_LOG_TIME = 06/08/2011 13:49:19.000000 (1307566159)
                       STANDBY_LOG_TIME = 06/08/2011 13:49:19.000000 (1307566159)
                STANDBY_REPLAY_LOG_TIME = 06/08/2011 13:49:19.000000 (1307566159)
           STANDBY_RECV_BUF_SIZE(pages) = 16
               STANDBY_RECV_BUF_PERCENT = 0
             STANDBY_SPOOL_LIMIT(pages) = 0
                  STANDBY_SPOOL_PERCENT = 0
                   PEER_WINDOW(seconds) = 0
               READS_ON_STANDBY_ENABLED = Y
      STANDBY_REPLAY_ONLY_WINDOW_ACTIVE = Y
       STANDBY_REPLAY_ONLY_WINDOW_START = 06/08/2011 13:50:23
  STANDBY_REPLAY_ONLY_WINDOW_TRAN_COUNT = 5

Recommendations for minimizing the impact of the replay-only window

Because replay operations on an HADR standby take priority over readers, frequent read-only windows can be disruptive to readers connected to or attempting to connect to the standby. To avoid or minimize this impact, consider the following recommendations:

Run DDL and maintenance operations during a scheduled maintenance window, preferably at off-peak hours.
Run DDL operations collectively rather than in multiple groups.
Run REORG or RUNSTATS only on the required tables instead of all tables.
Terminate applications on the active standby using the FORCE APPLICATION command with the ALL option before running the DDL or maintenance operations on the primary. Monitor the replay-only window to determine when it is inactive, and redeploy the applications on the standby.