set sql tracing argument: Set global SQL tracing (SQL administration API)

Use the set sql tracing argument with the admin() or task() function to set global SQL tracing.

Syntax

Read syntax diagramSkip visual syntax diagram
>>-EXECUTE FUNCTION--+-admin-+---------------------------------->
                     '-task--'   

>--(--+-"set sql tracing info"-----------------------------------------------------------------------+-->
      +-"set sql tracing off"------------------------------------------------------------------------+   
      +-"set sql tracing on"--,--"number_traces"--+------------------------------------------------+-+   
      |                                           '-,--"trace_size"--+---------------------------+-' |   
      |                                                              '-,--"level"--+-----------+-'   |   
      |                                                                            '-,--"mode"-'     |   
      +-"set sql tracing resume"---------------------------------------------------------------------+   
      '-"set sql tracing suspend"--------------------------------------------------------------------'   

>--)--;--------------------------------------------------------><

Element Description Key Considerations
level The tracing level. The default is low. Possible values are:
  • low
  • med
  • high
mode Whether all or selected users are traced. Possible modes are:
  • global
  • user
number_traces The number of SQL statements to trace. The default value is 1000.  
trace_size The number of KB for the size of the trace buffer. If this buffer size is exceeded, the database server discards saved data. The default size is 2 KB.  

Usage

Use this function to reset the value of the SQLTRACE configuration parameter.

Use the set sql tracing info argument to display the state of global SQL tracing.

Use the set sql tracing off argument to turn off global SQL tracing.

Use the set sql tracing on argument to turn on global SQL tracing. Optionally specify the tracing level and mode or change the size of the trace buffer.

  • Use the low argument to capture statement statistics, statement text, and statement iterators.
  • Use the med argument to capture all of the information included in low-level tracing, plus table names, the database name, and stored procedure stacks.
  • Use the high argument to capture all of the information included in medium-level tracing, plus host variables.
  • Use the global argument to enable tracing for all users.
  • Use the user argument to enable tracing for those users who have tracing enabled by the set sql tracing user argument.

Use the set sql tracing resume argument to restart SQL tracing when it is suspended.

Use the set sql tracing suspend argument to pause SQL tracing without deallocating any resources.

Example

The following example starts a high level of global tracing for 1500 SQL statements into a 4 KB trace buffer:
EXECUTE FUNCTION task("set sql tracing on","1500","4","high","global");
The following example pauses SQL tracing:
EXECUTE FUNCTION task("set sql tracing suspend");

Copyright© 2019 HCL Technologies Limited