Technical Blog Post
Abstract
The basic rules of Db2 archival logging.
Body
The basic rules:
1. When a transaction log is full and/or closed Db2 will archive the log (assuming an archiving method is defined in LOGARCHMETH1).
2. When a transaction log no longer contains an open unit of work (i.e. it's extent number is less than the First Active Log) and it has been archived successfully it can be renamed and reused.
3. As a general rule, the minimum number of logs allocated at database activation is LOGPRIMARY. The maximum number of logs that will be allocated to handle a large transaction is LOGPRIMARY + LOGSECOND. There is no difference in the information written to a particular log or in the log header to distinguish them as a primary or secondary log. LOGPRIMARY and LOGPRIMARY + LOGSECOND simply act as a min and max value for logs that may be allocated in the active log path.
4. If Db2 allocates additional logs (more than LOGPRIMARY) to handle a larger transaction up to the limit of the sum of LOGPRIMARY and LOGSECOND, these logs will slowly reduce back to LOGPRIMARY when the need for additional log files beyond LOGPRIMARY no longer exists.
5. Infinite logging (LOGSECOND = -1) changes the rules a bit. As soon as a log has been archived, it is a candidate for renaming and reuse. We generally try to maintain LOGPRIMARY number of logs + some overhead in the active log directory at any time. This means that your First Active Log (and subsequent logs) may be stored in the log archive location and will need to be retrieved for a rollback or crash recovery operation. This allows for much larger transactions than the limit imposed by the sum of LOGPRIMARY and LOGSECOND, but it can dramatically increase the time needed to do a rollback or crash recovery due to the overhead of retrieving the logs from the archive method (Caveat Utilitor - Let the user beware).
6. As of Db2 Version 11.5.4, with the feature Advanced Log Space Management enabled, archived logs containing an open unit of work will be extracted into extraction log files and then are eligible for rename and reuse. After these log files have been reused, operations like rollback and crash recovery will not need to retrieve logs and instead can use the extraction log files.
7. The ARCHIVE LOG command does not actually archive a log, it simply truncates it at the last new log page written and switches writing to the next log. Now that the log is closed, the normal archiving of a closed logs kicks in. (see rule 1)
Note: Advanced Log Space Management was introduced in Db2 Version 11.5.4 and can be enabled using the Db2 registry variable DB2_ADVANCED_LOG_SPACE_MGMT.
These concepts are the key to understanding most Db2 logging behavior that you observe.
A few examples:
If you have an open transaction that spans multiple logs, you may see that there is a copy of a particular log (or logs) in both the active and archive path. That is a result of a combination of rule 1 & 2. It was archived when closed, but not renamed in the active path due to that open unit of work. As soon as the unit of work completes, you may see a clump of those previously active logs renamed with almost the same timestamp. A directory listing in timestamp order will likely not be in log extent number order, but rest assured, Db2 has a handle on which logs are next to be used.
If the log archiving method stops working, Db2 can continue to function by allocating additional logs in the active log path until it hits DISK FULL. It is not restricted by the sum of LOGPRIMARY and LOGSECOND as the First Active Log is moving forward and the total span from First Active Log to the current log will not exceed that sum value. Also, it cannot rename/reuse those closed logs due to the fact that they have not been archived, even if they no longer contain an open unit of work tracked in them (due to rule 2, as both conditions must be true to rename/reuse them).
An online backup will need the logs that span the entire backup, from the First Active Log when the backup starts, thru the last log that is truncated when the backup completes. That is why you may see archived logs being retrieved at the end of an online backup for inclusion in the online backup image. They were likely archived and renamed during the backup if it takes a while (rule 2 again).
If you are using infinite logging (LOGSECOND = -1) and you allow an open unit of work to remain uncommitted, you can end up with 100s or even 1000s of logs between your First Active Log (now in the archive location) and the current log being written to (you can see the Current Log in a db2pd -db <dbname> -logs output). It is recommended that you use the parameter NUM_LOG_SPAN to define an upper boundary that is reasonable for the largest workload that you anticipate having to prevent an errant open unit of work from leaving you in an untenable state. Alternatively, you can use the Workload Manager threshold UOWTOTALTIME to define a time based upper boundary on your workload.
Hopefully, these will provide a clear picture of how logging is expected to behave and allow you to understand any behaviors that you may observe.
Please leave a comment if you have any questions or feedback.
Thank you.
UID
ibm13286749