Handling SQL Errors

Using the error handling capability of the Stored Procedures stage, you can check for DB2-specific database errors, define your own error codes, or do both. Declare condition variables and a handler for each condition. Rather than communicating directly with programmers, SQL returns error codes to the application program in the form of SQLCODE and SQLSTATE. The Stored Procedure Stage uses RESIGNAL control statement to return a user-defined SQLSTATE and a user-defined error message to the calling process. The following example defines all key elements of the user-defined error handling:

DECLARE rec_cnt int;
DECLARE SQLSTATE CHAR(5);
DECLARE EXIT HANDLER FOR SQLSTATE '75000'
RESIGNAL SQLSTATE '75000' SET MESSAGE_TEXT = 'Invalid department number.';
SELECT count (*) INTO rec_cnt FROM DB2ADMIN.STPTEST WHERE STPTEST.ACHAR='XYZ';
IF rec_cnt = 0
THEN SIGNAL SQLSTATE '75000';
END IF;

Provide the following values on the Error Codes tab of the Stage page:

• Fatal errors: 2002
• Warnings: 2001 1479

where 2001 means NO DATA FOUND and 1476 means ZERO DIVIDE.

In this example, NO DATA FOUND and ZERO DIVIDE are treated as warnings, the job continues to run, and the stage processes subsequent rows. All other errors are fatal and cause the job to abort.

If you select Procedure status to link on the General tab of the Output page, RESIGNAL control statement does a rollback on pending rows. The error codes and messages from RESIGNAL control statement are passed down the output link as the first two columns.