SQL Execution Validation

A program that includes SQL statements needs to have an area set apart for communication with DB2an SQL communication area (SQLCA). When it processes an SQL statement in a program, DB2 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 deletes, could 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 10-1.

Table 10-1. SQLCA Codes

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: 0 (successful execution, although 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 z/OS.
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 table space. SQLERRD(3) can also contain the reason code of a timeout or deadlock for SQLCODES 911 and 913.
SQLERRD(4)	INTEGER	Generally contains a timer-on, 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 z/OS.
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 z/OS.
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, such as 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 because of a character-conversion error.
SQLSTATE	CHAR(5)	Contains a return code for the outcome of the most recent execution of an SQL statement.

SQLCODE and SQLSTATE Fields

Whenever an SQL statement executes, the SQLCODE and SQLSTATE fields of the SQLCA receive a return code. Although both fields indicate whether the statement executed successfully, they have some differences.

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.

• If SQLCODE = 100, no data was found.

The meaning of SQLCODEs other than 0 and 100 varies with the particular product implementing SQL.

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 can SQLSTATE. Many of the SQLCODEs have associated tokens in the SQLCA that indicate, for example, which object incurred an SQL error. The advantage of SQLSTATE is its cross-vendor usability.

To conform to the SQL standard, the SQLCODE and SQLSTATE fields (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 the SQLCA does not need to be included in the program.

GET DIAGNOSTICS Statement

The GET DIAGNOSTICS statement can be used to return diagnostic information about the last SQL statement that was executed. Requests can be made for all diagnostic items or individual items of diagnostic information from the following categories:

• Statement items, which contain information about the SQL statement as a whole

• Condition items, which contain information about each error or warning that occurred during the execution of the SQL statement

• Connection items, which contain information about the SQL statement if it was a CONNECT statement

The GET DIAGNOSTICS statement can be used to handle multiple SQL errors that might result from the execution of a single SQL statement. First, check SQLSTATE or SQLCODE to determine whether diagnostic information should be retrieved by using GET DIAGNOSTICS. This method is especially useful for diagnosing problems that result from a multiple-row INSERT that is specified as NOT ATOMIC CONTINUE ON SQLEXCEPTION.

Even if the GET DIAGNOSTICS statement is used in the application program to check for conditions, instructions must be included to use the SQLCA, or SQLSTATE or SQLCODE must be declared separately in the program.

When the GET DIAGNOSTICS statement is used, the requested diagnostic information is assigned to host variables. Declare each target host variable with a data type that is compatible with the data type of the requested item. Then, in order to retrieve condition information, the number of condition items must first be retrieved.

Table 10-2 shows the data types for the GET DIAGNOSTICS items that return statement information. The various GET DIAGNOSTIC statements can provide useful and informative information for application communication. The following example shows how to use the GET DIAGNOSTIC statement to get the count of the number of rows that a particular statement updated:

 EXEC SQL    GET DIAGNOSTICS :rcount = ROW_COUNT; 

Table 10-2. Data Types for GET DIAGNOSTICS Items Returning Statement Information

Item	Description	Data Type
DB2_GET_DIAGNOSTICS _DIAGNOSTICS	After a GET DIAGNOSTICS statement, if any error or warning occurred, contains all the diagnostics as a single string	VARCHAR (32672)
DB2_LAST_ROW	After a multiple-row FETCH statement, contains a value of +100 if the last row in the table is in the row set that was returned.	INTEGER
DB2_NUMBER_PARAMETER_MARKERS	After a PREPARE statement, contains the number of parameter markers in the prepared statement.	INTEGER
DB2_NUMBER_RESULT_SETS	After a CALL statement that invokes a stored procedure, contains the number of result sets that are returned by the procedure.	INTEGER
DB2_NUMBER_ROWS	After an OPEN or FETCH statement for which the size of the result table is known, contains the number of rows in the result table. After a PREPARE statement, this item contains the estimated number of rows in the result table for the prepared statement. For SENSITIVE DYNAMIC cursors, this item contains the approximate number of rows.	DECIMAL (31,0)
DB2_RETURN_STATUS	After a CALL statement that invokes an SQL procedure, contains the return status if the procedure contains a RETURN statement.	INTEGER
DB2_SQL_ATTR _CURSOR_HOLD	After an ALLOCATE or OPEN statement, indicates whether the cursor can be held CHAR(1) open across multiple units of work (Y or N).	CHAR(1)
DB2_SQL_ATTR _CURSOR_ROWSET	After an ALLOCATE or OPEN statement, indicates whether the cursor can use row set positioning (Y or N).	CHAR(1)
DB2_SQL_ATTR _CURSOR_SCROLLABLE	After an ALLOCATE or OPEN statement, indicates whether the cursor is scrollable (Y or N).	CHAR(1)
DB2_SQL_ATTR _CURSOR_SENSITIVITY	After an ALLOCATE or OPEN statement, indicates whether the cursor shows updates made by other processes (sensitivity A, I, or S).	CHAR(1)
DB2_SQL_ATTR _CURSOR_TYPE	After an ALLOCATE or OPEN statement, indicates whether the cursor is declared static (S for INSENSITIVE or SENSITIVE STATIC) or dynamic (D for SENSITIVE DYNAMIC).	CHAR(1)
MORE	After any SQL statement, indicates whether some condition items were discarded because of insufficient storage (Y or N).	CHAR(1)
NUMBER	After any SQL statement, contains the number of condition items. If no warning or error occurred or if no previous SQL statement has been executed, the number 1 is returned.	INTEGER
ROW_COUNT	After DELETE, INSERT, UPDATE, or FETCH, contains the number of rows deleted, inserted, updated, or fetched. After PREPARE, contains the estimated number of result rows in the prepared statement.	DECIMAL (31,0)

Table 10-3 shows the data types for GET DIAGNOSTICS items that return condition information.

Table 10-3. Data Types for GET DIAGNOSTICS Items Returning Condition Information

Item	Description	Data Type
CATALOG_NAME	Contains the server name of the table that owns a constraint that caused an error or that caused an access rule or check violation.	VARCHAR(128)
CONDITION_NUMBER	Contains the number of the condition.	INTEGER
CURSOR_NAME	Contains the name of a cursor in an invalid cursor state.	VARCHAR(128)
DB2_ERROR_CODE1	Contains an internal error code.	INTEGER
DB2_ERROR_CODE2	Contains an internal error code.	INTEGER
DB2_ERROR_CODE3	Contains an internal error code.	INTEGER
DB2_ERROR_CODE4	Contains an internal error code.	INTEGER
DB2_INTERNAL_ERROR_POINTER	For some errors, contains a negative value that is an internal error pointer.	INTEGER
DB2_MESSAGE_ID	Contains the message ID that corresponds to the message contained in the CHAR(10)MESSAGE_TEXT diagnostic item.	INTEGER
DB2_MODULE_DETECTINGERROR	After any SQL statement, indicates which module detected the error.	CHAR(8)
DB2_ORDINAL_TOKEN_n	After any SQL statement, contains the nth token, where n is a value from 1 to 100.	VARCHAR(515)
DB2_REASON_CODE	After any SQL statement, contains the reason code for errors that have a reason-code token in the message text.	INTEGER
DB2_RETURNED_SQLCODE	After any SQL statement, contains the SQLCODE for the condition.	INTEGER
DB2_ROW_NUMBER	After any SQL statement that involves multiple rows, contains the row number on which DB2 detected the condition.	DECIMAL (31,0)
DB2_TOKEN_COUNT	After any SQL statement, contains the number of tokens available for the condition.	INTEGER
MESSAGE_TEXT	After any SQL statement, contains the message text associated with the SQLCODE.	VARCHAR (32672)
RETURNED_SQLSTATE	After any SQL statement, contains the SQLSTATE for the condition.	CHAR(5)
SERVER_NAME	After a CONNECT, DISCONNECT, or SET CONNECTION statement, contains the name of the server specified in the statement.	VARCHAR(128)

Table 10-4 shows the data types for GET DIAGNOSTICS items that return connection information.

Table 10-4. Data Types for GET DIAGNOSTICS Items Returning Connection Information

Item	Description	Data type
DB2_AUTHENTICATION_TYPE	Contains the authentication type (S, C, D, E, or blank).	CHAR(1)
DB2_AUTHORIZATION_ID	Contains the authorization ID used by the connected server.	VARCHAR(128)
DB2_CONNECTION_STATE	This item indicates whether the connection is unconnected (-1), local (0), or remote (1).	INTEGER
DB2_CONNECTION_STATUS	This item indicates whether updates can be committed for the current unit of work (1 for yes or 2 for no).	INTEGER
DB2_ENCRYPTION_TYPE	Contains A or D, indicating the level of encryption for the connection: (A = only the authentication tokens (authid and password) are encrypted; D = all the data for the connection is encrypted).	CHAR(1)
DB2_SERVER_CLASS_NAME	After a CONNECT or SET CONNECTION statement, contains the DB2 server class name.	VARCHAR(128)
DB2_PRODUCT_ID	Contains the DB2 product signature.	VARCHAR(8)