Methods of parallel processing

Sequential processing

The following figure shows sequential processing. With sequential processing, DB2® takes the three partitions in order, completing partition 1 before starting to process partition 2, and completing 2 before starting 3. Sequential prefetch allows overlap of CP processing with I/O operations, but I/O operations do not overlap with each other. In the example in the following figure, a prefetch request takes longer than the time to process it. The processor is frequently waiting for I/O.

. Sequential processing.

Parallel I/O

Query I/O parallelism is deprecated and is likely to be removed in a future release. The following figure shows parallel I/O operations. With parallel I/O, DB2 manages data from the three partitions at the same time. The processor processes the first request from each partition, then the second request from each partition, and so on. The processor is not waiting for I/O, but there is still only one processing task.

DB2 can use parallel I/O to improve the efficiency of SELECT and INSERT statements. Parallel INSERT I/O is only available on universal table spaces and partitioned table spaces.

. Parallel I/O processing.

Parallel CP processing and sysplex query parallelism

The following figure shows parallel CP processing. With parallel CP processing, DB2 can use multiple parallel tasks to process the query. Three tasks working concurrently can greatly reduce the overall elapsed time for data-intensive and processor-intensive queries. The same principle applies for Sysplex query parallelism, except that the work can cross the boundaries of a single CPC. Sysplex query parallelism is deprecated and is likely to be removed in a future release.

. Query processing using CP parallelism. The tasks can be contained within a single CPC or can be spread out among the members of a data sharing group.

Queries that are most likely to take advantage of parallel operations

Queries that can take advantage of parallel processing are those queries in which:

• DB2 spends most of the time fetching pages—an I/O-intensive query

  A typical I/O-intensive query is something like the following query, assuming that a table space scan is used on many pages:

  SELECT COUNT(*) FROM ACCOUNTS
   WHERE BALANCE > 0 AND
   DAYS_OVERDUE > 30;

  

• DB2 spends processor time and I/O time to process rows for certain types of queries. Those queries include:

  Queries with intensive data scans and high selectivity

  Those queries involve large volumes of data to be scanned but relatively few rows that meet the search criteria.

  Queries that contain aggregate functions

  Column functions (such as MIN, MAX, SUM, AVG, and COUNT) typically involve large amounts of data to be scanned but return only a single aggregate result.

  Queries that access long data rows

  Those queries access tables with long data rows, and the ratio of rows per page is low (one row per page, for example).

  Queries that require large amounts of central processor time

  

  Those queries might be read-only queries that are complex, data-intensive, or that involve a sort. For example, A typical processor-intensive query is something like:

  SELECT MAX(QTY_ON_HAND) AS MAX_ON_HAND,
    AVG(PRICE) AS AVG_PRICE,
    AVG(DISCOUNTED_PRICE) AS DISC_PRICE,
    SUM(TAX) AS SUM_TAX,
    SUM(QTY_SOLD) AS SUM_QTY_SOLD,
    SUM(QTY_ON_HAND - QTY_BROKEN) AS QTY_GOOD,
    AVG(DISCOUNT) AS AVG_DISCOUNT,
    ORDERSTATUS,
    COUNT(*) AS COUNT_ORDERS
  FROM   ORDER_TABLE
  WHERE SHIPPER = 'OVERNIGHT' AND
        SHIP_DATE < DATE('2006-01-01')
  GROUP BY ORDERSTATUS
  ORDER BY ORDERSTATUS;

  

Terminology

When the term task is used with information about parallel processing, consider the context.For parallel query CP processing or Sysplex query parallelism, a task is an actual z/OS® execution unit used to process a query. For parallel I/O processing, a task simply refers to the processing of one of the concurrent I/O streams.

A parallel group is the term used to name a particular set of parallel operations. A query can have more than one parallel group, but each parallel group within the query is identified by its own unique ID number.

The degree of parallelism is the number of parallel tasks that DB2 determines can be used for the operations on the parallel group. The maximum of parallel operations that DB2 can generate is 254. However, for most queries and DB2 environments, DB2 chooses a lower number.

You might need to limit the maximum number further because more parallel operations consume processor, real storage, and I/O resources. If resource consumption in high in your parallelism environment, use the value of the PARAMDEG subsystem parameter to limit the maximum number of parallel operations.

In a parallel group, an originating task is the TCB (SRB for distributed requests) that coordinates the work of all the parallel tasks. Parallel tasks are executable units composed of special SRBs, which are called preemptable SRBs.

With preemptable SRBs, the z/OS dispatcher can interrupt a task at any time to run other work at the same or higher dispatching priority. For non-distributed parallel work, parallel tasks run under a type of preemptable SRB called a client SRB, which lets the parallel task inherit the importance of the originating address space. For distributed requests, the parallel tasks run under a preemptable SRB called an enclave SRB.