An Open PL/I program that contains SQL statements must include an SQL communications area (SQLCA). As shown in the example below, SQLCA includes an SQLCODE variable and an SQLSTATE variable.
The SQLCODE value is set by the Database Manager after each SQL statement is executed. An application can check the SQLCODE value to determine whether the last SQL statement was successful.
The SQLSTATE variable can be used as an alternative to the SQLCODE variable when analyzing the result of an SQL statement. Like the SQLCODE variable, the SQLSTATE variable is set by the Database Manager after each SQL statement is executed.
The SQLCA should be included by using the SQL INCLUDE statement:
exec sql include sqlca;
The SQLCA must not be defined within an SQL declare section. The scope of the SQLCODE and SQLSTATE declarations must include the scope of all SQL statements in the program.
/*--------------------------------------------------------------- */
/* DB2 SQL Communications Area */
/* This should be included by the */
/* 'EXEC SQL INCLUDE SQLCA;' statement. */
/* Do not place this within a SQL declare statement. */
/*--------------------------------------------------------------- */
declare 1 sqlca,
2 sqlcaid char(8), /* Eyecatcher = 'SQLA ' */
2 sqlcabc fixed bin(31), /* SQLCA size in bytes = 136 */
2 sqlcode fixed bin(31), /* SQL return code */
2 sqlerrm char(70) varying, /* Error message tokens */
2 sqlerrp char (8), /* Diagnostic information */
2 sqlerrd(1:6) fixed bin(31), /* Diagnostic information */
2 sqlwarn, /* Warning flags */
3 sqlwarn0 char(1),
3 sqlwarnl char(1),
3 sqlwarn2 char(1),
3 sqlwarn3 char(1),
3 sqlwarn5 char(1),
3 sqlwarn6 char(1),
3 sqlwarn7 char(1),
2 sqltext,
3 sqlwarn8 char(1),
3 sqlwarn9 char(1),
3 sqlwarna char(1),
3 sqlstate char(5); /* State corresponding to SQLCODE */
/* End of SQL Communications Area declaration */