DB2 checkpoints, restart, and units of recovery

What a checkpoint contains

A system checkpoint is a log record (and related writes) that captures:

• Currently open units of recovery
• Open page sets
• Page sets in exception states
• Page sets updated by open URs
• A push to externalize buffer pool pages that must be on DASD

The BSDS remembers where the latest checkpoint sits on the log. Restart begins there, not at RBA zero. More frequent checkpoints mean shorter restart and a bit more CPU and write activity. Rare checkpoints mean a long log scan after a crash.

How often: CHKTYPE, CHKFREQ, LOGLOAD

Installation fields on the checkpoint panel set these zparms. A CHKFREQ of 500,000 with SINGLE/log-records means “every 500,000 log records,” which might be two minutes at noon and twenty minutes at night. That is why many DBAs prefer a minutes interval in the 2–5 minute range (CHKTYPE SINGLE, CHKFREQ 2–5) or BOTH so a log-record cap still fires during a burst.

-SET LOG can change log-records-between-checkpoints (LOGLOAD) while Db2 is up. LOGLOAD(0) forces a checkpoint now without changing the configured value. DISPLAY LOG shows the current checkpoint scheduling parameters.

1
2
3
-DISPLAY LOG
-SET LOG LOGLOAD(0)
-SET LOG LOGLOAD(500000)

Coordinate checkpoint frequency with DWQT/VDWQT. If deferred write thresholds are so high that almost all writes wait for checkpoint, you get I/O spikes and still have a large dirty-page set if Db2 crashes just before the checkpoint.

Units of recovery

A unit of recovery is the Db2 name for a commit scope. All SQL changes in that UR commit together or roll back together. An application that never COMMITs holds one giant UR: long locks, huge undo, and ugly restart.

URCHKTH (zparm) warns when a UR writes more than a threshold of log records without commit—your hunt list for batch jobs that update all night. DISPLAY THREAD shows UR status after a failure.

Restart recovery phases (conceptual)

After an abnormal stop, a normal -START DB2 runs restart recovery automatically:

1. Find the restart point

Read the BSDS, locate the last complete checkpoint, identify active/archive logs needed.

2. Current status rebuild

Rebuild in-memory knowledge of open objects and exception states from checkpoint plus later log.

3. Forward log recovery (redo)

Replay logged committed (and in-commit) changes that might not have reached DASD. Database pages on disk can be older than the log; redo makes them current for committed work.

4. Backward log recovery (undo)

Walk inflight (and in-abort) URs backward and apply compensating changes so uncommitted work disappears.

5. Indoubt resolution

Two-phase commit URs that finished phase 1 but not phase 2 stay indoubt until CICS/IMS/XA (or the operator with -RECOVER INDOUBT) decides commit or abort.

This is crash / restart recovery: restore data integrity to the current consistent state. It is not point-in-time recovery (that is the RECOVER utility to an older RBA/LRSN) and not disaster recovery (that is another site’s copies and logs).

What applications must do

• COMMIT often enough — restart and lock duration both depend on it
• Do not hold cursors WITH HOLD across huge update batches without a commit strategy
• Handle SQLCODE -911/-913 so a timeout does not leave a confused UR
• CICS/IMS: understand syncpoint — that is the Db2 UR

A clean STOP DB2 MODE(QUIESCE) lets URs finish; MODE(FORCE) is closer to a crash for remaining threads. Restart after FORCE still runs restart recovery.

Data sharing note

Each member checkpoints itself. Member restart recovers that member’s URs. Group restart is the heavier case when the shared group state must be rebuilt. GBP and SCA failures have extra restart paths covered in high availability pages. LRSN, not only RBA, orders log records across members.

Explain It Like I'm Five

A unit of recovery is a Lego spaceship you have not glued yet. COMMIT glues it. Checkpoint is taking a photo of the table: which ships are glued, which are still loose, which boxes are open. If the lights go out, restart looks at the last photo, puts glued ships back together (redo), and dumps the loose bricks back in the box (undo). If you never glue (never COMMIT), the photo shows a mountain of loose bricks and cleanup takes forever.

Exercises

1. DISPLAY LOG and identify whether your subsystem uses minutes, log records, or BOTH for checkpoints.
2. Estimate checkpoints per hour from CHKFREQ and a typical log-record rate. Is it in the 2–5 minute band?
3. Find URCHKTH and list one batch job that would trip it.
4. Explain the restart fate of a CICS transaction that crashed after phase-1 commit (indoubt) versus one that never issued SYNCPOINT (inflight).
5. Issue SET LOG LOGLOAD(0) on a sandbox and confirm a new checkpoint RBA via DISPLAY LOG or DSNJU004.