Some actions are required to turn on parallelism. Even if everything is set correctly to make parallelism available in a DB2 subsystem, it’s not a forgone certainty that parallelism will be used. Several factors could still prevent DB2 from selecting parallelism even after it’s enabled:
- CPU parallelism isn’t considered if only one engine exists. If there’s any chance that parallelism could be part of your query execution, configure with at least two engines, including zIIPs. One zIIP and one CP should satisfy the multiple engine requirement of CPU parallelism.
- CPU parallelism can be disabled using the Resource Limit Facility (RLF). Setting RLFFUNC equal to “4” in the RLF table for a plan, package, or authid prevents parallelism for that object type. Sysplex query parallelism can be disabled with RLFFUNC set to “5” and I/O parallelism with “3.” If all types of parallelism are to be disabled, a row must be entered for each of these three values.
- Setting the buffer pool threshold, VPPSEQT, to “0” at run-time will selectively disable parallelism. For now, VPPSEQT must be set to a number greater than 0 for DB2 to take advantage of CPU parallelism.
- Using “cursor with hold” will prevent a query from taking advantage of parallelism.
- An ambiguous cursor could also prevent parallelism.
- Sysplex query parallelism will degrade to CPU parallelism if you use a star join, sparse index, RID access, or IN-list parallelism.
There are a few other DSNZPARMs that might be of interest. These ZPARMs are referred to as opaque or hidden, which means they can’t be set up or modified via the DB2 installation panels. The first is PTASKROL on the DSN6SYSP macro. This ZPARM rolls up the accounting trace records for parallelism into a single record. Its possible values are YES and NO with YES being the default. If set to YES, all the parallel task records are rolled up into a single record. With YES, less System Management Facility (SMF) data is collected and processing costs are reduced. However, some detail is lost. Generally, you should use the default for performance reasons. If you’re attempting to diagnose a balancing issue, NO should be considered to obtain the more detailed individual records. If NO is specified, each parallel child task produces its own accounting trace record.
Next is the hidden DSNZPARM SPRMPTH on the DSN6SPRC macro. This ZPARM can be extremely helpful and is well-documented in numerous DB2 presentations. There’s a threshold, by default 120 milliseconds (ms), a query must reach before actually using parallelism. Because of the initial cost set up for parallelism, it shouldn’t be used for short (and quick) running queries. This threshold prevents that from happening. Nothing that runs in less than 120 ms will use parallelism. In some instances 120 ms is still too low to eliminate what still might be considered “fast running” queries from considering parallelism. For those situations, consider increasing SPRMPTH. While there are situations where you might make this threshold higher than the default, there is no reason to make it any smaller.
PARAPAR1 and OPTOPSE are ZPARMs that no longer exist in DB2 9. OPTOPSE was removed from DB2 V8. The enhancements they delivered are no longer selectable options; the fix OPTOPSE delivered is now always ON. The more aggressive parallel IN-List processing delivered by PARAPAR1 is now part of DB2 9.
There are many sources, including information available in the accounting records and available through almost all monitors, to help you determine what parallelism is doing. Additional details are in IFCIDs:
- 221 covering the degree of parallel processing for a parallel group
- 222 about the elapsed time for a parallel group
- 223 about when a parallel group completes.
There are also columns in EXPLAIN’s PLAN_TABLE and DSN_STATEMENT_CACHE tables with parallel details, plus two EXPLAIN tables parallelism specifically uses, DSN_GROUP_TABLE and DSN_RTASK_TABLE.
Another reason parallelism can be especially significant is its potential to reduce the cost of doing business on the System z platform. Parallelism breaks a query into multiple parts, each part running under its own Service Request Block (SRB), and each part performing its own I/O. Although there’s additional CPU cost when DB2 first decides to take advantage of query parallelism for the setup, there’s still a close correlation between the degree of parallelism achieved and the query’s elapsed time reduction. The use of SRB is significant. When taking advantage of parallelism in DB2, parallel child tasks can be redirected to a zIIP. Software charges are unaffected by the additional CPU capacity made available by the addition of zIIP processors when added to System z.
One of the easiest ways to improve the amount of zIIP redirect is to enable parallelism. There’s also batch work. Taking advantage of DB2’s parallelism in your batch jobs could increase the amount of redirect to a zIIP while also using a resource during a time when the usual Distributed Relational Database Architecture (DRDA) redirect is low.
Parallelism can be quite valuable, as it can significantly reduce elapsed times for some batch jobs. It can be a game-saver for warehousing, an area that often sees long-running queries that could benefit from the elapsed time improvements sometimes available with parallelism. Parallelism gets a little boost in performance, reporting, and stability with every new version of DB2; DB2 10 will continue that tradition.