Introduction
In any RDBMS software every changes made to the database needs to be logged. DB2 is no different to any other RDBMS product. Every transaction (insert, update, delete) performed on the data is logged in the log files. Log files are one of the important component of any RDBMS products
"DB2 log file" are sometimes called as "DB2 Transaction log Files". It records all the changes so that in the event of failure, the DB2 server refers the log records which help in recovering the database to the consistent state.
The information or changes stored in the DB2 log file are called as "Log Record". Each log record is associated with "Log Sequence Number (LSN)" which in simple terms can be referred as a unique identifer for the record in the file. When recovery is needed DB2 server uses the LSN to recover the database to consistent point in time.
DB2 Log Files
DB2 maintains two sets of log file namely PRIMARY and SECONDARY log files.
PRIMARY set of log file are pre-allocated when the database is activated.
| Item # | Description | Parameter | |
|---|---|---|---|
| 1 | The number of primary log file is determined by LOGPRIMARY DB CFG parmaeter | db2 get db cfg | grep -i logprimary | |
SECONDARY set of log file are allocated dynamically only when a transaction has used all the PRIMARY log files and no new PRIMARY log file can be allocated.
| Item # | Description | Parameter | |
|---|---|---|---|
| 1 | The number of secondary log file is determined by LOGSECOND DB CFG parmaeter | db2 get db cfg | grep -i logsecond | |
Based on the type of transaction data the DB2 log file stores, they are classified as
- Active Log files
- Archive Log files
1) Active Log files
- In this transaction log files, all the transaction (COMMITED and UNCOMMITTED) that are currently executing into the DB2 system are logged under this files. This is simply the record of what new data is INSERTed, UPDATEed and DELETEed.
- It store both the COMMITED and UNCOMMITED transaction executing into the DB2 systems.
- It also hold information about the transaction that are committed but its data pages are not externalised to the disk.
- While the Transaction is executing and there is a DB2 failure, in such case DB2 will use the active log file for Crash Recovery which will rollback all the UNCOMMITTED transactions and start the database in consistent state.
- Every DB2 database created has the Active Log files
- Location of the actvie log file is called as “Active log Path”
- Following command can be used to find the location of Active log path.
| Item # | Description |
|---|---|
| 1 | SELECT VARCHAR(NAME,30) AS NAME , VARCHAR(VALUE,70) AS VALUE FROM SYSIBMADM.DBCFG WHERE NAME = 'logpath' WITH UR |
| 2 | db2pd -db sample -dbcfg | grep -i "Path to log files (memory)" |
| 3 | db2 get db cfg for sample | grep -i "Path to log files" |
2) Archive Log files
-- Archive log files are available only when the database is enable for “Archival logging”
-- Archived log file are classified into two parts
a) Online Archive log files
-- This log file contain all the information about the transaction that are committed for the data pages externalised to disk
-- They are located in the “Active Log Path” along with the “Active Log file”.
-- When the database is enable for archival logging, the “Online Archive Log file” can be identified using the two step process
i) Find the “Log head” for the DB2 database. This find the current active log file that is used by the database
Item # | Description |
|---|---|
1 | SELECT VARCHAR(NAME,30) AS NAME , VARCHAR(VALUE,70) AS VALUE FROM SYSIBMADM.DBCFG WHERE NAME = 'loghead' WITH UR |
ii) Check the “Active log Path” for the DB2 database
Item # | Description |
|---|---|
1 | SELECT VARCHAR(NAME,30) AS NAME , VARCHAR(VALUE,70) AS VALUE FROM SYSIBMADM.DBCFG WHERE NAME = 'logpath' WITH UR |
iii) Under the “Active log Path” all files below the “LOG HEAD” are the “Online Archive Log” files
b) Offline Archive log files
-- When the “Online Archive Log” file are moved from “Active Log Path” to the “Archived Log path” they are then termed as “Offline Archive Log files”
-- The location of the “Offline Archive Log” file depends on the value set for “LOGARCHMETH1” parameter.
-- Click here to find more details about the “LOGARCHMETH1”
