debugMinor
Display line number where error occurred in DB2 stored procedure
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.