Report Command Reference - SQLACTIVITY command with TRACE subcommand

SQLACTIVITY command with TRACE subcommand

This section describes the SQLACTIVITY command with the TRACE subcommand.

Usage

Use the TRACE subcommand to produce traces with an entry for every DB2® SQL event.

Usage notes

Up to five traces can be requested in a job step.
For more information about including or excluding IFCIDs, see the Reporting User's Guide.

Syntax of the TRACE subcommand

>>-TRACE--+-------------------------------------------+--------><
          '-+-| FROM/TO block |---------------------+-'
            |                 .-,--------------.    |
            |                 V .-OCCURRENCE-. |    |
            +-SUMMARIZEBY--(----+-ALL--------+-+--)-+
            |                   +-CURSOR-----+      |
            |                   +-PROGRAM----+      |
            |                   +-STMTNO-----+      |
            |                   '-STMTTYPE---'      |
            |            .-DEFAULT-----.            |
            +-SORTBY--(--+-ELAPSEDTIME-+--)---------+
            |            +-EXITS-------+            |
            |            +-EXITTIME----+            |
            |            +-IOREQS------+            |
            |            +-IOTIME------+            |
            |            +-PAGESCAN----+            |
            |            +-ROWSPROC----+            |
            |            +-SCANS-------+            |
            |            +-SORTRECS----+            |
            |            +-SORTWORK----+            |
            |            +-SUSP--------+            |
            |            +-SUSPTIME----+            |
            |            '-TCBTIME-----'            |
            |              .-,----------.           |
            |              V .-NONE---. |           |
            +-WORKLOAD--(----+-ACCT---+-+--)--------+
            |                +-ALL----+             |
            |                +-DCAP---+             |
            |                +-EXIT---+             |
            |                +-HILITE-+             |
            |                +-IO-----+             |
            |                +-LOCK---+             |
            |                +-SCAN---+             |
            |                +-SORT---+             |
            |                '-VARS---'             |
            |           .-10------.                 |
            +-LIMIT--(--+-integer-+--)--------------+
            |            .-SQTRCDD*-.               |
            +-DDNAME--(--+-ddname---+--)------------+
            '-| INCLUDE/EXCLUDE block |-------------'

Subcommand options

The syntax diagram shows the options that are available with this subcommand. See OMEGAMON XE for DB2 PE subcommand options for comprehensive descriptions of these options. The following list gives additional or specific descriptions of selected options, where appropriate.

FROM/TO

Limits the range of records included in the trace by date and time.

For details, see FROM/TO subcommand options.

SUMMARIZEBY

Selects the SQL events to be summarized. You can specify one entry of SUMMARIZEBY for each TRACE subcommand. The following events can be specified:

OCCURRENCE: SQL statement occurrence. This is the default.
PROGRAM
CURSOR
STMTNO: Statement number
STMTTYPE: Statement type
ALL: All of the above

For more information about summarization, see the Reporting User's Guide.

SORTBY

Sorts the SQL events within each summary level within each thread. You can specify one entry of SORTBY for each TRACE subcommand. One of the following events can be specified:

DEFAULT: The default sort sequence depends on the summary level specified.
ELAPSEDTIME: Average elapsed time
EXITS: Number of exits
EXITTIME: Elapsed time for each exit
IOREQS: I/O requests
IOTIME: Elapsed time for each I/O request
PAGESCAN: Pages scanned
ROWSPROC: Rows processed
SCANS: Number of scans
SORTRECS: Records sorted
SORTWORK: Workfiles sorted
SUSP: Lock suspensions
SUSPTIME: Elapsed time for each lock suspension
TCBTIME: Average TCB time

For more information about sorting and default, see the Report Reference.

WORKLOAD

Displays the workload detail for each event. The following detail can be reported:

ACCT

Accounting

ALL

All workload activity

Including MINIBIND if IFCID 22 is included in the input.

Including UDF activity if IFCID 324 is included in the input.

Note that the more workload detail you request, the more time OMEGAMON® XE for DB2 PE requires for processing your request. It is recommended that you do not specify WORKLOAD(ALL) with a large amount of input data unless absolutely necessary.

DCAP

Data capture activity

EXIT

Exit activity

HILITE

Workload highlights

IO

I/O activity

LOCK

Lock suspension and page and row locking activity

NONE

No workload activity. This is the default.

SCAN

Scan activity, RID list activity, and query parallelism activity.

SORT

Sort activity

VARS

Show host variables data, if host variables are used by the SQL statements.

If option WORKLOAD(VARS) is specified, option SUMMARIZEDBY(OCCURRENCE) is required (the default).

Note:

When IFCID 3 is included in the input, Accounting Trace activity is automatically included as part of the workload detail.

For more information about workload detail, see the Reporting User's Guide and the Report Reference.

DDNAME

Specifies the data set where the trace is written.

LIMIT

Sets the maximum number of threads processed by TRACE. The range is 1 to 99 999. If, for example, 3 is specified for LIMIT, no more than three threads are reported. A different limit can be set for each of the five possible traces. The default is 10.

INCLUDE/EXCLUDE

Includes or excludes data associated with specific OMEGAMON XE for DB2 PE identifiers.

For details, see INCLUDE and EXCLUDE subcommand options, which lists other identifiers allowed with this command and subcommand combination, and OMEGAMON XE for DB2 PE identifiers.

In addition to the common identifiers used with these options, the SQLACTIVITY TRACE command also has an SQLCODE identifier. This identifier can be used to include statements that completed, for example, with a specific error. The following are usage examples of the SQLCODE identifier.

SQLACTIVITY TRACE EXCLUDE(SQLCODE(0)): This excludes any SQL statement that completed without warnings or errors.
SQLACTIVITY TRACE INCLUDE(SQLCODE(R(-251,-203))): This includes any SQL statements that ended with SQL error codes in the range -251 through -203.
SQLACTIVITY TRACE INCLUDE(SQLCODE(-805,-204,495)): This includes only SQL statements that ended with the specific error codes -805, -204, and 495.
SQLACTIVITY TRACE INCLUDE(SQLCODE(LT(0))): This includes only SQL statements that ended with an error (-n).
SQLACTIVITY TRACE INCLUDE(SQLCODE(GT(0))): This includes only SQL statements that ended with a warning.

Example using TRACE with SUMMARIZEBY, SORTBY, and LIMIT options


·
·
·

TRACE
   FROM        (,08:00:00.00)
   TO          (,08:10:00.00)
   SUMMARIZEBY (STMTNO)
   SORTBY      (ELAPSEDTIME)
   LIMIT       (20)
   INCLUDE     (PRIMAUTH(UID0001 UID0003 UID0005)
                PLANNAME(PLIT2A01,PLIT2A02))

·
·
·

It is summarized by statement number and sorted by elapsed time.

It reports the ten-minute period from 8:00 a.m. to 8:10 a.m. and includes only data that contains any of the following primary authorization IDs:

UID0001
UID0003
UID0005

Using any of the following plan names:

PLIT2A01
PLIT2A02

LIMIT has set the maximum number of threads processed to 20.

The trace is written to the data set defined by the default ddname SQTRCDD1.