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.

Begin program-specific programming interface information.

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 parallelism1 CP parallelism Comments
Parallel access through RID list (list prefetch and multiple index access) Yes 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 No  
Queries that qualify for direct row access 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 'Yes' for CP applies when there is no full outer join.
Security label column on table Yes Yes  
Query blocks that access XML values No No  
Query blocks that reference array values No No  
Multiple index access to return a DOCID list No No Indicated by 'DX', 'DI', or 'DU' in the ACCESSTYPE column of PLAN_TABLE
Outer join result at reference time No No  
CTE at reference time No No  
Table function No No  
Create global temporary table No No  
Parallel access through IN-list Yes Yes Indicated by ACCESSTYPE='N' or 'I' in the PLAN_TABLE.
Parallel access through IN-subquery 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  
Autonomous procedures No No  
Correlated subquery block No No  
Scrollable cursor No No  
Cursor hold with isolation level 'RR' or 'RS' Yes No  
Isolation level 'RR' or 'RS' Yes Yes  
Recursive CTE body No No  
Hash access No No  
Range list access No No  
Reverse index scan No No  
Table locator No No  
Parallel access through a ROWID column No No  
Parallel access through a decimal floating point column No No  
notes:
  1. Query I/O 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.

End program-specific programming interface information.