When a database is first created, three log files, known as primary log files, are allocated as part of the creation process. On Linux and UNIX platforms, these log files are 1,000 4K (kilobyte) pages in size; on Windows platforms, these log files are 250 4K pages in size. However, the number of primary log files used, along with the amount of data each is capable of holding, is controlled by the logprimary and logfilsiz parameters in the database's configuration file. The way in which all primary log files created are used is determined by the logging strategy chosen for the database. Two very different strategies, known as circular logging and archival logging, are available. But according to this short article from Informit, a hybrid method called infinite active logging might work best.
Circular logging requires that records stored in the log buffer are written to primary log files in a circular sequence. Once the primary log files are full and still marked as "unavailable" the DB2 Database Manager will allocate what is known as a secondary log file and begin writing records to it. The total number of secondary log files allowed is controlled by the logsecond parameter in the database configuration file.
With archival logging, like circular logging, log records stored in the log buffer are written to the primary log files that have been pre-allocated. However, unlike with circular logging, these log files are never reused. Each time a primary log file becomes full, another primary log file is allocated so that the desired number of primary log files (as specified by the logprimary database configuration parameter) are always available for use. This process continues as long as there is disk space available.
Infinite Active Logging. You would think that you could avoid running out of log space simply by configuring a database to use a large number of primary and/or secondary log files if needed. However, the maximum number of log files allowed (primary and secondary combined) is 256 and if the size of your log files is relatively small, you can still run out of log space quickly when transaction workloads become heavy or when transactions run for an inordinate amount of time. Furthermore, you want to avoid allocating a large number of secondary log files if possible because performance is affected each time a log file has to be allocated. Ideally, you want to allocate enough primary log files to handle most situations and you want to use just enough secondary log files to handle peaks in transaction workloads. If you are concerned about running out of log space and you want to avoid allocating a large number of secondary log files, you can configure a database to perform what is known as infinite active logging or infinite logging. Infinite active logging allows an active transaction to span all primary logs and one or more archive logs, effectively allowing a transaction to use an infinite number of log files. To enable infinite active logging, you simply set the database configuration parameters userexit and logsecond to YES and –1, respectively. It is important to note that when the userexit database configuration parameter is set to YES, a user-supplied userexit program will be invoked each time a log file is closed and this program can move unneeded log files to another location for permanent storage (thus the risk of running out of log storage space on the server is eliminated).
When the logsecond database configuration parameter is set to -1, the logprimary and logfilsiz configuration parameters are still used to specify how many primary log files DB2 should keep in the active log path as well as with how big each file should be. If DB2 needs to read log data from a log file, but the file is not in the active log path, DB2 will invoke the userexit program provided to retrieve the log file from the archive and copy it to the active log location so that other reads of log data from the same file will be fast. DB2 manages the retrieval, copying, and removal of these log files as required.
Note: Although infinite active logging can be used to support environments with large jobs that require more log space than you would normally allocate to the primary logs, it does have its tradeoffs. Specifically, rollback operations (both at the savepoint level and at the transaction level) could be very slow due to the need to retrieve log files from the archive storage location. Likewise, crash recovery could be very slow for the same reason.
Read more about transactions and other DB2 topics at Informit.
This was first published in October 2004