Parallel processing

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

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.

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
• Multi-row fetch, if the cursor is declared as read-only or the statement uses FOR FETCH ONLY

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.