Query Parallelism

This section introduces the Query Parallelism block.

This block shows query parallelism activity performed by the event.

Note: In query CP and sysplex query parallelism, this is the only place where the TCB time of the parallel records is shown.

Query Parallelism Workload Block Example

Here is an example of a Query Parallelism Workload block.

--- QUERY PARALLELISM -----------------------------------------------------------------------------
QUERY  PARALLEL  PLANNED  PLANNED  NEGOTIATED            PIPE            TASK             NUMBER OF
BLOCK  GROUP     AT BIND  AT  RUN  AT     RUN    REASON  ELAPSED TIME    CPU TIME   TYPE  MEMBERS
    1         1        3        3           3    NORMAL      0.895716    0.043467    CP           3

Field description

An example of a query parallelism workload block is shown in Query Parallelism Workload Block Example.

The fields in the query parallelism workload block are:

QUERY BLOCK

The query block number.

PARALLEL GROUP

The parallel group number.

PLANNED AT BIND

The degree of parallelism planned at bind time.

This field contains 0 if host variables in the statement caused the parallelism decision to be made at bind time.

PLANNED AT RUN

The degree of parallelism planned at run time.

NEGOTIATED AT RUN

The degree of parallelism negotiated at run time, which depends on buffer pool availability.

If the value in this field is 1, the plan for parallel I/O processing falls back to sequential execution mode.

REASON

The reason for deriving the planned run time degree of parallelism:

NORMAL: The planned run time degree is derived from planned bind-time degree.
HOSTVAR: Host variable partitioning
NOESA: No ESA sort support
CURSOR: Cursor that can be used for update and delete.
EMPTY: Empty parallel group
ENCLUNAV: MVS/ESA™ enclave services are not available
UNKNOWN: None of the above

PIPE ELAPSED TIME

The time of pipe creation subtracted from the time of pipe termination.

TASK CPU TIME

The sum of the normalized CPU times spent for the parallel tasks. In sysplex query parallelism, the CPU times are normalized by the conversion factor that is derived from IFCID 106 and related to the conversion factor of the originating task.

If IFCID 106 is not present, asterisks are printed.

The task CPU time is calculated as follows:

Let CV_O be the conversion factor for the member where the originating thread is running.
Let CV_P be the conversion factor for the member where the parallel thread is running.
Let TCB_P be the TCB time that is recorded by Db2® for an activity of the parallel thread.
Then the following formula applies:
Normalized TCB time for that activity = (TCB_P * (CV_O / CV_P))

TYPE

The type of parallelism:

CP: CP parallelism
I/O: I/O parallelism
SYS: Sysplex query parallelism

NUMBER OF MEMBERS

The number of members on which the query executed.