3

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;
Improve this question
2
  • What Db2 version? I presume it's Db2 for LUW. – mustaccio Jan 21 at 15:14
  • Yes, DB2 LUW and version 11.1 – Jake v1 Jan 21 at 16:16
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.

Improve this answer
1
  • 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 at 16:16
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

Improve this answer
1
  • 1
    Thanks for the link, but I forgot to mention I wanted it for error logging. – Jake v1 Jan 21 at 21:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.