Ways to control the resources used by parallel operations

You have several options for controlling the amount of system resources that are used for processing queries that use Sysplex query parallelism.

Important: Start of changeSysplex query parallelism is deprecated and will be removed in a future release of DB2®.End of change

Priority

You can use z/OS® workload management to control the priority of parallel query work within a member. Pay special attention to the task of classifying work that runs on parallelism assistants.

Buffer pool space

You can control how much of the total buffer pool is used for parallel processing in general, and for Sysplex query parallelism specifically.
  • The parallel threshold (VPPSEQT) determines the amount of space that is used for all parallel processing.
  • The assisting parallel sequential threshold (VPXPSEQT) determines what subset of the parallel space is used to assist queries originating from another member of the data sharing group.

Response time can degrade if these thresholds are set too low.

Resource limits

You can use the DB2 resource limit facility (governor) to help control dynamic queries that use Sysplex query parallelism. Resource limits are local in scope, so DB2 can ensure that a specific dynamic query does not overrun a member. Members of a data sharing group can share the same resource limit specification table (RLST), or each member can have its own RLST. In either case, DB2 honors the limits that are specified on that member, no matter how many tasks are included in the statement.

The following figure illustrates how dynamic queries that use Sysplex query parallelism are governed by the DB2 resource limit facility.

Figure 1. Governing a dynamic query that runs on four members
. This figure assumes that all members share the same RLST. Statement X is not allowed to consume more than 200 000 service units on any member.
Begin figure summary. The parallelism coordinator and assistant 1 both have two parallel tasks, whereas assistant 2 and assistant 3 each have one parallel task. Detailed description available.

Governing statements with more than one parallel group: If multiple parallel groups process queries on a member, each parallel group can consume up to the service unit limit that is set for that member. In contrast, on the coordinator, all parallel groups, including the originating task, are governed by the limit on that coordinating member. The following figure illustrates how a query with two parallel groups runs at the same time.

Figure 2. A query with two parallel groups running at the same time
. This figure assumes that members are sharing the same RLST. No parallel group that is participating in the processing of statement Y can consume more than 200 000 service units. On the parallelism coordinator, all tasks in all parallel groups are constrained by the limit specified for the statement.
Begin figure summary. The parallelism coordinator and Assistant 1 have 3 parallel tasks. Assistant 2 has 2 parallel tasks and Assistant 3 has 3 parallel tasks. Detailed description available.

Queries that use INSTALL SYSADM or SYSOPR authorities: If a query is submitted by an authorization ID with INSTALL authority, none of the parallel tasks is governed, regardless of where the parallel tasks run.

A statement that is executed more than once: If the same statement executes more than once in an application, the service unit limit is applied differently to the parallelism coordinator and assistants. The service unit limit applies to all executions of the statement on the parallelism coordinator such that the cumulative number of service units consumed cannot exceed the limit. Conversely, on the parallelism assistants, each execution of the statement is subject to the service unit limit. The number of service units consumed each time the statement executes cannot exceed the limit.