IFCID 316 - SQL Statement Statistics
This topic shows detailed information about "Record Trace - IFCID 316 - SQL Statement Statistics".
IFCID 316 reports on the contents of the prepared SQL statement cache. This record is only written when an IFI application requests IFCID 316 through the READS interface.
It provides one record for each qualifying SQL statement in the cache. These multiple records are placed in the output area provided by the IFI application. The IFI application can specify qualification criteria for which statements should be reported.
Record trace - IFCID 316 - SQL Statement Statistics
The field labels shown in the following sample layout of "Record Trace - IFCID 316 - SQL Statement Statistics" are described in the following section.
STATEMENT NAME : X'00650B786B4536444B3A2A490000003C' STATEMENT IDENTIFIER : 593
LITERAL REPLACEMENT : NO LINE NUMBER : 0
STATUS : INVALIDATED BY DROP OR ALTER ELIGIBLE FOR ACCELERATOR : YES
TIME STATISTICS COLLECTION START: 12/20/10 16:34:52.484040
TIME STATEMENT STORED IN CACHE : 12/22/10 11:36:42.874097 IN STORE CLOCK FORMAT : X'C710D9CFD18F1F1A'
TIME STATEMENT UPDATED IN CACHE : 12/22/10 11:36:42.874778 IN STORE CLOCK FORMAT : X'20101222113642874778'
STATEMENT COPIES : 0 STATEMENT EXECUTIONS : 1
SYNCH BUFFER READS : 0 SYNCH BUFFER WRITES : 0
CURRENT USERS : 0 GETPAGE OPERATIONS : 2
TABLESPACE SCANS : 0 PARALLEL GROUPS CREATED : 0
ROWS EXAMINED : 0 ROWS PROCESSED : 1
SORTS : 0 INDEX SCANS : 0
ACCUMULATED CPU TIME : 0.000210 ACCUMULATED ELAPSED TIME : 0.000218
STABILIZED STMT ID : 121 HASH-ID VERSION : 1
HASH-ID : X'7074620275690619425A2A4A5B2E2332'
STABILIZATION GROUP NAME : HONG1DEF
RID LIST SECTION
(HJA=HYBRID JOIN APPEND, IA=INDEX ACCESS, OV=OVERFLOW, RL=RID LIST)
RL NOT USED LIMIT EXCEEDED: 0 RL NOT USED NO STOR AVAIL : 0
RL OV - NO POOL STOR AVAIL: 0 RL OV - RIDS EXCEED LIMIT : 0
HJA - NO POOL STOR AVAI : 0 HJA - RIDS EXCEED LIMIT : 0
RL RETRIEVAL IA SKIPPED : 0
ACCUMULATED WAIT TIMES
READ BY OTHER THREAD : 0.005985 SYNCH EXECUTION UNIT SWITCH: N/P
WRITE BY OTHER THREAD : N/P SYNCHRONOUS I/O : 0.008773
GLOBAL LOCKS : N/P LOCK AND LATCH REQ : N/P
LATCH REQUEST : N/P PAGE LATCH : N/P
DRAIN LOCK : N/P DRAIN WAITING FOR CLAIM REL: N/P
LOG WRITER : N/P
WAIT CHILD L-LOCKS : 0.100000 WAIT OTHER L-LOCKS : 0.100000
WAIT P/P P-LOCKS : 0.100000 WAIT PAGE P-LOCKS : 0.100000
WAIT OTHER P-LOCKS : 0.100000 WAIT PIPE : N/A
WAIT PARALLEL QUERIES : N/A
CURRENT DATA BIND OPTION : NO CURSOR WITH HOLD : NO
CURRENT PRECISION SPEC REG: DEC15 DYNAMIC RULES BIND OPTION : RUN
CURRENT DEGREE SPECIAL REG: 1 ISOLATION BIND OPTION : CURSOR STABILITY
CURRENT RULES SPECIAL REG : DB2 DSG MEMBER : 'BLANK'
TRANSACTION NAME : db2jcc_application
SIGNON USER ID : skadm
WORKSTATION ID : candlelight
PROGRAM NAME : SYSLH200
USER ID : SKADM
USER GROUP : SKADM
OBJECT QUALIFIER : SKADM
REFERENCED TABLE QUALIFIER: SKADM
REFERENCED TABLE NAME : LE105
USER PROVIDED ID STRING : N/P
CURRENT SCHEMA : SKADM
LENGTH OF SQL STATEMENT : 60
SQL STATEMENT - FIRST 60 BYTES : INSERT INTO LE105 (ID, NAME, SALARY) VALUES(1 ,'dummy', 100)
ACCELERATOR DATA
ACCELERATOR NAME : SIM35
TIME STATEMENT STORED IN CACHE : 11/13/14 16:29:47.365140
STATEMENT IDENTIFIER : 18
MEMBER NAME : 'BLANK'
ACCELERATOR EXECUTIONS : 1 ACCUMULATED # ROWS RETURNED : 7
ACCUMULATED CPU TIME : 0.000001 ACCUMULATED # BYTES RETURNED : 454257
ACCUMULATED ELAPSED TIME : 0.042998 ACCUMULATED EXECUTION TIME : 0.118118
ACCUMULATED QUEUE WAIT TIME: 0.000000 WAIT TIME FOR DB2 : 0.401856
WAIT TIME FOR 1ST ROW : 0.061247 - STATEMENT NAME
The name of the statement generated by DB2.
Field Name: QW0316NM
- STATEMENT IDENTIFIER
The unique identifier of the statement. A number is generated to uniquely identify a statement in the prepared statement cache.
Field Name: QW0316TK
- LITERAL REPLACEMENT
- Indicates the cache literal replacement. Possible values are:
- NO
- No literal replacement was done.
- REPLACE
- Literals were replaced in the statement.
- DUPLICATE
- Literals were replaced in the statement, but the cached statement is a duplicate of another statement in the cache. A match with the other statement in the cache failed only because the literal reuse criteria were not met.
Field Name: QW0316LR
- LINE NUMBER
The precompiler line number of the initial PREPARE statement.
Field Name: QW0316LX
- STATUS
The status of the statement. If any of the following flags are set, the statement has actually been removed from the cache but current users might still have an active copy. DB2 will continue to track the statement until the use-count and copy-count are zero.
It can be one of the following:- INVALIDATED BY DROP OR ALTER
- INVALIDATED BY REVOKE
- REMOVED FROM CACHE BY LRU
- INVALIDATED BY UTILITIES
Field Name: QW0316FL
- ELIGIBLE FOR ACCELERATOR
The statement is eligible for the execution on an accelerator (DB2 field: QW0316ELI).
Field Name: 316ELI
- TIME STATISTICS COLLECTION START
Shows the time stamp when the statistics collection began.
Field Name: QW0316TS
- TIME STATEMENT STORED IN CACHE
The date and time when the statement was inserted into the cache (in DB2 timestamp format).
Field Name: QW0316TM
- IN STORE CLOCK FORMAT
The date or time when the statement was inserted into the cache (in store clock format) (DB2 field: QW0316TM2).
Field Name: RT316TM2
- TIME STATEMENT UPDATED IN CACHE
The date and time when the statement was updated, in internal format (DB2 field: QW0316UT2).
Field Name: RT316UT2
- IN STORE CLOCK FORMAT
The date or time when the statement statistic was updated (in store clock format) (DB2 field: QW0316UT1).
Field Name: RT316UT1
- STATEMENT COPIES
- The number of copies of the statement owned by all threads in the system.Note: This includes QW0316US and any copies owned by plans or packages bound with KEEPDYNAMIC(YES) that were not used in their current unit of work. These users prepared the statement in a previous unit of work and still have it in a prepared state.
Field Name: QW0316CP
- STATEMENT EXECUTIONS
- The number of statement executions.Note: For a cursor statement, this is the number of OPENS.
Field Name: QW0316NE
- SYNCH BUFFER READS
The number of synchronous buffer read operations performed for the statement.
Field Name: QW0316NB
- SYNCH BUFFER WRITES
The number of synchronous buffer write-operations performed for statement.
Field Name: QW0316NW
- CURRENT USERS
- Number of current users of the SQL statement.Note: These users have prepared or executed the statement during their current unit of work.
Field Name: QW0316US
- GETPAGE OPERATIONS
The number of Getpage operations performed for a statement.
Field Name: QW0316NG
- TABLESPACE SCANS
The number of scan operations for table spaces that are performed for a statement.
Field Name: QW0316NT
- PARALLEL GROUPS CREATED
The number of parallel groups that are created for a statement.
Field Name: QW0316NL
- ROWS EXAMINED
The number of rows that are examined for the statement.
Field Name: QW0316NR
- ROWS PROCESSED
The number of rows that are processed for the statement. For example, the number of rows returned for a SELECT statement, or the number of rows affected by an INSERT, UPDATE, or DELETE statement.
Field Name: QW0316NP
- SORTS
The number of sort operations performed for a statement.
Field Name: QW0316NS
- INDEX SCANS
The number of index scans performed for a statement.
Field Name: QW0316NI
- ACCUMULATED CPU TIME
The accumulated CPU time.
Field Name: QW0316CT
- ACCUMULATED ELAPSED TIME
Shows the accumulated elapsed time used for a statement.
Field Name: QW0316AE
- STABILIZED STMT ID
The ID of the dynamic statement that could be
stabilized by the catalog (DB2 field: QW0316_SDQ_STMTID).
Field Name: Q316SDQI
- HASH-ID VERSION
- The version of the hash ID of the dynamic query
(DB2 field: QW0316_QUERY_HASH_VER).
Field Name: Q316QHVE
- HASH-ID
- The hash ID of a stabilized dynamic query.
Field Name: Q316QHID
- STABILIZATION GROUP NAME
- The name of the stabilization group.
This is the group ID of the stabilization dynamic query
(DB2 field: QW0316_STBLGRP).
Field Name: Q316STBG
- RL NOT USED LIMIT EXCEEDED
- The number of times that a RID list was not used, because the number of:
- RIDs would have exceeded one or more internal DB2 limits
- RID blocks exceeded the value set by the MAXTEMPS_RID system parameter.
Field Name: QW0316RT
- RL NOT USED NO STOR AVAIL
The number of times that a RID list was not used because there was not enough storage. This also applies if the work file storage was not available.
Field Name: QW0316RS
- RL OV - NO POOL STOR AVAIL
The number of times a RID list was overflowed to a work file because no RID pool storage was available to hold the list of RIDs (DB2 field: QW0316WFRIDS).
Field Name: RT316IDS
- RL OV - RIDS EXCEED LIMIT
The number of times a RID list was overflowed to a work file because the number of RIDs exceeded one or more internal limits (DB2 field: QW0316WFRIDT).
Field Name: RT316IDT
- HJA - NO POOL STOR AVAI
The number of times a RID list append for a hybrid join was interrupted because no RID pool storage was available to hold the list of RIDs (DB2 field: QW0316HJINCS). For example, the number of times DB2 interrupted the RID phase and switched to thed data phase.
Field Name: RT316NCS
- HJA - RIDS EXCEED LIMIT
The number of times a RID list append for a hybrid join was interrupted because the number of RIDs exceeded one or more internal limits (DB2 field: QW0316HJINCT). For example, it shows the number of times DB2 interrupted the RID phase and switched to the data phase.
Field Name: RT316NCT
- RL RETRIEVAL IA SKIPPED
The number of times a RID list retrieval for multiple index access was skipped because DB2 predetermined the outcome of index ANDing or ORing (DB2 field: QW0316RSMIAP).
Field Name: RT316IAP
- READ BY OTHER THREAD
The accumulated wait time for a read activity that is performed by another thread.
Field Name: QW0316W5
- SYNCH EXECUTION UNIT SWITCH
The accumulated wait time for a synchronous execution unit switch.
Field Name: QW0316W3
- WRITE BY OTHER THREAD
The accumulated wait time for a write activity that is performed by another thread.
Field Name: QW0316W6
- SYNCHRONOUS I/O
- The accumulated wait time for a synchronous I/O.Note: This wait time and the following wait times are only collected if a CLASS 3 accounting trace is started.
Field Name: QW0316W1
- GLOBAL LOCKS
The accumulated wait time for global locks.
Field Name: QW0316W4
- LOCK AND LATCH REQ
The accumulated wait time for lock and latch requests.
Field Name: QW0316W2
- LATCH REQUEST
The accumulated wait time for lock requests.
Field Name: QW0316W7
- PAGE LATCH
The accumulated wait time for page latches.
Field Name: QW0316W8
- DRAIN LOCK
The accumulated wait time for drain locks.
Field Name: QW0316W9
- DRAIN WAITING FOR CLAIM REL
The accumulated wait time for drains when waiting for claims to be released.
Field Name: QW0316WA
- LOG WRITER
The accumulated wait time for log writers.
Field Name: QW0316WB
- WAIT CHILD L-LOCKS
- The accumulated wait time for child L-locks for the
statement.
Field Name: QW0316WC
- WAIT OTHER L-LOCKS
- The accumulated wait time for other L-locks for the
statement.
Field Name: QW0316WD
- WAIT P/P P-LOCKS
- The accumulated wait time for P/P P-locks for the
statement.
Field Name: QW0316WE
- WAIT PAGE P-LOCKS
- The accumulated wait time for page P-locks for the
statement.
Field Name: QW0316WF
- WAIT OTHER P-LOCKS
- The accumulated wait time for other P-locks for the
statement.
Field Name: QW0316WG
- WAIT PIPE
- The accumulated wait time for pipe wait
(DB2 field: QW0316_PIPE_WAIT).
Field Name: Q316PIPW
- WAIT PARALLEL QUERIES
- The
accumulated time waiting for parallel queries to synchronize
between parent and child tasks
(DB2 field: QW0316_PQS_WAIT).
Field Name: Q316PQSW
- CURRENT DATA BIND OPTION
- The CURRENTDATA bind option. It can be one of the following:
- YES
- NO
Field Name: QW0316X7
- CURSOR WITH HOLD
- Shows if the position for a cursor opened WITH HOLD. It can be one of the following:
- YES
- NO
Field Name: QW0316XC
- CURRENT PRECISION SPEC REG
- Shows the CURRENT PRECISION special register. It can be one of the following:
- DEC31
- DEC15
Field Name: QW0316XB
- DYNAMIC RULES BIND OPTION
- Shows the dynamic rules bind option. It can be one of the following:
- BIND
- RUN
Field Name: QW0316X8
- CURRENT DEGREE SPECIAL REG
- Shows value of the CURRENT DEGREE special register. It can be one of the following:
- ANY
- 1
Field Name: QW0316X9
- ISOLATION BIND OPTION
- The value of the ISOLATION bind option that is in effect for the initial PREPARE statement. It can be one of the following:
- UNCOMMITTED READ
- CURSOR STABILITY
- READ STABILITY
- REPEATABLE READ
Note: This value does not reflect if it is specified in a WITH clause.Field Name: QW0316X6
- CURRENT RULES SPECIAL REG
- Shows the value of the CURRENT RULES special register. It can be one of the following:
- DB2
- SQL
Field Name: QW0316XA
- DSG MEMBER
The data sharing DB2 member that cached the SQL statement (DB2 field: QW0316MBR).
Field Name: RT316MBR
- TRANSACTION NAME
The text of the transaction name.
Field Name: QW0316T2
- SIGNON USER ID
The End User ID is provided during RRS signon or resignon for initial prepare.
Field Name: QW0316XE
- WORKSTATION ID
The Workstation ID is provided during RRS signon or resignon for initial prepare.
Field Name: QW0316XF
- PROGRAM NAME
The text of the program name.
Field Name: QW0316T1
- USER ID
The user ID.
Field Name: QW0316T3
- USER GROUP
The name of the user group. The user group is the current SQLID of the user who started the initial PREPARE statement.
Field Name: QW0316X4
- OBJECT QUALIFIER
The qualifier that is used for unqualified table names.
Field Name: QW0316X5
- REFERENCED TABLE QUALIFIER
The qualifier of the referenced table name.
Field Name: QW0316QD
- REFERENCED TABLE NAME
The name of the referenced table.
Field Name: QW0316TD
- USER PROVIDED ID STRING
The identification (ID) string provided by the user.
Field Name: QW0316UI
- CURRENT SCHEMA
The special register text of the current schema.
Field Name: QW0316SC
- LENGTH OF SQL STATEMENT
The length of the entire statement.
Field Name: QW0316LN
- SQL STATEMENT - FIRST 60 BYTES
The first 60 bytes of the SQL statement text.
Field Name: QW0316TX
- ACCELERATOR NAME
The accelerator name (DB2 field: QW0316ANM).
Field Name: 316ANM
- TIME STATEMENT STORED IN CACHE
The date or time when the statement was inserted into the cache (in DB2 timestamp format) (DB2 field: QW0316ATM).
Field Name: 316ATM
- STATEMENT IDENTIFIER
The statement identifier, for correlation with IFCID 316 data section 1 (DB2 field: QW0316AID).
Field Name: 316AID
- MEMBER NAME
The member name in case of a data sharing group (DSG). It is used for correlation with IFCID data section 1 (if DSG) (DB2 field: QW0316AMBR).
Field Name: 316AMBR
- ACCELERATOR EXECUTIONS
The number of executions on this accelerator (DB2 field: QW0316AEXEC).
Field Name: 316AEXEC
- ACCUMULATED # ROWS RETURNED
Shows the accumulated number of rows returned for the SELECT statement (DB2 field: QW0316AROW).
For completed queries, this is the total number of rows returned that were computed by the accelerator (this is not necessarily the number of rows returned to DB2® in case DB2 does not fetch all rows).
For in-process queries, this is the number of rows that have been sent so far (and more rows may still be coming).
This value is always set to 0 for DML statements (INSERT, UPDATE, DELETE) because these wait times do not occur for DML statements.
Field Name: 316AROW
- ACCUMULATED CPU TIME
Shows the accumulated CPU time spent in the accelerator when processing the query request for the statement.
This value reflects parallel processing such that the CPU value may exceed the accumulated elapsed time (DB2 field: QW0316ACPU).
For completed queries, this is the CPU time from the initial request to the last row being returned to DB2. For in-process queries, this is the time from the initial request to the current point in time. The counter includes the CPU time spent in the accelerator and also the CPU time spent in the Netezza® backend (on the coordinator node and all worker nodes).
Field Name: 316ACPU
- ACCUMULATED # BYTES RETURNED
Shows the accumulated number of bytes returned for the SELECT statement (DB2 field: QW0316ABYT).
For completed queries, this is the total number of bytes returned and produced by the accelerator (this is not necessarily the total number of bytes returned to DB2 in case DB2 does not fetch all the data).
For in-process queries, this is the number of bytes that have been sent so far (and more bytes may still be coming).
This value is always set to 0 for DML statements (INSERT, UPDATE, DELETE) because no such wait times occurs.
Field Name: 316ABYT
- ACCUMULATED ELAPSED TIME
Shows the accumulated elapsed time spend in the accelerator processing the query request for the statement (DB2 field: QW0316AELA).
For completed queries, this is the time from the initial request to the last row that is returned to DB2. For in-process queries, this is the time from the initial request to the current point in time.
Field Name: 316AELA
- ACCUMULATED EXECUTION TIME
Shows the accumulated execution time spent in processing the query request for the statement (DB2 field: QW0316AEXE).
For completed queries, this is the time spent since starting the query execution until the query execution has finished. Subsequent processing and transfer of the result set is not included, but there may be an overlapping time window in which result set processing (fetching) and query execution takes place.
For in-process queries, it is the time measured from starting query execution inside the accelerator up to the current point in time or until query execution has finished and only result set processing and transfer remains (whichever occurs earlier).
This time is measured for the actual execution time spent for the query. Compared to the total elapsed time (QW0316AELA and QW0401AELA), it does not include any preprocessing done in the accelerator (such as PREPARE), and it does not include time spent, such as in spill-to-disk or other things, related to the final query result processing.
Field Name: 316AEXE
- ACCUMULATED QUEUE WAIT TIME
Shows the accumulated queue wait time for the statement (DB2 field: QW0316AWAT).
For completed queries, this is the time that the query has spent in queues, waiting to be processed.
For in-process queries, the value is only available once the query execution itself has finished and only result processing remains. Until then, the value will be (nearly) 0.
Field Name: 316AWAT
- WAIT TIME FOR DB2
Shows the total time the accelerator waited for DB2 to request query results (DB2 field: QW0316ATWDB2).
For completed queries, this is from the time when the first row of the result set was produced by the accelerator until the last row was sent to DB2. For in-process queries, it is 0 (if the accelerator has not yet computed a result row) or the time from computing the first row to the current point in time (if at least one result row is available).
This value is always set to 0 for DML statements (INSERT, UPDATE, DELETE) because these wait times do not occur for DML statements.
Field Name: 316ATWDB
- WAIT TIME FOR 1ST ROW
Shows the time waited for first row of query result to be available (DB2 field: QW0316ATW1R).
For completed queries, this is the time from receiving the query in the accelerator until the first row of the result set was computed. For in-process queries, this is the time from receiving the query in the accelerator to the current point in time (if no result rows are available yet) or until the first row of the result set was computed (if at least one result row is available).
This value is always set to 0 for DML statements (INSERT, UPDATE, DELETE) because no such wait times occurs.
Field Name: 316ATW1R