| A program that includes SQL statements needs to have an area set apart for communication with DB2a SQL communication area (SQLCA). When DB2 processes a SQL statement in a program, it places return codes in the SQLCODE and SQLSTATE host variables or corresponding fields of the SQLCA. The return codes indicate whether the executed statement succeeded or failed. Because the SQLCA is a valuable problem-diagnosis tool, it is a good idea to include instructions to display some of the information contained in the SQLCA in your application programs. For example, the contents of SQLERRD(3)which indicates the number of rows that DB2 updates, inserts , or deletescould be useful. If SQLWARN0 contains W, DB2 has set at least one of the SQL warning flags (SQLWARN1 through SQLWARNA). A description of the SQLCA is shown in Table 5-1. Table 5-1. SQLCA | Name | Data Type | Purpose | | SQLCAID | CHAR(8) | An "eye catcher " for storage dumps, containing the text 'SQLCA'. | | SQLCABC | INTEGER | Contains the length of the SQLCA: 136. | | SQLCODE | INTEGER | Contains the SQL return code. | | | | Code | Means | | | | | Successful execution (though there might have been warning messages). | | | | positive | Successful execution, with an exception 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 pertinent. | | SQLERRMC | VARCHAR(70) | Contains one or more tokens, separated by X'FF', that are substituted for variables in the descriptions of error conditions. | | SQLERRP | CHAR(8) | Provides a product signature and, in the case of an error, diagnostic information such as the name of the module that detected the error. In all cases, the first three characters are DSN for DB2 for OS/390. | | SQLERRD(1) | INTEGER | Contains an internal error code. | | SQLERRD(2) | INTEGER | Contains an internal error code. | | SQLERRD(3) | INTEGER | Contains the number of rows affected after INSERT, UPDATE, and DELETE (but not rows deleted as a result of CASCADE delete). Set to 0 if the SQL statement fails, indicating that all changes made in executing the statement were canceled . Set to 1 for a mass delete from a table in a segmented tablespace. SQLERRD(3) can also contain the reason code of a timeout or deadlock for SQLCODES 911 and 913. | | SQLERRD(4) | INTEGER | Generally contains a timeron, a short floating-point value that indicates a rough relative estimate of resources required. It does not reflect an estimate of the time required. When preparing a dynamically defined SQL statement, you can use this field as an indicator of the relative cost of the prepared SQL statement. For a particular statement, this number can vary with changes to the statistics in the catalog. It is also subject to change between releases of DB2 for OS/390. | | SQLERRD(5) | INTEGER | Contains the position or column of a syntax error for a PREPARE or EXECUTE IMMEDIATE statement. | | SQLERRD(6) | INTEGER | Contains an internal error code. | | SQLWARN0 | CHAR(1) | Contains a W if at least one other indicator also contains a W; otherwise , contains a blank. | | SQLWARN1 | CHAR(1) | Contains a W if the value of a string column was truncated when assigned to a host variable. | | SQLWARN2 | CHAR(1) | Contains a W if null values were eliminated from the argument of a column function; not necessarily set to W for the MIN function because its results are not dependent on the elimination of null values. | | SQLWARN3 | CHAR(1) | Contains a W if the number of result columns is larger than the number of host variables. Contains a Z if fewer locators were provided in the ASSOCIATE LOCATORS statement than the stored procedure returned. | | SQLWARN4 | CHAR(1) | Contains a W if a prepared UPDATE or DELETE statement does not include a WHERE clause. | | SQLWARN5 | CHAR(1) | Contains a W if the SQL statement was not executed because it is not a valid SQL statement in DB2 for OS/390. | | SQLWARN6 | CHAR(1) | Contains a W if the addition of a month or year duration to a DATE or TIMESTAMP value results in an invalid day (for example, June 31). Indicates that the value of the day was changed to the last day of the month to make the result valid. | | SQLWARN7 | CHAR(1) | Contains a W if one or more nonzero digits were eliminated from the fractional part of a number used as the operand of a decimal multiply or divide operation. | | SQLWARN8 | CHAR(1) | Contains a W if a character that could not be converted was replaced with a substitute character. | | SQLWARN9 | CHAR(1) | Contains a W if arithmetic exceptions were ignored during COUNT DISTINCT processing. Contains a Z if the stored procedure returned multiple result sets. | | SQLWARNA | CHAR(1) | Contains a W if at least one character field of the SQLCA or the SQLDA names or labels is invalid due to a character conversion error. | | SQLSTATE | CHAR(5) | Contains a return code for the outcome of the most recent execution of a SQL statement. | SQLCODE and SQLSTATE Whenever an SQL statement executes, the SQLCODE and SQLSTATE fields of the SQLCA receive a return code. Although both fields serve basically the same purpose (indicating whether the statement executed successfully), there are some differences between the two fields. SQLCODE DB2 returns the following codes in SQLCODE: -
If SQLCODE = 0, execution was successful. -
If SQLCODE > 0, execution was successful with a warning. -
If SQLCODE < 0, execution was not successful. -
SQLCODE 100 indicates no data was found. The meaning of SQLCODEs other than 0 and 100 varies with the particular product implementing SQL. SQLSTATE SQLSTATE allows an application program to check for errors in the same way for different IBM database management systems. An advantage to using the SQLCODE field is that it can provide more specific information than SQLSTATE. Many of the SQLCODEs have associated tokens in the SQLCA that indicate, for example, which object incurred a SQL error. To conform to the SQL standard, the SQLCODE and SQLSTATE (SQLCOD and SQLSTA in FORTRAN) can be declared as standalone host variables. If the STDSQL(YES) precompiler option is specified, these host variables receive the return codes, and a SQLCA does not need to be included in the program. |
