Troubleshooting
Problem
Starting in Version 11.1 Mod Pack 3 Fix Pack 3 (v11.1.3.3), issues requiring the triage of db2 transaction log file information can be more easily formatted using the db2fmtlog tool, available in the
Resolving The Problem
db2fmtlog – Format and display log file information tool
The db2fmtlog tool can be used to extract and display information from transaction log files. Various types of information can be displayed or inferred, including, but not limited to:
- Whether a log file is encrypted or compressed.
- The log chain that a log file is associated to.
- The range of log records in a log file.
- The log records in a log file which will invoke a replay-only window if replayed on an HADR Standby database enabled with Reads On Standby (ROS).
This tool is available starting in Db2 Version 11.1 Mod Pack 3 Fix Pack 3 (v11.1.3.3).
Authorization
Anyone can run the command, but you must have read permission on the archive log files.
Required connection
None
Command syntax
>>-db2fmtlog---+------------------+---+---------+--------------->
| Keystore options | | Options |
>--+-log_file_number_start-+----------------------+------------->
'--log_file_number_end-'
Keystore options
>--+------------------------------------+----------------------->
+--kspassword--keystore-password-----+
+--kspassarg--+-fd:file_descriptor-+-+
'-filename:filepath--'
Options
>--+-------------------+---------------------------------------->
'--lsnrange---------'
'--replayonlywindow-'
Command parameters
log_file_number_start
Specifies the numeric identifier of the log file to format. For example, the numeric identifier of the log file S0000017.LOG log file is 17. If the -log_file_number_end parameter is also specified, then log_file_number_start represents the first numeric identifier in a range of log files to format.
log_file_number_end
Specifies the last numeric identifier in a range of log files to format. The value of log_file_number_End must be greater than or equal to log_file_number_start.
KEYSTORE OPTONS
Specification of keystore password information is only required for encrypted log files when the keystore password is not stashed.
-kspassword keystore-password
Specifies the password to use when opening the keystore.
-kspassarg fd:file_descriptor | filename:filepath
Specifies the keystore password arguments. The file_descriptor parameter specifies a file descriptor that identifies an open and readable file or pipe that contains the password to use. The filepath parameter specifies the path of the file that contains the password to use.
OPTIONS
If no option is specified
Displays basic information from the log file(s).
-lsnrange
Displays a list of the range of LFS, LSN, and LSO values in the log file(s).
-replayonlywindow
Displays each log record which can cause a replay-only window when replayed on an HADR Standby database with Reads On Standby enabled.
If the log file(s) are encrypted, then this tool must be executed from an environment where decryption is possible. This includes access to the native encryption library, and either a stashed keystore password file must be available or a valid keystore password option must be specified.
Examples
The following example shows the encrypted log file S0000003.LOG formatted with no options.
$ db2fmtlog 3
Log File S0000003.LOG:
Extent Number 3
Format Version 14
Architecture Level Version V:11 R:1 M:3 F:3 I:0 SB:0
Encrypted Yes
Compression Mode OFF
Number of Pages 4
Partition 0
Log Stream 0
Database Seed 3136515636
Log File Chain ID 0
Master Key Label DB2_SYSGEN_db2admin1_ENCDB_2018-02-06-17.37.48_35002821
Previous Extent ID 2018-02-06-22.39.38.000000 GMT
Current Extent ID 2018-02-06-22.39.39.000000 GMT
Database log ID 2018-02-06-22.37.49.000000 GMT
Topology Life ID 2018-02-06-22.37.49.000000 GMT
First LFS/LSN 4173/000000000003F5AF
Last LFS/LSN 4185/000000000003F637
LSO range 44884913 to 44901216
The following example shows the encrypted log files S0000003.LOG to S0000009.LOG are formatted with -lsnrange option.
$ db2fmtlog -lsnrange 3-9
S0000003.LOG: First LFS/LSN 4173/000000000003F5AF Last LFS/LSN 4185/000000000003F637 LSO range 44884913 to 44901216
S0000004.LOG: First LFS/LSN 4185/000000000003F638 Last LFS/LSN 4200/000000000003F6E9 LSO range 44901217 to 44917520
S0000005.LOG: First LFS/LSN 4200/000000000003F6EA Last LFS/LSN 4218/000000000003F76F LSO range 44917521 to 44933824
S0000006.LOG: First LFS/LSN 4218/000000000003F770 Last LFS/LSN 4236/000000000003F795 LSO range 44933825 to 44946052
S0000007.LOG: First LFS/LSN 4237/000000000003F796 Last LFS/LSN 4237/000000000003F796 LSO range 44950129 to 44954204
S0000008.LOG: First LFS/LSN 4238/000000000003F797 Last LFS/LSN 4238/000000000003F797 LSO range 44966433 to 44970508
S0000009.LOG: First LFS/LSN 4239/000000000003F798 Last LFS/LSN 4247/000000000003F81A LSO range 44982737 to 44999040
The following example shows the unencrypted log file S0000004.LOG is formatted with -replayonlywindow option.
$ ls S0000004.LOG | db2fmtlog -replayonlywindow
|------|------------------------------------------------------------------------
| LREC | 4812 0003F9E6 000000000197
|------|------------------------------------------------------------------------
| LREC | Record LSO = 53456514
| | Record TID = 000000000197
| | Action = DDL
|------|------------------------------------------------------------------------
| LREC | 4812 0003F9E7 000000000197
|------|------------------------------------------------------------------------
| LREC | Record LSO = 53456942
| | Record TID = 000000000197
| | DDL Statement = create table t5ba.t1 (a int, b long varchar) in t1
|------|------------------------------------------------------------------------
| LREC | 4828 0003FA6F 0000000001CF
|------|------------------------------------------------------------------------
| LREC | Record LSO = 53467006
| | Record TID = 0000000001CF
| | Action = LOAD
|------|------------------------------------------------------------------------
| LREC | 4832 0003FA76 0000000001D0
|------|------------------------------------------------------------------------
| LREC | Record LSO = 53473142
| | Record TID = 0000000001D0
| | Action = LOAD
|------|------------------------------------------------------------------------
| LREC | 4839 0003FAD5 0000000001E9
|------|------------------------------------------------------------------------
| LREC | Record LSO = 53489660
| | Record TID = 0000000001E9
| | Action = DDL
The following example shows the formatting of an encrypted log file using -replayonlywindow option, but without a keystore password (and no stashed keystore password file is available). Once an error is encountered, the db2fmtlog command will skip the current log file and resume processing in the next log file.
$ db2fmtlog -replayonlywindow 4
Could not retrieve DEK for log file "S0000004.LOG".
Reason code: -2141452066, sqlcode: -1728.
Processing will resume in the next log file.
The following example shows how formatting an encrypted log file using -replayonlywindow option with a keystore password specified:
$ db2fmtlog -replayonlywindow 4 -kspassword This8Is0Password
|------|------------------------------------------------------------------------
| LREC | 4193 0003F6AB 000000000171
|------|------------------------------------------------------------------------
| LREC | Record LSO = 44911967
| | Record TID = 000000000171
| | Action = DDL
Related reference
1. Log record header: https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.apdv.api.doc/doc/r0024864.html
2. Creating a local keystore: https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.admin.sec.doc/doc/t0062028.html
3. Replay-only window on active standby database: https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.admin.ha.doc/doc/c0056190.html
4. HADR reads on standby feature: https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.admin.ha.doc/doc/c0054257.html
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg22014054