Dynamic prefetch (PREFETCH='D')
With dynamic prefetch, Db2 uses a sequential detection algorithm to determine whether data pages are being read sequentially.
Dynamic prefetch offers improved performance over sequential
prefetch, especially when Db2 cannot detect
whether the pages can be accessed sequentially (because the catalog statistics might not always be
correct). When Db2 chooses dynamic
prefetch, the value of the PREFETCH column is normally set to 'D.' However, Db2 might also use dynamic prefetch when the value
of PREFETCH indicates 'S.'
When dynamic prefetch is used
Db2 uses dynamic prefetch to read data pages whenever an index is used to determine which data pages contain the required rows.
Db2 also uses dynamic prefetch to avoid synchronous I/O for index pages when it
scans the leaf pages an index in key-sequential order. An organized index is likely to trigger
dynamic prefetch I/Os. However, when the leaf pages of an index are not well organized, dynamic
prefetch is not triggered, and some synchronous I/O might occur. However, when Db2 detects the synchronous I/OS it can switch to list prefetch for the next
set of leaf pages. In that case, synchronous I/O occurs only for the non-leaf pages. 