If you want to get something done fast, get a friend to help. Usually, two can get a lot more done than one, especially when you’re trying to reduce how long something will take to complete. The same applies to DB2’s queries. If you want a query to complete faster, have a bunch of DB2’s friends (processors) help out. The more processor friends you get involved, the faster that query will complete. Of course, other factors can influence the impact of letting a bunch of processors attack the same query.
DB2’s query parallelism targets I/O-intensive queries (i.e., table space scans and large index scans) and Central Processor- (CP-) intensive queries (i.e., joins, sorts, and complex expressions). Its objective is to reduce the overall elapsed time of a query by taking advantage of available I/O bandwidth and processor power. This seemingly easy fix for long query elapsed times is one reason parallelism is considered such a perfect companion to data warehousing. Splitting a query across multiple processors doing multiple concurrent I/Os is among the most straightforward ways of reducing the elapsed time of a long-running query. You can have a query run across two or more general purpose engines and redirect some portion of the work to an IBM System z Integrated Information Processor (zIIP) specialty engine, if available, with no application code changes.
Without parallelism, a query processes data sequentially. If the data is in a partitioned table space, that means processing occurs one partition at a time even though the data is split across partitions, or multiple independent physical data sets. The beauty of partitioning is that it gives the application the ability to process each partition independently. With parallelism turned on, DB2 can process each partition simultaneously, in parallel, using multiple CPs to reduce the overall elapsed time of the query while minimizing the additional CPU overhead for parallelism.
Parallelism comes in three different types:
- I/O query parallelism was the first variety of parallelism. It was delivered in DB2 Version 3 and allowed a single CP to process multiple I/Os—fetching multiple pages into the buffer pool in parallel. Today, I/O parallelism is infrequently observed. I/O parallelism is also not zIIP-eligible.
- CPU query parallelism became available in DB2 V4 and is by far the most common form of parallelism DB2 uses. This method breaks down a query into multiple parts; each part runs on a different general purpose processor and zIIP specialty engine if one is enabled. Each processor running its portion of a query can also perform I/O processing in parallel.
- Sysplex query parallelism, introduced with DB2 V5, spreads a query across multiple processors and can take advantage of processors available to other DB2 members of a data sharing group. There are additional DSNZPARMs and buffer pool thresholds that must be set before you can take advantage of Sysplex query parallelism.
DB2 parallelism isn’t a given; it isn’t “just available” in DB2. You must perform several actions before the optimizer decides to consider parallelism as an access path. First, all three forms of parallelism require that DB2 knows parallelism should be considered for a package or SQL statement. In a dynamic SQL environment (the SQL type most likely used in a data warehousing environment), the special register “CURRENT DEGREE” is used to enable/disable parallelism. If the CURRENT DEGREE register is set to “1” or “1 “, parallelism is disabled; it’s not available as an access path choice. If CURRENT DEGREE is set to “ANY,” then parallelism is enabled. The default value for CURRENT DEGREE is set on the installation panel DSNTIP8 or by the DSNZPARM keyword CDSSRDEF on the DSN6SPRM macro. Whatever value is set as the DB2 subsystem default, the value of the CURRENT DEGREE special register can be modified (overridden) via the SET CURRENT DEGREE SQL statement. For static SQL, a BIND or REBIND of a package specifying the DEGREE keyword can be used to set the CURRENT DEGREE special register for that package instance.
The default for CURRENT DEGREE normally should be set to “1,” disabling parallelism. Parallelism should be enabled on a per-task basis to ensure valuable CPU resources aren’t wasted.
The next parallelism control to consider is the number of CPs DB2 will be allowed to use. MAX DEGREE on the installation panel DSNTIP8, or the DSNZPARM keyword PARAMDEG on the DSN6SPRM macro, can be used to set the maximum number of CPs DB2 can use for CPU query parallelism. The default for this value is zero, which allows DB2 to choose the degree of parallelism. Although using 0 can simplify things, you should take time to determine the best value for MAX DEGREE. A guideline for choosing a starting point for MAX DEGREE is to choose a value somewhere midpoint between the maximum number of CPs available and the maximum number of partitions that will be processed. If you believe the queries will tend to be more on the CPU-intensive side, make this value closer to the number of CPs you have available. If the queries will be more I/O-intensive, make this number consistent with the number of partitions. Monitor and adjust accordingly. If the default 0 is accepted, be aware that it’s possible to get a degree of parallelism up to the 254 maximum. If multiple concurrent queries all get degrees that high, it could open up a whole different set of problems, not least of which could be available storage.
Once parallelism is enabled, VPPSEQT (the parallel sequential threshold for buffer pools) must also be adjusted to some value greater than zero to actually get the optimizer to consider a query for a parallel access path. The number used for VPPSEQT threshold is a percentage and is 50 percent by default. When specified, it allows for a percentage of the sequential steal threshold (VPSEQT) to be used for parallel processing. VPSEQT is a percentage of the VPSIZE (virtual pool size) value. The default for VPSEQT is 80 percent. If VPSIZE is set to the hypothetical value of 100 pages and VPSEQT is set to 80 percent, then 80 pages will be available from this particular pool for sequential processing. If you set VPPSEQT to 50 (%), then the default 40 pages of the VPSEQT pages are available for parallel processing.
Prior to DB2 9, the optimizer picked the lowest-cost sequential plan and then determined if anything in that plan could be run in parallel. As of DB2 9, that lowest cost figure is determined after parallelism has been considered; this is a significant change from previous versions of DB2.
The biggest contributor to the degree of parallelism that DB2 will pick is the number of partitions. Nothing influences DB2’s parallelism like partitions.