-START TRACE command (Db2)

Environment

This command can be issued from a z/OS® console, a DSN session, a DB2I panel (DB2 COMMANDS), an IMS or CICS® terminal, or a program using the instrumentation facility interface (IFI).

Authorization

Db2 commands that are issued from a logged-on z/OS console or TSO SDSF can be checked by Db2 authorization using primary and secondary authorization IDs. A logged-on z/OS user ID must be defined in RACF or a similar security server.

Syntax

constraint block

Notes:

• ¹ Do not specify CLASS or IFCID with AUDTPLCY. AUDTPLCY applies to trace type AUDIT.
• ² Specifying multiple AUDTPLCY traces with different filtering criteria causes the filtering to become a UNION among all AUDTPLCY traces.

filtering block

Option descriptions

You must specify a trace type.

The constraint and filtering blocks

The constraint and filtering blocks place optional limits on the kinds of data that are collected by the trace. The filtering options are exclusionary equivalents to the corresponding constraint options.

A START TRACE command for a statistics trace allows only the CLASS constraint option. A START TRACE command for any other trace type allows any constraint option or filter option. However, Db2 does not use any filters or constraints that you specify when you start a trace for any of the following IFCIDs:

A START TRACE command can contain multiple constraint options, multiple filtering options, or a combination of both. A constraint or a filtering option can contain multiple values. However, the command must not contain multiple constraint options that each contain multiple values. A single constraint option that has multiple values can be specified with:

• Multiple other constraint options, each of which has a single value
• Multiple filtering options, each of which has a single value or multiples values

An error message is issued if the START TRACE command contains two or more constraint options that each have multiple options.

The meaning of each option is as follows. Filtering options are described with their corresponding constraint options.

PLAN( plan-name , …) or XPLAN( plan-name , …)

Introduces a list of specific plans for which trace information is gathered. Use PLAN to constrain the trace to the specified plans or XPLAN to exclude the specified plans. You cannot use this option for a STAT trace.

The default is PLAN( * ).

( * )

Starts a trace for all plans.

plan-name

Is the name of an application plan. You can use up to eight names; a separate trace is started for each name. If you use more than one name, you can use only one value for AUTHID and LOCATION.

PKGLOC or XPKGLOC

Specifies the location name where the package is bound. Use PKGLOC to constrain the trace to the specified locations or XPKGLOC to exclude the specified locations.

PKGCOL or XPKGCOL

Specifies the package collection name. Use PKGCOL to constrain the trace to the specified collections or XPKGCOL to exclude the specified collections.

PKGPROG or XPKGPROG

Specifies the DBRM or program name. Use PKGPROG to constrain the trace to the specified programs or XPKGPROG to exclude the specified programs.

AUTHID( authorization-id , …) or XAUTHID( authorization-id , …)

Introduces a list of specific authorization IDs for which trace information is gathered. Use AUTHID to constrain the trace to the specified authorization IDs or XAUTHID to exclude the specified authorization IDs. The authorization IDs specified must be the primary authorization IDs. You cannot use this option for a STAT trace.

The default is AUTHID( * ).

( * )

Starts a trace for all authorization IDs.

authorization-id

Specifies an authorization ID. You can use up to eight identifiers; a separate trace is started for each identifier. If you use more than one identifier, you can use only one value for PLAN and LOCATION.

LOCATION( location-name , …) or XLOC( location-name , …)

Specifies a list of location names for which trace information is gathered. Use LOCATION to constrain the trace to the specified locations or XLOC to exclude the specified locations. The use of the LOCATION or XLOC option precludes tracing threads that have no distributed data relationship. LOCATION or XLOC cannot be specified when you want to start a statistics trace.

location-name

Identifies the Db2 subsystems whose distributed threads you want to trace. Activates the Db2 trace for the remote TCP/IP or SNA location that you specify by location-name.

You can specify up to eight locations; a separate trace is started for each one. You can specify only one location if you use more than one plan name or authorization ID.

<luname>

Activates the Db2 trace for the remote clients that are connected to DDF through the remote SNA LU name that you specified in luname.

ipaddr

Activates the Db2 trace for the remote clients that are connected to DDF through the remote TCP/IP host. ipaddr is the IP address.

The format of the IP address depends on whether the TCP/IP stack has been configured as an IPv4-only stack or a dual-mode stack. An IPv4 stack supports only the IPv4 protocol. A dual-mode stack supports IPv4 and IPv6 protocols.

• For an IPv4-only stack, you enter the IP address in IPv4 format:

  x.x.x.x

• For a dual-mode stack:
  • You enter an IPv6 address in IPv6 format:

    y:y:y:y:y:y

  • You enter an IPv4 address in dual IPv6 and IPv4 format, with ::FFFF: as the IPv6 portion:

    ::FFFF:x.x.x.x

(*)

Indicates that you want to start trace events that occur under distributed threads regardless of which location they are connected to. Specifying the local location name is equivalent to specifying LOCATION(*).

Clients other than Db2 for z/OS: Db2 for z/OS does not receive a location name from clients that are not Db2 for z/OS subsystems. To start a trace for a client that is not a Db2 for z/OS subsystem, enter its LUNAME or IP address. Enclose the LUNAME by the less-than (<) and greater-than (>) symbols. Enter the IP address in the form nnn.nnn.nnn.nnn. For example, to start a trace for a client with the LUNAME of LULA, enter the following command:

-START TRACE (PERFM) CLASS (*) LOCATION (<LULA>)

To start a trace for a client with the IP address of 123.34.101.98, enter the following command:

-START TRACE (PERFM) CLASS (*) LOCATION (::FFFF:123.34.101.98)

USERID or XUSERID

Specifies the user ID. Use USERID to constrain the trace to the specified user IDs or XUSERID to exclude the specified user IDs. You can specify multiple values and wildcard values as described in Usage notes.

USERID and XUSERID can be up to 16 characters.

APPNAME or XAPPNAME

Specifies the application name. Use APPNAME to constrain the trace to the specified applications or XAPPNAME to exclude the specified applications. You can specify multiple values and wildcard values as described in Usage notes.

APPNAME and XAPPNAME can be up to 32 characters.

WRKSTN or XWRKSTN

Specifies the workstation name. Use WRKSTN to constrain the trace to the specified workstations or XWRKSTN to exclude the specified workstations. You can specify multiple values and wildcard values as described in Usage notes.

WRKSTN and XWRKSTN can be up to 18 characters.

CONNID or XCONNID

Specifies the connection ID. Use CONNID to constrain the trace to the specified connections or XCONNID to exclude the specified connections.

The CONNID or XCONNID value is one of the following values:

CONNID value	Type of connection to Db2
BATCH	Batch
TSO	TSO
DB2CALL	QMF
UTILITY	Db2 utility
subsystem-id	Db2 internal
cics-connection-name	CICS
ims-connection-name	IMS
RRSAF	RRSAF
connection-name	A connection from a remote Db2 for z/OS requester. This value is the connection name of the thread at the requesting location.
SERVER	A connection from a remote requester that is not Db2 for z/OS.

CORRID or XCORRID

Specifies the correlation ID. Use CORRID to constrain the trace to the specified correlation IDs or XCORRID to exclude the specified correlation IDs.

ROLE or XROLE

Specifies the connection roles. Use ROLE to constrain the trace to the specified roles or XROLE to exclude the specified roles.

CLASS( integer , …)

Introduces a list of classes of data gathered. What classes are allowable, and their meaning, depends on the type of trace started.

Abbreviation: C

When the CLASS option is omitted, the default classes within the trace type are activated. The default classes are identified in the Description column of the following tables.

( * )

Starts a trace for all classes of the trace type.

integer

Any number in the following table. You can use any number of classes that are allowed for the type of trace started.

Tip: IFCID numbers are often presented in Db2 and its documentation with three or four digits and leading zeros. For example, you might find: "IFCID 1," "IFCID 001," or "IFCID 0001." However, you can assume that these references each have the same meaning.

Accounting trace (ACCTG)

Table 2. Classes for Db2 accounting trace

Class	Description of class	Activated IFCIDs
1	Standard accounting data. Class 1 is also activated when you omit the CLASS keyword from the START TRACE command when you start the accounting trace.	3, 106, 200, 239
2	Entry or exit from Db2 event signaling.	232
3	Elapsed wait time in Db2.	6 7, 8, 9, 32, 33, 44, 45, 117, 118, 127, 128, 170, 171, 174, 0175, 213, 214, 215, 216, 226, 227, 242, 243, 321, 322, 329, 378, 379, 382, 383, 413, 414
4	Installation-defined accounting record.	151
5	Time spent processing IFI requests.	187
6	Reserved.
7	Package-level accounting in-Db2 time.	200, 232, 239, 240
8	Package-level accounting wait time in Db2.	6, 7, 8, 9, 32, 33, 44, 45, 117, 118, 127, 128, 170, 171, 174, 175, 213, 214, 215, 216, 226, 227, 239, 241, 242, 243, 321, 322, 378, 379, 382, 383, 413, 414
10	Package detail. One of the following traces must also be activated before the IFCID 239 records are written: Accounting class 7 Accounting class 8 Monitor class 7 Monitor class 8	239
11	Plan-level accounting information. Class 11 is activated when you omit the CLASS keyword from the START TRACE command when you start the accounting trace. Start a trace for accounting class 11 instead of accounting class 1 if you do not want IFCID 239 records returned.	3, 200
12-29	Reserved.
30–32	Available for local use.

Audit trace (AUDIT)

Table 3. Classes for Db2 audit trace

Class	Description of class	Activated IFCIDs
1	Access attempts denied due to inadequate authorization. Class 1 is also activated when you omit the CLASS keyword from the START TRACE command when you start the audit trace.	140
2	Explicit GRANT and REVOKE.	141
3	CREATE, ALTER, and DROP operations against audited tables.	142
4	First change of audited object.	143
5	First read of audited object.	144
6	Bind time information about SQL statements that involve audited objects.	145
7	Assignment or change of authorization ID.	55, 83, 87, 169, 319
8	Utilities.	23, 24, 25, 219, 220
9	Installation-defined audit record.	146, 392
10	Trusted context information.	269, 270
11	Audits of successful access.	3611
12–29	Reserved.
30–32	Available for local use.
Notes: If IFCID 361 is started through START TRACE, all successful access is traced. If IFCID 361 is started because audit policy category SYSADMIN is on, only successful access using the SYSADMIN administrative authority is traced. If IFCID 361 is started because audit policy category DBADMIN is on, only successful access using the DBADMIN administrative authority is traced.

Statistics trace (STAT)

Table 4. Classes for Db2 statistics trace

Class	Description of class	Activated IFCIDs
1	Information about system services, database statistics, statistics for the database services address space (ssnmDBM1), aggregated CPU and wait time statistics by connection type, and information about the system parameters that were in effect when the trace was started. Class 1 is also activated when you omit the CLASS keyword from the START TRACE command when you start the statistics trace.	1, 2, 105, 106, 202, 225, 369
2	Installation-defined statistics record	152
3	Deadlock, lock escalation, group buffer pool, data set extension information, and indications of long-running uncommitted reads, and active log space shortages.	172, 196, 250, 258, 261, 262, 313, 330, 335, 337
4	Db2 exceptional conditions.	173, 191, 192, 193, 194, 195, 203, 204, 205, 206, 207, 208, 209, 210, 235, 236, 238, 267, 268, 334, 343, 402
5	Db2 data sharing statistics.	230, 254
6	Storage statistics for the Db2 subsystem.	225
7	DRDA location statistics.	365
8	Data set I/O statistics.	199 , 389
9	Aggregated CPU and wait time statistics by connection type.	369
10	DRDA remote application statistics based on the CURRENT CLIENT_APPLNAME special register.	411
11	DRDA remote user statistics based on the CURRENT CLIENT_USERID special register.	412
12–29	Reserved.
30–32	Available for local use.

Performance trace (PERFM)

Table 5. Classes for Db2 performance trace

Class	Description of class	Activated IFCIDs
1	Background events. Class 1 is also activated when you omit the CLASS keyword from the START TRACE command when you start the performance trace.	1, 2, 31, 42, 43, 76, 77, 78, 79, 102, 103, 105, 106 107, 153
2	Subsystem events. Class 2 is also activated when you omit the CLASS keyword from the START TRACE command when you start the performance trace.	3, 68-75, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 106, 174, 175
3	SQL events. Class 3 is also activated when you omit the CLASS keyword from the START TRACE command when you start the performance trace.	22, 53, 55, 58, 59, 60, 61, 62, 63, 64, 65, 66, 92, 95, 96, 97, 106, 112, 173, 177, 233, 237, 250, 272, 273, 325
4	Reads to and writes from the buffer and EDM pools.	6, 7, 8, 9, 10, 29, 30, 105-107, 127, 128, 226, 227, 321, 322, 477
5	Write to log; archive log.	32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 104, 106, 114, 115, 116, 117, 118, 119, 120, 228, 229
6	Summary lock information.	20, 44, 45, 105, 106, 107, 172, 196, 213, 214, 218, 337
7	Detailed lock information.	21, 105, 106, 107, 223
8	Data scanning detail.	13-18, 105, 106, 107, 125, 221, 222, 231, 305, 311, 363
9	Sort detail.	26, 28, 95, 96, 106
10	BIND, commands, and utilities detail.	23, 24, 25, 90, 91, 0105, 106, 107, 108, 109, 110, 111, 201, 256
11	Execution unit switch and latch contentions.	46-52, 56, 57, 93, 94, 106, 113
12	Storage manager.	98, 99, 100, 101, 106
13	Edit and validation exits.	11, 12, 19, 105, 106, 107
14	Entry from and exit to an application.	67, 106, 121, 122
15	Installation-defined performance record.	154
16	Distributed processing.	157, 158, 159, 163, 167, 183
17	Claim and drain information.	211, 212, 213, 214, 215, 216
18	Event-based console messages.	197
19	Data set open and close activity.	370, 371
20	Data sharing coherency summary.	249, 251, 256, 257, 261, 262, 267, 268
21	Data sharing coherency detail.	255, 259, 263
22	Authorization exit parameters.	314
23	Language environment runtime diagnostics.	327
24	Stored procedure detail.	380, 499
25-29	Reserved.
30–32	Available for local use.

Monitor trace (MONITOR)

Table 6. Classes for Db2 monitor types

Class	Description of class	Activated IFCIDs
1	Standard accounting data. Class 1 is also activated when you omit the CLASS keyword from the START TRACE command when you start the monitor trace.	200
2	Entry or exit from Db2 event signaling.	232
3	Db2 wait time for I/O, locks; resource usage information.	6, 7, 8, 9, 32, 33, 44, 45, 117, 118, 127, 128, 170, 171, 174, 175, 213, 214, 215, 216, 226, 227, 242, 243, 321, 322, 378, 379, 382, 383, 413, 414
4	Installation-defined monitor record.	155
5	Time spent processing IFI requests.	187
6	Changes to tables created with DATA CAPTURE CHANGES.	185
7	Entry or exit from Db2 event signaling for package accounting. The data traces the amount of time an agent spent in Db2 to process each package.	200, 232, 240
8	Wait time for a package.	6, 7, 8. 9, 32, 33, 44, 45, 51, 52, 56, 57, 117, 118, 127, 128, 170,171, 174, 175, 213, 214, 215, 216, 226, 227, 239, 241, 242, 243, 321, 322, 378, 379, 382, 383, 413, 414
9	Enables statement level accounting.	124
10	Package detail for buffer manager, lock manager and SQL statistics. One of the following traces must also be activated before the IFCID 239 records are written: Accounting class 7 Accounting class 8 Monitor class 7 Monitor class 8	239
11	Plan-level accounting information. Class 11 is activated when you omit the CLASS keyword from the START TRACE command when you start the monitor trace.	3, 200
12-28	Reserved.
29	Controls the subsystem-wide collection of statistics for SQL statements.	316 , 318, 400, 401
30–32	Available for local use.

IFCID ( ifcid , …)

Specifies which other IFCIDs (trace events), in addition to those IFCIDs contained in the classes specified in the CLASS option, are to be started. To start only those IFCIDs specified in the IFCID option, use trace classes 30, 31, or 32. These classes have no predefined IFCIDs and are available for a location to use. See Example 1 for an example of activating only those trace events specified in the IFCID option.

If you do not specify the IFCID option, only those IFCIDs contained in the activated trace classes are started.

The maximum number of IFCIDs is 156. The range of values that are valid for the IFCID option is 1-511, with the following exceptions: 4, 5, 185, 187, 217, 232, 234, 240, 241, and 362. These exceptions are invalid values for the IFCID option. IFCIDs 4 and 5 are always automatically active.

Tip: IFCID numbers are often presented in Db2 and its documentation with three or four digits and leading zeros. For example, you might find: "IFCID 1," "IFCID 001," or "IFCID 0001." However, you can assume that these references each have the same meaning.

IFCID 362 is automatically started if you specify the AUDTPLCY option. Some of the other invalid IFCIDs can be activated only by certain trace classes. The invalid values for the IFCID option that can be started only by trace classes are:

To start...

Start...

IFCID 185

monitor trace class 6

IFCID 232

monitor trace class 2 or 7, or accounting trace class 2 or 7

IFCID 240

monitor trace class 7 or accounting trace 7

IFCID 241

monitor trace class 8 or accounting trace 8

The default is IFCID( * ).

BUFSIZE ( k_bytes , …)

Specifies the size of an IFC managed buffer that receives the trace data. You can specify this option only if you specified an OP n destination.

k_bytes can range from 256 KB to 65536 KB. The number must be evenly divisible by 4. If you specify a value outside of this range, the range limit closest to the specified value is used. To allocate a buffer size of 256 KB, you would specify BUFSIZE(256).

The default is BUFSIZE ( * ), which is the size set when Db2 was installed.

AUDTPLCY( policy-name , …)

Introduces a list of up to eight audit policy names for which trace information is gathered. This option starts the IFCIDs that correspond to the categories that are specified in the listed audit policies, and starts a trace for IFCID 0362.

AUDTPLCY applies to trace type AUDIT. You cannot specify AUDTPLCY with CLASS or IFCID.

TDATA

Specifies the product section headers to be placed into the product section of each trace record. If you do not specify TDATA, then the type of trace determines the type of product section header. The product section of a trace record can contain multiple headers.

All IFC records have a standard IFC header. The correlation header is added for accounting, performance, audit, and monitor records. The trace header is added for serviceability records.

CORRELATION

Places a correlation header on the record.

Abbreviation: COR

TRACE

Places a trace header on the record.

Abbreviation: TRA

CPU

Places a CPU header on the record. The CPU header contains the current processor time for the z/OS TCB or SRB executing.

DISTRIBUTED

Places a distributed header on the record.

Abbreviation: DIST

Usage notes

Finding trace field (IFCID) descriptions

You can find descriptions of trace records in the IFCID flat file (DSNWMSGS). The most current version of DSNWMSGS is available only for clients who have Db2 12 for z/OS licenses. The information is in a PDF file. To locate this information, see Db2 12 for z/OS IFCID flat file (DSNWMSGS).

Audit policies

Up to 32 audit policies can be active concurrently. If you specify multiple audit policies to start, and some of those policies do not start successfully, warning message DSNW196I is returned. The remaining audit policies are started.

Recommendation: FL 509 Start all tamper-proof audit policies in one START TRACE command, which assigns them to one trace number. Doing so helps avoid the possibility of mismatching trace numbers among data sharing members if the SCOPE(GROUP) option is used to stop a tamper-proof audit policy.

Number of traces

If you use one or no values for PLAN, AUTHID, or LOCATION, the START TRACE command starts a single trace. If you use multiple values for PLAN, AUTHID, or LOCATION, the command starts a trace for each plan, authorization ID, or location. Up to 32 traces can be active concurrently. If a START TRACE command is entered from the console or from the DB2I panels to an OP n or an OPX destination, message DSNW133I is issued to indicate trace data lost.

Using the options PLAN, AUTHID, or LOCATION when starting monitor trace class 1 has no effect on the amount of data returned on IFI READS requests.

Using the options PLAN, AUTHID, or LOCATION has no effect when starting either accounting or monitor trace classes 2, 5, or 7.

Stopping and starting Db2

If Db2 is stopped and started after you have started a trace, the trace is not restarted automatically.

Specifying SCOPE (GROUP)

When you issue START TRACE with SCOPE(GROUP), Db2 issues a START TRACE command on each member of the data sharing group. The data goes to the destination as it is defined for each member of the data sharing group. If you want to gather trace data for all members of the data sharing group in one place, use a monitor program with IFI READA or READS calls to collect the data.

FL 509 If you use the SCOPE(GROUP) option to specify multiple audit policies to start, the audit policies are sent to all members of the data sharing group. However, if one of the specified audit policies is a tamper-proof audit policy, the tamper-proof audit policy is blocked for all members of the data sharing group. You cannot use SCOPE(GROUP) to start tamper-proof audit policies.

If a trace is started with SCOPE(GROUP), and a new member joins the data sharing group after the trace is started, the new member also writes the trace data that is specified by the START TRACE command.

Starting a trace with SCOPE(GROUP) can generate large amounts of trace data, so you might need to increase the size of the return area in your monitor program to hold the extra data.

Tracing threads using the * wildcard in a partial name or address

In a partial name or address, you can use the wildcard suffix, “*” to filter threads. For example, if you specify “-START TRACE PLAN (A,B,C*)”, Db2 will trace, and then return A, B, CDE, CDEFG, CDEFGH, and so on. It will trace threads “A”, “B” and all threads starting with “C”.

You cannot include the wildcard character in the middle of a partial name or address.

Tracing threads using the positional, (_) wildcard in a partial name or address

In a partial name or address, you can use the positional wildcard, which is represented by the, “_” character, to trace threads when you want the wildcard in the middle, or when you want to trace threads of a specific length. For example, if you specify “-START TRACE PLAN (A_C), all threads will be traced that are three characters that have “A” as the first character, and “C” as the third. This command would return “ABC”, “ADC”, “AEC”, and so on. If you want to trace the thread “A_C” then you can specify “-START TRACE PLAN (A/_C). The “/” before the “_” tells Db2 to trace for the underscore in the search, rather than treating it as a wildcard. The same logic applies if you are trying to trace a thread that includes a “/” or “*” character. Because the character “/” is an escape character, if you are trying to trace a thread that has an “/” character in it, you can specify, for example, “-START TRACE PLAN (A//C)” to trace the thread “A/C”. You can also specify “-START TRACE PLAN (A/*C) to trace the thread “A*C”.

Tracing multiple threads at once using wildcards

You also have the option of tracing multiple threads based on multiple trace qualifications. For example, you can specify, “-START TRACE PLAN (A*, B*, C*) to simultaneously trace ALL threads for plan that start with “A”, “B”, and “C”. The wildcard character, “*” will trace all threads. You can specify more complex combinations such as, “-START TRACE PLAN (A_B*, C*, AND C/_D*)”, which will return all threads that:

• Begin with “A”, have a one character wild card as the second character in the thread, have a “B” as the third character in the thread, and end with any type or number of characters (ADBIOP, AOBTYJDP,)
• begin with “C”, and end with any combination of characters (CDE, CGHKO)
• begin with “C_D” and end with any type of character combination (C_DEFGH, C_DLMNOP)

All of the possible thread combinations listed above will be returned with the command above.

Specifying filtering criteria for threads

You can control the set of threads for which trace records are written by setting specific filtering criteria in the -START TRACE command. The following rules apply to all trace filters:

• Db2 applies trace filters when an external trace record is written. The state of a thread at that time dictates whether a thread is written in its entirety or eliminated completely. For example, if the PKGPROG(ABC) option is specified, trace records are written for threads that execute package ABC only if package ABC is active during the SQL event that causes the trace records to be externalized.
• Trace filters do not alter the contents of a trace record.
• Trace classes or IFCIDs that are not associated with specific trace records, such as accounting classes 2, 3, 7, 8, and 10, or IFCID 318, are not affected by filtering. For example, the following command does not result in filtering because no external records are written by accounting classes 7 or 8:

  -START TRACE(ACCTG) CLASS(7,8) DEST(SMF) PKGPROG(ABC)

• Db2 allows only one filtering option to have more than one value in a -START TRACE command. For example, the following command is valid:

  -START TRACE PLAN(A,B) USERID(B) WRKSTN(E)

  The following command is not valid:

  -START TRACE PLAN(A,B) USERID(A,B) WRKSTN(E)

Filtering threads using exclude functionality

When you specify an “X” with any constraint keyword, such as XPLAN, when you are filtering threads, you are using the exclude functionality for the –START TRACE command. You have the option of excluding specific types of threads when you are running trace commands. You can use the “X” character to exclude specific combinations of characters when you are running a trace. For example, you can specify this command to trace all threads except “A”:

-START TRACE XPLAN(A)

In this instance, b, bcd, bcde, or cd might be returned.

You can also exclude multiple types of threads from your trace. For example, you can specify this command to start a trace for all threads except threads for plans that start with “A”, with any combination of characters following “A”, and all those characters starting with “B”, with any combination of characters following “B”:

-START TRACE XPLAN(A*, B*)

Specifying XPLAN (*) excludes all threads from your search, and is not allowed. You also cannot use the * wildcard in the middle of trace criteria with exclude functionality, such as: “-START TRACE XPLAN (A*C).” You can, however, specify this command to return all threads except those for plans that start with “A”, any two characters next, a “C” in the fourth space, and any characters at the end.

-START TRACE XPLAN (A_ _ C *)

You can start two traces at once, in order to help you optimize your tracing capabilities. For example, you can specify this command:

-START TRACE XPLAN (A, B, C) USERID(D)

This command tells Db2 to start tracing threads for all plans except plans “A”, “B”, or “C”, and only where the user ID = “D”.

Combining trace qualifiers

You can customize the threads you trace by commanding Db2 to trace specific threads, while excluding other specific threads. For example, you can specify, “-START TRACE USERID (A,B) XPLAN (C)” . This criteria only traces threads where the user ID is equal to “A” or “B”, and plan is not equal to “C”. In this example, a thread where the user id is “A” and the plan is equal to “D” would pass, and be traced, but a thread where the user ID is “A” and plan is “C” would not pass, and would not be traced.

You can introduce multiple wildcards into your start trace commands to add more customization to your traces. For example, you can specify “-START TRACE PLAN (C*) USERID (Z, X) XPLAN (C, D, E)”. In this example, for the thread to be traced, the plan must begin with C, the user ID must be equal to Z or to X, and the plan cannot be C, D, or E. So a plan of CB, with a user ID of Z would pass, and the thread would be traced, but plan C with a user ID of X would fail because the command specifies not to trace threads where the plan is “C”, without additional characters in the thread.

Trace destination precedence

If an IFCID is associated with a class, and you specify that IFCID in the IFCID keyword, the destination for the class takes precedence. This rule affects IFCIDs in accounting classes 2, 3, 5, 7, 8, and monitoring classes 1, 2, 3, 5, 7, 8 because these classes have preset destinations for the IFCIDs.

For example, the following command "-START TRACE(ACCTG) CLASS (1,2,3) IFCID(6,7) DEST SMF," will not write IFCIDs 6 and 7 to SMF because they are part of accounting class 3, which has a preset destination. To write IFCIDs 6 and 7 to SMF, you need to start the trace as follows:

-START TRACE(ACCTG) CLASS(1,2,3) DEST(SMF)
-START TRACE(ACCTG) CLASS(30) IFCID(6,7) DEST(SMF)

IFCIDs for local use

IFCIDs 1000 through 1999 are not used by Db2, and are available for local use.

Examples

Example: performance trace for threads with remote activity

Start a performance trace for threads with remote activity to location USIBMSTODB21. Only activate IFCIDs 0044 (lock suspends) and 0045 (lock resumes). Trace class 30 is available for installation use.

-START TRACE (PERFM)
  DEST(GTF)
  LOCATION(USIBMSTODB21)
  CLASS(30)
  IFCID(44,45)

Example: accounting trace for a plan

Start an accounting trace for plan DSN8BC81. Write records to SMF (that will happen by default). Include a comment to identify the trace.

-START TRACE (ACCTG)
  PLAN (DSN8BC81)
  COMMENT ('ACCTG TRACE FOR DSN8BC81')

Example: statistics trace

Start the statistics trace. Write records to SMF (by default).

-START TRACE=S

Example: start monitor tracing

Start monitor tracing (usually done by an application program) on the data sharing group. Write records to OPX (by default).

-START TRACE(MON) SCOPE(GROUP)

Example: performance trace records for threads that are executing package

Use the PKGPROG option to write performance trace records only for threads that are executing package ABC during the SQL event that causes the trace record to be externalized.

-START TRACE(PERFM) CLASS(3) DEST(SMF) PKGPROG(ABC)

Example: accounting trace to activate package-level accounting

Start an accounting trace to activate package-level accounting and to collect data that is externalized by IFCID 3 and IFCID 239. Use the PKGPROG option to externalize accounting information that is written when accounting records for package ABC are written.

-START TRACE(ACCTG) CLASS(1,2,3,7,8) DEST(SMF) PKGPROG(ABC)

The externalized records contain information for all packages that are executed during the accounting interval.

Example: provide IFC exclude filtering for correlation ID

START TRACE (A) XCORRID (*)
- 10.46.05 STC00051  DSNW150I  ) EXCLUDE FOR ALL CORRID VALUES IS NOT       
-  ALLOWED                                                                  
- 10.46.05 STC00051  DSN9023I  ) DSNWVCM1 '-START TRACE' ABNORMAL COMPLETION

Example audit trace using audit policy

Start an audit trace using audit policy AUDITADMIN.

-STA TRACE(AUDIT) DEST(GTF) AUDTPLCY(AUDITADMIN)

Trace information for commands issued with group scope

If this command is issued with options that specify group scope in a Db2 data sharing member, it also runs on all other active members. IFICID 090 trace records for other group members can show that the same command was issued by the SYSOPR authorization ID from the 016.TLPKN5F correlation ID, in addition to the trace records from the member where the original command was issued. See Command scope in Db2 data sharing.