About SQL PL and PL/SQL error stack logging
Starting in Db2® Version 10.5 Fix Pack 7, you can now determine the origin of run-time errors which occur within multiple nested layers of SQL routine invocations. There are two distinct features to help determine the source of an error in a list of active routines, including SQL and external routines, that were running at the time an SQL error took place.
The first error stack trace facility is intended to facilitate
problem determination in production environments where the cause of
a run-time error is unknown. It is activated at the database level.
- pl_stack_trace
- This database configuration parameter logs SQL errors in procedural code to the db2diag.log file. Each entry contains a formatted representation of the complete call stack, showing the routine name and source line number for all active SQL and external routines on the stack. This allows easy identification of the code path at the time the error was detected. This facility does not require modification of the user application.
The second feature is a pair of utility functions. These functions
can be used in application code, as part of an error reporting strategy,
or for problem determination in a development environment. The functions
are describes as follows:
- DBMS_UTILITY.FORMAT_CALL_STACK
- This function returns a formatted representation of the call stack, as it exists at the time the function is invoked. Each line of output describes one SQL or external routine. The lines are ordered from latest to oldest function calls.
- DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
- This function returns produces output similar to that returned by FORMAT_CALL_STACK() function, but for the call stack as it existed at the time of the last error in a compiled SQL routine.