SQLCA is a SQL communication area through which DB2 passes the feedback of SQL execution to the program. It tells the program whether an execution was successful or not. There are a number of predefined variables under SQLCA like SQLCODE which contains the error code. The value '000' in SQLCODE states a successful execution.
SQLCA stands for SQL communication area. An SQLCA is a collection of variables that is updated after each SQL statement executes. You must include SQLCA in your program that contains executable SQL statements.
The SQLCA Data Area Display shows the SQLCA area for the SQL call that issued the return code. You can switch between displaying the SQLCA area messages and displaying the SQLCA area in hexadecimal. To access the SQLCA Data Area Display, select an SQL statement from any SQL Error List display or SQL Error Detail display.
For information about the data that is contained in this area, see the current IBM DB2 SQL Reference.
The following example shows the SQLCA in message format -
DSNT408I SQLCODE = -805, ERROR: DBRM OR PACKAGE NAME D41ADB2..PTADRVAC.0F0F-
1F0F00404040 NOT FOUND IN PLAN RDTP025S. REASON 01
DSNT418I SQLSTATE = 51002 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXEPM SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -250 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'FFFFFF06' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION
******************************** BOTTOM OF DATA *******************************
The following example shows the SQLCA in hexadecimal format -
HEX OFFSET HEX DATA CHARACTER DATA ------ ----------------------------------- ------------------ + 0000 E2D8D3C3 C1404040 00000088 FFFFFCDB *SQLCA ........* + 0010 002EC4F4 F1C1C4C2 F24B4BD7 E3C1C4D9 *..D41ADB2..PTADR* + 0020 E5C1C34B F0C6F0C6 F1C6F0C6 F0F0F4F0 *VAC.0F0F1F0F0040* + 0030 F4F0F4F0 FFD9C4E3 D7F0F2F5 E2FFF0F1 *4040.RDTP025S.01* + 0040 40404040 40404040 40404040 40404040 * * + 0050 40404040 40404040 C4E2D5E7 C5D7D440 * DSNXEPM * + 0060 FFFFFF06 00000000 00000000 FFFFFFFF *................* + 0070 00000000 00000000 40404040 40404040 *........ * + 0080 404040F5 F1F0F0F2 * 51002 *
An SQLCA is a collection of variables that is updated at the end of the execution of every SQL statement. A program that contains executable SQL statements and is precompiled with option LANGLEVEL SAA1 (the default) or MIA must provide exactly one SQLCA, though more than one SQLCA is possible by having one SQLCA per thread in a multi-threaded application.
When a program is precompiled with option LANGLEVEL SQL92E, an SQLCODE or SQLSTATE variable may be declared in the SQL declare section or an SQLCODE variable can be declared somewhere in the program. An SQLCA should not be provided when using LANGLEVEL SQL92E. The SQL INCLUDE statement can be used to provide the declaration of the SQLCA in all languages but REXX. The SQLCA is automatically provided in REXX. To display the SQLCA after each command executed through the command line processor, issue the command db2 -a. The SQLCA is then provided as part of the output for subsequent commands. The SQLCA is also dumped in the db2diag log file.
SQLCA usage in partitioned database systems
In partitioned database systems, one SQL statement may be executed by a number of agents on different database partitions, and each agent may return a different SQLCA for different errors or warnings. The coordinator agent also has its own SQLCA. To provide a consistent view for applications, all SQLCA values are merged into one structure, and SQLCA fields indicate global counts, such that -
For all errors and warnings, the sqlwarn field contains the warning flags received from all agents.
Values in the sqlerrd fields indicating row counts are accumulations from all agents.
Updates to this topic are made in English and are applied to translated versions at a later date. Consequently, the English version of this topic always contains the most recent updates.
The SQL communications area (SQLCA) structure is used by the database manager to return error information to an application program. This structure is updated after every API call and SQL statement issued.
syntax
/* File: sqlca.h */
/* Structure: SQLCA */
/* ... */
SQL_STRUCTURE sqlca
{
_SQLOLDCHAR sqlcaid[8];
sqlint32 sqlcabc;
#ifdef DB2_SQL92E
sqlint32 sqlcade;
#else
sqlint32 sqlcode;
#endif
short sqlerrml;
_SQLOLDCHAR sqlerrmc[70];
_SQLOLDCHAR sqlerrp[8];
sqlint32 sqlerrd[6];
_SQLOLDCHAR sqlwarn[11];
#ifdef DB2_SQL92E
_SQLOLDCHAR sqlstat[5];
#else
_SQLOLDCHAR sqlstate[5];
#endif
};
/* ... */
COBOL Structure
* File: sqlca.cbl
01 SQLCA SYNC.
05 SQLCAID PIC X(8) VALUE "SQLCA ".
05 SQLCABC PIC S9(9) COMP-5 VALUE 136.
05 SQLCODE PIC S9(9) COMP-5.
05 SQLERRM.
05 SQLERRP PIC X(8).
05 SQLERRD OCCURS 6 TIMES PIC S9(9) COMP-5.
05 SQLWARN.
10 SQLWARN0 PIC X.
10 SQLWARN1 PIC X.
10 SQLWARN2 PIC X.
10 SQLWARN3 PIC X.
10 SQLWARN4 PIC X.
10 SQLWARN5 PIC X.
10 SQLWARN6 PIC X.
10 SQLWARN7 PIC X.
10 SQLWARN8 PIC X.
10 SQLWARN9 PIC X.
10 SQLWARNA PIC X.
05 SQLSTATE PIC X(5).
*