This month is all about going parallel! In the Db2 world, we have had the ability to run SQLs using parallel processing for decades. It started off a bit wobbly and most people didn’t use it, or even like it, but these days it is extremely useful for certain cases.
Sort Yourself Out
Sort is very important here. If you can do a parallel sort with only one extra parallel task it will halve your elapsed time… if you can add more tasks it takes even less elapsed. Naturally, you do not save on CPU here, in fact it will probably “cost” more, but you are trading CPU for elapsed time.
Ground Rules
IBM states in the documentation that parallel processing is “Only for partitioned objects” but then they mention that even non-partitioned objects can benefit, as the access to the non-clustering index and the data can be done in parallel… which is not helpful if you only have clustering index access, of course!
Bells and Whistles!
There are quite a few things to adjust and play with on the road to parallel processing!
No Way!
If you declare your cursor as WITH HOLD and with isolation level RR or RS then there is *no* CPU parallelism allowed at all but you can still get parallel sorts.
ZPARM Time
First up is CDSSRDEF (CURRENT DEGREE) where the IBM recommendation must be read:
CURRENT DEGREE field (CDSSRDEF subsystem parameter)
The CDSSRDEF subsystem parameter determines the default value that is to be used for the CURRENT DEGREE special register. The default value is used when a degree is not explicitly set in the SQL statement SET CURRENT DEGREE.
Acceptable values: 1, ANY
Default: 1
Update: option 30 on panel DSNTIPB
DSNZPxxx: DSN6SPRM CDSSRDEF
1 Specifies that when a query is dynamically prepared, the execution of that query will not use parallelism. If this value is specified, Db2 does not use any optimization hints for parallelism.
ANY Specifies that when a query is dynamically prepared, the execution of that query can involve parallelism.
Recommendation: In almost all situations, accept the default value of 1. You should use parallelism selectively where it provides value, rather than globally. Although parallelism can provide a substantial reduction in elapsed time for some queries with only a modest overhead in processing time, parallelism does not always provide the intended benefit. For some queries, and in many other situations, query parallelism does not provide an improvement, or it uses too many resources. If you are using nearly all of your CPU, I/O, or storage resources, parallelism is more likely to cause degradation of performance. Use parallelism only where it is most likely to provide benefits.
The ZPARM INDEX_IO_PARALLELISM should get an honorable mention here as it was there right up until Db2 11 and it is, in fact, still within the Db2 12 and 13 indexes at the end of the installation guide pdf, but it has thrown off its mortal coil…
INDEX_IO_PARALLELISM
Specifies whether I/O parallelism is enabled for index insertion.
Acceptable values: YES, NO
Default: YES
DSNZPxxx: DSN6SPRM INDEX_IO_PARALLELISM
Security parameter: No
YES I/O parallelism is enabled for index processing. I/O parallelism allows concurrent insert operations on multiple indexes and can reduce I/O wait time when many indexes are defined in a table.
NO I/O parallelism is disabled for index processing.
Naturally, YES is what it should be set to!
To round out the dearly departed, there was also this one:
PARALLELISM EFFICIENCY field (PARA_EFF subsystem parameter)
Controls the efficiency that DB2 assumes for parallelism when DB2 chooses an access path. Valid values are integers 0 – 100. The integer represents a percentage efficiency.
It came in Db2 9 with PM16020 and started life with a default value of 100 before getting a default change to 50 in Db2 10. It got deprecated in Db2 12 and was removed in Db2 13.
Then PARAMDEG with a handy Top Tip:
MAX DEGREE field (PARAMDEG subsystem parameter)
The PARAMDEG subsystem parameter specifies the maximum degree of parallelism for a parallel group. When you specify a non-zero value for this parameter, you limit the degree of parallelism so that Db2 cannot create too many parallel tasks that use virtual storage.
Acceptable values: 0 – 254
Default: 0
Update: option 30 on panel DSNTIPB
DSNZPxxx: DSN6SPRM PARAMDEG
0 Specifies no limit to the maximum degree of parallelism that Db2 chooses based on the cost estimate for the query and the system configuration, in particular the number of processors online. Db2 counts both general purpose and zIIP processors equally, and applies further adjustment to determine the degree to use.
1 – 254 Specifies the maximum degree of parallelism that Db2 uses. When optimization hints for parallelism are used, the value of the PARAMDEG subsystem parameter does not limit the degree of parallelism at bind time. However, the value of the PARAMDEG subsystem parameter is enforced at execution time. So, if the value of the PARAMDEG subsystem parameter is lower than the degree of parallelism that is specified at bind time, the degree of parallelism is reduced at execution time.
Tip: For systems with more than two zIIP processors configured, use the number of zIIP processors as the starting value, and then adjust as needed for your response time requirements.
Basically, set this value to be one or two times the number of online available CPUs but take into account the ZiiPs.
Then its new DPSI baby brother:
MAX DEGREE FOR DPSI (PARAMDEG_DPSI subsystem parameter)
The PARAMDEG_DPSI subsystem parameter specifies the maximum degree of parallelism that you can specify for a parallel group in which a data partitioned secondary index (DPSI) is used to drive parallelism.
A DPSI is a non-partitioning index that is physically partitioned according to the partitioning scheme of the table. When you specify a value of greater than 0 for this parameter, you limit the degree of parallelism for DPSIs so that Db2 does not create too many parallel tasks that use virtual storage.
Acceptable values: 0-254, DISABLE
Default: 0
Update: option 30 on panel DSNTIPB
DSNZPxxx: DSN6SPRM PARAMDEG_DPSI
Data sharing scope: All members use the same setting
0 Specifies that Db2 uses the value that is specified for the PARAMDEG subsystem parameter, instead of PARAMDEG_DPSI, to control the degree of parallelism when DPSI is used to drive parallelism. This is the default value for the field.
1 Specifies that Db2 creates multiple child tasks but works on one task at a time when DPSI is used to drive parallelism.
2-254 Specifies that Db2 creates multiple child tasks and works concurrently on the tasks that are specified. The number of specified tasks may be larger or smaller than the number of tasks as specified in PARAMDEG. When PARAMDEG is set to 1, the rest of the query does not have any parallelism.
DISABLE Specifies that Db2 does not use DPSI to drive parallelism. Parallelism might still occur for the query if PARAMDEG is greater than 1.
This is for fine-tuning the DPSI use case. Remember, you can have 4096 partitions and so it could well be that a query goes crazy if it sees the ability to go massively parallel. Here you can limit, or even inhibit, that from happening. Only use if you have been bitten by a rogue SQL!
Then we get to Utility parallel processing which is not for all Utilities – but REORG TABLESPACE and COPY are there!
MAX UTILS PARALLELISM field (PARAMDEG_UTIL subsystem parameter)
The PARAMDEG_UTIL subsystem parameter specifies the maximum number of parallel subtasks for some utilities.
PARAMDEG_UTIL affects the following utilities:
• REORG TABLESPACE
• REBUILD INDEX
• CHECK INDEX
• UNLOAD
• LOAD
• COPY
• RECOVER
Acceptable values: 0 – 32767
Default: 99
Update: option 34 on panel DSNTIPB
DSNZPxxx: DSN6SPRM PARAMDEG_UTIL
0 No additional constraint is placed on the maximum degree of parallelism in a utility.
1 – 32767 Specifies the maximum number of parallel subtasks for all affected utilities.
Interesting default huh? In Db2 11 it was actually zero!
LOAD must get a special mention here, as back in Db2 11 it got a new keyword PARALLEL
( nnn ) which enabled parallel loading from a *single* input file (Initially not for PBGs but then that was allowed in Db2 12). This little chestnut has often been forgotten.
Further into the guts of REORG is this little one:
REORG LIST PROCESSING field (REORG_LIST_PROCESSING subsystem parameter)
The REORG_LIST_PROCESSING subsystem parameter specifies the default setting for the PARALLEL option of the Db2 REORG TABLESPACE utility.
Acceptable values: PARALLEL, SERIAL
Default: PARALLEL
Update: option 37 on panel DSNTIPB
DSNZPxxx: DSN6SPRM REORG_LIST_PROCESSING
PARALLEL The default value PARALLEL specifies that the REORG TABLESPACE utility is to use a default PARALLEL YES option when the PARALLEL keyword is not specified in the utility control statement. The PARALLEL YES option specifies that the REORG TABLESPACE utility is to process all partitions that are specified in the input LISTDEF statement in a single execution of the utility.
SERIAL Specifies that the REORG TABLESPACE utility is to use a default PARALLEL NO option when the PARALLEL keyword is not specified in the utility control statement. The PARALLEL NO option specifies that each partition that is specified in the input LISTDEF statement is to be processed in a separate execution of the utility.
I would also happily stick with the default unless you have experienced serious problems with VTS or some such.
Don’t Forget the Bufferpool, Stupid!
BUFFERPOOLs play a major role. The setting of VPSEQT (Virtual bufferpool sequential steal threshold) and VPPSEQT (Virtual bufferpool parallel sequential threshold) might well both have to be raised. Remember that the VPPSEQT is a percentage of the VPSEQT available pages. The bufferpool size itself might well have to be raised (VPSIZE) if you do not see an improvement in the degree of parallelism.
All Ready?
So, you have set, checked, reviewed, changed the ZPARMS and are ready to go?
Where’s the ON Switch?
For Static SQL just bind or rebind with DEGREE(ANY), for dynamic SQL issue a
SET CURRENT DEGREE = ‘ANY’ ;
CDSSRDEF Zparm has the default for this parameter.
If you bind with isolation level CS, then also try and make sure you use CURRENTDATA(NO) as well. This helps performance anyway and also aids Db2 in working with ambiguous cursors. Explicit read-only is always better!
For sorts, where the big elapsed time gains come from, make sure you have sufficiently sized work files allocated! Here, even the WITH HOLD and isolation RR or RS can benefit.
Gotchas?
Always at least one problem isn’t there? If you do DEGREE(ANY) you can expect the EDMPOOL usage to go up between 50% and 70% due to run-time structures. Check your SYSPACKAGE AVGSIZE before and after the BIND/REBIND if you are worried. Always monitor this pool and make sure it is correctly sized for your workload!
Naturally, the CPU might well go up but you should see a good drop in elapsed times and, as far as sort is concerned, you might actually manage a successful parallel sort in a normally constrained sub-system!
Where’s the OFF Switch?
For Static SQL just bind or rebind with DEGREE(1), for dynamic SQL issue a
SET CURRENT DEGREE = ‘1’;
This is also the default value but what if someone changed your default?
If you shrink the VPPSEQT to 0 that will disable all parallel access for objects in that bufferpool.
Insert rows in the resource limit tables – Not recommended as this is a lot of work!
Are You Running in Parallel then? EXPLAIN is Your Friend!
There are a few “access patterns” that allow CP parallelism and they are all documented in the Managing Performance book – “Checklist of query restrictions for query CP parallelism” table.
How do You Check?
The columns of interest in the PLAN_TABLE are the ACCESS_DEGREE and JOIN_DEGREE. If either of these two is not NULL then you are using, or hoping to use, parallel processing! The moment you have more than one table then four other columns become interesting: ACCESS_PGROUP_ID, JOIN_PGROUP_ID, SORTN_PGROUP_ID and SORTC_PGROUP_ID. PGROUP is short for PARALLEL GROUP and for a given PLANNO step they all have the same number. Finally, the PARALLELISM_MODE reflects which type you are using which, these days, can *only* be ‘C’ for Query CP parallelism (Came in DB2 V4). It used to also have the values ‘I’ for parallel I/O operations (Came in DB2 V3) and ‘X’ for Sysplex query parallelism (Came in DB2 V5) but they were deprecated in Db2 9 and are now both dead and buried!
Which SQLs are Best for Parallel Processing?
SQLs that are I/O intensive and scan lots of pages while returning just a few rows, SQLs that have lots of aggregate functions, and naturally SQLs that require Sort – all of these are good candidates.
Trial it DUMMY!
Best thing to do is a trial rebind to a dummy collection of all your SQL in a sandbox system with production statistics copied over and with parallel processing enabled (Do not forget the bufferpools!). This will then quickly reveal which queries could indeed benefit from going parallel and enable you to activate it only at the package/SQL level that you require in production.
Apply and Test
Once you have your candidate list, and I hope it is not that long, you can simply enable it all in production and do a live run through and review. First with EXPLAIN and then really live.
Remember that it is not good for *all* SQLs but it can really help when it hits the spot!
My Favorite Table Ever!
Db2 11 introduced an automatic five level control system for parallel queries:
Level 1 OK: Query runs with planned parallel degree
Level 2 Mild warning: Reduce parallel degree by ¼
Level 3 Moderate warning: Reduce parallel degree by ½ or to degree 2
Level 4 Severe warning: Reduce to sequential run
Level 5 Melt down: Reduce to sequential run
Level 5 is sub-optimal!
The original table is here: (Do a search for “Melt”)
https://www.redbooks.ibm.com/redbooks/pdfs/sg248222.pdf
How to Tame the Beast?
For static SQL, just bind/rebind with DEGREE(1) to switch off or DEGREE(ANY) to switch on.
For dynamic SQL, if you cannot add the SET CURRENT DEGREE = ‘ANY’ and RLF tables do not work for you then the only way is to assign the tables in question to their own bufferpools and set the VPPSEQT to a value, or leave at default 50, and for *all* other bufferpools set the VPPSEQT to 0 which switches off parallel processing.
Another way of handling dynamic, but a bit over the top for my taste, is a new data-sharing member where the ZPARM CDSSRDEF is set to ‘ANY’. Then any dynamic work that should be allowed to go parallel is simply routed to just this member.
Whaddya all think?
Going to start testing out parallel processing anytime soon?
I’d love to hear from you!
TTFN
Roy Boxwell
Feedback:
One of my readers mentioned that the primary reason they use parallelism is to increase zIIP offload which offers not just elapsed reduction but also saves the cost of the cpu as well.
Naturally, if you get this, then you are really laughing all the way to the bank!