Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

    Unanswered: How to find which SQL from stored procedure has return error message?

    Hi,
    using DB2 v9.5 fixpack 2a on Linux I have several SQL statements in single stored procedure. But when I get an error I don't know which SQL in stored procedure returned error. Is there any way I can get info which SQL inside stored procedure returned error?

    Bellow simple sample:
    Code:
    CREATE TABLE ADMIN.TAB1 (A INT)@
    CREATE TABLE ADMIN.TAB2 (A INT NOT NULL)@
    
    CREATE PROCEDURE ADMIN.STORED_PROCEDURE
    LANGUAGE SQL
    BEGIN ATOMIC
        INSERT INTO ADMIN.TAB1 (A) VALUES (1);
        INSERT INTO ADMIN.TAB2 (A) SELECT A FROM ADMIN.TAB1;
    END@
    
    -- Stored procedure executes without error.
    CALL ADMIN.STORED_PROCEDURE@
    
    -- Now I will intentionally insert a null value and I know the
    -- error should be produce by second SQL, because definition
    -- of tab2 column A is defined as not null.
    INSERT INTO ADMIN.TAB1 VALUES (NULL)@
    
    -- Calling stored procedure
    CALL ADMIN.STORED_PROCEDURE@
    
    -- Above command produces error like expected:
    SQL0407N  Assignment of a NULL value to a NOT NULL column "TBSPACEID=2,
    TABLEID=1663, COLNO=0" is not allowed.  SQLSTATE=23502
    This is simple sample, I know from sample above that second SQL produced error, but is there any way I can get info: "SQL 2 produced error"?

    This is simple sample and is very easy to find out which SQL produces error, but if I have 20 or more SQLs in stored procedure it is time consuming to execute each SQL in stored procedure to find out which SQL produced error.

    How to find which SQL from stored procedure has return error message?
    Thanks

  2. #2
    Join Date
    Oct 2004
    Location
    DELHI INDIA
    Posts
    338
    You can go for exception handling, can also maintain error_log table after every critical SQL statement for diagnostics.

    E.g. you can declare the sqlcode in your output string during the create procedure statement,

    OUT OUT_SQLCODE INT,
    OUT OUT_SQLSTATE CHAR(5),
    OUT OUT_MESSAGE_TEXT VARCHAR(300)

    similarly, declaring them

    DECLARE SQLCODE INTEGER DEFAULT 0;
    DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
    DECLARE V_SQLSTATE CHAR(5);
    DECLARE V_SQLCODE INTEGER;

    ---EXCEPTION HANDLING -------------------(for a special situation))

    DECLARE C_DUPLICATE CONDITION FOR SQLSTATE '23505';
    DECLARE CONTINUE HANDLER FOR C_DUPLICATE
    VALUES (SQLSTATE,SQLCODE) INO V_SQLSTATE, V_SQLCODE;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    VALUES (SQLSTATE,SQLCODE) INTO V_SQLSTATE, V_SQLCODE;

    Jayanta Datta
    DB2 UDB DBA
    IBM India, Global Delivery
    New Delhi

  3. #3
    Join Date
    Jan 2003
    Posts
    1,605
    Quote Originally Posted by JAYANTA_DATTA View Post
    maintain error_log table after every critical SQL statement
    Thanks for help. The problem is I would like to have an info for EVERY SQL, not just critical ones. When error appears I would like to get sequence number of SQL inside stored procedure that has failed? Like "2nd SQL failed" or getting the whole SQL syntax that produced error like "INSERT INTO ADMIN.TAB2 (A) SELECT A FROM ADMIN.TAB1;" Is there some way to do that?
    Last edited by grofaty; 12-29-10 at 04:24.

  4. #4
    Join Date
    Apr 2007
    Location
    Chicago
    Posts
    68
    I set the SQLSTATE to a different value immediately prior to executing every SQL statement, and then execute a SIGNAL in an EXIT HANDLER. When there's a failure, the SQLSTATE returned in the error message points to the offending statement. DB2 only permits certain ranges of SQLSTATE values, so you can't pick any value at random.

    Code:
      declare VHOLD_SQL_STATE    char(5);
    
      declare exit handler for sqlexception
        begin
          set ERROR_MESSAGE = '<error text>';
              signal sqlstate VHOLD_SQL_STATE
              set MESSAGE_TEXT = ERROR_MESSAGE;
          end;
    
      set VHOLD_SQL_STATE = '75000';
      select into...;
    
      set VHOLD_SQL_STATE = '75001';
      open CSR1;
      set VHOLD_SQL_STATE = '75002';
      fetch CSR1 into ...;

  5. #5
    Join Date
    Oct 2004
    Location
    DELHI INDIA
    Posts
    338
    a simple way to achieve this is to introduce an error_activity_log table in the design with fields like ( SR_SQL_Nbr SMALLINT, SQL_STMT_DETAILS Varchar (300) , Error_Timestamp Timestamp, SQL_Excp_details (optional field)).

    1> After declaring the stored procedure, DELETE the error_activity_log table if you don't want to maintain history of errors of the previous runs.

    2> Inside the SP, just before every SQL statement, ensure to insert a record into the error_activity_log table with details about the serial_number_SQL_statement, The entire sql statement in a string, Timestamp (with current timestamp).

    Ensure to COMMIT each of these INSERT INTO ERROR_ACTIVITY_LOG table statement.

    3> Repeat Step(2) above for all the subsequent SQL statement you are having inside the procedure.

    4> You can use the GET DIAGNOSTICS for EXCEPTION ( get diagnostics exception 1 v_msg = MESSAGE_TEXT) for tracking exception of individual SQL statement too and assign the output to some variable. This one is required for further diagnostics if needed.

    5> Now whenever you encounter any error during the execution of the stored procedure, you can query the error_activity_log to see the statement where the error was encountered. ( select * from error_activity_log order by error_timestamp desc with ur).

    This is easier to implement with minimal impact in your existing code/application.

    Jayanta Datta
    DB2 UDB DBA
    IBM India, Global Delivery
    New Delhi

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I guess the short answer is: there is nothing built-in into DB2 to give you those details automatically. You'll have to implement your own error handling mechanism.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Jan 2003
    Posts
    1,605
    As I see there is no build-in feature in DB2 to get this info and some kind of my own error code handling has to be implemented.

    So I must change the code from:
    Code:
    BEGIN ATOMIC
     statement1;
     statement2;
    END
    to:
    Code:
    BEGIN NOT ATOMIC
     statement1;
     insert 'label_SQL1' into log_table;
     commit;
    
     statement2;
     insert 'label_SQL2' into log_table;
     commit;
    END
    If statement2 fails then only "label SQL1" will be stored in log_table.

    Thank you all for you advices.
    Regards
    Last edited by grofaty; 12-30-10 at 05:08.

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    The COMMIT is a very bad idea because you actually commit the transaction that called your stored procedure. If that transaction did more than just the CALL, you may cause a lot of problems. I suggest that you have a look at exception handlers. Those can do the logging and also terminate the procedure if necessary.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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