Db2 Thread History Detail Lock Counts
Use these attributes to view detailed information about Db2 Thread Detail Lock Counts.
ACE Address The agent control element (ACE) of the thread that was used when this record was written. (Field name: NTHACE). An ACE is an identifier that is unique to a given thread for the life of the thread. Using the ACE values, it is possible to separate out trace records from concurrent unrelated threads. When a thread is terminated, the ACE value is freed and can be used later by a subsequent thread. There are times when an ACE value in the data indicates that the function performed under the ACE in NTHACE is actually being performed on behalf of another ACE, as indicated by the ACE value in the data section. An example of this is when a sequential prefetch read is done by a Db2 service task on behalf of a thread.
- For TSO: The logon ID.
- For batch: The user ID on the job statement.
- For RRSAF:If the following conditions are true, RRSAF uses the value in ACEEUSRI as the initial primary authorization ID:
- The system authorization facility (SAF) and a security product, such as RACF, is used.
- There is an ACEE associated with the job step TCB or with the AXSB.
- The first 7 characters of the job step user ID match the first 7 characters of the value in ACEEUSRI.
If any of the above conditions are not true, then the initial primary authorization ID is the value in ASXBUSER.
- For IMS (message-driven regions): SIGNON-ID, LTERM, ASXBUSR, OR PSB NAME.
- For IMS (control regions): USER-ID on job statement, or RACF, started procedure entry if RACF is used.
- For CICS: USERID, OPID, GROUP, SIGN, or TERM, or TX, as specified in the DB2ENTRY resource definition.
- For z/OS OPERATOR commands and Db2 system internal agents: = 'SYSOPR'.
- For a distributed application server (AS):
- If the application requester (AR) is a Db2 system, this is the same value that was assigned at the AR.
- If the application requester is not a Db2 system, this is the user ID that was used to make the initial connection with the application server.
- Passed by the requester in the allocate conversation flow (FMH5).
- Passed by the requester in the DRDA security flow.
- Derived by the server from the RACF passticket.
- Provided by the requester in the DRDA security flow.
Change Requests The change request count. (Field name: QTXACHG).
Claim Requests The number of claim requests. (Field name: QTXACLNO).
Claims Failed The number of unsuccessful claim requests. (Field name: QTXACLUN).
- 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 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'.
- 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. (Field name: GROUP).
DB2 Subsystem The Db2 subsystem ID. (Field name: DB2ID).
DB2 Version The Db2 version. (Field name: DB2VER).
Deadlocks Detected The number of times deadlocks were detected. This number should be low, ideally 0. (Field name: QTXADEA). 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: QTXADRNO).
Drains Failed The number of unsuccessful drain requests. (Field name: QTXADRUN).
DSG Member Name The data sharing group member name. (Field name: MEMBER).
Escalations to Exclusive The count of lock escalations to exclusive mode. (Field name: QTXALEX). 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 prompted from a page or row lock (X) 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.
Escalations to Shared The count of lock escalations to shared mode. (Field name: QTXALES). 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 prompted 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.
Filler The pad field. (Field name: FILLER01).
Interval Start The start time of this interval. (Field name: TIMESTAMP).
Lock Requests The lock request count. (Field name: QTXALOCK).
- A fully qualified network name (QWHSLUNM)
- The fully qualified network name is a maximum 16-bytes in length, consisting of two 8-character fields, QWHSNID and QWHSLUNM, that together uniquely identify a client system. The LUWID remains the same on IMS, CICS, and RRSAF thread reuse. Second part of network name (see QWHSLWID).
- An LUW instance number (QWHSLUUV)
- The instance number is displayed as 12 hex characters. When concatenated with the fully
qualified network name, it uniquely identifies a distributed thread.Note: Though this field might appear to be a timestamp, it is not to be processed as one. For more information. See the -DISPLAY THREAD command in Command Reference.
- An LUW sequence number
- The LUW sequence number identifies the last COMMIT scope in which the logical unit participated. This number is incremented whenever a thread commits or is rolled back.
Maximum Page/Row Locks The maximum number of page or row locks held. (Field name: QTXANPL). 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. (Field name: MVSID).
Originating System The managed system name of the agent. Valid format is alphanumeric, with a maximum of 32 characters; example, DB91:SYS1:DB2. (Field name: ORIGINNODE).
Other IRLM Requests The other IRLM request count. (Field name: QTXAIRLM).
- 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: The application 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 being 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: QTXAQRY).
RECORD STCK The store clock value of header. For data sharing, this is the sysplex timer value. (Field name: NTHSTCK).
Suspends - Latch Only The suspend count because of IRLM latch conflict. (Field name: QTXASLAT).
Suspends - Lock Only The number of suspends because of lock conflict. (Field name: QTXASLOC). 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.
- 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
Thread End Time Ending store clock value. (Field name: NTHETIME). You can use this field with the field NTHSTIME (beginning store clock value) to determine the elapsed time of an application. See comments on NTHSTIME for more information.
Thread Start Time Beginning store clock value (STCK) for the period covered by this accounting record. (Field name: NTHSTIME). You can determine the elapsed time of the application by subtracting this field from QWACESC (ending store clock value). Threads that do not terminate (such as CICS primed threads and IMS wait-for-input message regions) can have an ending clock value that includes the time the thread was inactive and waiting to perform work. (Field name: NTHSTIME).
Thread Type The connecting system type code (in hexadecimal). This field can have a null value. (Field name: NTHATYPE). For example, this field contains a null value for some utilities.
Timeouts Detected 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: QTXATIM).
Unlock Requests The unlock request count. (Field name: QTXAUNLK).