Checking the execution of SQL statements by using the WHENEVER statement
The WHENEVER statement causes DB2® to check the SQLCA and continue processing your program. If an error, exception, or warning occurs, DB2 branches to another area in your program. The condition handling area of your program can then examine the SQLCODE or SQLSTATE to react specifically to the error or exception.
About this task
The WHENEVER statement is not supported for REXX.
The WHENEVER statement enables you to specify what to do if a general condition is true. You can specify more than one WHENEVER statement in your program. When you do this, the first WHENEVER statement applies to all subsequent SQL statements in the source program until the next WHENEVER statement.
The WHENEVER statement looks like this:
EXEC SQL
WHENEVER condition action
END-EXECThe condition of the WHENEVER statement is one of these three values:
- SQLWARNING
- Indicates what to do when SQLWARN0 = W or SQLCODE contains a positive value other than 100. DB2 can set SQLWARN0 for several reasons—for example, if a column value is truncated when moved into a host variable. Your program might not regard this as an error.
- SQLERROR
- Indicates what to do when DB2 returns an error code as the result of an SQL statement (SQLCODE < 0).
- NOT FOUND
- Indicates what to do when DB2 cannot find a row to satisfy your SQL statement or when there are no more rows to fetch (SQLCODE = 100).
The action of the WHENEVER statement is one of these two values:
- CONTINUE
- Specifies the next sequential statement of the source program.
- GOTO or GO TO host-label
- Specifies the statement identified by host-label. For host-label, substitute a single token, preceded by an optional colon. The form of the token depends on the host language. In COBOL, for example, it can be section-name or an unqualified paragraph-name.
The WHENEVER statement must precede the first SQL statement it is to affect. However, if your program checks SQLCODE directly, you must check SQLCODE after each SQL statement.