COMMIT and ROLLBACK in COBOL DB2 programs

Unit of work versus unit of recovery

A unit of recovery (UR) is the set of Db2 changes that will commit or roll back together. A unit of work (UOW) in CICS or IMS is the same idea at the transaction-manager level and can include non-Db2 resources. In a TSO batch program that only touches Db2, UR and UOW are practically the same thing and SQL COMMIT ends both.

After COMMIT:

• Inserts, updates, deletes, and DDL in that UR become permanent and visible
• Locks for that UR are released, except those still needed for open WITH HOLD cursors
• Cursors not declared WITH HOLD close
• A new UR starts with the next SQL that needs one

After ROLLBACK (without TO SAVEPOINT): all relational changes of the UR are undone, locks release, and cursors close — including WITH HOLD for the SQL ROLLBACK statement.

COMMIT in COBOL programs (TSO and CAF)

Under the TSO attachment (DSN RUN) and the call attachment facility (CAF), SQL COMMIT and SQL ROLLBACK are the right verbs.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
       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.

Check SQLCODE after COMMIT too. A failed COMMIT is not “probably fine.” Batch programs that update many rows should COMMIT on a count or key boundary (every 500 rows, every account) so a restart does not replay an hour of uncommitted work and so you do not hold locks that stall online transactions.

WITH HOLD cursors survive SQL COMMIT: reopen is unnecessary, but you must FETCH again before WHERE CURRENT OF. If you commit inside a FETCH loop without WITH HOLD, the next FETCH fails because the cursor is closed — CLOSE is optional at that point, OPEN is required again.

ROLLBACK in COBOL programs

1
2
3
       EXEC SQL
            ROLLBACK
       END-EXEC.

Use ROLLBACK when a business rule fails after you already changed rows, or after SQLCODE -913 (timeout/deadlock without automatic rollback). After -911 Db2 already rolled back the UR; issuing another ROLLBACK is usually harmless but the data changes are already gone. Savepoints (ROLLBACK TO SAVEPOINT) undo part of a UR without ending it — useful in complex batch, and one of the few SQL rollback forms CICS/IMS still allow, for Db2 changes only.

Unit of work with CICS and IMS

CICS

IBM documents that SQL COMMIT and SQL ROLLBACK are not valid in CICS. You get SQLCODE -925 (COMMIT) or -926 (ROLLBACK). CICS is the coordinator. Db2 participates through two-phase commit with any other recoverable resources on the task.

1
2
3
4
5
       EXEC CICS SYNCPOINT
            END-EXEC.
 
       EXEC CICS SYNCPOINT ROLLBACK
            END-EXEC.

A unit of work in CICS ends:

• Explicitly — EXEC CICS SYNCPOINT
• Implicitly — EXEC CICS RETURN at the highest logical level (normal end of transaction)
• Implicitly — certain DL/I TERM/checkpoint cases when IMS DB is also in the picture

If the task abends, CICS rolls back recoverable resources, including Db2. SYNCPOINT in the middle of a pseudo-conversational design commits what you just did; the next task is a new UOW. Do not expect a WITH HOLD cursor to remain after EOT.

IMS

Message-driven and batch DL/I programs must not issue SQL COMMIT or SQL ROLLBACK (again -925/-926). Use:

• CHKP (checkpoint) — commit IMS and Db2 together; DL/I positioning is lost; non-held Db2 cursors close
• ROLB — roll back and keep running
• ROLL — roll back and terminate

IBM warns batch DL/I programs to checkpoint before the program ends. If the job step fails after the COBOL program returned but before IMS finished its own commit, you can get Db2 committed and IMS not, or indoubt URs. Unique checkpoint IDs plus XRST on restart are the IMS recovery pattern — SQL COMMIT cannot substitute.

RRSAF

Programs that use the Recoverable Resource Services attachment (DSNRLI) should commit with SRRCMIT and back out with SRRBACK, not SQL COMMIT. -925/-926 apply here too. RRSAF is how many stored procedures and mixed-resource batch jobs join an RRS-coordinated UOW.

When Db2 commits automatically

You will hear “it commits when the program ends.” That is only true in some attachments and only on normal termination:

• TSO / CAF batch — if you never COMMIT or ROLLBACK, normal end of the application (GOBACK from the highest program, thread termination) typically commits. Abend, cancel, or a nonzero backout path rolls back. Debuggers that stay as the highest program can delay that implicit commit until the debug session ends.
• CICS — implicit SYNCPOINT on successful RETURN at the top level. Abend: rollback.
• JDBC/ODBC clients — default autocommit ON: each statement is its own UR unless you disable autocommit. That is not how embedded COBOL SQL works in TSO; do not copy Java autocommit assumptions into a batch COBOL loop.
• SPUFI / some interactive tools — often autocommit per statement or per run; still not COBOL.

Implicit commit is a safety net, not a design. A 2-hour UPDATE without checkpoints holds locks, fills the log, and makes restart painful. Issue COMMIT (or SYNCPOINT/CHKP) at documented intervals. After an implicit commit you cannot ROLLBACK the work that already committed — only compensating transactions.

Locks, cursors, and restart

Commit frequency is a lock and restart decision:

• Too rare: timeouts (-911/-913), huge rollback time, log volume
• Too often: extra CPU, and without WITH HOLD you reopen cursors constantly

Restartable COBOL batch stores the last committed key and skips to that key on rerun. That only works if you COMMIT after writing the checkpoint record in the same UR as the Db2 changes — or you use IMS checkpoints that include your restart token.

Explain It Like I'm Five

Changing a Db2 row without COMMIT is like stacking blocks on a table but not gluing them. COMMIT is the glue. ROLLBACK knocks the un-glued blocks off. In the batch classroom the teacher (Db2) will glue whatever is left when you leave the room nicely. If you knock the table over (abend), the un-glued blocks fall. In the CICS playground a different teacher (CICS) holds the glue bottle; you must ask that teacher (SYNCPOINT), not the Db2 teacher, or the VSAM blocks and the Db2 blocks will not stick at the same time.

Exercises

1. Write a TSO COBOL fragment that updates 1,000 rows and COMMITs every 100, reopening a non-held cursor after each commit.
2. Replace that COMMIT with the CICS equivalent and name the SQLCODE you would see if you left SQL COMMIT in the CICS program.
3. Explain why an IMS CHKP is not the same as SQL COMMIT even though both “save” Db2 rows.
4. After SQLCODE -911, should you ROLLBACK again before retrying? Why or why not?
5. Describe when implicit commit at GOBACK would hide a missing COMMIT in unit test but fail in production under a debugger or a called subprogram that never returns to the top.