Scan logs files to report log records that can trigger replay only window

on HADR Standby with db2fmtlog_replayonlywindow

Introduction

db2fmtlog_replayonlywindow is a tool that scans DB2 LUW transaction log files (such as S0000001.LOG) to report log records that can trigger replay only window on a HADR Standby with Reads On Standby (ROS) feature enabled. Reads on Standby (ROS) feature allows user to run read-only operations on a HADR Standby database as long as it's not in replay only window. When replay only window does happen, all applications performing read-only operations will be forced off. Therefore, it's vital to understand what operations could trigger replay only window and plan them accordingly. db2fmtlog_replayonlywindow provides necessary information to help user identify such operations from their existing workload.

Download - Db2 v11.1.3.3+

As of Db2 v11.1.3.3, the db2fmtlog_replayonlywindow tool is now part of the new db2fmtlog tool shipped with the product. The functionality of db2fmtlog's "-replayonlywindow" option is exactly the same as the db2fmtlog_replayonlywindow tool. The new db2fmtlog tool is able to run in Linux, AIX, and Windows environment. For details, please see the Knowledge Center .

Download - Older releases

db2fmtlog_replayonlywindow is a stand-alone tool. You only need to download a single binary executable. It has no dependency on DB2 installation. Download from below:

Note:

1. The OS version listed above is the version where the binary is built. Generally, the binary can also run on newer OS versions, but not older versions.
2. The Db2 version listed above is the library used to compile the binary. It's usually fine to use the tool for the logs generated by Db2 instance from the same major release (ie. the tool built in 10.5.0.9 should work for 10.5.0.8).

If you need the the tool on other platforms or Db2 releases, email db2hadr@wwpdl.vnet.ibm.com.

Syntax

Run the db2fmtlog_replayonlywindow tool in the directory containing the log files.

db2fmtlog_replayonlywindow log-file-range [output-file]

Specifying Log Files to format
To format a single log file, specify the log file number. For example,
the log file number of S0000123.LOG is 123.

db2fmtlog 123

To format a range of log files, specify the first and last log file
number.

db2fmtlog 123-150

To format log files by path name, pass - as the range of log files to
format. The path names will be read from standard input. The following
example will format all the log files in the current directory.

\ls *.LOG | db2fmtlog -

If no output-file is specified, standard out will be used.

Sample Output

|------|------------------------------------------------------------------------
| LREC | 10744  0004DF69  0000000011C7
|------|------------------------------------------------------------------------
| LREC |                     Record LSO = 51259777
|      |                     Record TID = 0000000011C7
|      |                         Action = DDL
|------|------------------------------------------------------------------------
| LREC | 10744  0004DF6A  0000000011C7
|------|------------------------------------------------------------------------
| LREC |                     Record LSO = 51260205
|      |                     Record TID = 0000000011C7
|      |                  DDL Statement = create table t5ba.t1 (a int, b long varchar)
|------|------------------------------------------------------------------------
| LREC | 10765  0004DFFC  0000000011CB
|------|------------------------------------------------------------------------
| LREC |                     Record LSO = 51272765
|      |                     Record TID = 0000000011CB
|      |                         Action = LOAD
|------|------------------------------------------------------------------------
| LREC | 10770  0004E003  0000000011CC
|------|------------------------------------------------------------------------
| LREC |                     Record LSO = 51278901
|      |                     Record TID = 0000000011CC
|      |                         Action = LOAD
|------|------------------------------------------------------------------------
| LREC | 10915  0004E210  000000001532
|------|------------------------------------------------------------------------
| LREC |                     Record LSO = 51440438
|      |                     Record TID = 000000001532
|      |                         Action = DDL
|------|------------------------------------------------------------------------
| LREC | 11131  0004E3FE  00000000163A
|------|------------------------------------------------------------------------
| LREC |                     Record LSO = 51593237
|      |                     Record TID = 00000000163A
|      |                         Action = DDL
|------|------------------------------------------------------------------------
| LREC | 11131  0004E3FF  00000000163A
|------|------------------------------------------------------------------------
| LREC |                     Record LSO = 51593665
|      |                     Record TID = 00000000163A
|      |                  DDL Statement = truncate table t5ba.t1
|------|------------------------------------------------------------------------