Query parallelism in DB2 for z/OS

Three historical modes — only CP remains

Query I/O parallelism overlapped I/O for I/O-bound scans. Query CP parallelism added real multi-processor CPU for scans, joins, and sorts. Sysplex query parallelism shipped child tasks to other data sharing members. IBM deprecated I and X in Db2 9. If you still see old training that treats PARALLELISM_MODE = X as a tuning lever, ignore it for current subsystems. Tune DEGREE, PARAMDEG, partitioning, and buffer pools instead.

Utility PARALLEL keywords (LOAD, REORG, and friends) are a different feature. This page is about SQL query CP parallelism.

How query CP parallelism works

At BIND or PREPARE, if DEGREE is ANY (static bind option or CURRENT DEGREE for dynamic SQL), the optimizer may split a query block into a parallel group. Each task works on a slice of the data—often partition ranges, key ranges, or work file ranges. Results are merged. The planned number of tasks is the degree.

Decision timing:

• Bind / prepare — if parallelism is not chosen here, runtime cannot invent it
• Execution — Db2 can lower the degree or fall back to sequential mode; it does not fail the SQL just because parallelism was reduced

Host variables and parameter markers can prevent a precise partition split at bind time, so the final degree may be decided at run time. Insufficient virtual buffer pool space for the planned degree is a common reason for reduction. An ambiguous cursor that Db2 decides is updatable at run time also disables parallelism; declare FOR READ ONLY (or FOR FETCH ONLY) when you mean it, and prefer CURRENTDATA(NO) rather than hoping an ambiguous cursor stays read-only.

1
2
3
4
5
6
7
-- Dynamic SQL: allow CP parallelism for subsequent PREPAREs
SET CURRENT DEGREE = 'ANY';
 
SELECT E.EMPNO, E.LASTNAME, E.SALARY
  FROM HR.EMPLOYEE E
 WHERE E.SALARY > 40000
 ORDER BY E.EMPNO;

Knobs: DEGREE, CURRENT DEGREE, PARAMDEG, RLF

On z/OS, CURRENT DEGREE is '1' (no intra-query parallelism) or 'ANY' (Db2 may parallelize). The installation default is CDSSRDEF, which IBM recommends you leave at 1 unless you have a measured reason: blindly parallelizing every dynamic statement can spike CPU on an already busy LPAR.

PARAMDEG is the subsystem maximum degree for a parallel group (0–254). Zero means Db2 derives a cap from online general-purpose CPs and zIIPs. On LPARs with many zIIPs, PARAMDEG = 0 can produce a surprisingly large degree for I/O-bound queries. IBM guidance is conservative: if you have more than two zIIPs, start PARAMDEG near the zIIP count and raise it only when elapsed-time tests justify it. When optimization hints specify a degree, PARAMDEG may not cap bind-time planning, but a lower PARAMDEG still reduces the degree at execution.

The resource limit facility can disable CP parallelism for dynamic SQL with RLFFUNC = '4' in DSNRLSTxx. IFCID 0002/0003 field QXRLFDPA and IFCID 0022 QW0022RP help confirm that RLF, not the optimizer, turned parallelism off.

1
2
3
4
5
-- Static packages
BIND PACKAGE (HRAPP) MEMBER(EMPRPT) DEGREE(ANY) ...
 
-- Disable again for a specific session
SET CURRENT DEGREE = '1';

Reading parallelism in PLAN_TABLE

If ACCESS_DEGREE or JOIN_DEGREE is not NULL, the optimizer planned parallel work. The PGROUP_ID columns share the same number for steps that belong to one parallel group. DSN_PGROUP_TABLE (when created) lists parallel groups. Runtime accounting still matters: planned degree 8 and executed degree 1 is a reduction story, not a bind-time story.

When parallelism is a bad idea

• Tiny result / OLTP — task start-up costs more than the scan
• CPU already saturated — elapsed time may not fall; other work suffers
• Updatable or ambiguous cursors — runtime sequential fallback
• RR/RS with WITH HOLD — documented cases disable CP parallelism
• Poor clustering and random I/O — extra tasks thrash the buffer pool

Parallel child tasks in Db2 12 are fully zIIP-eligible (Db2 11 was about 80%). That makes parallelism attractive for eligible queries, but an oversized degree can starve other zIIP work and spill onto general-purpose CPs.

Predicate filtering and parallel access

Parallelism does not replace good predicates. Each child task still applies stage 1 (Data Manager) and stage 2 (RDS) predicates. Highly selective indexable predicates can make a sequential matching index cheaper than a parallel tablespace scan. Weak predicates plus a large partitioned table are the classic parallel tablespace-scan win.

• Stage 1 predicates — evaluated close to the page; can be matching, index screening, page-range screening, or data screening
• Stage 2 predicates — residual RDS filters after the row is returned; every parallel task still pays that CPU
• Predicate transitive closure — Db2 may generate extra predicates (COL1 = COL3 from COL1 = COL2 and COL2 = COL3) so more tasks can filter early
• Predicate pushdown — outer predicates applied inside views or nested table expressions so each parallel slice reads less

Filter factor is the estimated fraction of rows that survive a predicate (0–1). Selectivity is the same idea in everyday language. Cardinality is the estimated number of rows after filtering. Parallel degree is a cost decision: if cardinality is tiny, degree 1 wins; if a partition-wise scan of millions of rows is expected, a higher degree can win. Wrong filter factors (missing statistics, correlated columns treated as independent) produce both “surprise sequential” and “surprise parallel” plans.

1
2
3
4
5
6
-- Transitive closure can add EMP.DEPTNO = 'A00' from the join + local predicate
SELECT E.EMPNO, D.DEPTNAME
  FROM HR.EMPLOYEE E
  INNER JOIN HR.DEPARTMENT D
    ON E.WORKDEPT = D.DEPTNO
 WHERE D.DEPTNO = 'A00';

Statistics that steer the degree

The optimizer’s cost model—and therefore whether it bothers to parallelize—reads catalog statistics:

• Table statistics — CARDF, NPAGESF in SYSTABLES / SYSTABLESPACE / SYSTABSTATS (partition level)
• Column statistics — COLCARDF, HIGH2KEY, LOW2KEY in SYSCOLUMNS
• Frequency statistics — SYSCOLDIST TYPE = F for skewed values
• Histogram statistics — SYSCOLDIST TYPE = H, QUANTILENO buckets
• COLGROUP statistics — multi-column cardinality and frequencies so correlated predicates are not multiplied as if independent
• Distribution statistics — the family of frequency/histogram/COLGROUP rows that describe non-uniform data
• Clustering — CLUSTERRATIOF (and DATAREPEATFACTORF) on indexes; parallel list prefetch vs sequential partition scans depend on it
• Correlation — related columns (CITY and STATE) need COLGROUP or the filter factor is too optimistic
• Real-time statistics — SYSTABLESPACESTATS / SYSINDEXSPACESTATS; used for some decisions and by DSNACCOX, but they do not replace RUNSTATS distribution stats
• Optimizer statistics — the catalog numbers the cost model actually reads at bind/prepare

SYSCOLSTATS is historically tied to parallelism degree and, after APARs, can bound filter factors. Do not treat RTS TOTALROWS as a substitute for RUNSTATS CARDF when you care about access path quality. Details live on the statistics page in this section.

Query rewrite, MQTs, cache, and hints

Before costing parallel versus sequential plans, Db2 may transform the statement (predicate generation, subquery-to-join, join elimination, UNION ALL simplification). A rewritten shape can be much easier to split into parallel groups.

Materialized query tables (MQTs) can be substituted by automatic query rewrite on eligible read-only dynamic query blocks. If the MQT is small and indexed, the optimizer may drop parallelism because the rewrite already made the query cheap. Query rewrite using MQTs is cost-based: Db2 keeps the rewrite only if estimated cost is better.

The dynamic statement cache stores prepared access paths. CURRENT DEGREE is part of what makes two PREPARE texts share or not share a cached statement. Flipping DEGREE between ANY and 1 in the same application can look like “the plan changed” when you actually prepared a different cached copy.

Optimization hints, OPTHINT, and optimization profiles can freeze or steer access paths, including parallelism degree. Hints that specify a degree may not be limited by PARAMDEG at bind time; execution still enforces PARAMDEG. Use hints as an exception, not as a substitute for statistics.

Explain It Like I'm Five

One person packing a huge toy box takes a long time. Query CP parallelism is calling several friends (tasks) and giving each a section of the box. DEGREE(ANY) means “you may call friends.” DEGREE 1 means “pack it yourself.” PARAMDEG is “no more than this many friends, the living room is small.” If there are only three toys, calling eight friends is slower. If the toys are already in tiny labeled drawers (a sharp index), you might not need friends at all. Old Sysplex parallelism was mailing drawers to other houses; Db2 does not do that anymore.

Exercises

1. EXPLAIN the same report query bound DEGREE(1) and DEGREE(ANY). Compare ACCESS_DEGREE and PARALLELISM_MODE.
2. Issue SET CURRENT DEGREE = 'ANY' then PREPARE a dynamic SELECT against a large partitioned table. Confirm the cached statement’s degree.
3. Look up PARAMDEG and CDSSRDEF on your subsystem and explain whether PARAMDEG = 0 is safe given your zIIP count.
4. Find a query that planned degree > 1 but ran sequentially. Check cursor updatability, CURRENTDATA, and buffer pool size as causes.
5. List three predicates on that query and classify each as stage 1 matching, stage 1 screening, or stage 2. Would fixing a stage 2 predicate remove the need for parallelism?