PARAMDEG subsystem parameter considerations

Content

The PARAMDEG subsystem parameter defines the maximum degree of parallelism that can be used for a parallel group.  
When query CPU parallelism is enabled for a given query,  Db2 uses the cost estimate to determine the optimal degree of parallelism.  If the determined degree is larger than the PARAMDEG value, the degree is adjusted at bind or prepare, and also at execution. The PARAMDEG value can be 0 - 254.  The default value 0 means that Db2 determines the maximum degree of parallelism based on the numbers of processors online in the system.   Db2 counts both general purpose processors and zIIPs equally in the number of processors and applies further adjustment to determine the degree.   When the query is considered  I/O bound and the z/OS LPAR is configured with large numbers of both zIIPs and general purpose processors,  it is possible that the parallel degree can be a large number with PARAMDEG=0.    

Although larger numbers of parallel tasks are likely to improve response time, they can also impact the system level performance, for example: 

• Higher real storage usage: Each parallel tasks require its own thread storage (mostly 64 bit ) in addition to parent task.   Systems without enough real storage may observe memory constraint or paging.     
• CPU spikes: A higher degree of parallelism means results in potentially faster execution time,  but at the cost of higher CPU utilization during execution.   To lower the cost of parallel queries,   80% and 100% of parallel tasks are zIIP eligible in Db2 11 and Db2 12 respectively.   An aggressive degree of parallelism could starve zIIPs and may impact other zIIP eligible tasks or impact general purpose processor usage.     
• Coupling Facility impact: If the table spaces or index spaces become GBP dependent in a data sharing environment,  concurrent prefetch requests from parallel tasks may cause large numbers of Coupling Facility accesses -  such as Register Page List (RPL) requests.  A surge of CF requests may cause a performance impact to structures located in the same CF.   

The impact of large values of parallel degree is highly dependent on the complexity of queries and the system configuration, such as memory allocation and the numbers of zIIPs and GPs.

A general recommendation is to set PARAMDEG conservatively to lower the possible impact.   If you have more than 2 zIIPs per LPAR,  use the number of zIIPs as the starting point for the PARAMDEG value; then adjust as needed for your response time requirements.