DB2 Version 9.7 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 (SQL1776N Reason Code 4). 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 reclaim reorg
Load
Bind or rebind
db2rbind
Runstats
Table move
Auto statistics
Auto reorg
Real Time Statistics

Other operation 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 block on insert into MDC table
Asynchronous background processes updating catalog tables SYSJOBS and SYSTASKS for inserting, updating, and deleting tasks

Monitoring the replay-only window

To monitor a replay-only window on an active standby, use the db2pd command with the -hadr option. In the example, below the three pertinent elements are:

ReplayOnlyWindowStatus, which indicates whether DDL or maintenance-operation replay is in progress on the standby. Normally, the value is "Inactive", but when the replay-only window is active, the value is "Active".
ReplayWindowStartTime, which indicates the time at which the current replay-only window (if there is one) became active.
MaintenanceTxCount or DDLTxCount, 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).

db2pd -db hadrdb -hadr
Database Partition 0 -- Database HADRDB -- Active -- Up 0 days 00:00:06

HADR Information:
Role    State		SyncMode HeartBeatsMissed   LogGapRunAvg (bytes)
Standby Peer		Nearsync 0                  0                   

ConnectStatus ConnectTime                           Timeout   
Connected     Sat Jun 15 03:09:35 2008           120       

ReplayOnlyWindowStatus       ReplayOnlyWindowStartTime           MaintenanceTxCount
Active                       Sun Jun 16 08:09:35 2008            5                       

LocalHost		LocalService      
skua     		52601             

RemoteHost		RemoteService		RemoteInstance    
gull      		52600        		vinci             

PrimaryFile  PrimaryPg  PrimaryLSN        
S0000000.LOG 1          0x000000000137126F

StandByFile  StandByPg  StandByLSN        
S0000000.LOG 0          0x000000000137092E

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.