Dear all,

If you do a SELECT * FROM TAB WHERE DATE=?, the explain plan might pick up a
TBSCAN and you'll see in db2exfmt PREFETCH=SEQUENTIAL.

Now, TAB is a MDC, and DATE is a dimension. The explain plan will pick up
the block index and db2exfmt indicates PREFETCH=NONE. However, monitoring
bufferpools shows async IO is done.

Would it make sense to have db2exfmt indicate in that case
PREFETCH=SEQUENTIAL when the MDC is read from 1 dimension ? I believe all
extents will be read ahead in the order indicated by the block index ?

Regards,

Jean-Marc

"Jean-Marc Blaise" <nobody@nowhere.com> a écrit dans le message de
news:425c0bd5$0$829$8fcfb975@news.wanadoo.fr...
> Dear all,
>
> If you do a SELECT * FROM TAB WHERE DATE=?, the explain plan might pick up
a
> TBSCAN and you'll see in db2exfmt PREFETCH=SEQUENTIAL.
>
> Now, TAB is a MDC, and DATE is a dimension. The explain plan will pick up
> the block index and db2exfmt indicates PREFETCH=NONE. However, monitoring
> bufferpools shows async IO is done.
>
> Would it make sense to have db2exfmt indicate in that case
> PREFETCH=SEQUENTIAL when the MDC is read from 1 dimension ? I believe all
> extents will be read ahead in the order indicated by the block index ?
>
> Regards,
>
> Jean-Marc
>
I have found this in the DB2 Information Center:

"Second, prefetching of the data pages does not rely on sequential detection
when block indexes are used. DB2 UDB looks ahead in the index, prefetching
the data pages of the blocks into memory using big-block I/O, and ensuring
that the scan does not incur the I/O when the data pages are accessed in the
table."

I understand from this that "block prefetching" is always done due to MDC.

Improvment request: it should be nice to externalize this in explains, why
not PREFETCH=BLOCK instead of NONE ?

Jean-Marc

"Jean-Marc Blaise" <nobody@nowhere.com> wrote in message
news:425c2513$0$3103$8fcfb975@news.wanadoo.fr...
> "Jean-Marc Blaise" <nobody@nowhere.com> a écrit dans le message de
> news:425c0bd5$0$829$8fcfb975@news.wanadoo.fr...
> > Dear all,
> >
> > If you do a SELECT * FROM TAB WHERE DATE=?, the explain plan might pick
up
> a
> > TBSCAN and you'll see in db2exfmt PREFETCH=SEQUENTIAL.
> >
> > Now, TAB is a MDC, and DATE is a dimension. The explain plan will pick
up
> > the block index and db2exfmt indicates PREFETCH=NONE. However,
monitoring
> > bufferpools shows async IO is done.
> >
> > Would it make sense to have db2exfmt indicate in that case
> > PREFETCH=SEQUENTIAL when the MDC is read from 1 dimension ? I believe
all
> > extents will be read ahead in the order indicated by the block index ?
> >
> > Regards,
> >
> > Jean-Marc
> >
> I have found this in the DB2 Information Center:
>
> "Second, prefetching of the data pages does not rely on sequential
detection
> when block indexes are used. DB2 UDB looks ahead in the index, prefetching
> the data pages of the blocks into memory using big-block I/O, and ensuring
> that the scan does not incur the I/O when the data pages are accessed in
the
> table."
>
> I understand from this that "block prefetching" is always done due to MDC.
>
> Improvment request: it should be nice to externalize this in explains, why
> not PREFETCH=BLOCK instead of NONE ?
============
When I used DB2 UDB Version 8.1 GA (or fixpak1). I couldn't find the block
I/O information either, when I took a look at the explain output.
I know my query did using block I/O, since I found that information from
snapshot output.
I never try it by using the late release.

>
> Jean-Marc
>
>