April 12, 2011

Controlling System Checkpoints

Recently I was asked about controlling the number of system checkpoints being taken by DB2. In this case the DBA would see system checkpoints taken every couple of minutes, and the logs fill up as a result.

The system parameters used to control system logging are defined during the install and can later be changed in the DSNZPARMs. In older DB2 versions, the LOGLOAD paramenter controlled the number of log records written. If you set this parameter to 100,000, DB2 would take a system checkpoint once 100,000 records had been written. (DB2 will pause activity and take a system checkpoint to establish a point of consistency.) The problem with setting system checkpoints to occur with every X number of records is that you never know when a checkpoint is coming or how busy the system will be when it does. Depending on system activity, a checkpoint could come every hour -- or every second.

With DB2 8, IBM made it easier to control and consistently predict when and how frequently system checkpoints would occur. CHKTYPE, CHKMIN and CHKFREQ replaced LOGLOAD as the key parameters.

The CHKTYPE (checkpoint type) subsystem parameter indicates whether the interval between log checkpoints is to be based on the number of written log records, the time between checkpoints, or both. The acceptable values are LOGRECS, MINUTES and BOTH. The default is MINUTES, which is what I like to use. For details, see checkpoint parameters in panel DSNTIPL1 in the DB210 Installation and Migration Guide.

The parameter you choose to control the frequency of checkpoints (CHKFREQ or CHKMINS) is determined by the checkpoint type you use, LOGRECS or MINUTES. With LOGRECS, the valid values are between 1,000 and 16,000,000. With MINUTES, the valid values are between 1 and 60.

When choosing a checkpoint frequency value, you must consider the trade-offs between the overhead needed for frequent subsystem checkpoints and the time needed to restart a DB2 subsystem after a termination without a quiesce. In a production environment, you should set the frequency between 2 and 5 minutes. In a testing or development environment, a frequency between 15 and 20 minutes is acceptable.

Next week I'll discuss how log size can impact system checkpoint.

Do you tune system checkpoints? What settings do you employ for production and development? Please post your thoughts in Comments.