Create a UDR to turn tracing on

The registration of a sample user-defined procedure to perform such a task follows:

CREATE PROCEDURE trace_on(LVARCHAR, LVARCHAR)
EXTERNAL NAME '/usr/lib/udrs/my_tools.so(trace_on)'
LANGUAGE C;

The code for this user-defined procedure can be something like the following example:

void trace_on(trace_path, trace_level)
   mi_lvarchar *trace_path;
   mi_lvarchar *trace_level;
{
   mi_tracefile_set(mi_lvarchar_to_string(trace_path));
   mi_tracelevel_set(mi_lvarchar_to_string(trace_level));
};

After you register the trace-on procedure, you can turn on tracing for an SQL session with the following SQL statement:

EXECUTE PROCEDURE trace_on('trace_log_path', 
   'my_trace_class 20');

Alternatively, you can create a user-defined procedure to turn on a particular trace class. The following CREATE PROCEDURE statement registers a user-defined procedure to turn on the MyBlade trace class:

CREATE PROCEDURE traceset_myblade(LVARCHAR, INTEGER)
EXTERNAL NAME '/usr/lib/udrs/myblade.bld(db_trace_on)'
LANGUAGE C;

The following code implements such a user-defined procedure:

void db_trace_on(trace_path, trace_level)
   mi_lvarchar *trace_path;
   mi_integer trace_level;
{
   char[16] trace_cmd;

   mi_tracefile_set(mi_lvarchar_to_string(trace_path));
   sprintf(trace_cmd, "%s %d", "MyBlade", trace_level);
   mi_tracelevel_set(trace_cmd);
}

Now the following SQL statement turns on tracing for trace class MyBlade with a trace level of 20 whose tracing output goes in the UNIX file /u/dexter/udrs/myblade.trc:

EXECUTE PROCEDURE trace_on('/u/dexter/udrs/myblade.trc', 20);