12.3 Specifying the prefetch facility
(1) Effects of the prefetch facility
When a large amount of data is to be retrieved using a character special file, the prefetch facility can reduce the input/output time. This facility is especially effective for retrieving data without using an index or for using an index to search a table that contains many data items in ascending order.
(2) Criteria
The prefetch facility can be used to input multiple pages in the batch mode for the following SQL statements and utility:
- For the SELECT, UPDATE, and DELETE statements without using an index, multiple data pages can be input in the batch mode.
- For the SELECT, UPDATE, and DELETE statements (excepting the = and IN conditions) for a search in ascending order using an index, multiple index leaf pages can be input in the batch mode.
- For the SELECT, UPDATE, and DELETE statements (excepting the = and IN conditions) for a search in ascending order using a cluster key, multiple index leaf pages and data pages can be input in batch mode.
- For the database reorganization utility's (pdrorg) unload processing without using a local buffer, multiple index leaf pages and data pages can be input in the batch mode.
(3) Specification
(a) Global buffers
To use the prefetch facility, specify 1 or a greater value in the pdbuffer operand's -m option in the system common definition. Specify the number of pages to be read in batch mode in the pdbuffer operand's -p option.
(b) Local buffers
To use the prefetch facility, use the -p option of the pdlbuffer operand to specify the number of pages for batch input.
(4) Considerations
- When the prefetch facility is used, a buffer dedicated to batch input is obtained separately from the global buffers or local buffers. This results in an increase in the amount of shared memory required for global buffers. For details about the formula for calculating the amount of shared memory used by global buffers, see 16. Storage Requirements for HiRDB.
- Whether or not the prefetch facility is operating effectively can be determined by checking the prefetch hit rate with the statistics analysis utility (pdstedit) or with the pdbufls command.