-START TRACE command (Db2)
The Db2 command START TRACE starts Db2 traces.
An additional option for this command and additional values for a few other options exist. This additional information is intended for service and use under the direction of IBM Support.
Abbreviation: -STA TRA
Environment for -START TRACE
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).
Data sharing scope: Group or member
Authorization for -START TRACE
- TRACE privilege
- SQLADM authority
- System DBADM authority
- SYSOPR authority
- SYSCTRL authority
- SYSADM authority
- SECADM authority
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 for -START TRACE
constraint block
- 1 Do not specify CLASS or IFCID with AUDTPLCY. AUDTPLCY applies to trace type AUDIT.
- 2 Specifying multiple AUDTPLCY traces with different filtering criteria causes the filtering to become a UNION among all AUDTPLCY traces.
Option descriptions for -START TRACE
You must specify a trace type.
- ( PERFM )
- The performance trace is intended
for performance analysis and tuning. This trace includes records of
specific events in the system, including events related to distributed
data processing. The data can be used for program, resource, user,
and subsystem-related tuning.
Abbreviation: P
- ( ACCTG )
- The accounting trace records transaction-level data that is
written when the processing for a transaction is completed. It provides data that enables you to
conduct Db2 capacity planning and to tune
application programs.
Abbreviation: A
- ( STAT )
- The statistics trace collects statistical data that is broadcast
by various components of Db2 at certain
time intervals. You can specify intervals for statistics collection during installation.
Abbreviation: S
LOCATION cannot be specified when you choose a statistics trace.
- ( AUDIT )
- The audit trace collects information about Db2 security controls and can be used to ensure that
data access is allowed only for authorized purposes.
Abbreviation: AU
- ( MONITOR )
- The
monitor trace enables attached monitor programs to access Db2 trace data through calls to the instrumentation
facility interface (IFI). Monitor programs can access the trace data asynchronously through an
OPx buffer by issuing READA requests, or synchronously in the monitor return area
by issuing READS requests.
Abbreviation: MON
- SCOPE
- Specifies the scope of the command in a data sharing group.
- ( LOCAL )
- Specify to start traces on the local member only.
- (GROUP)
- Specify to start traces on all members of the data sharing group.
FL 509 You cannot specify SCOPE(GROUP) to start audit traces that use tamper-proof audit policies.
- ASID(x'dddd')
- Specifies the address space for which trace data is collected.
dddd is a four-byte hexadecimal address space ID (ASID).
- COMMENT ( string )
- Gives a comment that is reproduced in the trace output (except in the resident trace tables). This option can be used to record why the command was issued.
string is any character string; it must be enclosed between apostrophes if it includes a blank, comma, or special character.
- RMID
- Specifies resource manager identifier. You can specify up to 8 valid RMIDs, which are one or two digit identifiers. You cannot specify RMID for accounting or statistic traces.
- DEST
- Specifies where the trace output is to be recorded. You can use more than one value, but do not use the same value twice. If you do not specify a value, the trace output is sent to the default destination shown in the following table.
If the specified destination is not active or becomes inactive after you issue the START TRACE command, you receive message DSNW133I, which indicates that the trace data is lost. This applies for destinations GTF, SRV, and SMF. You also receive this message for destinations OP n and OPX if START TRACE is not issued by an application program.
Abbreviation: D
The allowable values and the default value depend on the type of trace started, as shown in the following table.Table 1. Allowable destinations for each trace type Type GTF SMF SRV OP n OPX ZAI PERFM Default Allowed Allowed Allowed Allowed ACCTG Allowed Default Allowed Allowed Allowed STAT Allowed Default Allowed Allowed Allowed Allowed AUDIT Allowed Default Allowed Allowed Allowed MONITOR Allowed Allowed Allowed Allowed Default The meaning of each value is as follows:- GTF
- The z/OS generalized trace facility (GTF). The record identifier for records from Db2 is X'0FB9'.
- SMF
- The system management facility. The SMF record type of Db2 trace records depends on the IFCID record, as follows:
- IFCID record
- SMF record type
- 1 (System services statistics)
- 0100
- 2 (Database services statistics)
- 0100
- 3 (Agent accounting)
- 0101
- 202 (Dynamic system parameters)
- 0100
- 225 (System Storage® summary statistics)
- 0100
- 230 (Data sharing global statistics)
- 0100
- 239 (Package accounting)
- 0101
- 369 (Aggregate accounting statistics)
- All others
- 0102
- SRV
- An exit to a user-written routine. For instructions and an example of how to write such a routine, see the macro DSNWVSER in library prefix .SDSNMACS.
- OP n
- A specific destination.
n can be an integer from 1 to 8.
- OPX
- A generic destination which uses the first free OP n slot.
Only applications that start a trace to an OP n buffer can read that buffer.
All traces to an OPX destination must be stopped before the buffer is marked as not in use. Traces that are started to an OPX buffer that was formerly in use write over the storage any previous traces had set.
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:
IFCID | IFCID | IFCID | IFCID | IFCID | IFCID | IFCID | IFCID | IFCID | IFCID |
---|---|---|---|---|---|---|---|---|---|
1 | 2 | 4 | 5 | 104 | 105 | 107 | 124 | 129 | 147 |
148 | 149 | 150 | 185 | 186 | 199 | 202 | 217 | 225 | 230 |
254 | 306 | 316 | 317 | 365 | 401 | 402 | 411 | 412 |
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
- You enter an IPv6 address in IPv6 format:
- For an IPv4-only stack, you enter the IP address in IPv4 format:
- (*)
- 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:
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 (<LULA>)
-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 theCLASS
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. Client inventory information. 411, 417 11 DRDA remote user statistics based on the CURRENT CLIENT_USERID special register. Client inventory information. 412, 417 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 for -START TRACE
- 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)
- 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 for -START TRACE
- 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.
The externalized records contain information for all packages that are executed during the accounting interval.-START TRACE(ACCTG) CLASS(1,2,3,7,8) DEST(SMF) PKGPROG(ABC)
- 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.