Thread Lock Usage Data

Use these attributes to view the detailed statistics.

ACE Address The agent control element (ACE) (see QWHSACE) of the thread that is reported. (Field name: QW0148AC). The value in QWHSACE is the ACE of the IFI application that requested this trace record.

Authorization ID The primary authorization ID from connection or signon (Field name: UWHCAID). For z/OS operator commands and Db2 system internal agents, the value is SYSOPR. Secondary authorization IDs might be the RACF groups that are associated with this primary authorization ID. The SQL ID is initially set to this primary authorization ID. The connection authorization exit and the signon authorization exit can change the primary authorization ID so that it is not the same as the original primary authorization ID (QWHCOPID). Distributed authorization ID translation can also change the primary authorization ID.

Change Requests The number of change requests. (Field name: QTXTCHG).

Claim Request The number of claim requests. (Field name: QTXTCLNO).

Claims Failed The number of unsuccessful claim requests. (Field name: QTXTCLUN).

Collection Name The collection name. (Field name: UW0148CI).

Connection ID The connection name. Not valid on end of memory and reflects the z/OS home ASID connection name. (Field name: QWHCCN).
  • For batch: 'BATCH'
  • For TSO: 'TSO'
  • For QMF: 'DB2CALL'
  • For UTILITY: 'UTILITY'
  • For DB2 INTERNAL: Db2 subsystem ID
  • For IMS: IMS-ID
  • For CICS: CICS-ID
  • For RRSAF: RRSAF
For distributed database access threads:
  • For threads from a Db2 requester, this field contains the connection name of the thread at the requesting location.
  • For threads using the DRDA protocol from a non-Db2 requester, this field contains the constant 'SERVER'.
Correlation ID The correlation ID value. (Field name: QWHCCV).
  • For batch: JOBNAME
  • For TSO: LOGON-ID
  • For IMS/VS: PST#.PSBNAME
  • For CICS: CONNECTION_TYPE.THREAD_TYPE.THREAD_#.TRAN-ID
  • For RRSAF: CORRELATION-ID value from signon function
  • For threads from a Db2 requester, this field contains the CORRELATION-ID name of the thread at the requesting location.
  • For threads using the DRDA protocol from a non-Db2 requester, this field contains the first 12 characters in the DDM external name (EXTNAM) parameter of the DDM EXCSAT command received as part of the SQL CONNECT.

Data Sharing Group The data sharing group name.

DB2 Subsystem The Db2 subsystem ID.

DB2 Version The Db2 version.

Deadlock Count The number of times deadlocks were detected. This number should be low, ideally 0. (Field name: QTXTDEA). Background and Tuning Information: Deadlocks occur when two or more application processes each hold locks on resources that the others need, without which they cannot proceed. Ensure that all applications accessing the same tables access them in the same order. Deadlocks can also occur through index page splits if there is high insert activity. In this case, the recommendation is to set SUBPAGES to 1 for the index. This field is incremented once for each deadlock encountered. There is no correlation between this field and the deadlock events reported in the Locking report set or the number of IFCID 172 records written. This field reports all deadlocks, regardless of how they were resolved. The locking report and record trace IFCID 172 show only those deadlocks that were resolved by Db2.

Drain Requests The number of drain requests. (Field name: QTXTDRNO).

Drains Failed The number of unsuccessful drain requests. (Field name: QTXTDRUN).

DSG Member Name The data sharing group member name.

End User ID The user ID of the workstation end user. (Field name: QWHCEUID). This user ID can be different from the authorization ID used to connect to Db2. This field contains blanks if the client does not supply this information.

Filler1 The pad field. (Field name: FILLER01).

Flag The first flag byte. This field is applicable for Accounting IFCID 0003 and does not apply to IFCID 0002. (Field name: QTXTFLG1).
  • InfLimit=64
  • NoRun=128

High 16 MicroSec The highest CPU time (in 16-microsecond units) used in a successful Db2 internal call, rather than in a single SQL statement. (Field name: QTXTCHUS). Typically, many Db2 calls are made for each SQL statement, so this value might be considerably smaller than the total CPU time used for the full SQL statement. Only successful Db2 calls are used to determine the value of this field. This field is applicable for Accounting IFCID 0003 and does not apply to IFCID 0002.

Interval Start The start time of this interval.

Latch Suspends The suspend count because of IRLM latch conflict. (Field name: QTXTSLAT).

Limit 16 MicroSec The internal CPU limit (in 16-microsecond units). (Field name: QTXTCLMT). This field is applicable for Accounting IFCID 0003 and does not apply to IFCID 0002.

Limit in SUs The limit in service units as defined in the ASUTIME column in the resource limit specification table. (Field name: QTXTSLMT). This field is applicable for Accounting IFCID 0003 and does not apply to IFCID 0002.

Location Name The location name. (Field name: UW0148LN). The location name indicates the name of either the local or remote location, depending on where the thread executes a package. The following statements are executed locally, regardless of the current package name:
  • COMMIT
  • ROLLBACK
  • SET <HOST VARIABLE> = CURRENT PACKAGE SET
  • SET <HOST VARIABLE> = CURRENT SERVER
Note: For these statements, this field contains the location where the package executes, not the location where the statement executes.

Lock Escal Excl The count of lock escalations to exclusive mode. (Field name: QTXTLEX). This field represents the number of times the value specified in the LOCKS PER TABLE(SPACE) installation parameter on IRLM Panel 2 (DSNTIPJ) or in the LOCKMAX CLAUSE of the CREATE TABLESPACE statement was exceeded and the table space lock was promoted from a page or row lock (IX) to a table or table space lock (X) for this thread. Escalation can cause unpredictable response times. Lock escalation should only happen on an exception basis; for example, when an application process updates or references (if repeatable read is used) more pages or rows than it normally does.

Lock Escal Shared The count of lock escalations to shared mode. (Field name: QTXTLES). This field represents the number of times the value specified in the LOCKS PER TABLE(SPACE) installation parameter on IRLM Panel 2 (DSNTIPJ) or in the LOCKMAX CLAUSE of the CREATE TABLESPACE statement was exceeded and the table space lock was promoted from a page or row lock (IS) to a table or table space lock (S) for this thread. Escalation can cause unpredictable response times. Lock escalation should only happen when an application process updates or references (if repeatable read is used) more pages or rows than it normally does.

Lock Requests The lock request count. (Field name: QTXTLOCK).

Lock Suspend Count Th number of suspends because of lock conflict. (Field name: QTXTSLOC). The number of times a lock could not be obtained and the unit of work was suspended. Suspensions are highly dependent on the application and table space locking protocols. This number should be low, ideally zero.

Logical Unit of work ID The logical unit of work ID (LUWID) as defined for the LU 6.2 interface. (Field name: QW0148LU). The LUWID uniquely identifies the thread within the network, and consists of the following:
  • A fully qualified network name (QW0148LM)
  • An LUW instance number (QW0148UV)
  • An LUW sequence number
The fully qualified network name is a maximum 16-bytes in length, consisting of two 8-character fields, QW0148NI and QW0148LM, that together uniquely identify a client system.

Max Page/Row Locks The maximum number of page or row locks held. (Field name: QTXTNPL). This field is a count of the maximum number of page or row locks concurrently held by a single application during its execution. This count cannot exceed the LOCKS PER USER installation parameter value on panel DSNTIPJ. This field is not applicable for statistics IFCID 0002; it is applicable for Accounting IFCID 0003 and Monitor IFCID 0148.

MVS System ID The MVS identifier.

Originating System The managed system name of the agent. Valid format is alphanumeric, with a maximum of 32 characters; example, DB91:SYS1:DB2.

Other IRLM Requests The other IRLM request count. (Field name: QTXTIRLM).

Other Suspends The suspend count due to other reasons. (Field name: QTXTSOTH). Other reasons include:
  • IRLM SYNC requests (up to one request per agent canceled)
  • IRLM SYNCHRONOUS notifies. Examples include:
    • Requests to invalid DBDS in other members
    • Requests for a data set extend
    • Requests for DROP or REVOKE to invalid EDM authorization caches in other members
    • GBP recovery

Package ID The package ID. (Field name: UW0148PN).

Plan Name The plan name. (Field name: QWHCPLAN). The plan name is blank for a Db2 command thread; Otherwise:
  • For SPUFI with cursor stability: 'DSNESPCS'
  • For SPUFI with repeatable read: 'DSNESPRR'
  • For TSO: the application plan name
  • For IMS: the application plan name
  • For CICS: application plan name, IMS and CICS commands have a blank plan name.
  • For RRSAF create thread with the collection parameter: '?RRSAF '
  • For QMF: 'DSQPLAN'
  • For Distributed Database Access threads:
    • For threads using the DRDA protocol from a requester, this field contains the plan name that is executed at the requesting location.
    • For threads using the DRDA protocol from a non-Db2 requester or from a Db2 2.3 requester, this field contains the constant 'DISTSERV'.
  • For binding: 'DSNBIND' (system plan)
  • For utility: 'DSNUTIL ' (system plan)
  • For authorization: 'ACT' + X'0000000000' (system plan)
  • For unallocated threads and miscellaneous Db2 system service tasks: 'BCT' + X'0000000000' (system plan)
  • For startup: 'STARTCT' + X'00' (system plan)

Query Requests The query request count. (Field name: QTXTQRY).

QWACFLGS Flags. (Field name: QWACFLGS).

RES Limit Reason Specifies how the resource limit was determined. (Field name: QTXTPREC). The following table shows the combinations of resource limit facility table values that determine QTXAPREC values from 1 to 24. A value of NAME for a column indicates that the resource limit facility table column contains a user-specified primary authorization ID, package collection name, package name, or LUNAME.
QTXAPREC AUTHID RLFCOLLN RLFPKG LUNAME
1 NAME NAME NAME NAME
2 NAME NAME NAME BLANK
3 NAME NAME NAME PUBLIC
4 NAME NAME BLANK NAME
5 NAME NAME BLANK BLANK
6 NAME NAME BLANK PUBLIC
7 NAME BLANK NAME NAME
8 NAME BLANK NAME BLANK
9 NAME BLANK NAME PUBLIC
10 NAME BLANK BLANK NAME
11 NAME BLANK BLANK BLANK
12 NAME BLANK BLANK PUBLIC
13 BLANK NAME NAME NAME
14 BLANK NAME NAME BLANK
15 BLANK NAME NAME PUBLIC
16 BLANK NAME BLANK NAME
17 BLANK NAME BLANK BLANK
18 BLANK NAME BLANK PUBLIC
19 BLANK BLANK NAME NAME
20 BLANK BLANK NAME BLANK
21 BLANK BLANK NAME PUBLIC
22 BLANK BLANK BLANK NAME
23 BLANK BLANK BLANK BLANK
24 BLANK BLANK BLANK PUBLIC
The following table shows the combinations of resource limit facility table values that determine QTXAPREC values from 25 to 40. A value of name for the RLFEUAN, RLFEUID, or RLFEUWN column means that the corresponding resource limit facility table column contains a user-specified application name, end-user ID, or end-user workstation name. A value of ADDR for the RLFIP column means that the corresponding resource limit facility table column contains a user-specified IP address.
QTXAPREC RLFEUAN RLFEUID RLFEUWN RLFIP
25 NAME NAME NAME ADDR
26 NAME NAME NAME BLANK
27 NAME NAME BLANK ADDR
28 NAME NAME BLANK BLANK
29 NAME BLANK NAME ADDR
30 NAME BLANK NAME BLANK
31 NAME BLANK BLANK ADDR
32 NAME BLANK BLANK BLANK
33 BLANK NAME NAME ADDR
34 BLANK NAME NAME BLANK
35 BLANK NAME BLANK ADDR
36 BLANK NAME BLANK BLANK
37 BLANK BLANK NAME ADDR
38 BLANK BLANK NAME BLANK
39 BLANK BLANK BLANK ADDR
40 BLANK BLANK BLANK BLANK
The following values of QTXAPREC depend on factors other than resource limit facility table values.
41
The resource limit is determined by Db2 subsystem parameter values.
42
The resource limit is infinite because the user has installation SYSADM or installation SYSOPR authority.

RES Tbl ID The resource limit specification table ID. (Field name: QTXTRLID). This field is applicable for Accounting IFCID 0003 and does not apply to IFCID 0002.

Thread Type The connecting system type code (in hexadecimal). This field can have a null value. (Field name: QWHCATYP). For example, this field contains a null value for some utilities.

Timeout Count The number of times a unit of work was suspended for a time exceeding the timeout value. This number should be low, ideally 0. (Field name: QTXTTIM).

Transaction Name The transaction or application name that the end user is running. (Field name: QWHCEUTX). This field identifies the application that is currently running, not the product that is used to run the application. This field contains blanks if the client did not supply this information.

Unlock Requests The unlock request count. (Field name: QTXTUNLK).

Workstation Name The end user's workstation name. (Field name: QWHCEUWN). This field contains blanks if the client did not supply this information.