Procedure execution trace

SQL Guide : Diagnostics and troubleshooting for SQL : Tracing facilities for stored procedures and triggers : Procedure execution trace

If you must trace EVERY statement in your stored procedure or trigger, then you do not want to spend time to write a WRITETRACE statement for every SQL statement. Instead, you can simply turn on “PROCTRACE”, which traces every statement inside the specified stored procedure or trigger. As with USERTRACE, you can turn proctrace on for a specified procedure, a specified trigger, or for all triggers associated with a particular table. The syntax is:

ADMIN COMMAND 'proctrace { on | off }
user username { procedure | trigger | table } entity_name'

The entity_name is the name of the procedure, trigger, or table for which you want to turn tracing on or off.

Trace is activated only when the specified user calls the procedure / trigger. This is useful, for example, when tracing propagated procedure calls in a advanced replication master.

Turning on tracing turns it on in all procedure/trigger calls by this user, not just calls from the connection that switched the trace on. If you have multiple connections that use the same username, then all of the calls in all of those connections will be traced. Furthermore, the tracing will be done on calls propagated to (executed on) the master, as well as the calls executed on the replica.

If the keyword “table” is specified, then all triggers on that table are traced.

Example:

"create procedure trace_sample(i integer)
returns(j integer)
begin
j := 2*i;
return row; end";
commit work;

admin command 'proctrace on user DBA procedure TRACE_SAMPLE'; call trace_sample(2);

OUTPUT FROM EXAMPLE:

23.01 17:25:17 ---- PROCEDURE 'DBA.DBA.TRACE_SAMPLE' TRACE BEGIN 0001:CREATE PROCEDURE TRACE_SAMPLE(I INTEGER)
0002:RETURNS(J INTEGER)
0003:BEGIN
      --> I:=2
      --> J:=NULL
      --> SQLSUCCESS:=1
      --> SQLERRNUM:=NULL
      --> SQLERRSTR:=NULL
      --> SQLROWCOUNT:=NULL
0004: J := 2*I;
      --> J:=4
0005: RETURN ROW;
0006:END
23.01 17:25:17 --- PROCEDURE 'DBA.DBA.TRACE_SAMPLE' TRACE END ---