Teaches you how to use a DB2 conditional processor to handle SQL errors during stored procedures

DB2 conditional processors play an irreplaceable role in stored procedures. In DB2, SQL stored procedures can use the DB2 conditional processor Condition Handler) to handle SQL errors in the stored procedure running SQLERROR), SQL warning SQLWARNING) and no data NOT FOUND) you can use three condition processors, including EXIT, CONTINUE), and UNDO, as well as the trigger conditions you have defined.

If SQL error, SQLWARNING, and not found occur during SQL stored procedures, the SQL stored procedure will automatically store the SQLCODE and SQLSTATE after executing the SQL statement in the variables SQLCODE and SQLSTATE you have defined in advance, and trigger the conditional processor you have defined in the stored procedure.

To handle errors in SQL stored procedures, you need to perform the following two steps: declare SQLCODE and SQLSTATE variables, and define a condition processor. In SQL stored procedures, you declare the SQLCODE and SQLSTATE variables using the following statements:
Declare sqlcode integer default 0;
Declare sqlstate char (5) DEFAULT '20140901 ';

When the stored procedure is executed, DB2 automatically pays the return code of the SQL statement to the two variables. you can insert these two values into the debugging table during program debugging, or use the condition processor to return these two values to the caller. This facilitates debugging of SQL stored procedures. Note: When you access SQLCODE and SQLSTATE during SQL storage, DB2 automatically sets SQLCODE and SQLSTATE to zero.

You can use the following statements to define the DB2 conditional processor:
DECLARE handler-type handler for condition
SQL-procedure-statement

Handler-type can be:

CONTINUE: After the SQL statement in the condition processor is executed, the SQL statement following the SQL statement with an error continues to be executed.

EXIT: After the SQL statement in the condition processor is executed, the SQL stored procedure is exited.

UNDO: This conditional processor is limited to ATOMIC actions ATOMIC) composite SQL statements. The SQL stored procedure rolls back the composite SQL statements containing the conditional processor, after executing the SQL statement in the condition processor, continue to execute the ATOMIC action ATOMIC) the SQL statement following the composite SQL statement.

The conditions include the following three common cases:

SQLEXCEPTION: returns any negative value during SQL Execution.

SQLWARNING: The warning SQLWARN0 is 'W' during SQL Execution, or any positive SQL return values that are not + 100, the corresponding SQLSTATE starts with '01.

Not found: the SQL return value is + 100 or the SQLSTATE starts with '02.
Of course, you can also use the DECLARE statement to define your own conditions for a specific SQLSATE.

Implementation of DB2 date format change

Basic knowledge of DB2 Date and Time

Tips for DB2 data deletion

DB2 event monitoring command

How to Implement online DB2 Import