CICS DB2 Error Handling

Every SQL statement can fail, so every CICS DB2 program needs a consistent error-handling strategy built around SQLCODE.

Check SQLCODE after every SQL statement

• DB2 sets SQLCODE in the SQLCA after each statement: 0 means success, +100 means not found, negative means error.
• Test SQLCODE immediately. Any later statement overwrites the SQLCA.
• Route all negative SQLCODEs to one error paragraph so handling is consistent across the program.
• For diagnosis, capture SQLCODE together with SQLSTATE and the SQLERRMC message tokens.

Common SQLCODEs in CICS DB2 programs

• +100 - row not found, or no more rows on a cursor. Normal outcome, not an error.
• -117 - number of values does not match the number of columns on INSERT.
• -180 / -181 - bad date, time or timestamp value or representation.
• -205 - column name not in the table. Check spelling.
• -305 - null value fetched without an indicator variable.
• -501 / -502 - cursor not open on FETCH, or OPEN on an already open cursor.
• -530 / -532 - referential integrity stops the INSERT, UPDATE or DELETE.
• -803 - duplicate key on INSERT or UPDATE.
• -805 - DBRM or package not found in the plan. Check the plan name.
• -811 - singleton SELECT returned more than one row.
• -818 - timestamp mismatch between the load module and the bound plan or package.
• -904 - resource unavailable, for example a stopped tablespace.
• -911 / -913 - deadlock or timeout. The unit of work was rolled back; retry the transaction.
• -923 - connection not established. CICS is not connected to DB2.

Deadlock and timeout handling

• SQLCODE -911 (deadlock) and -913 (timeout) mean DB2 rolled back the current unit of work to break the deadlock.
• The correct response is to retry the whole transaction from the start, not to continue where it failed.
• Keep a retry counter (for example 3 attempts) so a persistent problem does not loop forever.
• Reduce deadlocks by keeping units of work short, accessing tables in the same order everywhere, and committing often.

When the connection itself fails

• If DB2 goes down, SQL statements return SQLCODE -923 (or the task abends, depending on CONNECTERROR).
• With CONNECTERROR(SQLCODE), the program sees the negative SQLCODE and can display a friendly "system unavailable" message.
• With STANDBYMODE(RECONNECT) on the DB2CONN, CICS reconnects automatically when DB2 returns.
• Never retry a -923 in a tight loop. Tell the operator DB2 is unavailable and end the transaction cleanly.

Example

• A single error paragraph that classifies the SQLCODE and acts accordingly.
• 9000-DB2-ERROR. EVALUATE SQLCODE WHEN 100 MOVE 'CUSTOMER NOT FOUND' TO MSGO WHEN -911 WHEN -913 ADD 1 TO WS-RETRY-COUNT IF WS-RETRY-COUNT < 4 EXEC CICS SYNCPOINT ROLLBACK END-EXEC GO TO 0000-MAIN ELSE MOVE 'PLEASE TRY LATER' TO MSGO END-IF WHEN -923 MOVE 'DB2 UNAVAILABLE - TRY LATER' TO MSGO WHEN OTHER MOVE SQLCODE TO ERRCODEO MOVE 'DB2 ERROR - SEE CODE' TO MSGO END-EVALUATE. EXEC CICS SYNCPOINT ROLLBACK END-EXEC.
• Deadlocks retry the transaction, connection loss shows a clear message, and everything else displays the SQLCODE for the support team.