COMMIT and ROLLBACK in DB2

Units of recovery and commit scope

A unit of recovery (UR) is the set of Db2 changes that will be made permanent together or backed out together. It starts after the previous commit/rollback (or at first SQL that updates) and ends at COMMIT or ROLLBACK.

A unit of work (UOW) is the application’s transaction. If Db2 is the only recoverable resource, ending the UR usually ends the UOW. If CICS also updated a VSAM file, the CICS UOW is larger: one SYNCPOINT commits file and Db2 together.

Commit scope is “what gets included.” Everything since the last commit in this thread: INSERT, UPDATE, DELETE, MERGE, and most DDL/GRANT/REVOKE in that UR. SELECT does not need a commit to be “kept,” but it can still hold locks until commit depending on isolation.

COMMIT

COMMIT ends the UR successfully:

• Changes become durable (logged and recoverable)
• Other applications can see the committed data (subject to their isolation)
• Locks acquired for that UR are released, except locks still required for open WITH HOLD cursors
• Cursors not declared WITH HOLD are closed
• A new UR begins with the next SQL that needs one

1
2
3
4
5
6
7
8
9
10
       EXEC SQL
            UPDATE DSN8C10.EMP
               SET SALARY = SALARY * 1.03
             WHERE WORKDEPT = :WS-DEPT
       END-EXEC.
       IF SQLCODE = 0
           EXEC SQL COMMIT END-EXEC
       ELSE
           EXEC SQL ROLLBACK END-EXEC
       END-IF.

That pattern is for TSO, batch CAF/RRSAF, and similar environments where SQL COMMIT is the right API. Under CICS, replace COMMIT with SYNCPOINT.

ROLLBACK

ROLLBACK (without TO SAVEPOINT) ends the UR and backs out all relational changes of that UR. Locks release. Cursors close, including WITH HOLD, for the SQL ROLLBACK statement.

1
2
3
ROLLBACK;
-- Partial undo without ending the UR (next lesson):
-- ROLLBACK TO SAVEPOINT SP1;

Savepoints are a later page: they undo part of the UR without committing the rest. This page is full ROLLBACK.

IMS/CICS: a ROLLBACK TO SAVEPOINT rolls back Db2 only in those environments; other resources are not undone by that SQL. Full transaction rollback must go through IMS/CICS. If CICS/IMS requests rollback but Db2 did no work since the last commit, the request might not be broadcast to Db2—WITH HOLD cursors from an earlier UR might still be open. Edge case, but real.

Commit and rollback behaviour

Uncommitted changes are not supposed to be visible under CS/RS/RR. UR (uncommitted read) can see them—another reason batch and reporting isolation choices matter. After ROLLBACK, your session does not keep the failed UPDATE’s values in the table; host variables in COBOL are unchanged unless you wrote them yourself.

Who issues commit: environment

Mixing SQL COMMIT with CICS resources is a classic integrity bug: Db2 might commit while the file update is still uncommitted, or the statement is rejected. One coordinator, one syncpoint.

Autocommit

Autocommit means the client commits after each SQL statement. JDBC and ODBC typically default to true. That makes each INSERT its own UR: easy for ad-hoc scripts, terrible for “debit A and credit B” (a crash between them leaves a half transfer). Turn autocommit off, run both statements, then commit.

Embedded COBOL in batch does not autocommit each statement. SPUFI often has an autocommit-like option per statement or per script—know your tool. DSNTEP2 commits based on its control cards.

Commit frequency

Long-running UPDATE without COMMIT holds locks, fills the log with one UR, and makes ROLLBACK after a failure take as long as the run. Short COMMITs add overhead and complicate restart (which rows already committed?).

• OLTP — one COMMIT per business transaction (one customer action)
• Batch — COMMIT every n rows or every n seconds; log n in a control table or use WITH HOLD + a key
• Too few commits — lock timeouts, deadlock, rollback measured in minutes, utility incompatibility
• Too many commits — extra log write and CPU; still required if concurrency demands it

Restart: after COMMIT, those rows stay updated. On rerun, skip already-processed keys (watermark) or make the update idempotent. Never restart a half-committed job from record 1 without a skip rule.

Two-phase commit

When two resource managers must agree, the coordinator runs two-phase commit:

1. Prepare (phase 1) — each participant hardens a prepare record: “I can commit or roll back.”
2. Commit or abort (phase 2) — coordinator broadcasts the decision.

Examples: CICS + Db2, IMS + Db2, RRSAF with other RRS participants, distributed update across two Db2 subsystems through DDF with a coordinator. If the coordinator dies after prepare, Db2 shows an indoubt UR until restart/resolve (DISPLAY THREAD, recover indoubt). Applications should not “guess” and issue a local COMMIT on one side only.

Single-resource SQL COMMIT is one-phase: Db2 is coordinator and participant. That is enough for a standalone batch job that only touches this subsystem.

Practical COBOL batch sketch

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
       MOVE 0 TO WS-COUNT.
       EXEC SQL OPEN C_BAT END-EXEC.
       PERFORM UNTIL SQLCODE NOT = 0
           EXEC SQL FETCH C_BAT INTO :WS-KEY, :WS-AMT END-EXEC
           IF SQLCODE = 0
               PERFORM APPLY-CHANGE
               ADD 1 TO WS-COUNT
               IF WS-COUNT >= 1000
                   EXEC SQL COMMIT END-EXEC
                   MOVE 0 TO WS-COUNT
               END-IF
           END-IF
       END-PERFORM.
       EXEC SQL COMMIT END-EXEC
       EXEC SQL CLOSE C_BAT END-EXEC.

For that loop to keep FETCHing after COMMIT, declare C_BAT WITH HOLD. Otherwise OPEN again and reposition (WHERE KEY > :LAST-COMMITTED-KEY).

Explain It Like I'm Five

Imagine building with Lego on a tray. COMMIT is gluing the tray so nobody can take your bricks and other kids can see the castle. ROLLBACK is dumping the tray back into the box—the castle never happened. If you glue after every single brick (autocommit), a two-brick bridge can end up with only one brick glued when you drop the second. Commit frequency is how often you glue during a long castle. Two-phase commit is you and a friend both promising “ready to glue” before either of you glues, so you do not glue yours while your friend dumps theirs.

Exercises

1. In TSO batch, write UPDATE then COMMIT on success and ROLLBACK on negative SQLCODE.
2. Why is EXEC CICS SYNCPOINT preferred over SQL COMMIT in a CICS program that also writes a VSAM file?
3. List three things COMMIT does to cursors and locks.
4. A JDBC program inserts a parent and child row. Why disable autocommit?
5. Explain in one sentence what “indoubt” means after two-phase prepare.