EDM Snapshot Dynamic SQL Cache Statement Statistics Detail
This panel provides additional details about a particular SQL statement.
________________ ZEDD3 VTM O2 V550./C SE11 07/30/13 1:59:11 2
> Help PF1 Back PF3 Up PF7 Down PF8
> A-SQL PA
===============================================================================
> Dynamic SQL Cache Statement Statistics Detail
> statistics require that monitor class 1 and ifcid 318 be started
EDD3
+ Authorization Id: HONG
+
+ Insert Date 07/29/2013 Insert Time 23:16:52.6634
+ Update Date 07/29/2013 Update Time 23:16:52.8470
+ Collection Began Date 07/29/2013 Collection Began Time 16:22:43.7640
+
+ Statement Token 0000177A Times Executed 1
+ Getpages 5 Rows Examined 0
+ Rows Processed 1 Sorts Performed 0
+ Index Scans 1 Tablespace Scans 0
+ Synchronous Buffer Reads 4 Synchronous Writes 0
+ Number of Current Users 1 Copies of Statement 1
+ Parallel Groups Created 0 AVG Actual Degree 0
+ AVG Estimated Degree 0 AVG Planned Degree 0
+
+ #RIDs Not Used/Storage 0 #RIDs Not Used/Limit 0
+ #RIDs Overflow/Storage 0 #RIDs Overflow/Limit 0
+ #RIDs HB Join/Storage 0 #RIDs HB Join/Limit 0
+ #RIDs No IX Access 0
+
+ Elapsed Time 00:00:00.140 CPU Time 00:00:00.000
+
+ Wait for Synch I/O 00:00:00.017 Wait for Lock/Latch 00:00:00.000
+ Synch Exec Switch 00:00:00.000 Wait for Log Writer 00:00:00.000
+ Wait Othr Thread Read 00:00:00.000 Wait Othr Thread Write 00:00:00.000
+ Wait for Latch Req 00:00:00.000 Wait Time Page Latch 00:00:00.000
+ Wait for Drain Lock 00:00:00.000 Wait for Drain/Claim 00:00:00.000
+ Wait Glb L-Locks 00:00:00.000 Wait Glb P/P P-Locks 00:00:00.000
+ Wait Glb Child L-Locks 00:00:00.000 Wait Glb Page P-Locks 00:00:00.000
+ Wait Glb Other L-Locks 00:00:00.000 Wait Glb other P-Locks 00:00:00.000
+ Wait for PIPE lock 00:00:00.000 Wait for PQS lock 00:00:00.000
+
+ Isolation Bind RR Currentdata Bind N
+ Dynamic rules Bind R Current Degree 1
+ Current Rules D Current Precision N
+ Cursor Hold N Concentrate Statement N
+ Status of Statement UNKNOWN Expansion Reason
+ ACCEL Eligible NO
+ Stabilized Stmt ID 4 Hash ID Version 1
+ Query Hash ID 5B263645202428130208737318120876
+
+ DSG Member SC21
+
+ Stabilization Grp
+ HONGTST2_BUT_WITH_LONGNM
+
+ Transaction Name
+ HONG2
+
+ Workstation Name
+ TSO
+
+ End User ID
+
+ Program Name
+ SYSLH200
+
+ Prepare ID
+ HONG
+ User Group
+ HONG
+
+ Object Qualifier
+ HONG
+
+ Ref Tabl Qualifier
+ HONG
+
+ Ref Table
+ HONGLTBN_WHICH_IS_MORE_THAN_80_CHARACTERS_LONG_SO_THEY_WILL_BE_DISPLAYE
+
+ Appl Data
+
+ Schema
+ HONG
+
+ SELECT count_big(*) withAcctInfo from HONG.HONGLTBN_WHICH_IS_MORE_THAN_80_
+ CHARACTERS_LONG_SO_THEY_WILL_BE_DISPLAYE
=============================================================================== Navigation
For additional information about other topics, use the PF keys.
Fields
Each field reflects the data that was available when collection was executed. OMEGAMON® for Db2 PE collects data and refreshes the panel each time you navigate to the panel.
- Insert Date/Time
- The date and the time when the statement is inserted into the cache.
- Update Date/Time
- The date and the time when the statement is updated in the cache.
- Collection Began Date/Time
- The date and the time when the dynamic SQL cache statistics collection begins.
- Statement Token
- Unique statement identifier generated for uniquely identifying a statement in the prepared statement cache.
- Times Executed
- The number of times the SQL statement has been executed. For a cursor statement, this number represents the number of OPENs.
- Getpages
- The number of Getpage requests performed for the SQL statement.
- Rows Examined
- The number of rows examined for the SQL statement.
- Rows Processed
- The number of rows processed for the SQL statement. For example, the number of rows returned for a SELECT, or the number of rows affected by an INSERT, UPDATE, or DELETE.
- Sorts Performed
- The number of sorts performed for the SQL statement.
- Index Scans
- The number of index scans performed for the SQL statement.
- Tablespace Scans
- The number of tablespace scans performed for the SQL statement.
- Synchronous Buffer Reads
- The number of synchronous Buffer Read operations performed for the SQL statement.
- Synchronous Writes
- The number of synchronous Buffer Write operations performed for the SQL statement.
- Number of Current® Users
- The number of current users of the statement. Current users have prepared or executed the statement during their current unit of work.
- Copies of Statement
- The number of copies of the statement that is owned by all threads in the system.
- Parallel Groups Created
- The number of parallel groups created for the SQL statement.
- AVG Actual Degree
- The average parallel group actual degree. It is obtained at execution time after considering the buffer pool negotiation and the system negotiation.
- AVG Estimated Degree
- The average parallel group estimated degree. This is the bind time estimated parallel group degree that is based on the cost formula. If the parallel group contains a host variable or a parameter marker, the bind time estimates the parallel group degree based on a valid assumption value.
- AVG Planned Degree
- The average parallel group plan degree. This is the ideal parallel group degree that is obtained at execution time after the host variable or the parameter marker value is plug-in and before buffer pool negotiation and system negotiation are performed.
- # RIDs Not Used/Storage
- The number of times that a RID list is not used because enough storage is not available to hold the list of RIDs.
- # RIDs not Used/Limit
- The number of times that a RID list is not used because the number of RIDs exceeded one or more internal Db2 limits.
- # RIDs Overflow/Storage
- The number of times a RID list is overflowed to a work file because a RIDPOOL storage is not available to hold the list of the RIDs.
- # RIDs Overflow/Limit
- The number of times a RID list is overflowed to a work file because the number of RIDs exceeded one or more internal limits.
- # RIDs HB Join/Storage
- The number of times a RID list append for a Hybrid Join is interrupted because a RIDPOOL storage is not available to hold the list of RIDs, for example, the number of times Db2 interrupts the RID phase and switches to the Data phase.
- # RIDs HB Join/Limit
- The number of times a RID list append for a Hybrid Join is interrupted because the number of RIDs exceeded one or more internal limits, for example, the number of times Db2 interrups the RID phase and switches to the Data phase.
- RIDs No IX Access
- The number of times a RID list retrieval for multiple index access is skipped because Db2 can predetermine the outcome of index ANDing or ORing.
- Elapsed Time
- The accumulated elapsed time used for the SQL statement.
- CPU Time
- The accumulated CPU time for the SQL statement. This includes CPU that is consumed on an IBM® specialty engine.
The following wait time fields are only collected when Accounting trace class 3 is active.
- Wait for Synch I/O
- The accumulated wait time for synchronous I/O operations.
- Wait for Lock/Latch
- The accumulated wait time for lock and latch requests.
- Synch Exec Switch
- The accumulated wait time for synchronous execution unit switch.
- Wait for Log Writer
- The accumulated wait time for log writers.
- Wait Othr Thread Read
- The accumulated wait time for read activity performed by another thread.
- Wait Othr Thread Write
- The accumulated wait time for write activity performed by another thread.
- Wait for Latch Req
- The accumulated wait time for Latch requests.
- Wait Time Page Latch
- The accumulated wait time for Page latches.
- Wait for Drain Lock
- The accumulated wait time for Drain locks.
- Wait for Drain/Claim
- The accumulated wait time for Drain locks that are waiting for claims to be released.
- Wait for Glb L-Locks
- The accumulated wait time for global locks.
- Wait Glb P/P P-Locks
- Accumulated wait for global P/P P-Locks for the statement. Db2 12 or later.
- Wait Glb Child L-Locks
- Accumulated wait for global child L-Locks for the statement. Db2 12 or later.
- Wait Glb Page P-Locks
- Accumulated wait for global page P-Locks for the statement. Db2 12 or later.
- Wait Glb Other L-Locks
- Accumulated wait for global other L-Locks for the statement. Db2 12 or later.
- Wait Glb Other P-Locks
- Accumulated wait for global other P-Locks for the statement. Db2 12 or later.
- Wait for PIPE lock
- Accumulated wait for pipe wait. Db2 12 or later.
- Wait for PQS lock
- Accumulated time waiting for parallel queries to synchronize between parent and child tasks. Db2 12 or later.
- Isolation Bind
- ISOLATION BIND is in effect on initial PREPARE; it does not reflect
ISOLATION specified in the WITH clause
- UR: ISOLATION(UR)
- Uncommitted Read
- CS: ISOLATION(CS)
- Cursor stability
- RS: ISOLATION(RS)
- Read stability
- RR: ISOLATION(RR)
- Repeatable read
- Currentdata Bind
- The status of the CURRENTDATA BIND option:
- CURRENTDATA(YES)
- CURRENTDATA(NO)
- Dynamic rules Bind
- The status of the DYNAMICRULES BIND option:
- DYNAMICRULES(BIND)
- DYNAMICRULES(RUN)
- Current Degree
- The status of CURRENT DEGREE special register value:
- CURRENT DEGREE = 'ANY'
- CURRENT DEGREE = '1'
- Current Rules
- The status of CURRENT RULES special register value:
- CURRENT RULES = 'DB2'
- CURRENT RULES = 'SQL'
- Current Precision
- The status of CURRENT PRECISION special register value:
- CURRENT PRECISION = 'DEC31'
- CURRENT PRECISION = 'DEC15'
- Cursor Hold
- If YES, the statement was prepared for a held cursor. If NO, the statement was not prepared for a held cursor.
- Concentrate Statement
- An indicator of the cache literal replacement.
- N
- No literal replacement was done.
- R
- Literals were replaced in the statement.
- D
- Same as R, but the cached statement is a duplicate cache entry instance, because a cache match failed because of literal reusability criteria.
- Status of statement
- The status of the statement. The statement has actually been removed from the cache, but current
users might still have an active copy.
- BYDROP
- The statement was invalidated by DROP or ALTER.
- BYREVOKE
- The statement was invalidated by REVOKE.
- BYLRU
- The statement was invalidated by LRU.
- Expansion Reason
- The reason that caused the expansion. The query contains an implicit query transformation that
is caused by one of the following items:
A- The SYSIBMADM.GET_ARCHIVE built-in global variable.
B- The current temporal BUSINESS_TIME special register.
S- The current temporal SYSTEM_TIME special register.
SB- The current temporal BUSINESS_TIME special register and the current temporal SYSTEM_TIME special register.
' '- The query does not contain an implicit query transformation that is caused by the current temporal BUSINESS_TIME special register, the current temporal SYSTEM_TIME special register, or the SYSIBMADM.GET_ARCHIVE built-in global variable.
N/A- No data available.
- ACCEL Eligible
- Statement is eligible to run on an accelerator.
- Stabilized StmtID
- Stabilized dynamic query statement ID. Db2 12 and higher. (QW0316_SDQ_STMTID)
- Query Hash ID
- Hash ID of a stabilized dynamic query. Db2 12 and higher. (QW0316_QUERY_HASH_ID)
- Hash ID Version
- Version of stabilized dynamic query's hash ID. Db2 12 and higher. (QW0316_QUERY_HASH_VER)
- Program Name
- The name of the program. The value is provided on RRS sign-on or resign-on.
- Transaction Name
- The name of the transaction. The value is provided on RRS sign-on or resign-on.
- Stabilization Grp
- Group ID of a stabilized dynamic query. Db2 12 or higher. (Field name: QW0316_STBLGRP)
- Workstation Name
- The name of the workstation. The value is provided on RRS sign-on or resign-on.
- End User ID
- The identification (ID) of the end user. The value is provided on RRS sign-on or resign-on.
- Prepare ID
- The primary authorization ID of the user who did the initial PREPARE.
- User Group
- The current SQLID of the user who did the initial PREPARE.
- Object Qualifier
- The object qualifier for unqualified table names.
- Ref Table Qualifier
- The table qualifier.
- Ref Table
- The name of the table.
- DSG Member
- The data sharing Db2 member that cached the SQL statement.
- Appl Data
- An identification string that is provided by the user.
- Schema
- The special register of the current schema.
- SQL Statement
- The text of the SQL statement.