DB2 parallelism and acceleration tuning

Three different “go faster” machines

• Intra-query (CP) parallelism — one SQL, several z/OS tasks on one member
• Utility parallelism — REORG/COPY/LOAD/REBUILD using multiple streams or partitions
• Query acceleration — offload eligible SQL to IDAA

Do not enable DEGREE ANY on every OLTP package “just in case.” Parallelism has startup cost, uses virtual storage, and can flood buffer pools and zIIPs. Save it for queries whose elapsed time is dominated by scans and hashes, not by a unique-index get.

Query parallelism and DEGREE

Query CP parallelism is intra-query: child tasks read disjoint page ranges or partitions and merge. PLAN_TABLE shows parallel mode and degree. The query parallelism tutorial covers how EXPLAIN looks; here are the tuning knobs.

1
2
SET CURRENT DEGREE = 'ANY';  -- allow parallelism
SET CURRENT DEGREE = '1';    -- forbid it for this session

1
2
3
BIND PACKAGE (HR.REPORTS) DEGREE(ANY)
-- OLTP:
BIND PACKAGE (HR.OLTP) DEGREE(1)

PARAMDEG is the subsystem maximum for a parallel group. Values 1–254 cap the degree; 0 lets Db2 derive a maximum from online GPs and zIIPs. On a fat LPAR, PARAMDEG=0 can choose a huge degree, spike CPU, and starve other zIIP work. IBM’s practical advice: if you have more than two zIIPs, start PARAMDEG near the zIIP count and raise only if elapsed time requires it.

CDSSRDEF is the default CURRENT DEGREE (1 or ANY). Shops that set ANY system-wide often regret it when an accidental SELECT * FROM BIGTAB goes parallel-32 in the middle of the online day. Prefer ANY on reporting packages and 1 as the subsystem default.

Even with ANY, the optimizer may pick degree 1 if costing says so (small table, indexed OLTP, or parallelism disabled for the statement type). Runtime can also reduce degree if the system is constrained (buffer pool, storage). Accounting “planned versus actual degree” (wording varies by monitor) tells you the cap was hit.

Parallel sequential processing

Parallel tasks often use sequential prefetch on their slice of pages. That is “parallel sequential”: many sequential streams at once. VPSEQT and work-file pools must absorb that or prefetch disables and elapsed time collapses back to sync I/O. Parallel index I/O is the index-leaf version of the same idea.

Sysplex query parallelism (legacy)

Older Db2 data sharing could split one query across members (Sysplex query parallelism) so the whole Parallel Sysplex chewed on one SELECT. It needed coupling-facility traffic and careful enabling. IBM deprecated it and directed customers to CP parallelism on one member plus horizontal scale of the application, or to an accelerator for analytics. If you still see COORDINATOR / ASSISTANT language in ancient runbooks, treat it as history unless your version’s Installation Guide still lists it as supported.

Utility parallelism

Utilities are often the longest elapsed jobs on the platform. PARALLEL (and related options like NUM, the number of parallel index builds, or partition-level jobs you schedule yourself) lets REORG, COPY, LOAD, REBUILD INDEX, and UNLOAD use multiple tasks.

• Partitioned table spaces: one task per partition (or a subset) is the usual pattern
• Nonpartitioned objects: PARALLEL(n) on the utility statement where supported
• Index build and sort: extra sort work data sets and CPU

1
2
3
4
5
6
7
REORG TABLESPACE HRDB.HRTS
  SHRLEVEL CHANGE
  PARALLEL(8)
 
COPY TABLESPACE HRDB.HRTS
  PARALLEL(4)
  SHRLEVEL CHANGE

Tune against DASD subsystem limits and WLM. PARALLEL(99) on a box with four zIIPs just queues. Inline STATISTICS plus parallelism is a common REORG pattern: finish faster and leave stats current. Utility parallelism does not use CURRENT DEGREE; it is a utility keyword and ZPARM world (for example PARAMDEG_UTIL or the options your release documents for utility degree).

Query acceleration and Db2 Analytics Accelerator

IBM Db2 Analytics Accelerator for z/OS (IDAA) is an extra engine. You add tables to the accelerator (load or replication). Db2 then may route a query there if:

• Acceleration is enabled (ZPARM QUERY_ACCELERATION and/or CURRENT QUERY ACCELERATION)
• The SQL is eligible (supported functions, isolation, no disallowed constructs)
• Heuristics (table size, estimated response time) say the accelerator should win (ENABLE mode)

1
2
3
4
5
6
SET CURRENT QUERY ACCELERATION = ENABLE;
SET CURRENT QUERY ACCELERATION = ENABLE WITH FAILBACK;
SET CURRENT QUERY ACCELERATION = NONE;
 
-- Optional: pin a named accelerator
SET CURRENT ACCELERATOR = 'ACCEL1';

Values (also on the ZPARM) include NONE, ENABLE, ENABLE WITH FAILBACK, ELIGIBLE, and ALL. ENABLE is the usual “offload if it looks beneficial.” Failback returns to Db2 if the accelerator errors at prepare/open. ALL routes even when Db2 thinks it would be faster locally—use with care. Static SQL acceleration has its own bind/enable story on recent releases; dynamic SQL is the classic path.

Tuning acceleration is mostly which tables you load, how current the replica is (WAITFORDATA), and not sending OLTP singletons to the appliance. EXPLAIN DSN_QUERYINFO_TABLE and the acceleration registers tutorial cover eligibility. Virtual accelerators (no hardware) exist to test eligibility and estimates.

A tuning sequence

1. Measure elapsed vs CPU vs zIIP on the query or utility (Accounting / utility SYSPRINT).
2. For SQL: DEGREE ANY only on the reporting package; cap PARAMDEG; confirm EXPLAIN parallel degree.
3. If the query is still hours long and scan-heavy, consider IDAA rather than PARAMDEG 64.
4. For REORG/COPY: raise PARALLEL until DASD or CPU saturates, then stop.
5. Watch buffer pools and work files when many parallel streams prefetch at once.

Explain It Like I'm Five

Query parallelism is ten kids each reading one chapter of the same book, then telling the teacher the answer. DEGREE ANY is permission to call those friends; DEGREE 1 is “read it yourself.” PARAMDEG is “no more than this many friends—the classroom only has so many chairs (zIIPs).” Utility parallelism is ten kids packing boxes (REORG) at once. The accelerator is sending the giant book to a super-fast library across town that already photocopied the pages, while your classroom keeps doing homework that does not need the giant book.

Exercises

1. Display PARAMDEG and CDSSRDEF. Explain to a teammate what would happen if both were ANY/0 on a 20-zIIP LPAR.
2. EXPLAIN a reporting SELECT with CURRENT DEGREE ANY versus 1; compare PARALLELISM_MODE and elapsed on a sandbox.
3. Find a REORG job and note whether PARALLEL is specified.
4. List CURRENT QUERY ACCELERATION values and which one your warehouse uses.
5. Using DSN_QUERYINFO_TABLE or a monitor, identify one query that was and one that was not accelerated—and why.