Methods of parallel processing

The figures in this topic show how the parallel methods compare with sequential prefetch and with each other.

Assume that a query accesses a table space that has three partitions, P1, P2, and P3. The notations P1, P2, and P3 are partitions of a table space. R1, R2, R3, and so on, are requests for sequential prefetch. The combination P2R1, for example, means the first request from partition 2.

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.

Figure 1. CP and I/O processing techniques. Sequential processing.
Begin figure description. CP and I/O operations arranged along two horizontal lines, and an arrow indicates a timeline from left to right. End figure description.

Parallel CP processing

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.

Figure 2. CP and I/O processing techniques. 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.
Begin figure description. CP and I/O operations arranged in horizontal lines: 3 for separate CP tasks and 3 for I/O operations. An arrow indicates time from left to right. End figure description.

Queries that are most likely to take advantage of parallel operations

Begin general-use programming interface information. 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;

    End general-use programming interface information.

  • 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
    Begin general-use programming interface information.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;
    End general-use programming interface information.

Terminology

When the term task is used with information about parallel processing, consider the context. For parallel query CP processing, a task is an actual z/OS® execution unit used to process a query.

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.