Query CPU Parallelism
DB2 V3.1 provided IO Parallelism -- that is, the ability to start multiple read engines for a single query against a partitioned tablespace. Testing at Norwest showed a 60% reduction in the elapsed time required to scan a large tablespace having 5 partitions, when IO parallelism was used.
Now, DB2 V4.1 provides CPU parallelism -- that is, the ability to decompose a query into multiple operations that can be executed concurrently by multiple CPUs.
DB2 employs both "horizontal" (or "partitioned") parallelism and "vertical" (or "pipelined") parallelism. DB2 can parallelize:
- Static and dynamic queries
- Local and remote data access
- Queries using single-table scans and multi-table joins
- Access through an index, by tablespace scan, or by list prefetch.
- Sort operations
Parallelism is not limited to partitioned tablespaces, although according to IBM partitioned tablespaces show the best performance improvements because data is physically separated and distributed (usually) across many disk devices.
To enable parallelism:
- For static SQL, specify DEGREE(ANY) on BIND or REBIND (the default is DEGREE(1), which prohibits parallel operations). This will have no effect on any dynamic SQL that the program executes.
- For dynamic SQL, set the CURRENT DEGREE special register to ANY by executing the SQL SET statement: SET CURRENT DEGREE = 'ANY'.
Columns have been added to PLAN_TABLE to indicate, in an EXPLAIN, the type and degree of parallel operations performed. But these may be ignored by DB2 at execution time, depending on resource availability.
In any case, do not use CPU parallelism without first consulting with your DBA.
©Copyright 1996 Chuck Anesi all rights reserved