Thread Detail
This panel provides detailed information about the activity of a selected thread or parallel task.
A parallel task is created from an originating thread to process a part of a query. Thread activity is broken down into general status and resource consumption information, and buffer manager activity.
________________ ZTDTL VTM O2 V540.#P DC11 06/30/16 21:35:11 2
> Help PF1 Back PF3
> THREAD INFORMATION: Enter a selection letter on the top line.
> *-THREAD DETAIL B-LOCK COUNTS C-LOCK WAITS D-LOCKS OWNED E-GLOBAL LOCKS
> F-CURRENT SQL G-SQL COUNTS H-DISTRIBUTED I-BUFFER POOL J-GROUP BP
> K-PACKAGES L-RES LIMIT M-PARALLEL TASKS N-UTILITY O-OBJECTS
> P-CANCEL THREAD Q-DB2 CONSOLE R-DSN ACTIVITY S-APPL TRACE T-ENCLAVE
> U-LONG NAMES W-ACCEL ACTIVITY
===============================================================================
> THREAD DETAIL
PLAN
+ Thread: Plan=DSNTEP12 Connid=BATCH Corrid=HONGDGTS Authid=HONG
+ Attach: BATCH JOB Name=HONGDGTS JOB Asid= 53
+ Package: DSN@EP2L Collection=DSNTEP2
+ Luwid=DEIBMIPS.IPSAQC11.D0F944025684=15539
act
+ Thread Activity User Defined Functions
+ ------------------------------------- -------------------------------------
+ DB2 Status = WAIT-SYNC-IO TCB Time (SQL) = 00:00:00.000
+ MVS Status = WAIT-MISC Wait for TCB Time = 00:00:00.000
+ Total Elapsed Time = 00:00:08.818 Elapsed Time = 00:00:00.000
+ CP CPU Utilization = 05.8% Elapsed Time (SQL) = 00:00:00.000
+ Total CP CPU Time = 00:00:02.266 SQL Events = 0
+ IIP CPU Time = 00:00:00.000
+ Total Parallel Tasks = 0
+ Current Parallel Tasks= 0
+ Thread Status = T
+
+ In-DB2 Times Total Current
+ ------------------------------ ------------ ------------
+ Elapsed Time 00:00:00.007 00:00:00.000
+ CP CPU Time 00:00:00.005 N/A
+ IIP CPU Time 00:00:00.000 N/A
+ Stored Procedure CPU Time 00:00:00.000 00:00:00.000
+ STP CP CPU Time 00:00:00.000 N/A
+ STP IIP CPU Time 00:00:00.000 N/A
+ STP Elapsed Time Main 00:00:00.000 N/A
+ UDF CP CPU Time 00:00:00.000 N/A
+ UDF IIP CPU Time 00:00:00.000 N/A
+ UDF Elapsed Time Main 00:00:00.000 N/A
+
+ Stored Procedures Triggers
+ ------------------------------------- -------------------------------------
+ Total CPU = 00:00:00.000 TCB not in Enclave = 00:00:00.000
+ Elapsed time = 00:00:00.000 Elapsed not in Enclave = 00:00:00.000
+ Elapsed Time (SQL) = 00:00:00.000 TCB prior to Enclave = 00:00:00.000
+ Wait for TCB Time = 00:00:00.000
+ Wait Event Count = 0
+ Curr Wait TCB Time = 00:00:00.000
+
+ SavePoints
+ -------------------------------------
+ Savepoint Requests = 0
+ Release Savepoints = 0
+ Rollback Savepoints = 0
+ 
+ WorkFile
+ -------------------------------------
+ Workfile Max Blocks = 691200
+ Workfile Curr Blocks = 691200
+
+ Waits Count Total Current
+ --------------------------------- ---------- ------------ ------------
+ Synchronous I/O Wait 4 00:00:00.001 00:00:00.000
+ Asynchronous Read I/O Wait 5752 00:00:05.189 00:00:00.000
+ Asynchronous Write I/O Wait 423 00:00:00.474 00:00:00.000
+ Local Lock/Latch Wait 0 00:00:00.000 00:00:00.000
+ Page Latch Wait 0 00:00:00.000 00:00:00.000
+ Drain Lock Wait 0 00:00:00.000 00:00:00.000
+ Drain of Claims Wait 0 00:00:00.000 00:00:00.000
+ Archive Log Mode(Quiesce) Wait 0 00:00:00.000 00:00:00.000
+ Archive Read from Tape Wait 0 00:00:00.000 00:00:00.000
+ Switch to Open/Close Wait 0 00:00:00.000 00:00:00.000
+ Switch to SYSLGRNG Service Wait 0 00:00:00.000 00:00:00.000
+ Switch to DMS Waits 0 00:00:00.000 00:00:00.000
+ Other Service Waits 0 00:00:00.000 00:00:00.000
+ Force at Commit Waits 0 00:00:00.000 00:00:00.000
+ Log Write I/O Wait 0 00:00:00.000 00:00:00.000
+ Sync EX Unit Sw-com/abort/dealloc 0 00:00:00.000 00:00:00.000
+ LOB Materialization 0 00:00:00.000 00:00:00.000
+ Latch Contention Wait 461 00:00:00.040 00:00:00.000
+ Autonomous Proc Wait 0 00:00:00.000 00:00:00.000
+ Parallel Query Sync Wait 0 00:00:00.000 00:00:00.000
+ Asynch IXLCACHE/IXLFCOMP 0 00:00:00.000 00:00:00.000
+ Fast Insert Pipe Wait 0 00:00:00.000 00:00:00.000
+ Accelerator Wait 0 00:00:00.000 00:00:00.000
+ ------------------------------------------------------------
Total Class 3 Wait Time 6640 00:00:05.705
==============================================================================
Highlighting
The following table shows
the fields that might be highlighted in the panel above to indicate
that an exception that is related to this field exceeded its threshold
value:
| Field | Exception | Reason |
|---|---|---|
| Plan | ARCM | Backout requires an archive tape log mount. |
| ETIM | This thread has reached the Elapsed Time threshold value. | |
| GETP | The Getpage to Read I/O ratio indicates poor read efficiency. | |
| INDB | The thread is indoubt and terminated. | |
| PREF | The thread Sequential Prefetch rate is high. | |
| RIO | The synchronous Read I/O rate is high. | |
| PGUP | The rate for system page updates is high. | |
| Total Elapsed Time | ETIM | This thread reached the Elapsed Time threshold value. |
| CPU Utilization | TCPU | The CPU utilization associated with the thread is high. |
| Current® Drain Lock Wait | WDLK | The thread reached drain lock wait threshold. |
| Current Drain of Claims Wait | WCLM | The thread reached wait for drain of claims threshold. |
| Current Service Task Wait | WSRV | The thread reached DB2® service wait threshold. |
| Current Archive Log Mode (Quiesce) Wait | WLGQ | The thread reached Archive Log Mode (Quiesce) wait threshold. |
| Current Stored Procedure Schedule Wait | WSPS | The thread reached stored procedure schedule wait threshold. |
| Total elapsed time | IDBT | In-DB2 Times - Total elapsed time. |
| Total CP CPU wait | IDBC | In DB2-Times - Total CP CPU wait. |
| Current global parent L-lock wait | WGLK | The thread reached the global lock wait threshold. |
Navigation
You can scroll through the information using F7 and F8.
For additional
information about
- related topics, select one of the options on the top of the panel.
- other topics, use the PF keys.
Fields
If the accounting trace classes are not active, the fields show N/A (not applicable) instead of a value.
Thread identifier: This information
identifies the thread to which the information in this panel applies.
- Plan
- The DB2 plan name of the active thread.
- Connid
- The DB2 connection identifier of the active thread.
- Corrid
- The DB2 correlation identifier of the active thread. If the correlation is not set, N/A is displayed.
- Authid
- The DB2 authorization identifier of the active thread.
- Attach
- Depending on the type of connection, the appropriate information is displayed.
- Attach information is displayed only if the thread is a distributed allied thread (not for distributed database access threads).
- Connection Type
- Batch
- The MVS™ jobname and ASID.Note: For threads from remote DB2, the MVS job name is N/A.
- CICS®
- The CICS jobname, task
name, task number, terminal ID, and thread type. The thread type is:
- Pool
- The thread in use is a pool thread.
- Enty
- The thread in use is a nonprotected entry thread.
- Prot
- The thread in use is a protected thread. Protected threads are defined in an RCT entry definition using the THRDS operand.
- IMS™
- The IMS region number, transaction name, region name, and terminal ID (LTERM).
- RRSAF
- The MVS job name and ASID.Note: For threads from remote DB2, the MVS job name is N/A.
- System
- The originating DB2 job name and the resource manager that is the source of the thread. An additional line below the Attach line identifies the user thread, if any, being served by the system thread.
- TSO
- The TSO user ID and region ASID.
- Utility
- No additional information.
- DB2
- The DB2 subsystem identifier.
- MVS
- The MVS system identifier.
- ORIGAUTH
- The original (primary) DB2 authorization identifier of the thread. This field displays only when the original identifier is different from the Authid.
Package
identifier: This information identifies the package to which the
information in this panel applies.
Distributed
thread identifier: The following fields are displayed if the thread
has a distributed relationship with a remote DB2 subsystem.
- Type
- The distributed thread type.
- Distributed Allied
- A requesting thread; one that has issued an SQL call to a remote DB2 location.
- Database Access
- A responding thread; one that is serving a remote DB2 location by responding to an SQL call.
- DB2=
- The DB2 subsystem ID, indicating the member of the data sharing group of this thread.
- Luwid
- This value consists of two parts: the logical unit
of work ID (luw-id) and a token. The token can be used in place of
the luw-id in any DB2 command
that accepts luw-id as input. Format:
luw-id=tokenThe luw-id consists of the network name, the originating VTAM® LUNAME, and a unique identifier (separated by periods). Thus, the Luwid field displays data like in the following example:
USCACO01.O2D22A.A1FE8E04B9D4=8 - System
- The originating DB2 job name and the resource manager that is the source of the thread. An additional line below the Attach line identifies the user thread, if any, being served by the system thread.
Thread Activity:
- DB2 Status
- For definitions of all possible status values, see "DB2 Thread Status".
- MVS Status
- The current MVS status
of the thread:
- Asid Notdisp
- The address space in which the active task is not available for dispatch.
- DB2 I/O Wait
- The thread is waiting for the completion of DB2 database I/O.
- Logical Swap
- The address space is currently logically swapped out.
- Swapped Out
- The memory is physically swapped out.
- Using CPU
- The thread is currently using CPU cycles.
- Wait-Dispatch
- The task is waiting on the active CPU dispatching queue. It is available for use, but is not yet running on a processor.
- WAIT-MISC
- The thread is waiting for some other reason and is not on the
available dispatch queue. Note: If the panel displays a distributed database access thread, this field is blank.
- Total Elapsed Time
- The total elapsed time for the thread, in the format hh:mm:ss.tht. Elapsed time is the time which has elapsed since thread creation or DB2 signon.
- CP CPU Utilization
- The central processor CPU rate (percent) associated
with the thread.
For non-CICS threads, this is the central processor CPU rate of the address space from which the thread originates. For CICS threads, this is the central processor CPU rate attributable to the thread originating from the CICS connection. For more information about central processor CPU use, see "Analyzing DB2 CPU Usage".
- Total CP CPU Time
- The total central processor CPU time accumulated for the thread. This value includes only MVS TCB time. SRB time is not included.
- IIP CPU Time
- The accumulated CPU time consumed while executing on an IBM® ZIIP in all environments.
- Total Parallel Tasks
- The total number of parallel tasks that were created on behalf of this thread.
- Current Parallel Tasks
- The number of parallel tasks currently executing. To see information about these tasks, select option M-PARALLEL TASKS.
- Thread Status
- Valid values:
- AC
- Execute in accelarator
- AT
- Process autonomous procedure
- D
- Termination due to allied task's termination
- DA
- DBaccess thread slot is available for new connection
- DI
- Disconnected, no TCB associated with the DB2 thread. (Available for reuse.)
- N
- In IDENTIFY or SIGNON status
- ND
- IN IDENTIFY or SIGNON status with no TCB associated
- PT
- Parallel task thread
- QD
- Queued for termination due to allied task's termination
- QT
- CREATE THREAD request is queued
- R2
- Type2 inactive thread waiting for agent to become available
- RA
- Active distributed thread performing remote access
- RK
- Distributed thread invokes Kerberos services
- RN
- Distributed thread waits for system conversation service task
- RQ
- Distributed thread waits for dbaccess agent, due to MAXDBAT or DSN_PROFILE_ATTRIBUTES_TABLE
- RX
- Distributed thread executes XA transaction
- SP
- In stored procedure
- SW
- Wait for a stored procedure to be scheduled
- T
- Non-distributed allied thread
- TD
- An allied thread is not associated with TCB
- TN
- An allied thread is waiting for system conversation service task
- TR
- An allied thread is active
- N/A
- QW0148ST is not populated
User-Defined Functions:
- TCB Time (SQL)
- Accumulated TCB time consumed in DB2 processing SQL statements issued by user-defined functions.
- Wait for TCB Time
- Total elapsed time spent waiting for an available TCB before the user-defined function could be scheduled.
- Elapsed Time
- Total elapsed time spent in user-defined functions, including time executing SQL statements.
- Elapsed Time (SQL)
- Total elapsed time spent in user-defined functions executing SQL statements.
- SQL Events
- Number of SQL entry/exit events performed by user-defined functions.
Stored
Procedures:
- Total CPU
- The CPU time (TCB time) spent in DB2 processing SQL statements issued by stored procedures for this thread.
- Elapsed Time
- Total elapsed time spent in stored procedures including time executing SQL statements.
- Elapsed Time (SQL)
- Total elapsed time spent for stored procedures executing SQL statements.
- Wait for TCB Time
- The total time waiting for an available TCB to schedule a stored procedure.
- Wait Event Count
- The number of waits for an available TCB to schedule a stored procedure.
- Curr Wait TCB Time
- The current time waiting for an available TCB to schedule a stored procedure.
Triggers:
- TCB not in Enclave
- The accumulated TCB time consumed while executing triggers not under an enclave.
- Elapsed not in Enclave
- The accumulated elapsed time expanded executing triggers not under an enclave.
- TCB prior to Enclave
- The accumulated TCB time consumed before enclave creation time.
SavePoints:
- Savepoint Requests
- The number of savepoints set.
- Release Savepoints
- The number of savepoints deleted.
- Rollback Savepoints
- The number of rollback-to-savepoint requests issued.
WorkFile:- Workfile Max Blocks
- Maximum number of workfile blocks being used by this agent at any given point in time (traditional workfile use, DGTT and DGTT indexes). DB2 12 and later.
- Workfile Curr Blocks
- Current number of workfile blocks being used by this agent (traditional workfile use, DGTT and DGTT indexes). DB2 12 and later.
In-DB2 Times: In-DB2 times
require an Accounting trace class 2. If this trace was not active, N/A is
displayed.
- Elapsed Time (Total)
- The total In-DB2 elapsed time for the thread, in the format hh:mm:ss.tht.
- Elapsed Time (Current)
- The total time spent executing the currently active SQL statement.
- CP CPU Time (Total)
- The total In-DB2 central processor CPU time for the thread. This includes MVS TCB time only. SRB time is not included.
- CP CPU Time (Current)
- The total central processor CPU time used by the currently active SQL statement, in the format hh:mm:ss.tht. The value is always zero for database access threads.
- IIP CPU Time (Total)
- The total In-DB2 SE (Specialty Engine) CPU time for the thread.
- IIP CPU Time (Current)
- The total SE (Specialty Engine) CPU time used by the currently active SQL statement in seconds. The value is always zero for database access threads.
- Stored Procedure CPU Time (Total)
- The CPU time (TCB time) spent in DB2 processing SQL statements issued by DB2 stored procedures for this thread.
- Stored Procedure CPU Time (Current)
- The CPU time (TCB time) spent in DB2 processing SQL statements issued by DB2 stored procedure that is currently active.
- UDF CP CPU Time (Total)
- The CPU time spent in DB2 processing SQL statements issued by user defined functions for this thread. This time represents central processor time only and does not include SE (Specialty Engine) time.
- UDF IIP CPU Time (Total)
- The amount of SE (Specialty Engine) CPU time spent in DB2 processing SQL statements issued from user defined functions for this thread.
- UDF Elapsed Time Main (Total)
- The elapsed time by the user defined functions for this thread in the format hh:mm:ss.tht.
Waits (Class 3): Wait times require an
Accounting trace class 3. If this trace is not active, N/A is
displayed. For each field described below the following statistics
are provided:
- Count is the total number of waits.
- Total is the total wait time that has elapsed.
- Current is the elapsed time waiting for the current event to complete.
- Synchronous I/O Wait
- Waits for synchronous I/O reads or writes.
- Asynchronous Read I/O Wait
- Waits for Read I/O performed under another thread (for example, list or Sequential Prefetch).
- Asynchronous Write I/O Wait
- Waits for Write I/O performed under another thread (for example, deferred writes).
- Local Lock/Latch Wait
- Waits for locks or latches.
- Page Latch Wait
- Waits for page latch.
- Drain Lock Wait
- Waits to acquire drain lock.
- Drain of Claims Wait
- Waits for claims to be released after acquiring drain lock.
- Archive Log Mode (Quiesce) Wait
- Wait for ARCHIVE LOG MODE(QUIESCE) command to complete.
- Archive Read from Tape Wait
- Waits for read of archive log from tape.
- Switch to Open/Close Wait
- Wait for switches to the OPEN/CLOSE service.
- Switch to SYSLGRNG Service Wait
- Wait for switches to the SYSLGRNG recording service.
- Switch to DMS Waits
- Wait for switches to the database-managed space (DMS).
- Other Service Waits
- Wait for switches to other DB2 service tasks.
- Force at Commit Waits
- Wait for force-at-commit DB2 service tasks.
- Log Write I/O Wait
- Waits due to Log write I/O.
- Sync EX Unit Sw-com/abort/dealloc
- Waits due to synchronous execution switch for DB2 commit, abort, or deallocation processing.
- LOB Materialization
- Waits for TCP/IP LOB and XML materialization.
- Latch Contention Wait
- Waits due to page latch contention.
- Autonomous STP Wait (QWAC_AT_WAIT QWAC_AT_COUNT)
- The amount of waits that are due to autonomous procedures.
- This applies to DB2 11 and higher.
- Parallel Query Sync Wait (QPAC_PQS_WAIT QPAC_PQS_COUNT)
- The amount of waits after parallel query processing suspended waiting for parent/child to be synchronized.
- This applies to DB2 11 and higher.
- Asynch IXLCACHE/IXLFCOMP (QWAXIXLT)
- The accumulated wait time for IXLCACHE and IXLFCOMP requests.
- Fast Insert Pipe Wait
- The accumulated wait time for a pipe wait. DB2 12 and later.
- Accelerator Wait (QWACAACW QPACAACC)
- The accumulated wait time for requests to an accelerator. DB2 11 and later.
- Total Class 3 Wait Time
- The total class 3 wait time.
For Data Sharing Group members, the following waits are also displayed:- Global Child L-Locks (QWACAWTK QWACARNK)
- Waits because of global contention for child L-locks.
- Global Other L-Locks (QWACAWTM QWACARNM)
- Waits because of global contention for other L-locks.
- Global Pageset/Partition P-Locks (QWACAWTN QWACARNN)
- Waits because of global contention for Pageset/Partition P-locks.
- Global Page P-Locks (QWACAQTO QWACARNO)
- Waits because of global contention for Page P-locks.
- Global Other P-Locks (QWACAQTQ QWACARNQ)
- Waits because of global contention for other P-locks.