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

For the parallel index scan-key selection access method, the possible key values are logically partitioned. Each partition is processed by a separate task just as in the index scan-key selection access method. The number of partitions processed concurrently is determined by the query optimizer. Because the keys are not processed in order, this method cannot be used by the optimizer if the index is being used for ordering. Key partitions that contain a larger portion of the existing keys from the index are further split as processing of other partitions complete.

Where the parallel index scan-key selection access method is most effective

The following example illustrates a query where the optimizer could choose the index scan-key selection method:

    CREATE INDEX X1 ON EMPLOYEE(LASTNAME,WORKDEPT)
 
    DECLARE BROWSE2 CURSOR FOR
    SELECT * FROM EMPLOYEE
    WHERE WORKDEPT = 'E01'
    OPTIMIZE FOR 99999 ROWS

OPNQRYF example:

OPNQRYF FILE((EMPLOYEE)) 
          QRYSLT('WORKDEPT *EQ ''E01''')

If the optimizer chooses to run this query in parallel with a degree of four, the following might be the logical key partitions that get processed concurrently:

LASTNAME values            LASTNAME values
leading character          leading character
partition start            partition end
   'A'                          'F'
   'G'                          'L'
   'M'                          'S'
   'T'                          'Z'

If there were fewer keys in the first and second partition, processing of those key values would complete sooner than the third and fourth partitions. After the first two partitions are finished, the remaining key values in the last two might be further split. The following shows the four partitions that might be processed after the first and second partition are finished and the splits have occurred:

LASTNAME values            LASTNAME values
leading character          leading character
partition start            partition end
   'O'                          'P'
   'Q'                          'S'
   'V'                          'W'
   'X'                          'Z'

Processing requirements

Parallel index scan-key selection 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 selection 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. 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. SQL defined queries that are not defined as scrollable can use this method. Parallel index scan-key selection 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.
• 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 capabilitiy.

You should run the job in a shared pool with *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 the Work Management book.

Parallel index scan-key selection requires that SMP parallel processing be enabled either by the system value QQRYDEGREE, the query options file, 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.