April 19, 2011

Sizing DB2 Active Logs

Last week I wrote about ways to control DB2 system checkpoints. Now I'll look at some of the system parameters used to configure the size and number of active logs and explain how this can impact system checkpoints and the overall performance of your DB2 subsystem.

When the log data set is small, it fills up quickly, prompting an active log switch that generates a checkpoint. The general guideline for reducing the number of log switches is to define sufficient space to support 10 checkpoint intervals in the current active log. Last week's post provides a detailed explanation.

The capacity of the active log can significantly impact your application's performance. If the log is too small, DB2 may have to access data in an archive log during rollback, DB2 restart or recovery. And again, small logs can cause excessive system checkpoints that drive up CPU usage and slow all applications access that DB2 subsystem.

The characteristic of the active log data set is defined during installation through panel DSNTIPL. Parameters defined include:

* Number of logs -- controls the number of active log data sets you create.
* ARCHIVE LOG FREQ -- provides an estimate of how often active log data sets are copied to the archive log.
* UPDATE RATE -- provides an estimate of how many database changes (inserts, updates and deletes) you can expect per hour.
* DB2 installation clist DSNTINST -- uses UPDATE RATE and ARCHIVE LOG FREQ to calculate the data set size of each active log data set.

It's tough to come up with an update rate for a new system when you don’t know what the workload will be. Here are some guidelines to help you make an educated estimate for an initial install:

•    Small site changes data 1,800 times per hour, and the active log is offloaded once each day.
•    Medium site changes data 3,600 times per hour, and the active log is offloaded once each day.
•    Large site changes data 36,000 times per hour, and the active log is offloaded once each day.
•    Extra-large site changes data 72,000 times per hour, and the active log is offloaded once each day.

The following space calculation formula is used by the DB2 installation clist DSNTINST to size the active log data set based on your update rate and how often you want the archive log generated. This example is based on the large site with an update rate of 36,000 changes per hour. The system job DSNTINST assumes the average record length of the changed data is 400 bytes.

megabytes = (data change log record size * data change rate per hour  * hours in archive period)
MB = (400 * 36,000 * 24)
MB = 34,560,000
CYL = 34,560,000 / 720000
CYL = 48

Learn more about clist DSNTINST and DSNTIJIN in the DB2 10 for z/OS Installation and Migration Guide.