IFCID 401 - Static Statements in EDM Pool

This topic shows detailed information about Record Trace - IFCID 401 - Static Statements in EDM Pool.

IFCID 401 has READS and READA capability. It supports threshold value filtering.

Record Trace - IFCID 401 - Static Statements in EDM Pool

The field labels shown in the following sample layout of Record Trace - IFCID 401 - Static Statements in EDM Pool are described in the following section. 


PACKAGE NAME   :        CTS2H122
COLLECTION ID  :        OPMTEST
DATE/TIME WHEN INSERTED:  X'20110418104541179034'         STORE CLOCK FORMAT:  X'C7A3DB35A929A195'
DATE/TIME WHEN UPDATED:   X'20110418112535104180'         STORE CLOCK FORMAT:  X'C7A3E420AF8B4109'
CONSISTENCY TOKEN:        X'7844424B71324169'

STATEMENT IDENTIFIER      :                132285         ELIGIBLE FOR ACCELERATOR  :  YES
NBR OF EXECUTIONS         :                    50         NBR OF SYNC BUFFER READS  :                     0
NBR OF GETPAGES           :                     0         NBR OF ROWS EXAMINED      :                     0
NBR OF ROWS PROCESSED     :                     0         NBR OF SORTS              :                     0
NBR OF INDEX SCANS        :                     0         NBR OF TABLESPACE SCANS   :                     0
NBR OF BUFFER WRITES      :                     0         NBR OF PAR. GRPS CREATED  :                     0

                                      ACCUMULATED TIME VALUES SECTION                                      
IN-DB2 ELAPSED            :                   N/P         IN-DB2 CPU                :                   N/P
                                                            CPU TIME ON ZIIP        :                   N/P
WAIT FOR SYNC I/O         :                   N/P         WAIT FOR LOCK/LATCH       :                   N/P
SYNC EXEC UNIT SWITCH     :                   N/P         WT FOR GLOBAL LOCKS       :                   N/P
WT FOR READ BY OTHER THR  :                   N/P         WT FOR WRTE BY OTHER THR  :                   N/P
WT FOR LATCH REQ          :                   N/P         WAIT FOR PAGE LATCH       :                   N/P
WAIT FOR DRAIN LOCK       :                   N/P         WAIT FOR CLAIM RELEASE    :                   N/P
WAIT FOR 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        :                   N/P         FAST INSERT PIPE WAIT TIME:                   N/P
PARENT CHILD WAIT TIME    :                   N/P   

                                            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 AVAIL  :                     0         HJA - RIDS EXCEED LIMIT   :                     0
RL RETRIEVAL IA SKIPPED   :                     0

                                              ACCELERATOR DATA                                          
                                                                                                               
ACCELERATOR NAME       :SIM35                                                                                  
STATEMENT IDENTIFIER   :               985622                                                                  
ACCELERATOR EXECUTIONS :                    1               ACCUMULATED # ROWS RETURNED  :                   24
ACCUMULATED CPU TIME       :         0.000004               ACCUMULATED # BYTES RETURNED :               150797
ACCUMULATED ELAPSED TIME   :         0.005998               ACCUMULATED EXECUTION TIME   :             0.138040
ACCUMULATED QUEUE WAIT TIME:         0.000000               WAIT TIME FOR DB2            :             0.065245                                                                  
WAIT TIME FOR 1ST ROW      :         0.061247               ACCUMULATED WAIT FOR HTAP    :             0.054567
HTAP TIMEOUTS              :                0
PACKAGE NAME

The package name.

Field Name: QW0401PK

COLLECTION ID

The collection ID.

Field Name: QW0401CL

DATE/TIME WHEN INSERTED

The date or time when the statement was inserted into the EDM Pool.

Field Name: QW0401TM

STORE CLOCK FORMAT

The date or time when the statement was inserted into the EDM pool (in store clock format) (Db2 field: QW0401TM2).

Field Name: RT401TM2

DATE/TIME WHEN UPDATED

The date or time when statement statistics were updated (in external format) (Db2 field: QW0401UT2).

Field Name: RT401UT2

STORE CLOCK FORMAT

The date or time when statement statistics were updated (in store clock format) (Db2 field: QW0401UT1).

Field Name: RT401UT1

CONSISTENCY TOKEN

The consistency token of the package.

Field Name: QW0401CT

STATEMENT IDENTIFIER

The statement identifier.

Field Name: QW0401ID

ELIGIBLE FOR ACCELERATOR

The statement is eligible for the execution on an accelerator (Db2 field: QW0401ELI).

Field Name: 401ELI

NBR OF EXECUTIONS

The number of executions.

Field Name: QW0401EX

NBR OF SYNC BUFFER READS

The number of synchronous buffer reads.

Field Name: QW0401SR

NBR OF GETPAGES

The number of Getpages.

Field Name: QW0401GP

NBR OF ROWS EXAMINED

The number of rows examined.

Field Name: QW0401ER

NBR OF ROWS PROCESSED

The number of rows processed.

Field Name: QW0401PR

NBR OF SORTS

The number of sorts.

Field Name: QW0401ST

NBR OF INDEX SCANS

The number of index scans.

Field Name: QW0401IX

NBR OF TABLESPACE SCANS

The number of tablespace scans.

Field Name: QW0401TB

NBR OF BUFFER WRITES

The number of buffer writes.

Field Name: QW0401WT

NBR OF PAR. GRPS CREATED

The number of parallel groups created.

Field Name: QW0401PG

IN-DB2 ELAPSED

Accumulated in-Db2 elapsed time.

Field Name: QW0401ET

IN-DB2 CPU

The accumulated in-Db2 CPU time. This time includes CPU consumed on an IBM specialty engine.

Field Name: QW0401CP

CPU TIME ON ZIIP

Accumulated CPU time consumed executing SQL on an IBM specialty engine.

Field Name: QW0401ZIIP

WAIT FOR SYNC I/O

The accumulated wait time for synchronous I/O.

Field Name: QW0401SI

WAIT FOR LOCK/LATCH

The accumulated wait time for locks.

Field Name: QW0401LK

SYNC EXEC UNIT SWITCH

The accumulated wait time for synchronous execution unit switch.

Field Name: QW0401EU

WT FOR GLOBAL LOCKS

The accumulated wait time for global locks.

Field Name: QW0401GL

WT FOR READ BY OTHER THR

The accumulated wait time for a read activity done by another thread.

Field Name: QW0401OR

WT FOR WRTE BY OTHER THR

The accumulated wait time for a write activity done by another thread.

Field Name: QW0401OW

WAIT FOR LATCH REQ

The accumulated wait time for a latch request.

Field Name: QW0401LH

WAIT FOR PAGE LATCH

The accumulated wait time for a page latch.

Field Name: QW0401PL

WAIT FOR DRAIN LOCK

The accumulated wait time for a drain lock.

Field Name: QW0401DL

WAIT FOR CLAIM RELEASE

The accumulated wait time for a drain lock that is waiting for claims to be released.

Field Name: QW0401CM

WAIT FOR LOG WRITER

The accumulated wait time for a log writer.

Field Name: QW0401LW

WAIT CHILD L-LOCKS

The accumulated wait time for child L-locks for the statement.

Field Name: QW0401WC

WAIT OTHER L-LOCKS

The accumulated wait time for other L-locks for the statement.

Field Name: QW0401WD

WAIT P/P P-LOCKS

The accumulated wait time for P/P P-locks for the statement.

Field Name: QW0401WE

WAIT PAGE P-LOCKS

The accumulated wait time for page P-locks for the statement.

Field Name: QW0401WF

WAIT OTHER P-LOCKS

The accumulated wait time for other P-locks for the statement.

Field Name: QW0401WG

FAST INSERT PIPE WAIT TIME

The accumulated wait time for pipe wait.

Field Name: QW0401WH

PARENT CHILD WAIT TIME

The accumulated time waiting for parallel queries to synchronize between parent and child tasks.

Field Name: QW0401WPQS

RL NOT USED LIMIT EXCEEDED
The number of times RID list was not used because the number of:
  • RIDS would have exceeded the Db2 limits
  • RID blocks exceeded the value set by the MAXTEMPS_RID system parameter

Field Name: QW0401RL

RL NOT USED NO STOR AVAIL

The number of time a RID list was not used because there is not enough storage available to hold the list of RIDs. This also applies if the work file storage was not available.

Field Name: QW0401RS

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

Field Name: RT401IDS

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

Field Name: RT401IDT

HJA - NO POOL STOR AVAIL

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. It shows the number of times Db2 interrupted the RID phase and switched to the data phase (Db2 field: QW0401HJINCS).

Field Name: RT401NCS

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. It shows the number of times Db2 interrupted the RID phase and switched to the data phase (Db2 field: QW0401HJINCT).

Field Name: RT401NCT

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

Field Name: RT401IAP

ACCELERATOR NAME

The accelerator name (Db2 field: QW0401ANM).

Field Name: 401ANM

STATEMENT IDENTIFIER

The statement identifier, for correlation with IFCID 401 data section 1 (Db2 field: QW0401AID).

Field Name: 401AID

ACCELERATOR EXECUTIONS

The number of executions on this accelerator (Db2 field: QW0401AEXEC).

Field Name: 401AEXEC

ACCUMULATED # ROWS RETURNED

Shows the accumulated number of rows returned for the SELECT statement (Db2 field: QW0401AROW).

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: 401AROW

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

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: 401ACPU

ACCUMULATED # BYTES RETURNED

Shows the accumulated number of bytes returned for the SELECT statement (Db2 field: QW0401ABYT).

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: 401ABYT

ACCUMULATED ELAPSED TIME

Shows the accumulated elapsed time spend in the accelerator processing the query request for the statement (Db2 field: QW0401AELA).

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: 401AELA

ACCUMULATED EXECUTION TIME

Shows the accumulated execution time spent in processing the query request for the statement (Db2 field: QW0401AEXE).

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: 401AEXE

ACCUMULATED QUEUE WAIT TIME

Shows the accumulated queue wait time for the statement (Db2 field: QW0401AWAT).

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: 401AWAT

WAIT TIME FOR DB2

Shows the total time the accelerator waited for Db2 to request query results (Db2 field: QW0401ATWDB2).

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: 401ATWDB

WAIT TIME FOR 1ST ROW

Shows the time waited for first row of query result to be available (Db2 field: QW0401ATW1R).

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: 401ATW1R

ACCUMULATED WAIT FOR HTAP

The amount of time spent waiting for completion of data replication to the accelerator using the delay protocol (HTAP).

Background and Tuning Information

For more information about how to determine appropriate WAITFORDATA delay time values for query acceleration with replication in your environment, see the IBM Db2 Analytics for z/OS documentation for Hybrid Transactional and Analytical Processing (HTAP) and the WAITFORDATA feature.

Field Name: QW0401TWDP

HTAP TIMEOUTS

The number of requests where the replication of data to the accelerator needed to execute the query did not complete before the delay protocol time limit expired (HTAP).

Background and Tuning Information

For more information about how to determine appropriate WAITFORDATA delay time values for query acceleration with replication in your environment, see the IBM Db2 Analytics for z/OS documentation for Hybrid Transactional and Analytical Processing (HTAP) and the WAITFORDATA feature.

Field Name: QW0401NWDP