Mainframe, MVS and zOS Discussion

Hi all,

Reading about DB2 internals and performance metrics has always been my favourite topic of interest. I recently read a column on Sequential Prefetch, one of the coolest features of DB2. DB2 can asynchronously read consecutive pages of data, if DB2 thinks you are likely to read pages sequentially. With a pre-fetch size of n pages, once you read (n/2)th page, DB2 triggers the next pre-fetch. I also know, that the DB2 optimizer decides to use Sequential Pre-fetch at BIND time.

With DB2 V9, dynamic pre-fetch feature makes it possible to switch from indexed-access to sequential pre-fetch on-the-fly. There are no dumb questions, so I'd like to ask, does this access path change reflect in the PLAN_TABLE? As an application programmer, how would I detect, if dynamic pre-fetch was leveraged?

Thanks,
Quasar C.

does this access path change reflect in the PLAN_TABLE? As an application programmer, how would I detect, if dynamic pre-fetch was leveraged?

I don't think so that "change", as such, will reflect. Because to see if the DB2 optimizer has chosen a prefetch access method, with EXPLAIN you'd look at the rows of data in your PLAN_TABLE as you've suspected. The PREFETCH column should have "D" for dynamic fetch. However, if the statistics aren't up to date, the PREFETCH column value may be "S" - said that, DB2 still detects whether the data is truly being processing sequentially.

Also, in DB2 10 for z/OS, the Dynamic Prefetch detection process becomes more row oriented rather than detecting purely if pages (DB2 9) are accessed sequentially.

Good insight - thanks Anuj!

You're welcome!