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 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 field descriptions

Table 1. Fields of the SQLCA. The field names shown are those present in an SQLCA that is obtained via an INCLUDE statement.
Name Data Type Field Values
sqlcaid CHAR(8) An eye catcher for storage dumps containing SQLCA. The sixth byte is L if line number information is returned from parsing an SQL routine, SQL trigger, or dynamic compound SQL statement. The sixth byte is M if 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:
0
Successful execution (although one or more SQLWARN indicators may be set).
positive
Successful execution, but with a warning condition.
negative
Error condition.
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:
  • vv represents the version number
  • rr represents the release number
  • m represents the modification value

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.

Product           SQLERRP
signature         token
Db2 10.5.0.7      SQL10057
Db2 11.0.0.0      SQL11010
Db2 11.1.1.1      SQL11011
Db2 11.1.2.0      SQL11012
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.
sqlerrd(2) 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 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.

If 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 L for this entry to be a valid line number.

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 M for this to be a valid line number.
sqlerrd(4) INTEGER

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 M for this entry to be a valid line number
sqlerrd(5) INTEGER
Contains the total number of rows deleted, inserted, or updated as a result of both:
  • The enforcement of constraints after a successful delete operation
  • The processing of triggered SQL statements from activated inlined triggers
If compound SQL is invoked, contains an accumulation of the number of such rows for all sub-statements. In some cases, when an error is encountered, this field contains a negative value that is an internal error pointer. If CONNECT is invoked, contains an authentication type value:
  • 0 for server authentication
  • 1 for client authentication
  • 2 for authentication using Db2® Connect
  • 4 for SERVER_ENCRYPT authentication
  • 5 for authentication using Db2 Connect with encryption;
  • 7 for KERBEROS authentication
  • 9 for GSSPLUGIN authentication
  • 11 for DATA_ENCRYPT authentication
  • 255 for unspecified authentication.
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). For more information, see Encryption of data in transit
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 blank; contains W if at least one other indicator is not blank.
sqlwarn1 CHAR(1) Contains W if the value of a string column was truncated when assigned to a host variable. Contains N if the null terminator was truncated. Contains A if the CONNECT or ATTACH is successful, and the authorization name for the connection is longer than 8 bytes. Contains P if 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.
sqlwarn2 CHAR(1) Contains W if null values were eliminated from the argument of an aggregate function. a

If CONNECT is invoked and successful, contains D if the database is in quiesce state, or I if the instance is in quiesce state.
sqlwarn3 CHAR(1) Contains W if the number of columns is not equal to the number of host variables. Contains Z if the number of result set locators specified on the ASSOCIATE LOCATORS statement is less than the number of result sets returned by a procedure.
sqlwarn4 CHAR(1) Contains W if a prepared UPDATE or DELETE statement does not include a WHERE clause.
sqlwarn5 CHAR(1) Contains E if an error was tolerated during SQL statement execution.
sqlwarn6 CHAR(1) Contains W if the result of a date calculation was adjusted to avoid an impossible date.
sqlwarn7 CHAR(1) If CONNECT is invoked and successful, contains a B if the server is BigSQL, or "D" if the server is Db2 Warehouse SaaS.
sqlwarn8 CHAR(1) Contains W if a character that could not be converted was replaced with a substitution character. Contains Y if there was an unsuccessful attempt to establish a trusted connection.
sqlwarn9 CHAR(1) Contains W if arithmetic expressions with errors were ignored during aggregate function processing.
sqlwarn10 CHAR(1) Contains W if 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.

Error reporting

The order of error reporting is:

  1. Severe error conditions are always reported. When a severe error is reported, there are no additions to the SQLCA.
  2. If no severe error occurs, a deadlock error takes precedence over other errors.
  3. For all other errors, the SQLCA for the first negative SQL code is returned.
  4. 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.

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.
Note: SQLSTATE 09000 might not be returned every time an error occurs during the processing of a triggered SQL statement.