Introduction, Configuration and application of DB2 Unlimited activity log Strategy

When the database was first created, there were 3 log files, called primary log files, that were assigned as part of the creation process. On Linux and UNIX platforms, these log files have a total of 1,000 pages of 4kb in size, and on the Windows platform there are 250 pages with a size of 4kb. However, the number of primary log files used, together with each amount of data that can be accommodated, is controlled by the logprimary and Logfilsiz parameters in the database configuration file. All of the primary log files that are created are used in the way that is determined by the logging policy that is selected for the database. There are two different strategies that can be used, one is the circular log, and the other is the file-building log. But a hybrid method called an infinite activity log may work best.

Circular logs require that records stored in the log buffer be written to the primary log file in a circular sequence. Once the primary log file is full and is still marked as unavailable, the DB2 Database Manager assigns a secondary log file and writes the record to it. The total number of secondary log files allowed is controlled by the Logsecond parameter of the database configuration file.

In the file-building log, similar to the circular log, log records stored in the log buffer are written to the previously allocated primary log file. However, unlike circular logs, these log files will never be reused. Each time the primary log file is filled, another primary log file is assigned, so the number of primary log files to use (specified by the database configuration parameter logprimary) is always available. As long as there is space on the disk, the process will continue.

Unlimited activity log. You might consider simply configuring the database to use a large number of primary and/or secondary log files to avoid all of the log space being exhausted. However, the maximum number of allowed log files (the main and minor combinations) is 256, and if your log file size is relatively small, then you may be able to quickly run out of all log space when the workload of the transaction becomes larger or the transaction runs for a long time. Also, because every time you are forced to assign a log file that affects performance, you want to avoid allocating a large number of secondary log files as much as possible. Ideally, you want to allocate enough primary log files to handle most situations, and use secondary log files that are just the number of times the workload at the peak of a transaction can be met. If you are very concerned about the depletion of the log space and you want to avoid allocating a large number of secondary log files, you can configure the database to perform a strategy called an infinite activity log or an infinite log. The infinite activity log allows an active transaction to be built across all primary logs and one or more files, and effectively allows transactions to use an unlimited number of log files. In order to be able to use an infinite activity log, you simply set the database configuration parameters userexit and Logsecond respectively Yes and –1. It is important to note that the following that is, when the database configuration parameter userexit is set to Yes, a user-supplied UserExit program is invoked whenever the log file is closed, and the program moves unwanted log files to another location that can be permanently stored (therefore, The risk that the log storage space on the server is depleted will be eliminated.

When the server configuration parameter Logsecond is set to-1, the configuration parameters logprimary and Logfilsiz are still used to specify how many primary log files The DB2 retains on the active log path and how large each file should be. If DB2 needs to read the log from a log file, but the file is not on the active log path, DB2 invokes the USEREXIT program to retrieve the log file from the archive file and copies it to the active log area, so that other reads for the same file will speed up. DB2 manages the retrieval, copying, and removal of these required log files.

Note: Although the infinite activity log can be used to support those large job environments, they require more log space than is normally allocated in the primary log space, but it still has its trade-offs. In particular, the execution of rollback operations, whether at the savepoint level or at the transaction level, becomes very slow due to the need to retrieve log files at the archive storage location. Similarly, crash recovery can be slow for the same reason.