DB2 LUW - Get Error Line in Stored Procedure

Question

I'm trying to determine the line in a stored procedure or the last SQL-statement which is causing an error. As a workaround I'm using temporary variables which I manually set to determine in which part of my stored procedure an error occurs.

See the following:

-- Create an ErrorLog table
Create Table SCHEMA.ErrorLog_lrc_test                                                        
  (                                 
   ErrSQLCODE     Integer ,                                   
   Codepart    Char(1),                             
   Type           Char(1) ,                                  
   MsgText        VarChar(1024));

CREATE OR REPLACE PROCEDURE SCHEMA.test_error(IN divisor INT)
LANGUAGE SQL
BEGIN
-- Define variables
DECLARE codepart_var Char(1);

DECLARE test_INT INT;

-- Define sqlcode
DECLARE SQLCODE INTEGER;

--Define Error-Handler
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN 
    INSERT INTO SCHEMA.ErrorLog_lrc_test(ErrSQLCODE, Codepart, TYPE, MsgText) 
        VALUES(SQLCODE, codepart_var, 'E', SYSPROC.SQLERRM(SQLCODE));
    END;
   
--Define Warning-Handler
DECLARE CONTINUE HANDLER FOR SQLWARNING, NOT FOUND 
    BEGIN 
    INSERT INTO SCHEMA.ErrorLog_lrc_test(ErrSQLCODE, Codepart, TYPE, MsgText) 
        VALUES(SQLCODE, codepart_var, 'W', SYSPROC.SQLERRM(SQLCODE));
    END;
 
-- Set temporary variable to 'a' to get part of code where error occured
SET codepart_var = 'a';
   
-- Create Error
sELECT  1/divisor into test_INT
FROM SYSIBM.SYSDUMMY1;

SET codepart_var = 'b';

-- Create Error
sELECT 1/divisor into test_INT
FROM SYSIBM.SYSDUMMY1;

SET codepart_var = 'c';

-- Create Not Found (Sqlcode 100)
INSERT INTO SCHEMA.ErrorLog_lrc_test
SELECT NULL, NULL, NULL, NULL FROM "SYSIBM".SYSDUMMY1
WHERE 1 = 0 ;

END 

call SCHEMA.test_error(0);

SELECT *
FROM SCHEMA.ErrorLog_lrc_test;

I get the following:

ERRSQLCODE	CODEPART	TYPE	MSGTEXT
-801	a	E	SQL0801N Division by zero was attempted.
-801	b	E	SQL0801N Division by zero was attempted.
100	c	W	SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table.

So I am able to get the part of the code where an error or warning occurs, but it would be better to get the line or the SQL statement as I don't want to specify every part of the code with a temporary variable.

I already found this SQLCA --> sqlerrd(3): "...If an error is encountered during the compilation of an SQL routine, trigger, or dynamic compound SQL (inlined or compiled) statement, sqlerrd(3) contains the line number where the error was encountered". For now I didn't manage to make use of SQLCA variables. I don't know how to implement them in DB2 LUW in a stored procedure.

Is there another/better way to log the specific line or SQL-statement in a stored procedure which is causing an error?

My DB2 version is 10.5.0.

Thank you!

Answer 1

If your Db2-server platform is Linux/Unix/Windows, and you are using a recent version, then consider using DBMS_UTILITY.FORMAT_ERROR_BACKTRACE which may help you.

Documentation here. The documentation includes a worked example.

When using this for stored procedures or routines, it is wise to always create those routines with a meaningful specific name with the SPECIFIC clause on the create or replace statement. Otherwise the routine will have a system generated name which will not be meaningful to users when it appears in the output of DBMS_UTILITY.FORMAT_ERROR_BACKTRACE. There are other reasons you should always use a specific name for your routines.

The SQLCA is for calling programs (i.e. the program that calls the stored procedure).