- David Sciaraffa, Software Development Manager – IBM Db2
In high performance database (and data storage) systems, the hardening of data changes to permanent storage (disk) is often managed by algorithms which batch many changes together into fewer larger I/O operations. This is meant to minimize the exponential cost disparity between cpu/memory versus I/O, to optimize performance.
Db2 write-ahead logging strategy
In order to achieve durability (in the case of a system failure) for data changes which reside in-memory but have not yet been written to permanent storage, many systems utilizes a write-ahead logging protocol – meaning that information pertaining to a data change is written to special transaction log files on permanent storage, before those corresponding data changes are batched together and written to disk. Examples of write-head logging protocols are abundant in mainstream IT systems, such as in journaled file systems, volume managers, and database management systems, such as Db2.
Db2 recovery log files
The Db2 recovery log files are a fundamental aspect of database tuning and administration, and all Database Administrators should have basic familiarity with log files. Db2’s recovery logs are well documented in both configuration and management, a few useful starting points provided here:
IBM DB2 Knowledge Center – Database Logging: https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.admin.ha.doc/doc/c0006073.html
Managing Db2 transaction log files: http://db2commerce.com/2011/05/04/managing-db2-transaction-log-files/
Administration tasks requiring log files
It’s not uncommon for a Database Administrator to perform some manual manipulation of Db2 recovery log files, usually the result of a support ticket, or recovery event. As an example, a DBA might be required to restore a database backup image to a secondary system, and perform a rollforward of all log file data to a point in time, in which case a DBA may need to manually copy active and/or archived recovery log files to another system.
A log file’s name does not tell us much about the log file itself. For example, consider log file “S0000117.LOG”. We know this is the 117th log file generated in sequence since the database was created, but not much else. Sometimes, high level information about log files is required, such as knowing the database which a log file belongs to, whether the log file is compressed or encrypted, or other details often useful for Db2 customer support in problem determination.
Starting in version 11.1.3.3, the db2fmtlog tool is available in the ~/sqllib/bin folder of the db2 instance owner userid home path.
db2fmtlog tool
The db2fmtlog tool takes a log file sequence number (or range of numbers) as an input parameter, and displays high level information about the log file(s):
$ cd ~/path_where_some_log_files_reside/
$ ls -l
-rw------- 1 db2inst1 db2grp1 4104192 Apr 14 13:46 S0000117.LOG
-rw------- 1 db2inst1 db2grp1 4104192 Apr 14 13:46 S0000118.LOG
-rw------- 1 db2inst1 db2grp1 4104192 Apr 14 13:48 S0000119.LOG
-rw------- 1 db2inst1 db2grp1 4104192 Apr 14 13:52 S0000120.LOG
-rw------- 1 db2inst1 db2grp1 4104192 Apr 14 14:01 S0000121.LOG
…etc…
$ /home/db2inst1/sqllib/bin/db2fmtlog 117-118Log File S0000117.LOG:
Extent Number 117
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 1000
Partition 0
Log Stream 0
Database Seed 1610235396
Log File Chain ID 0
Master Key Label DB2_SYSGEN_db2inst1__2018-04-16-09.31.20_24111712
Previous Extent ID 2018-04-14-17.01.44.000000 GMT
Current Extent ID 2018-04-14-17.01.45.000000 GMT
Database log ID 2018-04-05-16.35.08.000000 GMT
Topology Life ID 2018-04-05-16.35.08.000000 GMT
First LFS/LSN 10400/000000000004700A
Last LFS/LSN 10594/000000000004D99F
LSO range 61140001 to 65216000
Log File S0000118.LOG:
Extent Number 118
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 1000
Partition 0
Log Stream 0
Database Seed 1610235396
Log File Chain ID 0
Master Key Label DB2_SYSGEN_db2inst1__2018-04-16-09.31.20_24111712
Previous Extent ID 2018-04-14-17.01.45.000000 GMT
Current Extent ID 2018-04-14-17.01.46.000000 GMT
Database log ID 2018-04-05-16.35.08.000000 GMT
Topology Life ID 2018-04-05-16.35.08.000000 GMT
First LFS/LSN 10594/000000000004D9A0
Last LFS/LSN Unset
LSO range 65216001 to 69292000
The information displayed contains the following fields:
Extent Number –
The log file sequence number, which is also evident within the log file name.
Format Version –
An [internal] log file structure version number.
Architecture Level Version –
The version of Db2 when the log file was generated (V=Version, R=Release, M=Modification, F=Fixpack, I=iFix, SB=Special Build#)
Encrypted –
Whether the log file (and database) are natively encrypted. (See ‘Master Key Label’ for the associated key label).
Compression Mode –
Whether the log file is compressed (possible values are: OFF, ON, NX842, ZLIB, Unknown).
Number of Pages –
The number of 4k log pages within the log file. This will usually reflect the LOGFILSZ db config parameter, but can be smaller when log files are truncated.
Partition –
The database partition number where this log file was generated. (Always 0 for non-partitioned databases).
Log Stream –
The log stream number associated with the member where this log file was generated. (Always 0 for non-pureScale databases).
Database Seed –
The unique database identifier. (You can use db2ckbkp to display the database seed of a backup image).
Log File Chain ID –
The log chain number. (Db2 will begin a new log chain when a restore+rollforward is completed to a point in time that is not the end of the full log stream).
Master Key Label –
The master key label. (Displayed when a database, and it’s corresponding log files, are natively encrypted).
Previous Extent ID –
A unique timestamp identifier for the previous log file in the sequence.
Current Extent ID –
A unique timestamp identifier for the current log file.
Database log ID –
A unique timestamp identifier for the database from which this log file was generated.
Topology Life ID –
A unique timestamp representing the database topology (formation of pureScale members) when this log file was generated.
First LFS/LSN –
The unique Log Flush Sequence number and Log Sequence Number of the first log record within this log file. [This field is generally not useful for Database Administrators, but could be useful to a Db2 Support analyst].
Last LFS/LSN –
The unique Log Flush Sequence number and Log Sequence Number of the last log record within this log file. [This field is generally not useful for Database Administrators, but could be useful to a Db2 Support analyst].
LSO range –
The range of log record Log Sequence Offsets within this log file. [This field is generally not useful for Database Administrators, but could be useful to a Db2 Support analyst].
In addition to the log file information displayed by db2fmtlog, there are additional options:
LSNRANGE
- Displays only the first, last LFS/LSN ranges, and LSO range for each log file.
REPLAYONLYWINDOW
- Displays all log records which cause “replay only windows” in HADR Reads-on-Standby (ROS) environments. In addition, if the LOG_DDL_STMTS db config parameter was enabled at the time the log file was generated, then the DDL statement text will also be displayed.
For additional details about these options, and the db2fmtlog tool, please see: http://www-01.ibm.com/support/docview.wss?uid=swg22014054
I hope this information is useful. If you have any thoughts or questions, feel free to submit a comment below.
For more information about transaction logs, you can check the index of Db2 web resources: https://github.com/angoca/db2-index/wiki#t
ReplyDelete