DB2 Version 10.1 for Linux, UNIX, and Windows
DB2 Version 10.1 for Linux, UNIX, and Windows
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.
| 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.
|
| 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) | Begins with a three-letter
identifier indicating the product, followed by five alphanumeric characters
indicating the version, release, and modification level of the product.
The characters A-Z indicate a modification level higher than 9. A
indicates modification level 10, B indicates modification level 11,
and so on. For example, SQL0907C means DB2® Version
9, release 7, modification level 12). If SQLCODE indicates an error condition, this field identifies the module that returned the error. This field is also used when a successful connection is completed. |
| 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:
|
| 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) | Reserved for future use. |
| 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. |
||
The order of error reporting is as follows:
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.
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.
Note that SQLSTATE 09000 may not be returned every time an error occurs during the processing of a triggered SQL statement.