Changing the log record buffer (LOGRECORD BUFSIZE)

BMC recommends that you change the size of the log record buffer only when you experience repeated performance problems related to memory usage.

For this example, assume that

• A company runs a large enterprise resource planning (ERP) application on Db2

• A daily job generates a Summary report to show activity within the application

• The application’s tables are defined with DCC

• The application does not generate many rollback actions

• The 'commit level' parameter within the application is set to the highest value, causing the application to run up to five hours between COMMIT statements

• Transactions (units of recovery) are very large, with millions of logical transactions within one Db2 unit of recovery

The output from the daily job frequently contains many data set allocation and deallocation messages like the following sample:

BMC097342I DATA SET SYSADM1.T1825.D00403.LQ0001 WAS SUCCESSFULLY ALLOCATED 
BMC097343I DDNAME SYS1 (DSN SYSADM1.T1825.D00403.LQ0001) WAS SUCCESSFULLY UNALLOCATED

The daily job can also terminate with out-of-space messages, such as the following:

B3704,IFG0554A,QXT0111P,P02A,LOGSWK10,3BAD,B2L001,04210010,SYSADM1.T1825.D00403.LQ0001

As Log Master scans the log, it stores log record information from a selected unit of recovery in the log record buffer until it encounters the end of the unit of recovery. Log Master uses the log record buffer dynamically and re-uses memory after it encounters the end of each unit of recovery. Log Master uses the log record buffer unless a log record requires row completion processing. In this example, the DCC attribute is set so that the product retains all information from each unit of recovery in the log record buffer.

The best solution to this memory usage problem is to change the 'commit level' parameter within the application so that COMMIT statements occur more frequently. This example shows how to increase the size of the log record buffer, but standard practices of recoverable Db2 operation normally eliminate the need to take this action.

In this explanation, the log record buffer cannot hold all of the information in the large units of recovery, so Log Master spills to DASD, and performance degrades. The characters LQ in the last node of the data set name in the BMC097342 message indicate the log record buffer.

To increase the size of the log record buffer

1. Determine the current log record buffer size.

2. Locate the BMC097064 message, which lists the available above-the-bar memory, as in the following sample:

   BMC097064I AVAILABLE REGION BELOW 16M = 9752K, AVAILABLE REGION ABOVE 16M = 1633408K

3. Estimate a new log record buffer size.

   There is no exact calculation to determine the best log record buffer size. The most important factors are the size and number of log records in the units of recovery that are open simultaneously. Use your judgement and knowledge of your data, and observe the following guidelines:

   • The new value should be larger than the current value, but smaller than the amount of memory displayed in message BMC097064.

   • Because this is a maximum limit value, a high value does not impose a penalty. Log Master uses only the memory that it needs to hold log record information.

4. Determine whether to apply the new value to a single job, or to all jobs:

   • If the units of recovery selected by most jobs in your environment are large enough to spill the log record buffer (as would be the case in this explanation), change the value of the QBLRBUF installation option and reassemble your installation options module.

   • If only a small number of jobs select units of recovery large enough to spill the log record buffer, use the OPTION LOGRECORD BUFSIZE keyword to specify the new percentages in the SYSIN syntax in your JCL.

5. Monitor the next run of the job to observe performance.