Correct parsing of DB2 unlimited activity log policies

The following articles mainly analyze the practical skills of the DB2 unlimited activity log policy correctly. When the database is created for the first time, there are three log files called the main log files, assigned as part of the creation process. On Linux and Unix platforms.

These log files have a total of 1,000 pages with a size of 4 kb. On Windows, these log files have a total of 250 pages with a size of 4 kb. However, the number of main log files used, together with the amount of data that can be accommodated, is controlled by the logprimary and logfilsiz parameters in the database configuration file.

The usage of all created major log files is determined by the log policy selected for the database. There are two different policies available: loop logs and archive creation logs. However, a hybrid approach called DB2 unlimited activity logs may work best.

Cyclic logs require that the records stored in the log buffer be written into the main log files in a circular order. Once the main log file is fully written and marked as "unavailable", the DB2 database manager allocates a secondary log file and writes the record to it. The total number of allowed secondary log files is controlled by the logsecond parameter in the database configuration file.

Similar to cyclic logs, log records stored in the log buffer are written into pre-allocated main log files. However, unlike cyclic logs, these log files will never be reused. Each time when the main log file is full, the other major log file will be allocated, so that the number of main log files to be used (specified by the Database Configuration Parameter logprimary) it is always available. As long as there is space on the disk, this process will continue.

DB2 unlimited activity logs. You may consider simply configuring the database to use a large number of required primary and/or secondary log files to prevent all log space from being used up. However, the maximum number of allowed log files (major and secondary combinations) is 256. If the size of your log file is relatively small, when the transaction workload increases or the transaction runs for a long time, you may still use up all the log space quickly.

In addition, every time a log file is forced to be allocated, it will affect the performance, and you will want to avoid allocating as many secondary log files as possible. Ideally, you want to allocate enough major log files to handle the majority of cases, and use the number of secondary log files that meet the peak workload of transactions.

If you are very concerned about the consumption of log space and want to avoid the allocation of a large number of secondary log files, you can configure the database, execute a policy called unlimited activity logs or unlimited logs. Unlimited activity logs allow an activity transaction to create logs across all major logs and one or more archives, and effectively allow transactions to use an unlimited number of log files.

To be able to use DB2 unlimited activity logs, you just need to set the database configuration parameters userexit and logsecond to yes and-1 respectively. Note that the following is important: When the Database Configuration Parameter userexit is set to yes, a user-provided userexit program will be called whenever the log file is closed, in addition, this program will move unwanted log files to another location that can be permanently stored (therefore, the risk of log storage space consumption on the server 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 major log files DB2 retains on the active Log Path, and how large each file should be.

If DB2 needs to read logs from a log file but this file is not in the active Log Path, DB2 will call the userexit program to retrieve log files from the archive file, and copy it to the activity log area, so that other reading for the same file will speed up. DB2 manages the retrieval, copying, and removal of these required log files.

Note: Although DB2 unlimited activity logs can be used to support those large job environments, they require a larger log space than the primary log space normally allocated, however, it still has a trade-off. In particular, the rollback operation (either at the savepoint level or at the transaction level) will become very slow because the log files need to be retrieved at the archive storage location. Similarly, crash recovery also slows down for the same reason.