Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2002
    Posts
    48

    Unanswered: Agent- Reads page or Extent(Disk to Bufferpool)

    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

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    Last edited by Marcus_A; 07-21-06 at 22:12.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jul 2002
    Posts
    48
    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

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •