Parallel table scan method (available only when the DB2 UDB Symmetric Multiprocessing feature is installed)

DB2 UDB for AS/400 can use this parallel access method to shorten the processing time that is required for long-running table scan queries. The parallel table scan method reduces the I/O processing time like the parallel table prefetch access method. In addition, if running on a system that has more than one processor, this method can reduce the elapsed time of a query by splitting the table scan processing into tasks that can be run on the multiple processors simultaneously. All selection and column processing is performed in the task. The application's job schedules the work requests to the tasks and merges the results into the result buffer that is returned to the application.

Where the parallel table scan access method is most effective

This method is most effective when the following are true:

• The data is spread across multiple disk devices.
• The system has multiple processors that are available.
• There is an ample amount of main storage available to hold the data buffers and result buffers.

How the query optimizer selects queries that use this method

As mentioned previously, DB2 UDB for AS/400 automatically spreads the data across the disk devices without user intervention, allowing the database manager to pre-fetch table data in parallel.

The query optimizer selects the candidate queries that can take advantage of this type of implementation. The optimizer selects the candidates by estimating the CPU time required to process the query and comparing the estimate to the amount of time required for input processing. The optimizer reduces its estimated elapsed time for table scan based on the number of tasks it calculates should be used. It calculates the number of tasks based on the number of processors in the system, the amount of memory available in the job's pool, and the current value of the DEGREE query attribute. If the parallel table scan is the fastest access method, it is then chosen.

Processing requirements

Parallel table scan requires that SMP parallel processing must be enabled either by the system value QQRYDEGREE, the query option 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.

Parallel table scan 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 table scan cannot normally 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 table scan 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 storage 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 the Work Management book.

Parallel table scan requires active memory to buffer the data that is being retrieved, and to separate result buffers for each task. A typical total amount of memory that is needed for each task is about 2 megabytes. For example, about 8 megabytes of memory must be available in order to use 4 parallel table scan tasks concurrently. Increasing the amount of available memory in the pool allows more input streams to be used. Queries that access tables with large varying length character columns, or queries that generate result values that are larger than the actual row length of the table might require more memory for each task.

The performance of parallel table scan can be severely limited if numerous row locking conflicts or data mapping errors occur.