Queries cannot take advantage of parallelism unless you
enable parallel processing.
Before you begin
DB2® must be running on a central processor complex that contains two or more tightly coupled processors (sometimes called central processors, or CPs). If only one CP is online when the query is bound, DB2 considers only parallel I/O operations. 
Query
I/O parallelism is deprecated and is likely to be removed in a future
release.
DB2 considers only parallel I/O operations if you declare a cursor as WITH HOLD and bind the application with RR or RS isolation.
Procedure
To enable parallel processing:
- For static SQL, specify DEGREE(ANY) on BIND or REBIND. This bind option affects static SQL only and does not enable
parallelism for dynamic statements.
- For dynamic SQL, set the CURRENT DEGREE special register
to 'ANY'.
Setting the special register affects dynamic statements only.
It has no effect on your static SQL statements. You must also make
sure that parallelism is not disabled for your plan, package, or authorization
ID in the RLST.
- If you bind with isolation CS, choose also the option CURRENTDATA(NO),
if possible. This option can improve performance in general,
but it also ensures that DB2 considers
parallelism for ambiguous cursors. If you bind with CURRENTDATA(YES)
and DB2 cannot
tell if the cursor is read-only, DB2 does
not consider parallelism. When a cursor is read-only, it is best to
explicitly specify that the cursor is read-only. You can use the FOR
FETCH ONLY or FOR READ ONLY clause on the DECLARE CURSOR statement.
- Specify a virtual buffer pool parallel sequential threshold
(VPPSEQT) value that is large enough to provide adequate buffer pool
space for parallel processing. If you enable parallel
processing, multiple parallel tasks can be activated if DB2 estimates that high elapsed times can be
reduced.
- For parallel sorts, allocate sufficient work files to maintain
performance. DB2 also
considers only parallel I/O operations if you declare a cursor WITH
HOLD and bind with isolation RR or RS.
- For complex queries, run the query in parallel within a
member of a data sharing group. With
Sysplex query parallelism, use the power of the data sharing group
to process individual complex queries on many members of the data
sharing group.
- Limit the degree of parallelism. To limit the maximum number of parallel tasks that DB2
generates, you can set the value of the PARAMDEG subsystem parameter.
If system resources are limited, the best value of MAX DEGREE is 1 - 2 times the number
of online CPUs. However, do not change the value PARAMDEG subsystem parameter value to disable
parallelism. Instead, use the DEGREE bind parameter or CURRENT DEGREE special register to disable
parallelism.
You can set the special register
with the following SQL statement: