Restrictions for parallelism
Parallelism is not used for all queries; for some access paths, incurring parallelism overhead makes no sense. Similarly, certain access paths that would reduce the effectiveness of parallelism are removed from consideration when parallelism is enabled.
When parallelism is not used
For example, if you are selecting from a temporary table, parallelism is not used. Check the following table to determine whether your query uses any of the access paths that do not allow parallelism.
| If query uses this... | CP parallelism | Comments |
|---|---|---|
| Parallel access through RID list (list prefetch and multiple index access) | Yes | Indicated by 'L' in the PREFETCH column of PLAN_TABLE, or an M, MX, MI, or MQ in the ACCESSTYPE column of PLAN_TABLE. |
| Query blocks that access LOB values. | No | |
| Queries that qualify for direct row access | No | Indicated by 'D' in the PRIMARY_ACCESS_TYPE column of PLAN_TABLE |
| Materialized views or materialized table expressions at reference time | Yes | 'Yes' for CP applies when there is no full outer join. |
| Security label column on table | Yes | |
| Query blocks that access XML values | No | |
| Query blocks that reference array values | No | |
| Multiple index access to return a DOCID list | No | Indicated by 'DX', 'DI', or 'DU' in the ACCESSTYPE column of PLAN_TABLE |
| Outer join result at reference time | No | |
| CTE at reference time | No | |
| Table function | No | |
| Create global temporary table | No | |
| Parallel access through IN-list | Yes | Indicated by ACCESSTYPE='N' or 'I' in the PLAN_TABLE. |
| Parallel access through IN-subquery | No | Indicated by ACCESSTYPE='N' in the PLAN_TABLE. |
| A DPSI is used to access the fact table in a star-join | No | |
| Autonomous procedures | No | |
| Correlated subquery block | No | |
| Scrollable cursor | No | |
| Cursor hold with isolation level 'RR' or 'RS' | No | |
| Isolation level 'RR' or 'RS' | Yes | |
| Recursive CTE body | No | |
| Hash access | No | |
| Range list access | No | |
| Reverse index scan | No | |
| Table locator | No | |
| Parallel access through a ROWID column | No | |
| Parallel access through a decimal floating point column | No | |
 Declared temporary tables in partition-by-growth table spaces |
No |
|
Access paths that are restricted by parallelism
To ensure that you can take advantage of parallelism, Db2 does not select certain access paths when parallelism is enabled. When the plan or package is bound with DEGREE(ANY) or the CURRENT DEGREE special register is set to 'ANY,' Db2 does not choose Hybrid joins with SORTN_JOIN=Y.
