Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2012
    Posts
    15

    Unanswered: Vew queries run from inside stored procedure

    Hi

    I have built a stored procedure on DB2 9.7 and when i call it, somewhere it gets stuck and throws a deadlock error. I do not have any tool and building it straight from command line.

    Is there any means to step by step execution when a stored procedure is called to identify which SQL is throwing the error. I have tried calling as below

    db2 -v "CALL PS_DMF_JY(?)"

    but it shows up only CALL PS_DMF_JY(?) and not what is running in the background.

    Please help

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    you could use datastudio and run the sp with debug..
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Nov 2010
    Posts
    99
    Usually when I run into this situation i try to catch the error using a continuation handler.

    ***Note the sproc will continue on an -803 so you need to force the code to exit.

    Try adding this to your code

    1) add to the declarations:
    -- IGNORE THE DUPLICATE ERRORS
    DECLARE WHEREAMI_803 INT DEFAULT NULL;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '23505'
    IF SQLSTATE = '23505' THEN
    SET WHEREAMI_803 = 1;
    END IF;
    2) add after each insert statement
    INSERT INTO XYZ;
    if whereami_803 = 1 then
    SET NOTES = 'got 803 trying to insert into xyz';
    exit loop;
    end if;

    3) add msg to your arguments: MYPROC(out NOTES CHAR(50)...) to print out message.

    This will catch the 803 error and print out where you got it

  4. #4
    Join Date
    Nov 2010
    Posts
    99
    Of course you need to change the continuation handler to catch the -911

Posting Permissions

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