Query parallelism

MAXIMUM MEMBERS USED

Maximum number of Db2 members that participated in the processing of a query

MAXIMUM DEGREE

Maximum degree of parallel processing executed (QXMAXDEG)

This counter is set to the high-water mark among all parallel groups executed for query parallelism. The number reflects the plan that executed the highest degree of parallel processing among all parallel groups.

GROUPS EXECUTED

Number of parallel groups executed (QXTOTGRP)

This counter reflects the total number of parallel groups executed.

Tuning Tip: This number can provide a good idea of how often parallel processing was used for both read and sort activity.

RAN AS PLANNED

Number of parallel groups executed at planned degree (QXNORGRP)

This counter is incremented when the number of parallel tasks (degree) at execution time is the same as the number of parallel tasks planned at BIND time.

Tuning Tip: The higher this number is, the better Db2 is tuned. This situation is ideal.

RAN REDUCED

Parallel group degraded due to buffer shortage (QXREDGRP)

This counter is incremented when the buffer pool does not have enough buffers to support as many degrees of parallel processing as had been planned. Db2 checks buffer allocations at both BIND and execution time. It assumes there will be buffers set aside for parallel processing. If at execution time a similar number of buffers do not exist, Db2 will degrade the parallel processes to a lesser degree or no parallelism.

Tuning Tip: Three parameters can be altered (ALTER BUFFERPOOL command) to resolve this situation. The overall size of the buffer pool is controlled by the VPSIZE (virtual pool size) parameter. The amount of sequential buffers is set by the VPSEQT (virtual pool sequential threshold) parameter. In the amount of buffers reserved for sequential processing, a reserve of buffers available for parallel processing must be maintained by the VPPSEQT (virtual pool parallel sequential threshold) parameter. If a significant number of parallel processes are degraded due to buffer shortage or contention, consider using a different buffer pool or altering the buffer pool used so that sufficient parallel sequential buffers are present.

ONE DB2-COORDINATOR = NO

Number of parallel groups executed on a single Db2 due to one of the following reasons (QXCOORNO):

• When the plan or package was bound, the COORDINATOR subsystem parameter was set to YES, but the parameter is set to NO when the program runs.
• The plan or package was bound on a Db2 with the COORDINATOR subsystem parameter set to YES, but the program is being run on a different Db2 that has the COORDINATOR value set to NO.

ONE DB2-ISOLATION LEVEL

Number of parallel groups executed on a single Db2 because the plan or package was bound with an isolation value of repeatable read or read stability (QXISORR)

SEQUENTIAL-CURSOR

Parallel group fallback to sequential due to updateable cursor (QXDEGCUR)

This counter is incremented when Db2 detects a cursor that is not clearly read-only and falls back from parallel processing to sequential.

Tuning Tip: To resolve this problem, the application program should have a cursor which is unambiguously read-only, with updates through another cursor or statement.

SEQUENTIAL-NO ESA SORT

Parallel group fallback to sequential due to lack of sort assist (QXDEGESA)

This counter is incremented when Db2 detects that the hardware sort assist facility is not present to logically partition the Db2 temporary DSNDB07 workfiles. The parallel sort operation falls back to sequential.

Tuning Tip: This situation will occur until the necessary hardware assist is purchased.

SEQUENTIAL-NO BUFFER

Parallel group fallback to sequential due to buffer shortage (QXDEGBUF)

This counter is incremented when the buffer pool does not have enough buffer storage in the virtual pool to support parallel processing. Db2 checks buffer allocations at both BIND and execution time. It assumes that buffers will be set aside for parallel processing. If at execution time a similar number of buffers do not exist, Db2 will degrade the parallel processes to a lesser degree or to no parallelism.

Tuning Tip: Three parameters can be altered (ALTER BUFFERPOOL command) to resolve this situation. The overall size of the buffer pool is controlled by the VPSIZE (virtual pool size) parameter. The amount of sequential buffers is set by the VPSEQT (virtual pool sequential threshold) parameter. In the amount of buffers reserved for sequential processing, a reserve of buffers available for parallel processing must be maintained by the VPPSEQT (virtual pool parallel sequential threshold) parameter. If a significant number of parallel processes are degraded due to buffer shortage or contention, consider using a different buffer pool or altering the buffer pool used so that sufficient parallel sequential buffers are present.

SEQUENTIAL-ENCLAVE SERVICES

Parallel group fallback to sequential due to unavailable enclave services (QXDEGENC)

This counter is incremented when Db2 detects that MVS 5.2 enclave support is unavailable to support parallel CP processing. The parallel group falls back to sequential.

Tuning Tip: MVS Enclave Support in MVS 5.2 sets objectives for parallel services to perform within service goals set by management in the MVS Workload Manager. The Db2 parallel tasks run as enclave SRBs. The solution is to migrate this system to MVS 5.2 as soon as practical to do so.

MEMBER SKIPPED (%)

Number of times the parallelism coordinator had to bypass a Db2 when distributing tasks because there was not enough buffer pool storage on one or more Db2 members (QXXCSKIP)

This field is incremented only on the parallelism coordinator, and it is incremented only once per parallel group, even though it is possible that more than one Db2 has a buffer pool shortage for that parallel group.

Tuning Tip: The purpose of this count is to indicate that there are not enough buffers on one or more members. Therefore, this count is incremented only when the buffer pool is defined to allow parallelism. For example, if VPXPSEQT=0 on an assistant, Db2 does not send parallel work there, but this count is not incremented.

DISABLED BY RLF

Number of times query parallelism was disabled by the Resource Limit Facility (RLF) for at least one dynamic select statement (QXRLFDPA)

REFORM PARAL-CONFIG

Total number of parallel groups for which Db2 reformulated the parallel portion of the access path because the sysplex configuration at run time was different from the sysplex configuration at bind time (QXREPOP1)

This counter is incremented only by the parallelism coordinator at run time.

REFORM PARAL-NO BUF

Total number of parallel groups for which Db2 reformulated the parallel portion of the access path because not enough buffer pool resource existed (QXREPOP2)

This counter is incremented only by the parallelism coordinator at run time.

ONE DB2-DCL TEMP TABLE

Number of parallel groups for which Db2 reformulated the parallel portion of the access path because a query block used a user-defined function with a Declared Temporary Table (QXDEGDTT)