Thread History Dynamic SQL Calls

This panel provides information about dynamic SQL calls that were issued by a completed thread.

You must specify the keyword DYNAMICSQL(YES) if thread data is collected; otherwise the panel displays a message that dynamic SQL information is not available for the thread.

 
________________ ZHTCALL  VTM     O2       V540.#P SE12 11/06/13 13:53:17   2  
> Help PF1               Back PF3               Up PF7                 Down PF8 
                                                                                
>          THREAD HISTORY:  Enter a selection letter on the top line.           
                                                                                
> A-THREAD DETAIL   B-LOCK COUNTS  C-LOCK WAITS     D-GLOBAL LOCKS  E-SORT/SCAN 
> *-DYNAMIC SQL     G-SQL COUNTS   H-DISTRIBUTED    I-BUFFER POOL   J-GROUP BP  
> K-PACKAGE SUMMARY L-RES LIMIT    M-PARALLEL TASKS N-SQL PA                    
=============================================================================== 
>                      THREAD HISTORY DYNAMIC SQL CALLS                         
 HPLN                                                                           
+ Thread:  Plan=DSNESPCS  Connid=TSO      Corrid=MIS          Authid=MIS        
+ Attach:  TSO            DB2=SE12        MVS=PMO4                              
+ Time  :  Start=11/06/2013 13:30:02.142363     End=11/06/2013 13:31:39.784388  
+ Luwid=DEIBMIPS.IPSATE12.CC39197D8375                                          
 call                                                                           
+                                                                               
: Select Call=NEXT      (FIRST/LAST/NEXT/PREV/+nnnnn/-nnnnn/Snnnnn)             
+                                                                               
+                              SQL Statement      (      41 of      72)         
+                                                                               
+ UPDATE WEATHER2 SET CITY = 'DELETED'                                          
+                                                                               
+                              Miniplan Generated                               
+                                                                               
+ Plan #  1    (Estimated Cost:      118 )                                      
+                                                                               
+ Table: WEATHER2               Access Type: SEQUENTIAL SCAN                    
+ Prefetch Activity: SEQUENTIAL                                                 
===============================================================================

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.

Miniplan:

Select Call

Specifies the next SQL statement that is displayed. Initially this panel displays the first SQL statement that was collected for the thread. Use these keywords to control the display:

FIRST: First SQL statement encountered for the thread.
LAST: Last SQL statement encountered for the thread.
NEXT: Next SQL statement encountered for the thread.
PREV: Previous statement encountered for the thread.
+nnnnn: The nnnnn (1-99999) entry after the currently displayed SQL statement.
-nnnnn: The nnnnn (1-99999) entry before the currently displayed SQL statement.
Snnnnn: Statement number nnnnn.

Plan #

The plan for a select block within the prepared SQL statement.

Estimated Cost

The cost factor for this SQL statement, generated by the Db2 Optimizer.

Table

The name of the table that was accessed.

Access Type

The method by which the table was accessed. Possible values are:

Index: Index was used to access table data.
Index (One-Fetch): Index was used to determine which data page is needed for processing. This type of access is used for processing MIN and MAX functions.
Index (IN keyword): Index was used to access table data for processing the IN keyword in SQL statements.
Index (Page Range): Index was used to access table data in a particular page range.
Sequential Scan (Page Range): All pages within a particular page range of the partitioned tablespace were accessed sequentially.
Sequential Scan: All pages in the tablespace (or table if the tablespace is segmented) were accessed sequentially.

Join Method

The type of join being performed. Possible values:

Nested Loops
Hybrid
Merge Scan

Table Type

Indicates whether the table was the INNER or OUTER table for the join processing.

Prefetch Activity

The type of prefetch activity performed. If no prefetch was performed, this field is not displayed. Possible values are:

Sequential: Sequential Prefetch.
List: List Prefetch for one or more indexes.

Sort Activity

The reason for the sort. If no sort was performed, this field is not displayed. Possible values are:

Uniq: Sort to remove duplicate rows.
Join: Sort needed for join processing.
Order: Sort needed to satisfy Order By clause.
Group: Sort needed to satisfy Group By clause.
Uniq(C): Sort to remove duplicate rows (composite table).
Join(C): Sort needed for join processing (composite table).
Order(C): Sort needed to satisfy Order By clause (composite table).
Group(C): Sort needed to satisfy Group By clause (composite table).

Access Degree

The degree of parallelism used by the query. This is the number of parallel I/O streams determined by the optimizer at PREPARE time. The actual number of I/O streams used at execution time can be different.

Access Group ID

The parallel group identifier used for accessing the new table. This is the identifier for a group of consecutive parallel operations. These parallel operations have the same number of I/O streams. The value is determined at PREPARE time and can be changed at execution time.

Access Type

The type of parallel processing to be used. Possible values are:

CPU: CPU parallelism
I/O: I/O parallelism

Join Degree

The degree of parallelism used in joining the composite table with the new table. This is the number of parallel I/O streams used for the join. The value is set at PREPARE time and can change at execution time.

Join Group ID

The value that is used to identify the parallel group when Db2 joins the composite table with the new table. This is determined at PREPARE time and could be different at execution time.