SERVER_TRACE
SET SERVER_TRACE ON|OFF [with-clause]
SET SERVER_TRACE enables or disables query tracing for all sessions on the local server.
SERVER_TRACE overrides all SESSION_TRACE options in effect.
Requires trace point privilege or equivalent.
Valid options are:
ON | OFF
Turns tracing on or off.
with-clause
Consists of the keyword WITH followed by one or more of the following options separated by a comma.
Default: NODBCHECK, NOUSERCHECK, NOERROR, BLOBSEGS=16, NOQEP, NODETAIL, X100ALGEBRA, NOX100PROFILE, MINTIME=0
DIRECTORY='path'
Sets the output directory to the specified path. A complete (absolute) path is recommended, although not required. This directory must be writable by the installation owner. The trace files created in this directory have names of the form qtrace_dbname_ppp_nnn.log where dbname is the database name, ppp is the process ID of the DBMS server, and nnn is a hex number describing the session. If a trace file does not already exist, it is created, owned by the installation owner. If a trace file already exists for some session, it is appended to. Trace files cannot be symbolic links.
Required for SESSION_TRACE ON.
Optional for SERVER_TRACE if there is a "SET TRACE RECORD 'path'" in effect.
Example: SET SERVER_TRACE ON WITH DIRECTORY='/temp/servertrace/'
DBONLY=(dbname,dbname,...)
Traces only sessions that are accessing the specified databases. Limit 20 databases.
Invalid for SESSION_TRACE.
DBEXCEPT=(dblist)
Traces only sessions that are not accessing the specified databases. Limit 20 databases.
Invalid for SESSION_TRACE.
NODBCHECK
Turns off database checks.
USERONLY=(user,user,...)
Traces only sessions that are accessed by the specified users (real user, not effective user). Limit 20 users.
Invalid for SESSION_TRACE.
USEREXCEPT=(userlist)
Traces only sessions that are not accessed by the specified users (real user, not effective user). Limit 20 users.
Invalid for SESSION_TRACE.
NOUSERCHECK
Turns off user checks.
ERROR=('ffxxxx',...)
Traces only the queries that result in the listed errors. For example, 'US0083', 'E_US0083'.
ERROR (no parameters) traces any query that results in any error or warning.
NOERROR
Excludes error messages.
BLOBSEGS=n
Sets the number of segments of blob parameter values that are printed. Each segment is up to 2KB of blob text. Valid values: non-negative integers; 0 means none; large integer value means all.
QEP[=(list)] | NOQEP
Provides or does not provide QEPs for each query. List items affect the QEP style or display. Valid list items are:
empty list - Normal style
SEGMENTED - Segmented style
CONCISE - Concise style (all on one line)
NOCOST - Suppresses disk/CPU cost display in the QEP
FLOAT - Displays all disk/CPU costs as floating point in the QEP
Default is NOQEP.
[NO]DETAIL
Exclude or include certain information pertaining to recording and playback, such as TDESCR, COL, all but first FETCH of cursor.
MINTIME=n, MAXTIME=n
Traces only queries that meet the specified timing filters, plus session begin and end time.
Valid values: a non-negative integer, 0 for NO time filtering.
MAXTIME is invalid without a MINTIME.
Timing starts when query enters PSF and stops at the EQY record, that is, all results have been generated and sent. It includes parse, optimize, execution, query result send, lock-wait time, blocks, rule/DBP time, and so on.
WITH options with ONLY, EXCEPT, NO are mutually exclusive.
If tracing is ON, and another statement is issued at the same (server or session) level to turn on tracing, the statement operates as an "alter trace" whose WITH-options start with the existing settings. For example:
SET SESSION_TRACE ON WITH QEP, DETAIL;
SET SESSION_TRACE ON WITH NODETAIL;
leaves QEP on and turns off DETAIL.
If the trace is altered, the trace is, in effect, turned off and back on. So, for example, you can alter the directory path while tracing is on.
If duplicate options are entered, the last one entered is used. For example, if DETAIL, NODETAIL is entered, NODETAIL is used.