CICS and DB2 Application

Real CICS applications rarely use only VSAM files. Most banking and insurance systems keep account data in DB2 tables and reach them from the CICS program with embedded SQL.

CICS connects to DB2 through the CICS-DB2 attachment facility, and CICS coordinates DB2 updates together with file updates in one unit of work.

How a CICS program talks to DB2

• SQL is coded inline between EXEC SQL and END-EXEC. The DB2 precompiler converts it before the COBOL compile.
• Values are exchanged through host variables defined in WORKING-STORAGE, written with a colon prefix like :WS-ACCT-NO.
• The program never opens a DB2 connection itself - CICS manages the connection through the attachment facility.
• Each program needs a DB2 plan (bound with BIND). The RCT entries map the CICS trans-id to the plan name.
• After every SQL statement, check SQLCODE: 0 means success, +100 means row not found, negative means error.

Coding SQL inside a CICS program

• Use SELECT ... INTO for a single-row read, for example fetching one account balance.
• Use a cursor (DECLARE, OPEN, FETCH in a loop, CLOSE) when the query returns many rows.
• INSERT, UPDATE and DELETE work the same as in batch, but do NOT code SQL COMMIT - the CICS syncpoint commits the unit of work.
• A cursor cannot stay open across a pseudo-conversational RETURN, because the syncpoint at RETURN closes it.
• Always code a WHENEVER or explicit SQLCODE check; -803 means duplicate row, -911 means deadlock or timeout.
• Example:-
  
  EXEC SQL SELECT ACCT_BALANCE INTO :WS-BALANCE FROM ACCOUNTS WHERE ACCT_NUMBER = :WS-ACCT-NO END-EXEC. IF SQLCODE = 0 MOVE WS-BALANCE TO BALO ELSE IF SQLCODE = 100 MOVE 'ACCOUNT NOT FOUND' TO MSGO ELSE MOVE 'DB2 ERROR - CONTACT SUPPORT' TO MSGO END-IF END-IF.

Syncpoint and two-phase commit

• CICS is the syncpoint coordinator: EXEC CICS SYNCPOINT commits DB2 changes and CICS file changes together.
• Two-phase commit guarantees both commit or both roll back - the account balance and the audit log stay in sync.
• If the task abends or the program issues SYNCPOINT ROLLBACK, DB2 undoes the SQL changes automatically.
• Never hold DB2 locks across a terminal RETURN - finish the unit of work before sending the next screen.
• On SQLCODE -911 (deadlock), roll back and retry the unit of work instead of showing a technical error.