SQLTRACE configuration parameter

Use the SQLTRACE parameter to control the startup environment of SQL tracing.

onconfig.std value
#SQLTRACE level=low,ntraces=1000,size=2,mode=global
On UNIX™: Not set. SQL tracing is not enabled.
On Windows™: #SQLTRACE level=low,ntraces=1000,size=2,mode=global
values
See the Usage section.
takes effect
After you edit your onconfig file and restart the database server.
After you run the SQL administration API task() or admin() function with the set sql tracing argument.

Usage

Remove the # symbol from the onconfig value to retain basic information, up to 2 KB in size, about the last 1000 SQL statements that were run by any user. You can customize the scope of the SQL tracing information by adjusting the field values of the SQLTRACE configuration parameter.

Figure 1: Syntax for the SQLTRACE configuration parameter

1  SQLTRACE?  level =
2.1! low
2.1 medium
2.1 high
2.1 off
1 ,?  ntraces =
2.1! 1000
2.1 number_traces
1 ,?  size =
2.1! 2
2.1 buffer_size
1 ,?  mode =
2.1! global
2.1 user
Table 1. Options for the SQLTRACE configuration parameter value
Field Values
level Amount of information traced:
  • Low = Default. Captures statement statistics, statement text, and statement iterators.
  • Medium = Captures all of the information included in low-level tracing, plus table names, the database name, and stored procedure stacks.
  • High = Captures all of the information included in medium-level tracing, plus host variables.
  • Off = Specifies no SQL tracing.
ntraces The number_traces value is the number of SQL statements to trace before reusing the resources. Default is 1000. The range is 500 - 2147483647.
size The buffer_size value is the maximum size of variable length data to be stored, in KB. Default is 2. The range is 1 -100. If this buffer size is exceeded, the database server discards saved data.
mode Scope of tracing performed:
  • Global = Default. All users.
  • User = Users who have tracing enabled by an SQL administration API task() or admin() function. Specify this mode if you want to get a sample of the SQL that a small set of users is running.

The onstat -g his command displays SQL trace information.