Parallel processing

DB2® can initiate multiple parallel operations when it accesses data from a table or index in a partitioned table space.

Query I/O parallelism manages concurrent I/O requests for a single query, fetching pages into the buffer pool in parallel. Start of changeQuery I/O parallelism is deprecated and is likely to be removed in a future release.End of change This processing can significantly improve the performance of I/O-bound queries. I/O parallelism is used only when one of the other parallelism modes cannot be used.

Query CP parallelism enables true multitasking within a query. A large query can be broken into multiple smaller queries. These smaller queries run simultaneously on multiple processors accessing data in parallel, which reduces the elapsed time for a query.

To expand even farther the processing capacity available for processor-intensive queries, DB2 can split a large query across different DB2 members in a data sharing group. This feature is known as Sysplex query parallelism.

DB2 can use parallel operations for processing the following types of operations:

  • Static and dynamic queries
  • Local and remote data access
  • Queries using single table scans and multi-table joins
  • Access through an index, by table space scan or by list prefetch
  • Sort
  • Start of changeMulti-row fetch, if the cursor is declared as read-only or the statement uses FOR FETCH ONLYEnd of change

When a view or table expression is materialized, DB2 generates a temporary work file. This type of work file is shareable in CP mode if there is no full outer join case.

Parallelism for partitioned and nonpartitioned table spaces

Parallel operations usually involve at least one table in a partitioned table space. Scans of large partitioned table spaces have the greatest performance improvements where operations can be carried out in parallel.

Both partitioned, nonpartitioned, and partition-by-growth table spaces can take advantage of query parallelism. Parallelism is enabled to include non-clustering indexes. Thus, table access can be run in parallel when the application is bound with DEGREE (ANY) and the table is accessed through a non-clustering index.