Parallel index scan-key positioning access method (available only when the DB2 UDB Symmetric Multiprocessing feature is installed)

Using the parallel index scan-key positioning access method, the existing key ranges are processed by separate tasks concurrently in separate database tasks. The number of concurrent tasks is controlled by the optimizer. The query will start processing the key ranges of the query up to the degree of parallelism being used. As processing of those ranges completes, the next ones on the list are started. As processing for a range completes and there are no more ranges in the list to process, ranges that still have keys left to process are split, just as in the parallel index scan-key selection method. The database manager attempts to keep all of the tasks that are being used busy, each processing a separate key range. Whether using the single value, range of values, or multi-range index scan-key positioning, the ranges can be further partitioned and processed simultaneously. Because the keys are not processed in order, this method can not be used by the optimizer if the index is being used for ordering.

How the query optimizer uses this method

Consider the following example if the SQL statement is run using parallel degree of four.

    DECLARE BROWSE2 CURSOR FOR
      SELECT * FROM EMPLOYEE
      WHERE   (WORKDEPT = 'D11' AND FIRSTNME = 'DAVID')
           OR (WORKDEPT = 'D11' AND FIRSTNME = 'BRUCE')
           OR (WORKDEPT = 'D11' AND FIRSTNME = 'WILLIAM')
           OR (WORKDEPT = 'E11' AND FIRSTNME = 'PHILIP')
           OR (WORKDEPT = 'E11' AND FIRSTNME = 'MAUDE')
           OR (WORKDEPT = 'A00' AND FIRSTNME BETWEEN
                                            'CHRISTINE' AND 'DELORES')
           OR (WORKDEPT = 'C01' AND FIRSTNME BETWEEN
                                            'CHRISTINE' AND 'DELORES')
      OPTIMIZE FOR 99999 ROWS

OPNQRYF example:

OPNQRYF FILE((EMPLOYEE))
  QRYSLT('(WORKDEPT *EQ ''D11'' *AND FIRSTNME *EQ ''DAVID'')
  *OR (WORKDEPT *EQ ''D11'' *AND FIRSTNME *EQ ''BRUCE'')
  *OR (WORKDEPT *EQ ''D11'' *AND FIRSTNME *EQ ''WILLIAM'')
  *OR (WORKDEPT *EQ ''E11'' *AND FIRSTNME *EQ ''PHILIP'')
  *OR (WORKDEPT *EQ ''E11'' *AND FIRSTNME *EQ ''MAUDE'')
  *OR (WORKDEPT *EQ ''A00'' *AND 
  FIRSTNME*EQ %RANGE(''CHRISTINE'' ''DELORES''))
  *OR (WORKDEPT *EQ ''C01'' *AND
  FIRSTNME *EQ %RANGE(''CHRISTINE'' ''DELORES''))') 

The key ranges the database manager starts with are as follows:

      Index X3 Start value       Index X3 Stop value
Range 1   'D11DAVID'                 'D11DAVID'
Range 2   'D11BRUCE'                 'D11BRUCE'
Range 3   'D11WILLIAM'               'D11WILLIAM'
Range 4   'E11MAUDE'                 'E11MAUDE'
Range 5   'E11PHILIP'                'E11PHILIP'
Range 6   'A00CHRISTINE'             'A00DELORES'
Range 7   'C01CHRISTINE'             'C01DELORES'

Ranges 1 to 4 are processed concurrently in separate tasks. As soon as one of those four completes, range 5 is started. When another range completes, range 6 is started, and so on. When one of the four ranges in progress completes and there are no more new ones in the list to start, the remaining work left in one of the other key ranges is split and each half is processed separately.

Processing requirements

Parallel index scan-key positioning cannot be used for queries that require any of the following:

• Specification of the *ALL commitment control level.
• Nested loop join implementation. See Nested loop join implementation.
• Backward scrolling. For example, parallel index scan-key positioning cannot be used for queries defined by the Open Query File (OPNQRYF) command, which specify ALWCPYDTA(*YES) or ALWCPYDTA(*NO), because the application might attempt to position to the last row and retrieve previous rows. SQL-defined queries that are not defined as scrollable can use this method. Parallel index scan-key positioning can be used during the creation of a temporary result, such as a sort or hash operation, no matter what interface was used to define the query. OPNQRYF can be defined as not scrollable by specifying the *OPTIMIZE parameter value for the ALWCPYDTA parameter, which enables the usage of most of the parallel access methods.
• Restoration of the cursor position. For instance, a query requiring that the cursor position be restored as the result of the SQL ROLLBACK HOLD statement or the ROLLBACK CL command. SQL applications using a commitment control level other than *NONE should specify *ALLREAD as the value for precompiler parameter ALWBLK to allow this method to be used.
• Update or delete capability.

You should run the job in a shared pool with the *CALC paging option as this will cause more efficient use of active memory. For more information on the paging option see the "Automatic System Tuning" section of Work Management book.

Parallel index scan-key selection requires that SMP parallel processing be enabled either by the system value QQRYDEGREE, by the query options file PARALLEL_DEGREE option, or by the DEGREE parameter on the Change Query Attributes (CHGQRYA) command. See "Control parallel processing for queries" for information on how to control parallel processing.