Technologist Blog >> UDB SP Error Handling

Recently somebody asked us about how to ensure that when a DB2 UDB Stored Procedure fails Informatica workflow/session also fails.  This is not a very tough task but becomes handier if you have the code template with you.

Here is a sample procedure which returns the output of division operation. Its quite self explanatory.

SET SCHEMA SCHEMA_NAME;

DROP PROCEDURE SCHEMA_NAME.DIVIDE_BY;

CREATE PROCEDURE SCHEMA_NAME.DIVIDE_BY (IN NUMBER1 INTEGER,IN NUMBER2 INTEGER
        ,OUT V_RESULT INTEGER)
LANGUAGE SQL
SPECIFIC SCHEMA_NAME.DIVIDE_BY
BEGIN
        DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
        DECLARE O_SQLSTATE CHAR(5) DEFAULT '00000';

        DECLARE V_PROC_DATE TIMESTAMP;
        DECLARE V_PROC_NAME VARCHAR(20);

        DECLARE V_BATCH INTEGER;
        DECLARE V_MSG_TXT VARCHAR(500);
        -- DECLARE V_RESULT INTEGER;

        DECLARE EXIT HANDLER FOR SQLEXCEPTION
        BEGIN
                GET DIAGNOSTICS EXCEPTION 1 V_MSG_TXT = MESSAGE_TEXT;

                SELECT 'DIVIDE_BY', CURRENT TIMESTAMP, SQLSTATE
                INTO V_PROC_NAME, V_PROC_DATE, O_SQLSTATE
                FROM SYSIBM.SYSDUMMY1;

                ROLLBACK;

                INSERT INTO AUDIT_TRAIL (CREATE_TMS, PROC_NAME, MSG_TEXT)
                VALUES (V_PROC_DATE, V_PROC_NAME, V_MSG_TXT);

                COMMIT;

                SIGNAL SQLSTATE O_SQLSTATE SET MESSAGE_TEXT = 'PROCEDURE FAILED. SEE AUDIT_TRAIL TABLE' ;
        END;

        SET V_RESULT = NUMBER1 / NUMBER2;

END
/

CALL SCHEMA_NAME.DIVIDE_BY(20,2,?);     -- returns 10
CALL SCHEMA_NAME.DIVIDE_BY(20,0,?);     -- Populates error in the database table and throws the exception back.

The important statement used here is SIGNAL. The SIGNAL statement is used to signal an error or warning condition. It causes an error or warning to be returned with the specified SQLSTATE, along with optional message text. This is same as throwing an exception in Java. If you need more information do visit: http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/r0004232.htm