Query Parallelism Data window

Use the Query Parallelism Data window to view information about the number of parallel groups executed for the thread and to find out whether the type or degree of parallelism was changed at run time.

To display this window, select Query Parallelism Data from the Thread Detail panel.

Figure 1. Query Parallelism Data window (Thread Activity)

 DGOMTWQP              Query Parallelism Data

 Maximum Degree of Parallelism . . . . . . . . . . . . :         5

 Parallel Groups Executed  . . . . . . . . . . . . . . :         1

 Number of Parallel Groups executed with
   Planned Parallel Degree . . . . . . . . . . . . . . :         1
   Reduced Parallel Degree
     No buffer . . . . . . . . . . . . . . . . . . . . :         0
   One DB2
     COORDINATOR Parm = NO . . . . . . . . . . . . . . :         0
     Isolation Level . . . . . . . . . . . . . . . . . :         0
   Fall back to sequential mode
     Cursor  . . . . . . . . . . . . . . . . . . . . . :         0
     No ESA  . . . . . . . . . . . . . . . . . . . . . :         0
     No buffer . . . . . . . . . . . . . . . . . . . . :         0

     MVS/ESA Enclave Services. . . . . . . . . . . . . :         0

 Member skipped (%)  . . . . . . . . . . . . . . . . . :         0

 Parallelism Disabled by RLF . . . . . . . . . . . . . :        NO
 Command ===> ____________________________________________________
  F1=Help      F2=Split     F3=Exit      F5=Auto      F6=History
  F9=Swap     F12=Cancel   F16=Look     F17=Collect  F22=Purge

The main objective of query parallelism is to improve query response times.

By examining the information displayed in this window, you can discover the number of parallel groups that have fallen back to sequential mode and determine the cause.

The following list describes some of the important fields that are shown in this window:

Maximum Degree of Parallelism: Maximum degree of parallelism executed among all parallel groups. This field indicates the extent to which query parallelism applies.
The degree can be set on the BIND and REBIND commands or the SET CURRENT DEGREE statement. The default is set by the CURRENT DEGREE installation parameter. If a query is I/O bound, I/O parallelism can help reduce the response time. If a query is CPU bound, query CP parallelism can reduce the response time. If both conditions apply, the benefits for the query can be very large.
Parallel Groups Executed: Total number of parallel groups that have been executed.
Planned Parallel Degree: Total number of parallel groups that executed to the planned parallel degree. This field is incremented by one for each parallel group that executed with the planned degree of parallelism (as determined by DB2®).
Reduced Parallel Degree - No buffer: Total number of parallel groups that processed to a parallel degree less than planned because of a storage shortage or contention on the buffer pool. If this field is not zero, increase the size of the current buffer pool by using the ALTER BUFFERPOOL command, or use the ALTER TABLESPACE statement to assign table spaces accessed by this query to a different buffer pool.
One Db2 COORDINATOR Parm = NO: The total number of parallel groups scheduled for Sysplex query parallelism, but executed on a single Db2 because of 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.
One Db2 Isolation Level: The total number of parallel groups scheduled for Sysplex query parallelism, but executed on a single Db2 because of repeatable-read or read-stability isolation.
Cursor: Total number of parallel groups that fall back to sequential operation because of a cursor that can be used for update or delete.
No ESA: Total number of parallel groups that fall back to sequential operation because of a lack of MVS/ESA sort support.
No buffer: Total number of parallel groups which fall back to sequential mode because of storage shortage or contention on the buffer pool.
Member skipped (%): The percentage of Sysplex parallel groups that were not distributed as planned.
This field indicates situations where a member has insufficient buffers and so the parallelism coordinator has to bypass a Db2 when distributing tasks.
Parallelism Disabled by RLF: This field indicates whether parallelism was disabled by RLF governing for dynamic SQL statements of this thread.