SQLCA

What is SQLCA

• SQLCA stands for SQL Communication Area.
• It is a data structure that DB2 fills in after every SQL statement runs.
• It carries SQLCODE, SQLSTATE, the error message text, warning flags and row counts.
• Every DB2 program that runs SQL should include it.

Structure of the SQLCA

• 05 SQLCAID PIC X(8) - eye-catcher, always contains 'SQLCA'.
• 05 SQLCABC PIC S9(9) COMP-4 - length of the SQLCA, 136.
• 05 SQLCODE PIC S9(9) COMP-4 - the return code of the last SQL statement.
• 05 SQLERRM - error message area: SQLERRML (message length) and SQLERRMC (message text, 70 chars).
• 05 SQLERRP PIC X(8) - product signature of the module that set the code.
• 05 SQLERRD - six fullword fields. SQLERRD(3) holds the number of rows affected by an UPDATE, DELETE or INSERT.
• 05 SQLWARN - warning flags such as SQLWARNA for truncation warnings.
• 10 SQLSTATE PIC X(5) - the 5-character standard return code.
• You never code this layout by hand. The precompiler expands it for you.

Using the SQLCA in a COBOL program

• Code EXEC SQL INCLUDE SQLCA END-EXEC in WORKING-STORAGE. The precompiler expands the layout.
• Never MOVE values into SQLCA fields yourself. DB2 owns them.
• On error, DISPLAY SQLCODE, SQLSTATE and SQLERRMC for diagnosis.
• Example:
  
  WORKING-STORAGE SECTION. EXEC SQL INCLUDE SQLCA END-EXEC. ... PROCEDURE DIVISION. MAIN-PARA. EXEC SQL DELETE FROM EMP WHERE DEPTNO = :WS-DEPTNO END-EXEC IF SQLCODE = 0 DISPLAY 'Rows deleted: ' SQLERRD(3) ELSE DISPLAY 'Delete failed. SQLCODE = ' SQLCODE DISPLAY 'SQLSTATE = ' SQLSTATE DISPLAY 'Message: ' SQLERRMC STOP RUN END-IF.