-STOP TRACE (DB2)
The DB2® command STOP TRACE stops tracing.
One additional option to 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® Software Support.
Abbreviation: -STO TRA
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).
Data sharing scope: Group or local, depending on the value of the SCOPE option.
Authorization
- 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.
Syntax
.-,-------. V | >>-STOP TRACE--(-+-PERFM---+-)--DEST(---+-----+-+-)-------------> +-ACCTG---+ +-GTF-+ +-STAT----+ +-SMF-+ +-AUDIT---+ +-SRV-+ +-MONITOR-+ '-OPn-' '-*-------' >--+----------------------+--+---------------------+------------> '-| constraint block |-' '-| filtering block |-' >--+------+--+-----------------+--+--------------------+--------> '-RMID-' '-COMMENT(string)-' | .-LOCAL-. | '-SCOPE(-+-GROUP-+-)-' >--ASID(x'dddd')-----------------------------------------------><
constraint block >>-+---------------------------+--------------------------------> | .-*-------------. | | | .-,---------. | | | | V | | | '-PLAN(-+---plan-name-+-+-)-' >--+----------------------------------------+-------------------> | .-*------------------------. | | | .-,----------------. | | | | V | | | '-PKGLOC(-+---package-location-+-----+-)-' '-partial-package-location-' >--+---------------------------------------------+--------------> | .-*-----------------------------. | | | .-,---------------------. | | | | V | | | '-PKGCOL(-+---package-collection-id-+-----+-)-' '-partial-package-collection-id-' >--+---------------------------------------------+--------------> | .-*----------------------------. | | | .-,--------------------. | | | | V | | | '-PKGPROG(-+---package-program name-+-----+-)-' '-partial-package-program name-' >--+------------------------------------+-----------------------> | .-*--------------------. | | | .-,----------------. | | | | V | | | '-AUTHID(-+---authorization-id-+-+-)-' '-partial-userid-------' >--+--------------------------+--+------------------------+-----> | .-*-----------. | | .-*-----------. | | | .-,-------. | | | | .-,-------. | | | | V | | | | | V | | | '-CLASS(-+---integer-+-+-)-' '-TNO(-+---integer-+-+-)-' >--+-----------------------------------+------------------------> '-LOCATION(-+-*-----------------+-)-' | .-,-------------. | | V | | +---location-name-+-+ +-<luname>----------+ +-partial-<luname>*-+ +-ipaddr------------+ '-partial-ipaddr----' >--+------------------------------+-----------------------------> | .-*--------------. | | | .-,------. | | | | V | | | '-USERID(-+---userid-+-----+-)-' '-partial-userid-' >--+-----------------------------------------+------------------> | .-*------------------------. | | | .-,----------------. | | | | V | | | '-APPNAME(-+---application-name-+-----+-)-' '-partial-application-name-' >--+----------------------------------------+-------------------> | .-*------------------------. | | | .-,----------------. | | | | V | | | '-WRKSTN(-+---workstation-name-+-----+-)-' '-partial-workstation-name-' >--+------------------------------------------+-----------------> | .-*--------------------------. | | | .-,------------------. | | | | V | | | '-CONNID(-+---connection-role-id-+-----+-)-' '-partial-connection-role-id-' >--+--------------------------------------+---------------------> | .-*----------------------. | | | .-,--------------. | | | | V | | | '-CORRID(-+---correlation-id-+-----+-)-' '-partial-correlation-id-' >--+----------------------------------------+-------------------> | .-*--------------------------. | | | .-,---------------. | | | | V | | | '-ROLE(-+---connection-role-+--------+-)-' '-partial-connection-role-id-' >--+-------------------------------------+--------------------->< | .-,-----------. | | (1) V | | '-AUDTPLCY----(-----policy-name-+---)-'
- You cannot specify CLASS or IFCID with AUDTPLCY. AUDTPLCY applies to trace type AUDIT.
filtering block >>-+--------------------------------+---------------------------> | .-,---------. | | V | | '-XPLAN(-+---plan-name-+-----+-)-' '-partial-plan-name-' >--+-----------------------------------------+------------------> | .-,----------------. | | V | | '-XPKGLOC(-+---package-location-+-----+-)-' '-partial-package-location-' >--+----------------------------------------------+-------------> | .-,---------------------. | | V | | '-XPKGCOL(-+---package-collection-id-+-----+-)-' '-partial-package-collection-id-' >--+----------------------------------------------+-------------> | .-,--------------------. | | V | | '-XPKGPROG(-+---package-program-name-+-----+-)-' '-partial-package-program-name-' >--+-----------------------------------------+------------------> | .-,----------------. | | V | | '-XAUTHID(-+---authorization-id-+-----+-)-' '-partial-authorization-id-' >--+-----------------------------------+------------------------> | .-,-------------. | | V | | '-XLOC(-+---location-name-+-----+-)-' +-partial-location-name-+ +-<--luname-->----------+ +-partial-<luname>*-----+ +-ipaddr----------------+ '-partial-ipaddr--------' >--+-------------------------------+----------------------------> | .-,------. | | V | | '-XUSERID(-+---userid-+-----+-)-' '-partial-userid-' >--+------------------------------------------+-----------------> | .-,----------------. | | V | | '-XAPPNAME(-+---application-name-+-----+-)-' '-partial-application-name-' >--+-----------------------------------------+------------------> | .-,----------------. | | V | | '-XWRKSTN(-+---workstation-name-+-----+-)-' '-partial-workstation-name-' >--+-------------------------------------------+----------------> | .-,------------------. | | V | | '-XCONNID(-+---connection-role-id-+-----+-)-' '-partial-connection-role-id-' >--+---------------------------------------+--------------------> | .-,--------------. | | V | | '-XCORRID(-+---correlation-id-+-----+-)-' '-partial-correlation-id-' >--+-----------------------------------------+----------------->< | .-,---------------. | | V | | '-XROLE(-+---connection-role-+--------+-)-' '-partial-connection-role-id-'
Option descriptions
- ( PERFM )
- Specify
to stop a trace that is intended for performance analysis and tuning.
Abbreviation: P
- ( ACCTG )
- Specify
to stop an accounting trace.
Abbreviation: A
- ( STAT )
- Specify
to stop a trace that collects statistical data. The LOCATION option
cannot be specified when you choose a statistics trace.
Abbreviation: S
- ( AUDIT )
- Specify
to stop a trace that collects audit data from various components of DB2.
Abbreviation: AU
- ( MONITOR )
- Specify
to stop a trace that collects monitor data.
Abbreviation: MON
- (*)
- Specify to stop all trace activity. See Usage notes for information about using STOP TRACE (*) with traces that use monitor trace class 6.
- SCOPE
- Specifies
the scope of the command.
- ( LOCAL )
- Stops the trace only on the local DB2 subsystem.
- (GROUP)
- Stops the trace on all members of a data sharing group.
- ASID(x'dddd')
- Specifies that the trace for an address space is to be stopped.
dddd is a four-byte hexadecimal address space ID (ASID).
- 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.
- COMMENT ( string )
- Gives
a comment that is reproduced in the trace output record for the STOP
TRACE command (except in the resident trace tables).
string is any SQL string; it must be enclosed between apostrophes if it includes a blank, comma, or special character.
- DEST
- Limits
stopping to traces started for particular destinations. You can use
more than one value, but do not use the same value twice. If you do
not specify a value for DEST, DB2 does
not use destination to limit which traces to stop.
Abbreviation: D
Possible values and their meanings are:- Value
- Trace destination
- GTF
- The generalized trace facility
- SMF
- The System Management Facility
- SRV
- An exit to a user-written routine
- OP n
- A specific destination. n can be a value from 1 to 8
See -START TRACE (DB2) for a list of allowable destinations for each trace type.
- CLASS( integer , …)
- Limits
stopping to traces started for particular classes. For descriptions
of the allowable classes, see -START TRACE (DB2).
You cannot specify a class if you did not specify a trace type.
Abbreviation: C
The default is CLASS( * ) , which does not limit the command.
- TNO( integer , …)
- Limits stopping to particular traces, identified by their trace
numbers (1 to 32, 01 to 09). You can use up to eight trace numbers.
If you use more than one number, you can use only one value each for
PLAN, AUTHID, and LOCATION.
The default is TNO( * ) , which does not limit the command.
- 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( * ).
- ( * )
- Stops a trace for all plans.
- plan-name
- The name of an application plan. You can use up to eight names. 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( * ).
- ( * )
- Stops a trace for all authorization IDs.
- authorization-id
- Specifies an authorization ID. You can use up to eight identifiers. 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-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. 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.
- (*)
- Indicates that you want to stop 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 stop 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 stop a trace for a client with the LUNAME of LULA, enter the following command:
To stop a trace for a client with the IP address of 123.34.101.98, enter the following command:-START TRACE (*) LOCATION (<LULA>)-STOP TRACE (*) 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.
- 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.
- 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.
- CONNID or XCONNID
- Specifies the connection ID. Use CONNID to constrain the trace
to the specified connections or XCONNID to exclude the specified connections.
- 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.
- AUDTPLCY
- Stops
the IFCIDs that correspond to the categories specified in the listed
audit policies. You can specify up to eight audit policy names. AUDTPLCY
applies to trace type AUDIT. You cannot specify CLASS or IFCID with
AUDTPLCY.
Usage notes
-STOP TRACE (PERFM) CLASS (1,2)-STOP TRACE (*)Traces that use monitor trace class 6: When stopping trace classes, a special circumstance occurs if monitor trace class 6 is active. Monitor trace class 6 enables and disables data propagation. To avoid accidentally stopping this trace class, the commands STOP TRACE(*) and STOP TRACE(MON) CLASS(*) fail if monitor trace class 6 is active.
-STOP TRACE(MON) CLASS(1,3,6)-STOP TRACE(MON) CLASS(1,2,3,4,5,6,...32)However, if monitor trace class 6 is not active the STOP TRACE(*) command stops all active traces.
Traces started by a IFI/IFC program: Before you stop an active trace, ensure that an IFI application program or the IFC Selective Dump utility (DSN1SDMP) did not start the trace. If you stop a trace started by DSN1SDMP, the DSN1SDMP utility abnormally terminates.
Tracing threads using the * wildcard: You can use the wildcard suffix, “*” to filter threads. For example, if you specify “-STOP 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”.
Tracing threads using the positional, (_) wildcard: 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 “-STOP 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. NOTE: if you want to trace the thread “A_C” then you can specify “-STOP 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, “-STOP TRACE PLAN (A//C)” to trace the thread “A/C”. You can also specify “-STOP TRACE PLAN (A/*C) to trace the thread “A*C”.
- 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)
You have the ability to filter multiple threads at the same time, setting specific criteria for the trace. For example, you can specify “-STOP TRACE PLAN (A) USERID (B). This will trace the threads where the plan thread is A, and the user ID is B. When tracing threads, you can only specify more than one thread criteria for one filter per “-STOP TRACE” command. For example, you can specify “-STOP TRACE PLAN (A,B) USERID (B) WRKSTN (E),” but you cannot specify “-STOP TRACE PLAN (A, B) USER ID (A, B) WRKSTN (E) because, in this example, two of the filter qualifications have two elements defined to be traced, and DB2 only allows for one attribute to have more than one trace element to be defined per trace.
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 –STOP 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 “-STOP TRACE XPLAN(A), to trace all threads except “A”. In this instance B, BCD, BCDE, or CD could possibly be returned.
You also have the option of excluding multiple types of threads from your trace. For example, you can specify, “-STOP TRACE XPLAN (A*, B*) to trace all threads except those starting with “A”, with any combination of characters following “A”, and all those characters starting with “B”, with any combination of characters following “B”. Note: Specifying XPLAN (*) excludes all threads from your search, and is not allowed. You also cannot use the wildcard in the middle of a -STOP TRACE command with exclude functionality, such as, “-STOP TRACE XPLAN (A*C).” You can, however, specify “-STOP TRACE XPLAN (A_ _ C *)”, which will return all threads except those starting with “A”, a variety of two characters next, a “C” in the fourth space, and a variety of characters at the end. The wildcard symbol cannot be placed in the middle of trace criteria.
You have the ability to stop two traces at once, in order to help you optimize your tracing capabilities. For example, you can specify (-STOP TRACE XPLAN (A, B, C) USERID (D))”. This tells DB2 to stop tracing all threads for plans except threads “A”, “B”, or “C”, 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, “-STOP TRACE USERID (A,B) XPLAN (C)”. This criteria only traces threads where the user ID is equal to “A” or “B”, and the 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 wildcards into your stop trace commands to add more customization to your traces. For example, you can specify “-STOP TRACE PLAN (C*) USER ID (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. Therefore, 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.
Examples
-STOP TRACE (*) DEST (GTF)-STOP TRACE (ACCTG)
PLAN (DSN8BC81)
LOCATION (USIBMSTODB21)
COMMENT('ACCTG TRACE FOR DSN8BC81')-STOP TRACE (P) TNO(4)-STOP TRACE (*) LOCATION (USIBMSTODB22)-STOP TRACE=P-STOP TRACE(MON)-STOP TRACE(MON) SCOPE(GROUP)-STO TRACE(AUDIT) DEST(GTF) AUDTPLCY(AUDITADMIN)