The DEBUG ROUTINE pragma provides additional information on an execution of a routine which is possibly errored. This information can be read using the SYS_ROUTINE_ERRORS view of the SYS_INFO_SCHEMA, see "SYS_ROUTINE_ERRORS".
The DEBUG ROUTINE pragma is effective only outside routines. It is only effective ahead of the SQL statement CALL and ahead of the DML statements DECLARE CURSOR, DELETE, INSERT, MERGE, SELECT, and UPDATE. When specified ahead of DML statements, the pragma has an effect on all User Defined Functions (UDFs) and the routines of the DML statement these contain.
DEBUG ROUTINE [ALL | USER] [LEVEL unsigned_integer ]
unsigned_integer
When unsigned_integer > 0, additional information is collected for the executed SQL statements of the current routine.
unsigned_integer = 1 is the default value when the LEVEL clause is not specified.
When unsigned_integer = 0, the pragma is ignored.
The following approach makes sense:
The pragma is initially active in an application with a value > 0 in, and then later (without changing the text length) disabled by the value 0.
USER
Depending on the LEVEL set, information is collected for the SQL statements which are prefixed by the DEBUG VALUE pragma (see "DEBUG VALUE pragma").
ALL
In addition to the DEBUG information mentioned under USER, general DEBUG information is also created (irrespective of the LEVEL set).
For example, every SQLSTATE or SQLrowcount reported by an errored SQL statement is recorded. Internal calls of routines are also recorded. The position of an SQL statement within the text of a routine is normally also recorded.