Controlling parallel processing for queries

Even if parallelism is enabled for a system or job, individual queries might use a non-parallel implementation. This decision could relate to functional restrictions, or the optimizer might choose a non-parallel method.

Queries processed with parallel access methods aggressively use main storage, CPU, and disk resources.

For this reason, IBM recommends limiting the use of parallel processing to longer running queries, such as those typically found in an analytical or reporting workload. As a general rule parallel processing is best applied to queries that run for at least 60 seconds. IBM also recommends a parallel processing level of *OPTIMIZE since this provides the optimizer with the greatest flexibility in planning and running the query. Activating parallel processing system wide with the QQRYDEGREE system value is not recommended.

The parallel processing level can be controlled at a system or job level. For a job, either the CURRENT DEGREE special register or the PARALLEL_DEGREE QAQQINI option can be used.

In addition, when the parallel processing level is specified as *OPTIMIZE, additional controls are provided with QAQQINI options:

1. The query engine will actively monitor the system CPU usage. In order to provide a level of reserve processing capacity on the system, the query engine will reduce the number of parallel tasks used for processing queries if the system CPU usage exceeds 80 percent. This behavior can be disabled or modified by using the PARALLEL_MAX_SYSTEM_CPU QAQQINI option.
2. The amount of parallelism that is used by a query for parallel processing can be reduced or increased by specifying a percentage along with the *OPTIMIZE value for the PARALLEL_DEGREE QAQQINI option. The percentage influences the amount of parallelism that the optimizer can consider available for use. For example, on a system with eight processors, a percentage of 50 means that, subject to other constraints such as workload groups, the optimizer will consider using at most four of those processors.
3. The query engine will defer use of parallel processing until a query has run for longer than the number of seconds specified by the PARALLEL_MIN_TIME QAQQINI option. When the query has run longer than the specified amount of time, the optimizer will consider using parallel processing. The default time of 60 seconds is intended to limit the use of parallel processing to long-running queries, where parallel processing generally provides the greatest advantage. For workloads which are known to run well with parallel processing, this time delay can be disabled by setting PARALLEL_MIN_TIME to *NONE, which ensures that parallel processing is considered for all queries.