To determine if sequential detection was used, check the accounting IFCID 0003 or the performance IFCID 0006 records.
Although there seems to be less emphasis today on specify RELEASE (DEALLOCATE) at bind time, specifying RELEASE (DEALLAOCATE) does ensure that the sequential detection counters aren’t reset at each commit point. In a batch job with numerous commits, using RELEASE (COMMIT) could impact the batch application’s performance by negating the benefits of sequential detection and reducing the opportunities to take advantage of dynamic prefetch.
List prefetch probably won’t make it too high on your list of favorite access paths if you happen to be trying to obtain sub-second response time for an Online Transaction Processing (OLTP) transaction. List prefetch needs to do a little bit of work before your application gets to see any results. It can really shine in the right situation, such as when your data is clustered in one sequence, but you need to use an index whose key sequence goes completely against your clustering order, or if you just have a low cluster ratio (something less than 80 percent). Data scan via index with an index-screening predicate is another example, even with 100 percent cluster ratio. The 80 percent cluster ratio threshold for list prefetch was eliminated in DB2 9. With list prefetch, used only with an index access, you gather up all of the qualifying Record Identifiers (RIDs) that satisfy the predicates from the index access, sort the RIDs into page order, and then use sequential prefetch (or skip sequential prefetch) to read the noncontiguous but now ordered pages. Because of the faster speeds of today’s disk, and features such as Parallel Access Volume (PAV), the old limit of 180 CIs per I/O for list prefetch was removed in V7 and no limit currently exists.
List prefetch is a mixed bag. On one hand, DB2 can perform a little extra index screening at the RID level, which is a good thing. However, because you’re sorting the RID in page sequences, you lose any use of ordering you might have gained from the index access and will still have to perform a sort.
List prefetch isn’t just used for an unclustered index access. It’s also used:
• To improve restart and recovery times by the process Fast Log Apply (FLA)
• To access the inner table of a hybrid join
• For multiple index joins by getting the qualifying RIDs from each index involved, taking the union or intersection to provide a final RID list, and using that RID list via list prefetch to retrieve all the qualifying (pages).
List prefetch can even be used for reading Large Objects (LOBs) and by an incremental image copy.