Application Trace SQL Detail
This panel shows information about the traced application at the SQL statement level. It also shows the text of all dynamic SQL calls.
Depending on the SQL data, the information that is displayed
in this panel varies.
________________ ZATD1 VTM O2 V540./P SN13 12/16/11 1:41:03 3
> Help PF1 Back PF3 Up PF7 Down PF8
>
> APPLICATION TRACE: Enter a selection letter on the top line.
> Current Trace Status: ACTIVE
> A-PROGRAM B-SQL INDEX *-SQL DETAIL D-LOCK DETAIL E-EVENT DETAIL
===============================================================================
> APPLICATION TRACE SQL DETAIL
ATD1
+ Planname=HV02SN13 Connid=BATCH Corrid=HONGH213 Authid=HONG
+
: Control= NEXT Valid options are FIRST/LAST/NEXT/PREV/nnnnn/-nnnnn/Snnnnn
+ Current=000004 Total Number of SQL Calls=000005
+
+ Start Time Progname SQL Call Stmt# Retcode InDB2 Time InDB2 CPU
+ ------------ -------- ---------------- ----- ------- ------------ ---------
+ 01:40:46.108 HV02SN13 SELECT 00085 0 00:00.00228 .00092
+
+ Data Rows Rows Rows Rows Rows Rows Rows Rows Pages Pages
+ Type Proces Looked Qual/DM Qual/RD Update Insert Delete De/Ref Scand Sc/Ref
+ ---- ------ ------ ------- ------- ------ ------ ------ ------ ------ ------
+ INDX 0 0 0 0 0 0 0 0 0 0
+ DATA 1478 1478 0 0 0 0 0 0 185 0
+
+
+ Static SQL Call Text :
+ -----------------------
+ SELECT COUNT ( * ) INTO : H FROM SYSIBM . SYSTABLESPACESTATS WHERE EXTEN
+ TS > : H AND NPAGES > : H AND DBNAME = : H AND COPYUPDATELRSN = : H AND
+ IBMREQD LIKE : H AND SPACE > : H
+
+ # of HOSTVARs collected = 6
+
+ Entry No : 1 Null_Ind: NO Name : N/A
+ Precision: N/A Scale : N/A SQL Type: 500
+ Data Len : 3 DataType: SMALL INTEGER
+ Data : 100
+
+ Entry No : 2 Null_Ind: NO Name : N/A
+ Precision: N/A Scale : N/A SQL Type: 496 + Data
: 200
+
+ Entry No : 3 Null_Ind: NO Name : N/A
+ Precision: N/A Scale : N/A SQL Type: 448
+ Data Len : 25 DataType: VARYING-LENGTH CHARACTER STRING
+ Data : THIS IS A VCHAR24 FI
+
+ Entry No : 4 Null_Ind: NO Name : N/A
+ Precision: N/A Scale : N/A SQL Type: 452
+ Data Len : 7 DataType: FIXED-LENGTH CHARACTER STRING
+ Data :*00C3C3C3C3C3C3
+
+ Entry No : 5 Null_Ind: NO Name : N/A
+ Precision: N/A Scale : N/A SQL Type: 452
+ Data Len : 3 DataType: FIXED-LENGTH CHARACTER STRING
+ Data :*000159
+
+ Entry No : 6 Null_Ind: NO Name : N/A
+ Precision: N/A Scale : N/A SQL Type: 492
+ Data Len : 9 DataType: BIG INTEGER
+ Data : 1234567890
===============================================================================
The data is refreshed each time you press Enter, which shows details for another SQL call.
When the detail pertains to a dynamic SQL call (SQL Call is PREPARE), the panel also shows the text of the call and access path information. You can view both dynamic and static SQL call text in the panel described in SQL Call Being Executed.
Fields
- Planname
- 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.
- Authid
- The Db2 authorization identifier of the active thread.
- Control
- The next SQL statement that OMEGAMON XE for DB2 PE displays. This panel initially displays
the first SQL statement 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 SQL 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.
- Current
- The relative number of the SQL statement currently being displayed. This is relative to the total number of calls located for the Db2 unit of work.
- Total Number of SQL Calls
- The total number of SQL calls located for the DB2 unit of work.
- Start Time
- The time the SQL statement was issued.
- Progname
- The name of program that issued the SQL statement.
- SQL Call
- The SQL statement type.
- Stmt#
- The SQL statement precompiler statement number.
- Retcode
- The SQL statement return code returned to the application in the SQLCA.
- InDB2 Time
- The elapsed wall clock time spent executing the SQL call.
- InDB2 CPU
- The CPU time used executing the SQL statement. For parallel task activity, this value represents the sum of the parent and child tasks.
- Data Type
- The type of statistics displayed:
- INDX
- Index pageset
- DATA
- Data pageset
- WORK
- Data workfile (DSNDB07) pageset
- Rows Proces
- The number of rows processed by the Data Manager for the statement. For parallel task activity, this value represents the sum of the parent and child tasks.
- Rows Looked
- The number of rows looked at/examined by the Data Manager for the statement. For parallel task activity, this value represents the sum of the parent and child tasks.
- Rows Qual/DM
- The number of rows qualified by the Data Manager for the statement (stage 1). For parallel task activity, this value represents the sum of the parent and child tasks.
- Rows Qual/RD
- The number of rows qualified by the Relational Data Manager for the statement (stage 2). For parallel task activity, this value represents the sum of the parent and child tasks.
- Rows Update
- The number of rows updated by the statement. For parallel task activity, this value represents the sum of the parent and child tasks.
- Rows Insert
- The number of rows inserted by the statement. For parallel task activity, this value represents the sum of the parent and child tasks.
- Rows Delete
- The number of rows deleted by the statement. For parallel task activity, this value represents the sum of the parent and child tasks.
- Rows De/Ref
- The number of rows deleted or set to null because of enforcement of defined referential integrity constraints for the statement. For parallel task activity, this value represents the sum of the parent and child tasks.
- Pages Scand
- The total number of pages scanned by the Data Manager for the statement. For parallel task activity, this value represents the sum of the parent and child tasks.
- Pages Sc/Ref
- The total number of pages scanned because of enforcement of defined referential integrity constraints for the statement. For parallel task activity, this value represents the sum of the parent and child tasks.
- Sync Reads
- The number of synchronous buffer reads (DB2 field name: QW0058SR).
- Get Pages
- The number of Getpage operations (Db2 field name: QW0058GP).
- Buffer Writes
- The number of buffer writes (Db2 field name: QW0058WT).
- Parall Groups
- The number of parallel groups created (DB2 field name: QW0058PG).
- RID Number
- The number of times RID list was not used because the number of RIDs would have exceeded Db2 limits (Db2 field name: QW0058RL).
- RID Store
- The number of times a RID list was not used because there is not enough storage available to hold the list of RIDs (DB2 field name: QW0058RS).
- Num sorts
- The number of sorts (Db2 field name: QW0058ST).
- Num IXScan
- The number of index scans (Db2 field name: QW0058IS).
- Num TSScan
- The number of table space scans (Db2 field name: QW0058TB).
- SyncWait
- The accumulated wait for synchronous I/O (DB2 field name: QW0058SI).
- LockWait
- The accumulated wait for locks (Db2 field name: QW0058LK).
- UnitWait
- The accumulated wait time for synchronous execution unit switches (Db2 field name: QW0058EU).
- ReadWait
- The accumulated wait time for read activity done by another thread (Db2 field name: QW0058OR).
- WriteWait
- The accumulated wait time for write activity done by another thread (Db2 field name: QW0058OW).
- GLockWait
- The accumulated wait time for global locks (DB2 field name: QW0058GL).
- LatchWait
- The accumulated wait time for latches (DB2 field name: QW0058LH).
- PgLatchWait
- Accumulated wait time for page latches (DB2 field name: QW0058PA).
- DrainLokWait
- Accumulated wait time for drain locks (DB2 field name: QW0058DA).
- ClaimWait
- Accumulated wait time for claim counts (DB2 field name: QW0058CL).
- LogWrtWait
- Accumulated wait time for log writers (DB2 field name: QW0058LG).
Static SQL call text or Dynamic SQL call text The complete text of the SQL statement is displayed in this area if it is a static or dynamic call. You can view both dynamic and static SQL call text in the panel described in SQL Call Being Executed.
Miniplan Generated
by Db2: Access path information
is displayed in this area if the SQL statement is a PREPARE for a
SELECT, UPDATE, INSERT, or DELETE statement. A plan for each select
block within the prepared SQL statement is provided.
- Estimated Cost
- The cost factor generated by the DB2 Optimizer for this SQL statement.
- Table
- The name of the table being accessed.
- Access Type
- The method in which the table is accessed. Possible
values:
- INDEX
- Index will be used to access table data.
- INDEX (ONE-FETCH)
- Index will be 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 will be used to access table data for processing the IN keyword in SQL statements.
- INDEX (PAGE RANGE)
- Index will be used to access table data in a particular page range.
- SEQUENTIAL SCAN
- All pages in the tablespace (or table, if the tablespace is segmented) will be accessed sequentially.
- SEQUENTIAL SCAN (PAGE RANGE)
- All pages within a particular page range of the partitioned tablespace will be accessed sequentially.
- Index
- The name of the index used. If more than one index is used, only the first index is displayed.
- Matching Cols
- The number of index keys used in the index scan.
- Join Method
- Type of join being performed. Possible values are NESTED LOOP, HYBRID, and MERGE SCAN.
- Table Type
- Indicates whether the table is the INNER or OUTER table for the join processing.
- Sort Activity
- The reason for the sort. If no sort is performed,
this field will not be displayed. Possible values:
- 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).
- Prefetch Activity
- The type of prefetch activity being performed. If
no prefetch is performed, this field will not be displayed. Possible
values:
- SEQUENTIAL
- Sequential prefetch.
- LIST
- List Prefetch for one or more indexes.
- 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 might be changed at execution time.
- Parallel Mode
- The type of parallel processing to be used. Possible values are
CPUfor CPU parallelism orI/Ofor 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 might change at execution time.
- Join Group ID
- The value 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.
Data type conversion is done internally. For certain uncommon data types, internal SQLTYPE(integer) is displayed.
The
following SQL data types of the host variable are supported:
- DATE
- TIME
- TIMESTAMP
- DATALINK
- NUL-TERMINATED GRAPHIC STRING
- BLOB
- CLOB
- DBCLOB
- VARYING-LENGTH CHARACTER STRING
- FIXED-LENGTH CHARACTER STRING
- LONG VARYING-LENGTH CHARACTER STRING
- NUL-TERMINATED CHARACTER STRING
- VARYING-LENGTH GRAPHIC STRING
- FIXED-LENGTH GRAPHIC STRING
- LONG VARYING-LENGTH GRAPHIC STRING
- FLOATING POINT
- PACKED DECIMAL
- BIG INTEGER
- LARGE INTEGER
- SMALL INTEGER
- VARYING-LENGTH BINARY STRING
- FIXED-LENGTH BINARY STRING
- BLOB_FILE
- CLOB_FILE
- DBCLOB_FILE
- BLOB LOCATOR
- CLOB LOCATOR
- DBCLOB LOCATOR
- XML
- DECFLOAT
- UNKNOWN