SQLCA

SQLCA is the SQL Communication Area. DB2 fills it in after every SQL statement, and the program reads it to find out what happened.

What SQLCA is

• SQLCA is a fixed-layout data structure that DB2 updates after each SQL statement executes.
• It is brought into the program with one line: EXEC SQL INCLUDE SQLCA END-EXEC in WORKING-STORAGE.
• The most-used field is SQLCODE: 0 means success, +100 means row not found, negative values mean errors.
• Never trust the host variables from a SELECT until you have checked SQLCODE.

Structure of SQLCA

• SQLCAID - PIC X(8). Eye catcher, always contains 'SQLCA'.
• SQLCABC - PIC S9(9) COMP. Length of the SQLCA, 136 bytes.
• SQLCODE - PIC S9(9) COMP. The return code: 0 is success, +100 is not found, negative is an error.
• SQLERRM - message length (SQLERRML) plus message tokens (SQLERRMC) describing the error.
• SQLERRP - PIC X(8). Product code of the module that set SQLCODE, for example DSN for DB2.
• SQLERRD - six fullwords of diagnostic data, for example the number of rows inserted, updated or deleted.
• SQLWARN - eight one-byte warning flags SQLWARN0 to SQLWARN7. SQLWARN0 set means at least one warning is on.
• SQLSTATE - PIC X(5). The standardised five-character return code, for example 02000 for not found.

Reading SQLCODE in the program

• Test SQLCODE right after the SQL statement, before any other statement runs.
• SQLCODE = 0: the statement worked. Use the host variables.
• SQLCODE = +100: no row found (or end of cursor data). This is normal for a SELECT that matches nothing.
• SQLCODE negative: an error. Branch to error handling, and optionally display SQLERRM or SQLSTATE for diagnosis.

Example

• Include SQLCA once in WORKING-STORAGE, then check SQLCODE after every SQL statement.
• WORKING-STORAGE SECTION. EXEC SQL INCLUDE SQLCA END-EXEC. * In PROCEDURE DIVISION, after the SQL runs: EXEC SQL SELECT CUSTNAME INTO :WS-CUST-NAME FROM CUSTOMER WHERE CUSTNO = :WS-CUST-NO END-EXEC. EVALUATE SQLCODE WHEN 0 MOVE WS-CUST-NAME TO CUSTNAMEO WHEN 100 MOVE 'CUSTOMER NOT FOUND' TO MSGO WHEN OTHER PERFORM 9000-DB2-ERROR END-EVALUATE.
• The WHEN 100 branch treats "not found" as a normal business outcome, not a program error.