SQLCA
SQLCA
The SQL Communication Area (SQLCA) is a data structure used in DB2 programs (like COBOL) to capture the result and status of SQL operations. After each SQL statement executes, DB2 populates the SQLCA fields with information about the result—whether the SQL statement succeeded, failed, or returned a warning.
The SQLCA should be included in a COBOL program to check for SQL errors or warnings after executing SQL queries.
Coding in COBOL + DB2 program -
The SQLCA contains several fields that store detailed information about the result of the SQL statement. The INCLUDE SQLCA statement pulls in the SQLCA structure to our program.
EXEC SQL INCLUDE SQLCA END-EXEC.
Note! The EXEC SQL and END-EXEC keywords are used to embed SQL statements within COBOL code.
All SQL statements like SELECT, INSERT, UPDATE, DELETE, COMMIT, SQLCA and table DCLGENs are enclosed between these keywords.
Key Fields of SQLCA and Their Uses -
| Field Name | Type | Description |
|---|---|---|
| SQLCAID | CHAR(8) | Identifier for the SQLCA structure. After including SQLCA, this field will always have 'SQLCA'. |
| SQLCABC | INTEGER | Length of the SQLCA structure (136 bytes) |
| SQLCODE | INTEGER | Return code indicating the result of the SQL operation.
|
| SQLERRM | CHAR(70) | This field stores a short error message related to the SQLCODE. |
| SQLERRD (Array) | INTEGER Array of 6 items | Contains DB2-specific diagnostic information.
|
| SQLWARN (Array) | CHAR(1) of 11 items | Provides warning flags if the SQL operation completed with warnings.
|
| SQLSTATE | CHAR(5) | Provides a standardized return code for SQL operations.
SQLSTATE codes give more portable error messages across different databases.
|
All fields in SQLCA -
| Name | Data Type |
|---|---|
| SQLCAID | CHAR(8) |
| SQLCABC | INTEGER |
| SQLCODE | INTEGER |
| SQLERRML | SMALLINT |
| SQLERRMC | VARCHAR (70) |
| SQLERRP | CHAR(8) |
| SQLERRD | ARRAY |
| SQLERRD(1) | INTEGER |
| SQLERRD(2) | INTEGER |
| SQLERRD(3) | INTEGER |
| SQLERRD(4) | INTEGER |
| SQLERRD(5) | INTEGER |
| SQLERRD(6) | INTEGER |
| SQLWARN0 | CHAR(1) |
| SQLWARN1 | CHAR(1) |
| SQLWARN2 | CHAR(1) |
| SQLWARN3 | CHAR(1) |
| SQLWARN4 | CHAR(1) |
| SQLWARN5 | CHAR(1) |
| SQLWARN6 | CHAR(1) |
| SQLWARN7 | CHAR(1) |
| SQLWARN8 | CHAR(1) |
| SQLWARN9 | CHAR(1) |
| SQLWARN10 | CHAR(1) |
| SQLSTATE | CHAR(5) |