Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2013
    Posts
    3

    Unanswered: block based bufferpool (DB2 V9)

    I read the following reference point:IBM DB2 9.7 Information Center for Linux, UNIX, and Windows

    but still don't understand the concept and the benefit of using block based bufferpools. Can anyone explain this concept and benefits in lamen terms?

    Thanks,

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    As the doc clearly, it benefits sequential prefetch, which is used more often for data warehouse queries than it is used for OLTP. If you want to understand it better, research sequential prefetch. If you do use block-based bufferpools, don't allocate more than 5% of a bufferpool as block-based. If you are a layman, I would suggest you not use it.
    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
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    - OLTP: you need direct I/O's. You want your queries to use the right index and they point directly to the exact data-pages you need. You do not read more data-pages than you acutual result set.
    - DW: you need async I/O to scan through large parts of your of your data to summarize and that kind of things.

    Well the above is theory and describes Eldorado which does not exist. But when do execute a "DW like" query and db2 starts to do async I/O you could help a bit. You do that by reserving a small part of you bufferpool dedicated for this kind of I/O. The prefetcher presents itself to the bufferpool carrying a "truckload" of datapages and it would be nice if you had a contignuous space reserved in the bufferpool for the truck to unload efficiently.
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

  4. #4
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    double post, sorry
    Last edited by dr_te_z; 02-05-13 at 11:43. Reason: double post
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by dr_te_z View Post
    - OLTP: you need direct I/O's
    Sounds like a recipe for disaster.
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by n_i View Post
    Sounds like a recipe for disaster.
    what I mean is synchronous I/O. If every row you select has a perfect index pointing straight at the required data-page then you've found Utopia, right? No async I/O required whatsoever. That is what I tried to describe
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

Posting Permissions

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