Restrictions for parallelism

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.

Table 1. Checklist of parallel modes and query restrictions

If query uses this...	I/O parallelism 1	CP parallelism	Sysplex parallelism 2	Comments
Parallel access through RID list (list prefetch and multiple index access)	Yes	Yes	No	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	No	No
Queries that qualify for direct row access	No	No	No	Indicated by 'D' in the PRIMARY_ACCESS_TYPE column of PLAN_TABLE
Materialized views or materialized table expressions at reference time	No	Yes	No	'Yes' for CP applies when there is no full outer join.
Security label column on table	Yes	Yes	No
Multi-row fetch	Yes	Yes	Yes	Parallelism is available for multi-row fetch if the cursor is read-only or the query contains a FOR FETCH ONLY clause.
Query blocks that access XML values	No	No	No
Multiple index access to return a DOCID list	No	No	No	Indicated by 'DX', 'DI', or 'DU' in the ACCESSTYPE column of PLAN_TABLE
Outer join result at reference time	No	No	No
CTE at reference time	No	No	No
Table function	No	No	No
Create global temporary table	No	No	No
Parallel access through IN-list	Yes	Yes	No	Indicated by ACCESSTYPE='N' or 'I' in the PLAN_TABLE.
Parallel access through IN-subquery	No	No	No	Indicated by ACCESSTYPE='N' in the PLAN_TABLE.
A DPSI is used to access the fact table in a star-join	No	No	No
Correlated subquery block	No	No	No
Scrollable cursor	No	No	No
Cursor hold with isolation level 'RR' or 'RS'	Yes	No	No
Isolation level 'RR' or 'RS'	Yes	Yes	No
Recursive CTE body	No	No	No
Hash access	No	No	No
Range list access	No	No	No
Reverse index scan	No	No	No
Table locator	No	No	No
Parallel access through a ROWID column	No	No	No
Parallel access through a decimal floating point column	No	No	No
Key range partitioning on timestamp with timezone column	No	No	No
notes: Query I/O parallelism is deprecated and is likely to be removed in a future release. Sysplex query parallelism is deprecated and is likely to be removed in a future release.

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.
• Does not transform certain subqueries to joins.