This post is all about Error handling in DB2 in brief. I am trying to cover everything in only single post. So perhaps some advanced topics are missed or would be discussed in short.

Prerequisites:

• writing basic & simple stored procedures in DB2.

Write a procedure and use below steps to handle runtime errors.

1. Write this statement in declaration section. Declaration must take place before any cursor declaration.

DECLARE SQLCODE INT DEFAULT 0;

2. Now define Exit Handler in declaration area. It may take place after cursor declaration as well.

DECLARE EXIT HANDLER FOR SQLEXCEPTION
    C1:BEGIN
        SET O_RET_VAL = SQLCODE;
    END C1;

Explanation :
1. All statements which should executed against an exception, take place inside BEGIN..END block.
2. Last word of first line is EXCEPTION code or Name. Read more exception code in last of this post.
3. Inside BEGIN..END block, you may set OUT parameters, ROLLBACK etc.
Sample SP:

CREATE PROCEDURE proc_name(IN var INTEGER,
                            OUT debugStatement varchar(100),
                            OUT O_RET_VAL INTEGER,
                            OUT O_SQLSTATE CHAR(5))
    LANGUAGE SQL
    P1 :BEGIN
        --Declaration Of Local Variables---
        DECLARE SQLCODE INT DEFAULT 0;            --Declare before declaration of any cursor
        DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
        --Cursor Declaration--
    ----------------------------Error Handling--------------------------------
        DECLARE EXIT HANDLER FOR SQLEXCEPTION
        BEGIN
             SET O_RET_VAL = SQLCODE;
             SET O_SQLSTATE = SQLSTATE;
             SET debugStatement = 'Exit due to SQL Exception';
             --or in short
             --VALUES (SQLCODE, SQLSTATE, 'Exit due to SQL Exception' )INTO (O_RET_VAL, O_SQLSTATE, debugStatement);
            END;
    ---------------------------Error Handling End-----------------------------
        :
        SET debugStatement = 'Middle of SP';
        :
        :
        SET debugStatement = 'End of SP, completed Successfully.';
    END P1
    @

Compile in UNIX : db2 -td@ -f
Run in UNIX : db2 “call proc_name(23,?,?)

Tip: We can set Output parameters anywhere throughout the SP. When a SP stops normally or abnormally, it returns current value of output parameters automatically.

We have completed basic of exception handling in DB2. Now we are switching to some advanced topics.

Some more Handlers :

SQL PL supports three types of handlers: EXIT, CONTINUE, and UNDO.
The EXIT handler will execute the SQL PL statements in the handler. After that the handler will continue with execution at the end of the compound statement in which it was declared.

An UNDO handler is similar to the EXIT handler and continues with execution at the end of the compound statement in which it was declared. But instead of the EXIT handler each executed statement will be rolled back in this compound statement. UNDO handlers are just possible in ATOMIC compound statements.

In opposite to the EXIT handler, the CONTINUE handler will continue the execution at the statement which is following the statement that raised the exception.
Note: Both CONTINUE, and UNDO are defined in the same way as EXIT handler is defined. Only their working differs.

You can define multiple handlers together.

DECLARE c_duplicate CONDITION FOR SQLSTATE '23505';
    DECLARE FOREIGN_KEY_VIOLATION CONDITION FOR SQLSTATE ‘23503’;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    Begin
        :
    End;
    DECLARE EXIT HANDLER FOR c_duplicate
    Begin
        :
    End;
    DECLARE EXIT HANDLER FOR FOREIGN_KEY_VIOLATION
    Begin
        :
    End;
    DECLARE CONTINUE HANDLER FOR c_duplicate
    Begin
        :
    End;

Like Java or other programming language you might need to throw or raise an exception manually. For this;

    SIGNAL '23505'
or
    DECLARE c_duplicate CONDITION FOR SQLSTATE '23505';
    :
    SIGNAL c_duplicate;

Once an exception is raised it’ll search for corresponding exception handler block. If no one exist then it’ll terminate the SP abnormally.

You can also set Message with raised exception as follows;

    SIGNAL c_duplicate
    SET MESSAGE_TEXT = 'Records are duplicated for table name';

    DECLARE c_too_many_rows CONDITION FOR SQLSTATE '99001';
    DECLARE c_duplicate CONDITION FOR SQLSTATE '23505';
    DECLARE FOREIGN_KEY_VIOLATION CONDITION FOR SQLSTATE ‘23503’;
    DECLARE overflow CONDITION FOR SQLSTATE ’22003’;
    DECLARE c_error CONDITION FOR SQLSTATE '99999';

Amit Gupta

Hey! this is Amit Gupta (amty). By profession, I am a Software Eng. And teaching is my passion. Sometimes I am a teacher, as you can see many technical tutorials on my site, sometimes I am a poet, And sometime just a friend of friends...