Thread History Detail

This panel provides detail information about the activity of a completed thread or parallel task.

A parallel task is a subtask that is created from an originating thread to process a part of a query.

 
________________ ZHTACT   VTM     O2       V540.#P DC11 06/30/16 21:37:03   2  
> Help PF1               Back PF3               Up PF7                 Down PF8 
                                                                                
>          THREAD HISTORY:  Enter a selection letter on the top line.           
                                                                                
> *-THREAD DETAIL   B-LOCK COUNTS  C-LOCK WAITS     D-GLOBAL LOCKS  E-SORT/SCAN 
> F-DYNAMIC SQL     G-SQL COUNTS   H-DISTRIBUTED    I-BUFFER POOL   J-GROUP BP  
> K-PACKAGE SUMMARY L-RES LIMIT    M-PARALLEL TASKS                             
=============================================================================== 
>                            THREAD HISTORY DETAIL                              
 HPLN                                                                           
+ Thread:  Plan=DSNTEP12  Connid=BATCH    Corrid=HONGDGTB     Authid=HONG       
+ Attach:  BATCH          DB2=DC11        MVS=PMO1                              
+ Time  :  Start=06/30/2016 21:33:10.830412     End=06/30/2016 21:33:39.000595  
+ Luwid=DEIBMIPS.IPSAQC11.D0F943973839                                          
 act                                                                            
+ Termination Status    =         DEALLOC     Commits              =          1 
+ Total Elapsed Time    =    00:00:28.170     Aborts               =          0 
+ Total CP CPU Time     =    00:00:00.755                                       
+ ZIIP CPU Time         =    00:00:00.000                                       
+ Total Stored Proc CPU =    00:00:00.000                                       
+ Stored Proc Wait      =    00:00:00.000     Stored Proc Wait Cnt =          0 
+ Workfile Max Blocks   =           96768                                       
+ Workfile Curr Blocks  =            4096                                       
+                                                                               
+ In-DB2 Times                                    Total                         
+ ------------------------------                  ------------                  
+ Elapsed Time                                    00:00:28.152                  
+ CP CPU Time                                     00:00:00.753                  
+ IIP CPU Time                                    00:00:00.000                  
+ Stored Procedure CPU Time                       00:00:00.000                  
+ STP CP CPU Time                                 00:00:00.000            
+ STP IIP CPU Time                                00:00:00.000            
+ STP Elapsed Time Main                           00:00:00.000            
+ UDF CP CPU Time                                 00:00:00.000            
+ UDF IIP CPU Time                                00:00:00.000            
+ UDF Elapsed Time Main                           00:00:00.000            
+                                                                         
+ Waits                                  Count    Total                   
+ --------------------------------- ----------    ------------            
+ Synchronous I/O Wait                    3415    00:00:21.330            
+ Asynchronous Read I/O Wait               308    00:00:00.819            
+ Asynchronous Write I/O Wait              229    00:00:00.206            
+ Local Lock/Latch Wait                      1    00:00:00.002            
+ Page Latch Wait                         1852    00:00:00.663            
+ Drain Lock Wait                            0    00:00:00.000            
+ Drain of Claims Wait                       0    00:00:00.000            
+ Archive Log Mode(Quiesce) Wait             0    00:00:00.000            
+ Archive Read from Tape Wait                0    00:00:02.957            
+ Switch to Open/Close Wait                  0    00:00:00.000            
+ Switch to SYSLGRNG Service Wait            0    00:00:00.000            
+ Switch to DMS Waits                        0    00:00:00.000            
+ Other Service Waits                        4    00:00:00.015            
+ Force at Commit Waits                      0    00:00:00.000            
+ Log Write I/O Wait                         1    00:00:00.002            
+ Sync EX Unit Sw-com/abort/dealloc          1    00:00:00.001            
+ LOB Materialization                        0    00:00:00.000            
+ Latch Contention Wait                    479    00:00:00.062            
+ Autonomous Proc Wait                       0    00:00:00.000            
+ Parallel Query Sync Wait                   0    00:00:00.000            
+ Asynch IXLCACHE/IXLFCOMP                   0    00:00:00.000            
+ Fast Insert Pipe Wait                      0    00:00:00.000                  
+ Accelerator Wait                           0    00:00:00.000                  
+                                   ----------    ------------                  
+ Total Class 3 Wait Time                 6290    00:00:26.062                  
===============================================================================

Navigation

For additional information about

related topics, choose on of the options at the top of the panel.
other topics, use the PF keys.

Fields

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).

Attachment identifier:

Connection Type

The connection type is displayed in the first field:

APPLDIR: Application directed access (database access threads)
BATCH: Other Db2 batch threads
CALLATCH: Db2 call attach
CICS®: CICS attach
DLI: DL/I batch
IMSBMP: IMS attach BMP
IMSMPP: IMS attach MPP
IMSCTRL: IMS control region
IMSBMPB: IMS transaction BMP
RRSAF: Recoverable Resource Manager Services Attachment Facility
TSO: TSO foreground and background
Utility: Utility thread

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.

Time identifier:

Start: The start date and time of this thread execution.
End: The end date and time of this thread execution.

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=token

The 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.

Data fields:

Termination Status

The status that indicates the reason for the thread termination:

READS
RESIGNON
NEWUSER
DDFINACT
DEALLOC
COMMIT
EOT/TERM
EOT/ABND
EOM/ABND
RES/INDT
FORCE
IEOT/TRM
IEOT/ABN
IEOM/ABN
IFORCE
STALE
LIMIT
STORAGE
INACT

Commits

The number of Commits performed by the thread.

Total Elapsed Time

The total elapsed time for the thread, in the format hh:mm:ss.th.

Aborts

The number of aborts performed by the thread.

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.

Parallel Tasks

The number of parallel tasks that are created on behalf of this thread. Any activity that is performed on behalf of this thread by parallel tasks is not included in this thread. For a parallel task rollup record, this value is the number of child threads that are included in this record.

OR

Accounting Intervals

For a DDF or RRSAF rollup record, this value is the number of accounting intervals that are included in the record for the corresponding end user.

OR

Autonomous Procs

For an autonomous procedure rollup record, this value is the number of autonomous procedures that are included in this record.

This applies to DB2 11 and higher.

ZIIP CPU Time

The accumulated CPU time that is consumed while running on an IBM ZIIP in all environments.

Parallel Agents

For all non-rollup records, this value is always 0.

For a parallel query rollup record, this is the number of parallel child agents rolled into this record.

For an autonomous procedure rollup record, this value is always 0.

For a DDF/RRSAF rollup record, this value is the number of parallel query child agents that rolled into this record. These agents are not counted in QWACPCNT.

This applies to DB2 10 and higher.

Autonomous Count

For non-rollup records, this is the number of autonomous procedures that are completed.

For parallel query rollup records, this value is always 0.

For autonomous procedure rollup records, this value is always 0.

For DDF/RRSAF rollup recrods, this is the number of autonomous procedures that are completed. These procedures are not counted in QWACPCNT.

This applies to DB2 11 and higher.

IIP CPU Time

The total amount of zIIP in-DB2 CPU time that is accumulated for the thread.

Total Stored Proc CPU

The total CPU time (TCB time) spend processing SQL CALL statements in the Db2 stored procedures address space.

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 is not active, N/A is displayed.

Elapsed Time: The elapsed time spent in DB2 for the thread, in the format hh:mm:ss.th.
CP CPU Time: The In-DB2 CPU time accumulated for the thread. This includes only TCB time. SRB time is not included.
Stored Procedure CPU Time: The CPU (TCB) time spent in DB2 processing SQL CALL statements issued from stored procedures for this thread.
UDF CP CPU Time: The accumulated In-DB2 central processor CPU time consumed executing user defined functions (UDFs) on the main application execution unit. This time does not include SE CPU time.
Because these UDFs run entirely within Db2, this time represents class 1 and class 2 time.
UDF IIP CPU Time: The accumulated In-DB2 zIIP CPU time consumed executing user defined functions (UDFs) on the main application execution unit.
Because these UDFs run entirely within Db2, this time represents class 1 and class 2 time.
UDF Elapsed Time Main: The accumulated In-DB2 elapsed time consumed executing user-defined functions (UDFs) on the main application execution unit.
Because these UDFs run entirely within Db2, this time represents class 1 and class 2 time.

In-DB2 waits: Wait times require an Accounting trace class 2. If this Db2 trace is not active, N/A is displayed. For each of the following fields two values are provided:

Time displays the total wait time that has elapsed
Count displays the total number of waits.

Synchronous I/O Wait: Wait for synchronous I/O reads or writes.
Asynchronous Read I/O Wait: Wait for Read I/O performed under another thread (for example, list or Sequential Prefetch).
Asynchronous Write I/O Wait: Wait for Write I/O performed under another thread (for example, Deferred Writes).
Local Lock/Latch Wait: Wait for locks or latches.
Page Latch Wait: Wait for page latches.
Drain Lock Wait: Wait to acquire drain locks.
Drain of Claims Wait: Wait 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 reads of archive log from tape.
Switch to Open/Close Wait: Waits for switches to the OPEN/CLOSE service.
Switch to SYSLGRNG Service Wait: Waits for switches to the SYSLGRNG recording service.
Switch to DMS Waits: Waits for switches to the database managed space (DMS).
Other Service Waits: Waits for switches to other Db2 service tasks.
Force at Commit Waits: Waits 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 PROC 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.
LOB Compression Wait: The accumulated wait time for LOB compression. 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.