Limiting Parallelism

Answer

• Parallelism can use a significant amount resources. However, various "tuning knobs" in DB2 provide control over the extent to which parallelism uses resources, to support on the needs of a particular DB2 installation. For example, in a data warehouse environment, it might be desirable for a single query to use most of the available resources in order to deliver a particular report quickly. In a transactional environment with many applications running simultaneously, it is probably better to limit parallelism.

To limit parallelism you can use one or more of the following options:

• PARAMDEG subsystem parameter - controls the maximum degree of parallelism allowed. Since each parallel task uses additional resources, limiting the number of parallel tasks limits the resource usage.
  The conservative recommendation for setting PARAMDEG is between 50 and 75 percent of the number of CPs. PARAMDEG set to 0 means that no limit is imposed on the degree of parallelism. For more information see MAX DEGREE field (PARAMDEG subsystem parameter).
• CDSSRDEF subsystem parameter- controls the default degree for dynamic queries. A setting of 1 which means no parallelism is used for dynamic queries unless the application specifically requests it with "SET CURRENT DEGREE".
• DEGREE bind option - Controls whether parallelism can be used at a package level.
  
• Restricting parallelism by using RLF - The resource limit facility provides an option to disable each type of parallelism for dynamic queries with RLFFUNC '3', '4', and '5'. For information about about using RLF to disable parallelism, see Tuning Parallel Processing.
• WLM - can be used to limit the resources allocated to a given job. If sysplex parallelism is enabled, you can control it through WLM by using the subsystem type of 'DB2'.
  
• Buffer pool thresholds VPPSEQT and VPXPSEQT. VPPSEQT controls the portion of the buffer pool that is available to support parallel operations. VPXPSEQT controls the portion of the buffer pool available to support Sysplex parallelism specifically. Reducing these parameters can cause DB2 to reduce the degree of parallelism at runtime. For information about these buffer pool parameters, see Buffer pool thresholds that you can change.

Parallelism delivers improved elapsed time at the expense of CPU. With these "tuning knobs," you can decide whether to allow DB2 to use all resources available for a single query, disable parallelism completely, or specify something in between.

Note that when executing on System 9, System 10, or System z Enterprise Servers (z196, z114, zEC12 or zBC12) or subsequent machines, part of parallel tasks is eligible to execute on zIIPs. After reaching a CPU usage threshold, up to 80% of the processing of long-running parallel queries for DB2 10 and 11 for z/OS are eligible for zIIP execution. Long running parallel queries are those which the DB2 for z/OS Query Optimizer determines should be run in parallel and whose execution exceeds an identified period of time as established by DB2 for z/OS (the “CPU usage threshold.”). The CPU usage threshold is defined by DB2 to avoid excessive parallelism. In some situations, executing parallel tasks on zIIPs can reduce the cost of the query, but it is important to balance the zIIP eligible work with the zIIP capacity of your machine. See DB2 for z/OS zIIP and zAAP for more information.

Related information
Disabling query parallelism
Tuning parallel processing
Interpreting query parallelism