All topics
27 / 60
CICSadvanced

DB2 Access from CICS Programs

Understand how CICS programs issue SQL against DB2 and how the two transaction managers coordinate commit/rollback across a single unit of work.

An online transaction that reads a screen and updates relational data involves two transaction managers. The interesting part is how they agree.

CICS-DB2 coordination is like two separate cashiers at connected registers ringing up one customer's combined purchase — neither register finalizes the sale until both scanners confirm everything rang up correctly, and if either one jams, the whole transaction is voided on both registers together, not just one.

Key Concepts

1
The SQL itself looks exactly like batch.
    EXEC SQL
         SELECT NAME, BALANCE INTO :WS-NAME, :WS-BALANCE
         FROM CUSTOMER WHERE CUST_ID = :WS-CUST-ID
    END-EXEC.
2
The DB2 precompiler runs, then the CICS translator, then the COBOL compiler. Nothing about the SQL changes because it is online.
3
What changes is who controls the commit.
    batch DB2   EXEC SQL COMMIT END-EXEC.
    CICS DB2    EXEC CICS SYNCPOINT END-EXEC.
4
And using SQL COMMIT in a CICS program is the beginner mistake. CICS owns the unit of work, so the commit boundary is a CICS syncpoint — not a DB2 one.
COMMIT
5
Why it has to be that way. A single transaction might update a VSAM file through CICS and a table in DB2. Those are different subsystems with different logs.
6
    EXEC CICS WRITE FILE('AUDIT') ...      CICS resource
    EXEC SQL UPDATE ACCOUNT ...            DB2 resource
    EXEC CICS SYNCPOINT                    both commit, or neither
7
The CICS-DB2 attachment facility registers DB2 as a resource manager in CICS's two-phase commit. A syncpoint asks every participant to prepare, and commits only if all of them can.
8
So the VSAM write and the DB2 update are atomic together. Committing DB2 independently would break that — which is why the SQL verb is not the right tool here.
9
Rollback works the same way.
    EXEC CICS SYNCPOINT ROLLBACK END-EXEC.
10
Everything since the last syncpoint is backed out, in both subsystems.
11
There is an implicit syncpoint at task end. A task reaching EXEC CICS RETURN normally commits everything it did. A task that abends rolls back.
EXEC CICS RETURN
12
That is worth knowing because it means a program with no explicit syncpoint still commits — at the end. For a long-running task that is a long-held set of locks, which is one reason online transactions are kept short.
13
SQLCODE still applies, and checking it after every statement is as necessary here as in batch:
SQLCODE
14
    0     success
    +100  not found
    -911  deadlock or timeout -- DB2 has already rolled you back
    -803  duplicate key
15
-911 is the one that matters online, because contention is the norm rather than the exception.
-911
16
What the interviewer is probing.1. "How does the unit of work differ from batch?" Probing: the key point. Stalls: "It does not." Moves up: CICS coordinates the syncpoint, so an EXEC CICS SYNCPOINT commits DB2 too — the program does not issue its own COMMIT.
17
2. "What is the rule about holding locks?" Probing: the discipline. Stalls: "Hold them briefly." Moves up: never across a terminal interaction — pseudo-conversational design exists precisely so locks are not held while a user thinks.
18
3. "What does the thread and plan relationship look like?" Probing: the plumbing. Stalls: "Nothing." Moves up: the CICS attachment facility allocates a thread to a plan on first SQL, which is why thread limits show up as queuing under load.
19
4. "What do you check when SQL fails here?" Probing: the method. Stalls: "Restart CICS." Moves up: SQLCODE and SQLSTATE from the SQLCA, plus whether the package is bound and accessible to the plan.
Use cases:cics-db2syncpointunit-of-worktransaction-coordination