Display line number where error occurred in DB2 stored procedure

Question

I have added an EXIT handler in my procedure that captures the SQLSTATE and SQLCODE, and even found a way to get the procedure name, but I also need to know where the error occurred. Suggestions greatly appreciated.

declare EXIT handler for SQLEXCEPTION
begin
    select sysibm.routine_specific_name, SQLSTATE, SQLCODE 
    into v_sp_name, v_sqlstate, v_sqlcode 
    from sysibm.sysdummy1;

    call dbms_output.put_line('Error in '||v_sp_name ' ['||v_sqlstate, v_sqlcode||']');
end;

What Db2 version? I presume it's Db2 for LUW. – mustaccio Jan 21 '21 at 15:14 — mustaccio, Jan 21 '21 at 15:14
Yes, DB2 LUW and version 11.1 – Jake v1 Jan 21 '21 at 16:16 — Jake v1, Jan 21 '21 at 16:16

score 5 · Accepted Answer · answered Jan 21 '21 at 15:22

5

If you are on a currently supported version of Db2 for LUW (11.1 or 11.5) you can use either DBMS_UTILITY.FORMAT_CALL_STACK() or DBMS_UTILITY.FORMAT_ERROR_BACKTRACE() in your signal handler to log the troubleshooting information.

answered Jan 21 '21 at 15:22

mustaccio

20,465
17
49
60

Thank you. This looks like it prints out a message that contains the line number where this command is executed. So, yes I can use it in the signal handler to get a general idea of where the error is, but I have not been able to get the line number of the actual error. – Jake v1 Jan 21 '21 at 16:16

score 0 · Answer 2 · edited Jan 21 '21 at 17:06

0

You might need to add debugging code into your procedures to indicate progress within the procedure.

Otherwise, you could explore the routine debugger in IBM Data Studio to help you find errors in SQL Stored Procedures

edited Jan 21 '21 at 17:06

mustaccio

20,465
17
49
60

answered Jan 21 '21 at 16:41

Paul Vernon

462
3
7

1

Thanks for the link, but I forgot to mention I wanted it for error logging. – Jake v1 Jan 21 '21 at 21:38

Display line number where error occurred in DB2 stored procedure

2 Answers2