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

grofaty · #1 (**permalink**) 12-28-10, 08:57

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

JAYANTA_DATTA · #2 (**permalink**) 12-28-10, 14:44

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;

grofaty · #3 (**permalink**) 12-29-10, 02:09

Quote:

Originally Posted by JAYANTA_DATTA

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?

jsharon1248 · #4 (**permalink**) 12-29-10, 08:35

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 ...;

JAYANTA_DATTA · #5 (**permalink**) 12-29-10, 12:29

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.

stolze · #6 (**permalink**) 12-29-10, 12:56

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.

grofaty · #7 (**permalink**) 12-30-10, 03:45

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

stolze · #8 (**permalink**) 01-03-11, 11:35

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech