Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2014
    Posts
    2

    Question Unanswered: Error Handling for called stored procedure db2

    Am new to db2 and got stuck in an issue.

    I have a procedure try_sk as given below:
    CREATE or replace PROCEDURE tempdb.try_sk(in var1 integer,out o_var1 integer,out o_error varchar(60)
    )
    specific tempdb.try_sk
    BEGIN
    --declare ct_date date;
    declare i_var1 integer;
    declare v_sqlstate char(5) default '00000';
    declare v_sqlcode integer;
    declare sqlstate char(5) default '00000';
    declare sqlcode integer;
    declare v_tracker varchar(50);
    declare V_EXIT_MESSAGE varchar(70);
    declare o_error varchar(70);

    DECLARE continue HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND
    BEGIN
    VALUES (SQLCODE,SQLSTATE) INTO V_SQLCODE,V_SQLSTATE;--
    SET V_EXIT_MESSAGE = 'ERRORQLSTATE-'|| TRIM(V_SQLSTATE) || ',' || TRIM(CHAR(V_SQLCODE)) || ' AT ' || v_tracker;--
    SIGNAL SQLSTATE VALUE V_SQLSTATE SET MESSAGE_TEXT = V_EXIT_MESSAGE;--
    END;
    /*DECLARE GLOBAL TEMPORARY TABLE SESSION.t_error
    (
    error varchar (70)
    )
    ON COMMIT PRESERVE ROWS
    WITH REPLACE
    NOT LOGGED
    IN PDPG_USERTEMP;
    insert into session.t_error(error) values(v_exit_message);
    commit;*/
    SET V_TRACKER = 'divide by var1';
    set o_error = v_exit_message;
    begin
    declare c1 cursor with return to caller for select o_error from sysibm.sysdummy1;
    open c1;
    end;
    select 1/var1 into i_var1 from sysibm.sysdummy1;
    set o_var1 = i_var1;


    END


    here i am just dividing 1 by the input parameter we pass while calling proc. In case 0 is passed as input parameter(call try_sk(0,?,?), i get the below error message as desired.

    [IBM][CLI Driver][DB2/LINUXX8664] SQL0438N Application raised error or warning with diagnostic text: "ERRORQLSTATE-22012,-801 AT divide by var1". SQLSTATE=22012

    This procedure when called by an another procedure try1_sk
    doesn't return error by internal procedure.

    code for try1_sk:-

    create or replace procedure try1_sk(in var1 integer,out o_var1 integer,o_error varchar(60))
    specific try1_sk
    begin
    declare v_sqlstate1 char(5) default '00000';
    declare v_sqlcode1 integer;
    declare sqlstate char(5) default '00000';
    declare sqlcode integer;
    declare v_tracker1 varchar(50);
    declare V_EXIT_MESSAGE1 varchar(70);
    declare error varchar(60);
    DECLARE LOC1 RESULT_SET_LOCATOR VARYING;

    DECLARE continue HANDLER FOR SQLEXCEPTION
    BEGIN
    VALUES (SQLCODE,SQLSTATE) INTO V_SQLCODE1,V_SQLSTATE1;--

    SET V_EXIT_MESSAGE1 = 'ERRORQLSTATE-'|| TRIM(V_SQLSTATE1) || ',' || TRIM(CHAR(V_SQLCODE1)) || ' AT ' || v_tracker1;--
    SIGNAL SQLSTATE VALUE V_SQLSTATE1 SET MESSAGE_TEXT = V_EXIT_MESSAGE1;--
    END;
    SET V_TRACKER1 = 'in procedure';
    call tempdb.try_sk(var1,o_var1,o_error);
    /*ASSOCIATE RESULT SET LOCATOR (LOC1) WITH PROCEDURE tempdb.try_sk;
    ALLOCATE Cur1 CURSOR FOR RESULT SET loc1;
    fetch from cur1 into error;
    close cur1;*/
    end


    on calling the above procedure with 0 as input (call tempdb.try1_sk(0,?,?)
    the below error is shown

    [IBM][CLI Driver][DB2/LINUXX8664] SQL0438N Application raised error or warning with diagnostic text: "ERRORQLSTATE-22012,-438 AT in procedure". SQLSTATE=22012

    Here as we can see the error message shown is that of calling procedure.
    Since our real code will be very huge it becomes difficult to debug when the line where error is encountered by inner proc(called proc) is not shown.

    Can anyone please suggest a way in which we can get error message of inner proc/called proc
    i.e on running call try1_sk(0,?,?);

    we get the below error also

    IBM][CLI Driver][DB2/LINUXX8664] SQL0438N Application raised error or warning with diagnostic text: "ERRORQLSTATE-22012,-801 AT divide by var1". SQLSTATE=22012

    This is achieved if error handling of calling procedure is removed but we want to keep error handling in both called and calling procedures hence this method cannot be used.

    Please not :- i tried using global temporary table and cursor but couldn't use it efficiently (have commented that part of code)

  2. #2
    Join Date
    Jun 2014
    Posts
    2
    Problem:- To display the error message from called stored procedure .

    Solution :- There are two ways in which this issue can be resolved
    1. By using DGTT with continue handler and removing the signal command. The issue in this would be that even after encountering error the procedure will execute till end
    2. By using CGTT with exit handler itself. The issue with CGTT is that replace command while creating CGTT is not available. Hence, Dynamic Sql to truncate table can be used.

  3. #3
    Join Date
    Jan 2015
    Posts
    2

    Handling Stored Proc Errors in DB2

    Hi Sneha,

    I am trying something similar where I have a master SP which calls SP1, SP2 and SP3. In case of any of the SP's (SP1, SP2 or SP3) fails I need to propagate the error message to main SP. Do you have any idea on implementing this? I declared Exit handlers in each sub SP's and also in the main SP but couldnt get the inner SP's error message.

    Thanks in advance!!

    Regards,
    SK

    Quote Originally Posted by sneha.k17 View Post
    Am new to db2 and got stuck in an issue.

    I have a procedure try_sk as given below:
    CREATE or replace PROCEDURE tempdb.try_sk(in var1 integer,out o_var1 integer,out o_error varchar(60)
    )
    specific tempdb.try_sk
    BEGIN
    --declare ct_date date;
    declare i_var1 integer;
    declare v_sqlstate char(5) default '00000';
    declare v_sqlcode integer;
    declare sqlstate char(5) default '00000';
    declare sqlcode integer;
    declare v_tracker varchar(50);
    declare V_EXIT_MESSAGE varchar(70);
    declare o_error varchar(70);

    DECLARE continue HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND
    BEGIN
    VALUES (SQLCODE,SQLSTATE) INTO V_SQLCODE,V_SQLSTATE;--
    SET V_EXIT_MESSAGE = 'ERRORQLSTATE-'|| TRIM(V_SQLSTATE) || ',' || TRIM(CHAR(V_SQLCODE)) || ' AT ' || v_tracker;--
    SIGNAL SQLSTATE VALUE V_SQLSTATE SET MESSAGE_TEXT = V_EXIT_MESSAGE;--
    END;
    /*DECLARE GLOBAL TEMPORARY TABLE SESSION.t_error
    (
    error varchar (70)
    )
    ON COMMIT PRESERVE ROWS
    WITH REPLACE
    NOT LOGGED
    IN PDPG_USERTEMP;
    insert into session.t_error(error) values(v_exit_message);
    commit;*/
    SET V_TRACKER = 'divide by var1';
    set o_error = v_exit_message;
    begin
    declare c1 cursor with return to caller for select o_error from sysibm.sysdummy1;
    open c1;
    end;
    select 1/var1 into i_var1 from sysibm.sysdummy1;
    set o_var1 = i_var1;


    END


    here i am just dividing 1 by the input parameter we pass while calling proc. In case 0 is passed as input parameter(call try_sk(0,?,?), i get the below error message as desired.

    [IBM][CLI Driver][DB2/LINUXX8664] SQL0438N Application raised error or warning with diagnostic text: "ERRORQLSTATE-22012,-801 AT divide by var1". SQLSTATE=22012

    This procedure when called by an another procedure try1_sk
    doesn't return error by internal procedure.

    code for try1_sk:-

    create or replace procedure try1_sk(in var1 integer,out o_var1 integer,o_error varchar(60))
    specific try1_sk
    begin
    declare v_sqlstate1 char(5) default '00000';
    declare v_sqlcode1 integer;
    declare sqlstate char(5) default '00000';
    declare sqlcode integer;
    declare v_tracker1 varchar(50);
    declare V_EXIT_MESSAGE1 varchar(70);
    declare error varchar(60);
    DECLARE LOC1 RESULT_SET_LOCATOR VARYING;

    DECLARE continue HANDLER FOR SQLEXCEPTION
    BEGIN
    VALUES (SQLCODE,SQLSTATE) INTO V_SQLCODE1,V_SQLSTATE1;--

    SET V_EXIT_MESSAGE1 = 'ERRORQLSTATE-'|| TRIM(V_SQLSTATE1) || ',' || TRIM(CHAR(V_SQLCODE1)) || ' AT ' || v_tracker1;--
    SIGNAL SQLSTATE VALUE V_SQLSTATE1 SET MESSAGE_TEXT = V_EXIT_MESSAGE1;--
    END;
    SET V_TRACKER1 = 'in procedure';
    call tempdb.try_sk(var1,o_var1,o_error);
    /*ASSOCIATE RESULT SET LOCATOR (LOC1) WITH PROCEDURE tempdb.try_sk;
    ALLOCATE Cur1 CURSOR FOR RESULT SET loc1;
    fetch from cur1 into error;
    close cur1;*/
    end


    on calling the above procedure with 0 as input (call tempdb.try1_sk(0,?,?)
    the below error is shown

    [IBM][CLI Driver][DB2/LINUXX8664] SQL0438N Application raised error or warning with diagnostic text: "ERRORQLSTATE-22012,-438 AT in procedure". SQLSTATE=22012

    Here as we can see the error message shown is that of calling procedure.
    Since our real code will be very huge it becomes difficult to debug when the line where error is encountered by inner proc(called proc) is not shown.

    Can anyone please suggest a way in which we can get error message of inner proc/called proc
    i.e on running call try1_sk(0,?,?);

    we get the below error also

    IBM][CLI Driver][DB2/LINUXX8664] SQL0438N Application raised error or warning with diagnostic text: "ERRORQLSTATE-22012,-801 AT divide by var1". SQLSTATE=22012

    This is achieved if error handling of calling procedure is removed but we want to keep error handling in both called and calling procedures hence this method cannot be used.

    Please not :- i tried using global temporary table and cursor but couldn't use it efficiently (have commented that part of code)

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    I have always been fond of error log tables. Then have your error handling put all pertinent information in there for review by the appropriate staff. Also, makes it easy to review your most common errors to see if there is something you can do to proactively prevent them from occurring.
    Dave

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •