SQLCA (SQL communications area)
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
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 field descriptions
|Name||Data Type||Field Values|
eye catcherfor storage dumps containing
SQLCA. The sixth byte is
Lif line number information is returned from parsing an SQL routine, SQL trigger, or dynamic compound SQL statement. The sixth byte is
Mif the line number and object ID information is returned from executing a compiled SQL routine, compiled SQL trigger, or dynamic compound SQL (compiled) statement.
|sqlcabc||INTEGER||Contains the length of the SQLCA, 136.|
|sqlcode||INTEGER||Contains the SQL return code:
|sqlerrml||SMALLINT||Length indicator for sqlerrmc, in the range 0 through 70. 0 means that the value of sqlerrmc is not relevant.|
|sqlerrmc||VARCHAR (70)||Contains one or more tokens, separated by X'FF', which are
substituted for variables in the descriptions of error conditions.
This field is also used when a successful connection is completed.
When a NOT ATOMIC compound SQL statement is issued, it can contain information about seven or fewer errors.
The last token might be followed by X'FF'. The sqlerrml value will include any trailing X'FF'.
|sqlerrp||CHAR(8)||Starting with V11.1, the format of the
SQLERRP field is changed to SQLvvrrmm, where:
The following examples illustrate the relationship between the product signature and the new token in the SQLERRP field. All subsequent fix packs for a given Mod Pack return the same SQLERRP value.
|sqlerrd||ARRAY||Six INTEGER variables that provide diagnostic information. These values are generally empty if there are no errors, except for sqlerrd(6) from a partitioned database.|
|sqlerrd(1)||INTEGER||If connection is invoked and successful,
contains the maximum expected difference in length of mixed character
data (CHAR data types) when converted to the database code page from
the application code page. A value of 0 or 1 indicates no expansion;
a value greater than 1 indicates a possible expansion in length; a
negative value indicates a possible contraction.
On successful return from an SQL procedure, contains the return status value from the SQL procedure.
If connection is invoked and successful, contains the maximum expected difference in length of mixed character data (CHAR data types) when converted to the application code page from the database code page. A value of 0 or 1 indicates no expansion; a value greater than 1 indicates a possible expansion in length; a negative value indicates a possible contraction. If the SQLCA results from a NOT ATOMIC compound SQL statement that encountered one or more errors, the value is set to the number of statements that failed.
|sqlerrd(3)||INTEGER||If PREPARE is invoked and successful,
contains an estimate of the number of rows that will be returned.
After INSERT, UPDATE, DELETE, or MERGE, contains the actual number
of rows that qualified for the operation. For
a TRUNCATE statement, the value will be -1. If compound SQL is
invoked, contains an accumulation of all sub-statement rows. If CONNECT
is invoked, contains 1 if the database can be updated, or 2 if the
database is read only.
If the OPEN statement is invoked, and the cursor contains SQL data change statements, this field contains the sum of the number of rows that qualified for the embedded insert, update, delete, or merge operations.
an error is encountered during the compilation of an SQL routine,
trigger, or dynamic compound SQL (inlined or compiled) statement,
sqlerrd(3) contains the line number where the error was encountered.
The sixth byte of sqlcaid must be
If an error is encountered during the execution
of a compiled SQL routine, trigger, or dynamic SQL (compiled) statement,
sqlerrd(3) contains the line number where the error was raised. The
sixth byte of sqlcaid must be
If PREPARE is invoked and successful , contains a relative cost estimate of the resources required to process the statement. If compound SQL is invoked, contains a count of the number of successful sub-statements. If CONNECT is invoked, contains 0 for a one-phase commit from a client which is not at the latest level; 1 for a one-phase commit; 2 for a one-phase, read-only commit; and 3 for a two-phase commit.
If an error
is encountered during the execution of a compiled SQL routine or trigger,
sqlerrd(4) contains an integer number that uniquely identifies the
routine or trigger within which the error was raised. The sixth byte
of sqlcaid must be
Contains the total number of rows deleted, inserted, or updated as a result of both:
Important: The DATA_ENCRYPT authentication type is deprecated and might be removed in a future release. To encrypt data in-transit between clients and Db2® databases, we recommend that you use the Db2 database system support of Transport Layer Security (TLS).
|sqlerrd(6)||INTEGER||For a partitioned database, contains the partition number of the database partition that encountered the error or warning. If no errors or warnings were encountered, this field contains the partition number of the coordinator partition. The number in this field is the same as that specified for the database partition in the db2nodes.cfg file.|
|sqlwarn||Array||A set of warning indicators, each containing a blank or W. If compound SQL is invoked, contains an accumulation of the warning indicators set for all sub-statements.|
|sqlwarn0||CHAR(1)||Blank if all other indicators are
Wif at least one other indicator is not blank.
Wif the value of a string column was truncated when assigned to a host variable. Contains
Nif the null terminator was truncated. Contains
Aif the CONNECT or ATTACH is successful, and the authorization name for the connection is longer than 8 bytes. Contains
Pif the PREPARE statement relative cost estimate stored in sqlerrd(4) exceeded the value that could be stored in an INTEGER or was less than 1, and either the CURRENT EXPLAIN MODE or the CURRENT EXPLAIN SNAPSHOT special register is set to a value other than NO.
Wif null values were eliminated from the argument of an aggregate function. a
CONNECT is invoked and successful, contains
Wif the number of columns is not equal to the number of host variables. Contains
Zif the number of result set locators specified on the ASSOCIATE LOCATORS statement is less than the number of result sets returned by a procedure.
Wif a prepared UPDATE or DELETE statement does not include a WHERE clause.
Eif an error was tolerated during SQL statement execution.
Wif the result of a date calculation was adjusted to avoid an impossible date.
|sqlwarn7||CHAR(1)||If CONNECT is invoked and successful, contains a
Bif the server is BigSQL, or "D" if the server is Db2 Warehouse on Cloud.
Wif a character that could not be converted was replaced with a substitution character. Contains
Yif there was an unsuccessful attempt to establish a trusted connection.
Wif arithmetic expressions with errors were ignored during aggregate function processing.
Wif there was a conversion error when converting a character data value in one of the fields in the SQLCA.
|sqlstate||CHAR(5)||A return code that indicates the outcome of the most recently executed SQL statement.|
a Some functions may not set SQLWARN2 to W, even though null values were eliminated, because the result was not dependent on the elimination of null values.
The order of error reporting is:
- Severe error conditions are always reported. When a severe error is reported, there are no additions to the SQLCA.
- If no severe error occurs, a deadlock error takes precedence over other errors.
- For all other errors, the SQLCA for the first negative SQL code is returned.
- If no negative SQL codes are detected, the SQLCA for the first
warning (that is, positive SQL code) is returned.
In a partitioned database system, the exception to this rule occurs if a data manipulation operation is invoked against a table that is empty on one database partition, but has data on other database partitions. SQLCODE +100 is only returned to the application if agents from all database partitions return SQL0100W, either because the table is empty on all database partitions, or there are no more rows that satisfy the WHERE clause in an UPDATE statement.
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.
- 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.