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.


Anonymous said...

It is a great information. You are great.

Radhesh Kumar said...

Thanks for going through the posting and sharing the feedback. I am glad to know that you found it useful.

Anonymous said...

Thanks for sharing this knowledge.
I have a small doubt.Could yo please help me out in the following issue.

Actually i have heard that in DB2 each and every query with its test results and some extra information is stored in a Log Table.
Can i somehow get access to this Table?

It would be of great help if you could help me out.

Radhesh Kumar said...

I can't think of any table that exactly match with the description you have provided.

However, there are two ways to check the queries that has run against your database

1) Using Dynamic SQL Snapshot
2) Using Event Monitor

Dynamic SQL snapshot provides details of each dynamic SQL statements present in database package cache. The package cache may have all the dynamic SQL statements since database activation or at least the recent ones depending on whether the package cache overflow has occurred or not.

Event monitor can capture different types of events occurred in database and write the details in a file or in a table. However, you have to create and enable the event monitor in order to capture the SQL statements with this method. The "STATEMENTS" type event monitor can provide the details of SQL statements.

You can find out more details about the above at DB2 Information Center (http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp) and determine exactly which method suits your requirement. However, note that STATEMENTS type event monitor may severely affect your database performance depending on your workload.

Let me know if you need any further help.

Mohit said...

Thanks A Lot Radesh.

Anonymous said...
This comment has been removed by a blog administrator.
Yogi said...

Radhesh - is there a way to find daily LOG archiving patterns e.g I wanted to know how may LOGs are archiving per day or say between noon-to-6pm?

something similar to v$loghist on Oracle.


Radhesh Kumar said...

Hi Yogi,

DB2 maintains the history of log archival in database history file. You can use either DB2 LIST HISTORY command or DB_HISTORY admin view for checking the database history.



Руслан said...

Hi, Radhesh. Can you show the wayto find out the log file that has the log record for given LSN not using db2flsn but by use DB2 API. Thanks a lot