A Point of Clarification about DB2 9 for z/OS and "Pure" Sequential Prefetch

Many of you have probably heard (I've said it a number of times in presentations myself) that starting with DB2 9 for z/OS "pure" sequential prefetch (what we just called sequential prefetch before dynamic prefetch came along) would only be used for table space scans. In all other situations if DB2 utilized prefetch to access pages of a database object it would be dynamic or list prefetch.

Well, I was out a site running DB2 9 for z/OS recently, and a DBA and I were looking at the output of the DB2 command -DISPLAY BUFFERPOOL(ACTIVE) DETAIL. We were surprised to see an indication of sequential prefetch activity for a buffer pool to which only indexes had been assigned. If pure sequential prefetch were used only for table space scans, how could this be?

I brought this to the attention of a friend of mine in the DB2 for z/OS development organization, and he told me that, in fact, pure sequential prefetch could be used for index access in a DB2 9 environment in a couple of situations: 1) for a multi-index access path (this is sometimes referred to as index ANDing or ORing), and 2) for index access by a utility (for example, RUNSTATS or CHECK INDEX).

So, it turns out that in a DB2 9 system, pure sequential prefetch is used ALMOST exclusively for table space scans. You could indeed see some pure sequential prefetch activity for indexes.

Speaking of indexes and prefetch, I'll remind you of a nice enhancement delivered with DB2 10 for z/OS: namely, the ability of DB2 to utilize list prefetch to access the leaf pages of an index when performing an index scan operation. This can deliver a major improvement in elapsed time for queries, particularly when the index in question is disorganized (in which case you'd see a lot of synchronous I/Os to retrieve leaf pages in a DB2 9 or DB2 V8 system). You shouldn't be surprised, then, to see, in -DISPLAY BUFFERPOOL output or via your DB2 monitor in a DB2 10 system, list prefetch activity for a buffer pool to which only indexes are assigned (for prior releases of DB2 list prefetch is associated with access to table space pages).