A checkpoint is an internal operation where the database manager writes modified data and status information to DASD, and writes a summary status record to the log.
When the database manager takes a checkpoint:
A checkpoint has two performance implications:
To control the duration between database checkpoints, use the CHKINTVL initialization parameter. This parameter specifies how many log pages the database manager will fill before it takes its next checkpoint.
The time between checkpoints depends on the number of modifications you make to the database. If logging is turned on, the database manager writes to the log every time you perform an insert, update, or delete. The more modifications you make, the faster you will reach a checkpoint. If you only perform queries, the database manager may never perform a checkpoint.
We recommend that you adjust the CHKINTVL parameter so that the database manager takes a checkpoint every 10 to 15 minutes. Should you experience a system error, it should take you no longer than 10 to 15 minutes to restart the database manager after you have recovered your system. If you adjust CHKINTVL so that checkpoints occur less frequently, for example every four hours, it may take up to or more than four hours to restart your database.
If you set the CHKINTVL parameter too low, you minimize the risk of filling the log or storage pools. However, while each checkpoint is faster, you increase the overall number of checkpoints.
If you set it too high, you lower the overhead associated with checkpoint processing. However, you risk filling the log and storage pools, and you increase the time required to complete a checkpoint. It may also take longer to recover from a system error.
The SAVEINTV parameter limits the number of modified pages in main and expanded storage. When the number of blocks of modified pages in a data space exceeds this parameter, the database manager directs the operating system to write all the modified pages in that data space to DASD.
This is done asynchronously, meaning that the database manager can continue servicing other users while the save completes.
If you set the save interval appropriately, you can reduce the time it takes to perform a checkpoint. While the checkpoints will take place at the same intervals (the database manager still fills log pages at the same rate), they will be shorter because there will be fewer modified pages to write to DASD.
While the default setting should work well for most databases, you may consider changing it. If you find that your checkpoints take too long, reduce SAVEINTV. If checkpoint processing is not a problem, consider increasing it.
You may also need to reduce SAVEINTV if your current working storage size is always much larger than your target. The database manager does not release modified pages from main storage until a save interval or a checkpoint. So if you are using a high SAVEINTV, and performing many inserts, updates, or deletes, the database machine may keep too many modified pages in main storage.
You can compare the number of times the database manager requests the operating system to save pages to the number of times it performs a checkpoint, by using the COUNTER and COUNTER INTERNAL operator commands. The COUNTER command displays the CHKPOINT counter, which records the number of checkpoints that occurred since the last time you reset the counter. The COUNTER INTERNAL command displays the SAVEGNRL counter. SAVEGNRL counts the number of times the database manager directs the operating system to write all the modified pages in a data space to DASD. If you reset both the CHKPOINT and SAVEGNRL counters at the same time, you can monitor the number of save requests between each checkpoint.
For more information on the COUNTER and COUNTER INTERNAL operator commands, refer to the DB2 Server for VSE & VM Operation manual.