DB2 Server for VSE & VM: Application Programming

Handling Errors in a Select-List

The database manager tolerates the occurrence of certain errors resulting from the execution of expressions occurring in a select_list of an outer select statement.

Handling Arithmetic Errors

The arithmetic errors that can be tolerated are listed in Figure 60.

Figure 60. Tolerated Arithmetic Errors

Arithmetic Errors That Will Be Tolerated
DECIMAL Divide Exception Decimal Overflow FLOAT Divide Exception Exponent Overflow INTEGER, SMALLINT Divide Exception Fixed Point Overflow

Note:

FLOAT can be either single-precision or double-precision float. Refer to Assigning Data Types When the Column Is Created for more information on floating-point data types.

Errors in date and time arithmetic are not tolerated. For example,

   DATE('9999-12-31') + 1 DAY

results in a negative SQLCODE because the result would be an invalid date.

In the next example however, if the value of C2 is zero, the arithmetic error would be tolerated.

|   DATE('1999-12-31') + C1/C2 DAYS

The expression in the outer select_list may be by itself, or it can be an argument in a scalar or column function other than the column functions AVG and SUM.

If the errors occur on an outer select_list, and every output host variable that is associated with the expression that is in error has an associated output indicator variable, the system does the following:

• The output indicator variable for each arithmetic expression in error is set to -2.
• A positive warning SQLCODE is placed in the SQLCA.
• SQLWARN0 in the SQLCA remains unaffected.
• The value of the associated host variable is undefined.
• Execution of the statement continues, such that all expressions and values not having arithmetic errors are returned.
• If the statement is a FETCH, the cursor remains open.

However, if the errors do not occur on the outer select_list, or if there are arithmetic errors on the select_list and not every output host variable that is associated with the expression in error has an associated output indicator variable, the system takes the following actions:

• A negative error SQLCODE -802 (SQLSTATE '22003') is returned in the SQLCA.
• The values of the host variables and any supplied indicator variables in the select_list are undefined.
• Execution of the statement is halted.
• If the statement is a FETCH, the cursor will remain open.

In either case, the SQLERRM of the SQLCA error message will identify the first expression in error in the outer select_list. The following are returned in the error message:

• The exception type
• The arithmetic operation being performed at the time of the error
• The data type of the select_list items being manipulated
• The ordinal position of the expression in error.

Depending on when the error is detected, some parts of the error message will be blank.

Handling Numeric Conversion Errors

The numeric conversion errors that can be tolerated are listed in Figure 61.

Figure 61. Tolerated Numeric Conversion Errors

FLOAT to DECIMAL INTEGER SMALLINT DECIMAL to DECIMAL FLOAT INTEGER SMALLINT INTEGER to DECIMAL SMALLINT SMALLINT to DECIMAL

Note:

FLOAT can be either single-precision or double-precision float. Refer to Assigning Data Types When the Column Is Created for more information on floating-point data types.

If an error occurs while converting numeric values into the data type of the host variables, and output indicator variables are provided with host variables, for which numeric conversion errors occurred, the system does the following:

• The output indicator variable for each host variable for which a numeric conversion error occurred is set to -2.
• If no other warning SQLCODE is contained in the SQLCA, then a positive warning SQLCODE is placed in the SQLCA and the error message tokens in SQLERRM will identify the first conversion error.
• SQLWARN0 in the SQLCA is unaffected.
• The values of the associated host variables are undefined.
• Execution of the statement continues such that all values not in error are returned to your program.
• If the statement is a FETCH then the cursor will remain open.

If output indicator variables are not provided for host variables for which numeric conversion errors occurred, the system does the following:

• A negative error SQLCODE is placed in the SQLCA
• The SQLCA error message tokens identify the first conversion error
• The values of the host variables and indicators are undefined
• Execution of the statement is halted
• If the statement is a FETCH then the cursor will remain open.

In either case, the SQLERRM of the SQLCA will identify the first expression in error, and the following will be returned in the error message:

1. The data type of the value being moved into the host variable
2. The ordinal position of the expression in error
3. The data type of the host variable.

Handling CCSID Conversion Errors

The database manager tolerates CCSID conversion errors in which a character or characters have been mapped to the defined error byte.

If this occurred during CCSID conversion of data to be returned to the user, and output indicator variables are provided with host variables, the system does the following:

• For each host variable for which the CCSID conversion error byte mapping has occurred, the output indicator is set to -2.
• If no other warning SQLCODE is in the SQLCA, a positive warning SQLCODE is placed there, and the error message tokens in SQLERRM will identify the first conversion error.
• SQLWARN0 in the SQLCA is unaffected.
• The values of the associated host variables are undefined.
• Execution of the statement continues and returns all correct values to your program.
• For a FETCH statement, the cursor remains open.

If output indicator variables are not provided for host variables for which CCSID conversion errors occurred, the database manager does the following:

• A negative SQLCODE is placed in the SQLCA.
• The SQLCA error message tokens identify the first conversion error.
• The values of the host variables, and any indicators, are undefined.
• Execution of the statement is halted.
• For a FETCH statement, the cursor remains open.