DB2 UDB for AS/400 can also use parallel table prefetch processing to shorten the processing time that is required for long-running, I/O-bound table scan queries.
This method has the same characteristics as the table scan method; however, the I/O processing is done in parallel. This is accomplished by starting multiple input streams for the table to pre-fetch the data.
Where the parallel table prefetch access method is most effective
This method is most effective when the following are true:
DB2 UDB for AS/400 data spreading
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 database manager uses tasks to retrieve data from different disk devices. Usually the request is for an entire extent (contiguous set of data). This improves performance because the disk device can use smooth sequential access to the data. Because of this optimization, parallel prefetch can preload data to active memory faster than the SETOBJACC CL command.
Even though DB2 UDB for AS/400 spreads data across disk devices within an ASP, sometimes the allocation of the dataspace extents may not be spread evenly. This occurs when there is uneven allocation of space on the devices or a new device is added to the ASP. The allocation of the dataspace can be respread by saving, deleting, and restoring the table.
How the query optimizer selects queries that use this method
The query optimizer selects the candidate queries which 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. When the estimated input processing time exceeds the CPU time, the query optimizer indicates that the query may be implemented with parallel I/O.
If DB2 UDB Symmetric Multiprocessing is installed, then the query optimizer usually prefers the DB2 UDB Symmetric Multiprocessing parallel methods.
Processing requirements
Parallel table prefetch requires that input and output 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. Because queries being processed with parallel table prefetch aggressively utilize main store and disk I/O resources, the number of queries that use parallel table prefetch should be limited and controlled. Parallel prefetch utilizes multiple disk arms, but it does little utilization of multiple CPUs for any given query. Parallel prefetch I/O will use I/O resources intensely. Allowing a parallel prefetch query on a system with an overcommitted I/O subsystem may intensify the over-commitment problem.
You should run the job in a shared storage pool with the *CALC paging option because this will cause more efficient use of active memory. DB2 UDB for AS/400 uses the automated system tuner to determine how much memory this process is allowed to use. At run-time, the Licensed Internal Code will allow parallel table prefetch to be used only if the memory statistics indicate that it will not over-commit the memory resources. For more information on the paging option see the "Automatic System Tuning" section of the Work Management book.
Parallel table prefetch requires that enough memory be available to cache the data that is being retrieved by the multiple input streams. For large tables, the typical extent size is 1 megabyte. This means that 2 megabytes of memory must be available in order to use 2 input streams concurrently. Increasing the amount of available memory in the pool allows more input streams to be used. If there is plenty of available memory, the entire dataspace for the table may be loaded into active memory when the query is opened.
PRTSQLINF command messages
The messages created by the PRTSQLINF command to describe a query in an SQL program which is using the parallel table prefetch access method would appear as follows:
SQL4023 Parallel dataspace pre-fetch used.