DB2 units of recovery, syncpoint, and savepoints

Units of work and units of recovery

A unit of recovery is the work that changes Db2 data from one point of consistency to another. It begins with the first change after job start or after the last commit/rollback, and it ends at a later point of consistency. One application process can run many URs in sequence (COMMIT, more SQL, COMMIT again).

IBM’s bank example: subtract from account A, add to account B. After the subtract, the accounts are inconsistent. After the add, the program announces a point of consistency so other programs may see both changes. That pair of statements is one UR if nothing committed in between.

A unit of work is the broader transaction: everything the transaction manager treats as one atomic business action. When only Db2 is involved, UOW and UR often match. When CICS updates VSAM and Db2 in one SYNCPOINT, the UOW spans both managers; Db2 still has its own UR that participates in that UOW.

Syncpoint and transaction boundaries

A point of consistency (syncpoint, commit point) is the instant when all recoverable data the application accessed is consistent with the rest. Normal program termination creates one. During execution, the environment decides how you ask for it:

Beginners in CICS who code SQL COMMIT are often surprised: CICS is the coordinator. The supported path is EXEC CICS SYNCPOINT, which tells Db2 to commit as a participant. The same idea applies to IMS. Transaction boundaries are therefore “where the coordinator says the UOW ends,” not “wherever you feel like writing COMMIT” unless you are in an environment where SQL COMMIT is the coordinator.

Commit frequency is a locking and logging design choice: too rare, and you hold locks (and claims) for a long time; too often, and you pay syncpoint CPU. Batch usually COMMITs every N rows. OLTP usually COMMITs once per user message.

Savepoints inside the UR

A savepoint does not start a new UR and is not a syncpoint. It is a bookmark in the current UR. ROLLBACK TO SAVEPOINT undoes Db2 changes after the bookmark and leaves the UR open. Other resource managers in a CICS/IMS UOW are not undone by that SQL. The next real syncpoint still decides the fate of whatever remains.

1
2
3
4
5
-- One UR, two attempts, one syncpoint at the end
SAVEPOINT STEP2 UNIQUE ON ROLLBACK RETAIN CURSORS;
-- try optional step
ROLLBACK TO SAVEPOINT STEP2;  -- UR still open
COMMIT;                       -- now the UR ends

Full ROLLBACK (or the transaction manager’s rollback) is a syncpoint that aborts: the UR ends, savepoints vanish, held cursors close.

Held cursors

DECLARE CURSOR … WITH HOLD means COMMIT does not close the cursor. FETCH can continue in the next UR. That is how a batch program COMMITs every 500 rows without losing its place in a driving cursor — provided the cursor was declared WITH HOLD and the isolation/lock design allows it.

1
2
3
4
5
DECLARE C1 CURSOR WITH HOLD FOR
  SELECT EMPNO, LASTNAME
    FROM HR.EMPLOYEE
   ORDER BY EMPNO
   FOR FETCH ONLY;

Claims on a page set are normally released at commit. A WITH HOLD cursor is an important exception: the claim can persist, which delays a utility drain. CICS thread reuse also dislikes open held cursors (and some special-register changes) at syncpoint. Close the cursor when the driving scan is finished.

Two-phase commit and indoubt transactions

When more than one recoverable resource manager shares a UOW, the coordinator runs two-phase commit:

• Phase 1 (prepare) — each participant writes enough log that it can later COMMIT or ABORT and answers “ready” (or votes no, which aborts everyone).
• Phase 2 (commit or abort) — the coordinator broadcasts the decision. Each participant makes it durable and releases locks.

If contact is lost after a successful prepare and before the decision arrives, the participant is indoubt. It must not guess. Locks stay held. Automatic resynchronization usually completes the UR when the coordinator is reachable again. If it cannot, operations use -RECOVER INDOUBT with ACTION(COMMIT) or ACTION(ABORT) identified by connection, correlation ID, network ID / RRS URID, or LUWID — matching the coordinator’s log, not a coin flip.

Distributed DRDA work uses the same idea across locations. Three-part names and aliases that update remote sites pull extra participants into the UOW. That is another reason to keep savepoints released before remote access: the distributed UR is already complicated.

Syncpoint processing is the engine-room work: write commit log records, update the BSDS/log checkpoint story, release locks and claims, notify IRLM, and (in data sharing) coordinate globally. Application programmers rarely call it by name; they feel it as COMMIT elapsed time and as “why is REORG waiting for my thread?”

Putting the vocabulary together

• UOW — the coordinator’s transaction
• UR — Db2’s recoverable change set inside that UOW
• Syncpoint — the agreed consistent instant (commit or abort)
• Savepoint — a bookmark inside the UR, not a syncpoint
• Held cursor — scan that survives COMMIT
• Indoubt — prepared, decision unknown, locks held

Explain It Like I'm Five

A unit of recovery is a sandwich you are still making. Syncpoint is putting the plate on the table (COMMIT) or throwing the sandwich away (ROLLBACK). A savepoint is a sticky note that says “the bread and cheese were fine; undo only the pickle.” A held cursor is a bookmark in the recipe that stays when you put one sandwich on the table and start the next. Indoubt is when two cooks both said “ready” and then the phone died before anyone said “serve it” or “dump it” — nobody is allowed to eat or throw it away until the phone works or a grown-up reads the order pad.

Exercises

1. In one sentence each, define unit of work, unit of recovery, and syncpoint.
2. Why is EXEC CICS SYNCPOINT the commit for a CICS–Db2 program rather than SQL COMMIT?
3. Write a DECLARE CURSOR WITH HOLD and explain what COMMIT does to it versus ROLLBACK.
4. Describe phase 1 and phase 2 of two-phase commit, then say what “indoubt” means.
5. Is ROLLBACK TO SAVEPOINT a syncpoint? Why does that matter for CICS resources?