Hi

In DB2 UDB. whether reads from disk to bufferpool happens in page or extent. Please help to understand, so that i will be able to design the bufferpool and table accordingly.

Thanks
Nageswaran

Only table or index pages that DB2 needs to examine are placed in the bufferpool. The only exception is pre-fetching, when pages are placed in the bufferpool by DB2 in anticipation that they will be needed later.

Prefetch is only enabled when DB2 is fairly certain that the prefetched pages will be needed, and is almost always correct execpt when a cursor is not fetched to completion by a program. Prefetch can be disabled at the database level (probably a good idea for pure OLTP applications where data is accessed by indexes), or by specifying OPTIMIZE FOR 1 ROWS in the SQL statement.

Thanks for your reply. You said it's going to read pages, so any physical read will be page level not on extent level...

Thanks
Nagesh

If DB2 thinks it is advantageous to do so, it will read in "big-block" mode (usually at an extent level). This will happen if DB2 guesses that it needs all or most of the pages in the extent. As previously menitoned, DB2 may do prefetches of pages which may trigger the big-block reads.

Otherwise, DB2 will read in page mode.

Obviously, physical I/O at the operating system level is different than at the database level.