HiveBrain v1.2.0
Get Started
← Back to all entries
debugMinor

Display line number where error occurred in DB2 stored procedure

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
storederrornumberlinewheredb2procedureoccurreddisplay

Problem

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;

Solution

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.

Context

StackExchange Database Administrators Q#283731, answer score: 4

Revisions (0)

No revisions yet.