Tuesday, July 08, 2008

A Peek on DB2 Log Record

Every DB2 database has recovery log. Recovery log keeps track of all the changes made to a database and it is used for crash recovery or rollforward recovery. The recovery log is made up of a number of log extents, each contained in a separate file called a log file. Normally, a DBA will have to deal with only the sizing, archive method and archive destination of recovery logs. However, in some scenarios (e.g. SQL replication, HADR) you may come across a term called Log Sequence Number (LSN). In order to understand the LSN, we have to go one step further i.e. inside the log file. Here I will explain what is LSN and how can we map a LSN with a log file and vice versa.

A log file has number of log records inside it. A log sequence number (LSN) uniquely identifies a log record inside a log file. The LSN represents a relative byte address for the first byte of the log record within the recovery log. A transaction can generate more than one log records. Each log record also has a transaction identifier. Hence, the log records written by a single transaction have a common transaction identifier, a field in log record header.

If you have a LSN and you need to find out the log file that has the log record for that LSN, you may use db2flsn command.

db2flsn –q <input_LSN>

If you need to do the reverse i.e. find out the range of LSN and timestamp inside a log file, it becomes little tricky. There is a file DB2TSCHNG.HIS under database path, which contains the history of table space changes at a log file level. You can find out database path from database snapshot.

db2 get snapshot for db on <DB Name> | grep “Database path”

For each log file, DB2TSCHG.HIS contains information that helps to identify which table spaces are affected by the log file. Though the primary purpose of the file DB2TSCHNG.HIS is to track the table space changes, this file also has the information regarding range of LSN and timestamp within a log file. This information can be displayed using db2logsForRfwd command as shown below

$ db2logsForRfwd DB2TSCHG.HIS –all


Log file number : S0000000.LOG

First New Record LSN : 00000138800C

Log Extent Tail LSN : 000001770000

Backup End Time Stamp : 0

Flags : 107

pID : 1209781597 2008-05-03- GMT

cID : 1215376321 2008-07-06- GMT

Tablespace ID :


Log file number : S0000001.LOG

First New Record LSN : 00000177000C

Log Extent Tail LSN : 000001B58000

Backup End Time Stamp : 0

Flags : 107

pID : 1215376321 2008-07-06- GMT

cID : 1215376322 2008-07-06- GMT

Tablespace ID :


Log file number : S0000002.LOG

First New Record LSN : 000001B5800C

Log Extent Tail LSN : 000001F40000

Backup End Time Stamp : 0

Flags : 107

pID : 1215376322 2008-07-06- GMT

cID : 1215376323 2008-07-06- GMT

Tablespace ID : 0 2

If the DB2TSCHNG.HIS file is not in current path, you should provide the fully qualified path of this file to db2logsForRfwd command. However, note the following points regarding DB2TSCHNG.HIS file.

· This history file is available only for databases with archive logging enabled.

· This history file will have an entry for only the archived log files and not the active log files.

· The PRUNE HISTORY command prunes this history file also.

· When the registry variable DB2_COLLECT_TS_REC_INFO is off, history records will not be written to file DB2TSCHNG.HIS.

In the above output, “First New Record LSN” and “Log Extent Tail LSN” fields provide the range of LSN in a log file. The range of timestamp is available in pID and cID fields (second part). The pID and cID stands for previous and current LogID. Have you seen the reference of LogID anywhere else? The header record of a backup image also includes LogID. You can display the information in a backup image header using db2ckbkp command as shown below.

db2ckbkp –h <backup image filename>

You might be curious about the significance of LogID and so am I. But at this point, I don’t know any more about LogID.

If you want to dive deeper into log records, check the db2ReadLog API. You may also check the DB2 add-on tool Recovery Expert, if you are looking for a tool for log analysis.