SQLCA

SQLCA (SQL Communication Area) is the structure through which DB2 reports the result of every SQL statement back to the program. Its most important field is SQLCODE, the return code you check after each statement.

What is the SQLCA

• SQLCA is a fixed-layout structure that DB2 fills in after every SQL statement executes.
• You bring it into the program with EXEC SQL INCLUDE SQLCA END-EXEC in WORKING-STORAGE.
• Key fields: SQLCODE (the return code), SQLERRM (error message tokens), SQLERRD (six diagnostic numbers, e.g. rows affected), SQLWARN (warning flags), SQLSTATE (standardized return code).
• Layout from the copybook: 01 SQLCA with SQLCAID PIC X(8), SQLCABC PIC S9(9) COMP-4, SQLCODE PIC S9(9) COMP-4, SQLERRM group, SQLERRP PIC X(8), SQLERRD OCCURS 6 TIMES, SQLWARN group, SQLSTATE PIC X(5).

Understanding SQLCODE

• SQLCODE = 0: the statement finished successfully.
• SQLCODE negative (for example -911): the statement failed with an error. A -911 means a deadlock or timeout occurred and DB2 rolled back.
• SQLCODE positive (for example +100): success with a warning. +100 means no row satisfied the statement, or end of cursor on FETCH.
• Common codes: -180/-181 bad date/time data, -206 column does not exist, -305 null indicator needed, -501 cursor not open on FETCH, -502 cursor already open, -803 duplicate key, -805 DBRM/package not in plan, -811 more than one row in SELECT INTO, -818 timestamp mismatch, -904 resource unavailable, -913 deadlock/timeout, -922 authorization needed.

Checking SQLCODE in COBOL

• Check SQLCODE immediately after every EXEC SQL statement, before any other SQL runs and overwrites the SQLCA.
• Use EVALUATE: WHEN 0 continue, WHEN 100 handle end-of-data, WHEN OTHER branch to an error paragraph.
• Display SQLCODE and SQLERRMC in the error paragraph so the abend or log shows exactly what failed.
• SQLERRD(3) tells how many rows an INSERT, UPDATE or DELETE affected - useful for verification.

Example of including the SQLCA and checking SQLCODE:-