IFCID 002 - Query Parallelism

This topic shows detailed information about Record Trace - IFCID 002 - Query Parallelism.

                                                     QUERY PARALLELISM                                                    
MAX DEG ESTIMATED:                    0  MEMBERS SKIPPED .:                    0  PARALL. DISABLED :                  N/A
MAX DEG PLANNED .:                    0  
MAX DEG EXECUTED :                    0  

Parallel Groups:
FALL TO SEQ-CURSOR                    0  GROUPS EXECUTED .:                    0  ONE DB2-COORD=NO :                    0 
FALL TO SEQ-NOESA:                    0  PARALL.GROUPS ...:                    0  ONE DB2-ISO LVL .:                    0 
FALL TO SEQ-STOR :                    0  RAN REDUCED-STOR :                    0  ONE DB2-DCL TTAB :                    0 
FALL TO SEQ-NEGOTN                    0  RAN REDUCED-NEGOTN                    0  REFORM PARAL-CFG :                    0 
FALL TO SEQ-A.PROC                    0  RAN AS PLANNED ..:                    0  REFORM PARAL-BUF :                    0 
 
QXPAROPT ........:                    0

MAX DEG ESTIMATED

The maximum degree of parallelism estimated for a parallel group at bind time based on the cost formula. If the parallel group contains a host variable or parameter marker, then bind time will estimate the parallel group degree based on a valid assumption value.

Field Name: QXMAXESTIDG

MEMBERS SKIPPED

The number of times the parallelism coordinator had to bypass a Db2 when distributing tasks because one or more Db2 members did not have enough buffer pool storage. The number in this field is only incremented at the parallelism coordinator once per parallel group, even though more than one Db2 might have lacked buffer pool storage for that parallel group. It is also only incremented when the buffer pool is defined to allow for parallelism. For example, if VPXPSEQT=0 on an assistant, Db2 does not send parallel work there and the number in this field is not incremented.

Field Name: QXXCSKIP

PARALL. DISABLED

Indicates whether query parallelism is disabled by the Resource Limit Facility for at least one dynamic select statement in this thread. A non-zero value means that query parallelism is disabled.

Field Name: QXRLFDPA

MAX DEG PLANNED

The maximum degree of parallelism planned for a parallel group. It is the ideal parallel group degree obtained at execution time after the host variable or parameter marker value is "plug-in" and before buffer pool negotiation and system negotiation are performed.

Field Name: QXMAXPLANDG

MAX DEG EXECUTED

The maximum degree of parallelism executed among all parallel groups to indicate the extent to which queries were processed in parallel.

Field Name: QXMAXDEG

FALL TO SEQ-CURSOR

The total number of parallel groups that fell back to sequential mode due to a cursor that can be used by UPDATE or DELETE.

Field Name: QXDEGCUR

GROUPS EXECUTED

The total number of parallel groups executed.

Field Name: QXTOTGRP

ONE DB2-COORD=NO

The total number of parallel groups executed on a single Db2 subsystem due to the COORDINATOR subsystem value being set to NO. When the statement was bound, the COORDINATOR subsystem value was set to YES. This situation can also occur when a package or plan is bound on a Db2 subsystem with COORDINATOR=YES, but is run on a Db2 subsystem with COORDINATOR=NO.

Field Name: QXCOORNO

FALL TO SEQ-NOESA

The total number of parallel groups that fell back to sequential mode due to a lack of ESA sort support.

Field Name: QXDEGESA

PARALL.GROUPS

The total number of parallel groups that Db2 intended to run across the data sharing group. This number is only incremented at the parallelism coordinator at run time.

Field Name: QXXCBPNX

ONE DB2-ISO LVL

The total number of parallel groups executed on a single Db2 subsystem due to repeatable-read or read-stability isolation.

Field Name: QXISORR

FALL TO SEQ-STOR

The total number of parallel groups that fell back to sequential mode due to a storage shortage or contention on the buffer pool.

The exception field name is QXDEGBUF.

Field Name: QXDEGBUF

RAN REDUCED-STOR

The total number of parallel groups that did not reach the planned parallel degree because of a lack of storage space or contention on the buffer pool.

The exception field name is QXREDGRP.

Background and Tuning Information

If this field is not 0, increase the size of the current buffer pool using the ALTER BUFFERPOOL command or use the ALTER TABLESPACE command to assign table spaces accessed by this query to a different buffer pool.

Field Name: QXREDGRP

This is an exception field.

ONE DB2-DCL TTAB

The number of parallel groups in a query block that were downgraded to CPU parallelism because they referenced a UDF and a declared temporary table was detected at execution time.

Db2 enforces execution on a single Db2 (CPU parallelism), in this instance, because it cannot determine at incremental bind time for the statement whether the UDF will reference the declared temporary table. Other parallel groups in the same statement are not necessarily downgraded.

Field Name: QXDEGDTT

FALL TO SEQ-NEGOTN

The total number of parallel groups that fell back to sequential mode due to system negotiation result of system stress level.

Field Name: QXSTODGNGRP

RAN REDUCED-NEGOTN

The total number of parallel groups that did not reach the planned parallel degree due to system negotiation result of system stress level.

Field Name: QXSTOREDGRP

REFORM PARAL-CFG

The total number of parallel groups where Db2 reformulated the parallel portion of the access path because of a change in the number of active members, or because of a change of processor models on which they run, from bind time to run time. This counter is incremented only on the parallelism coordinator at run time.

Field Name: QXREPOP1

FALL TO SEQ-A.PROC

The total number of parallel groups that fell back to sequential mode under an autonomous procedure.

Field Name: QXDEGAT

RAN AS PLANNED

The total number of parallel groups that executed in the planned parallel degree. This field is incremented by one for each parallel group that executed in the planned degree of parallelism (as determined by Db2).

Field Name: QXNORGRP

REFORM PARAL-BUF

The total number of parallel groups in which Db2 reformulated the parallel portion of the access path because there were insufficient buffer-pool resources. This counter is incremented only at the parallelism coordinator at run time.

Field Name: QXREPOP2

QXPAROPT

This field is for IBM service.

Field Name: QXPAROPT