Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003

    Unanswered: Bufferpool for Index and tables

    I understand that most of the db2 shops use 2 different buffer pool, one BP for the tablespace and one BP for the indexspace. Just would like to know what is the purpose and the benefit of the same.


  2. #2
    Join Date
    Jul 2006
    Pune , India
    correct me if i am wrong ... but i think
    you can have two different tablespaces for tables and indexes and these tablespaces are releated to bufferpools
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  3. #3
    Join Date
    May 2003
    The theory of having 2 (or more) different bufferpools is that you want indexes (and small frequently used tables) in the bufferpool at all times (100% buffer pool hit ratio). This is usually feasible if the same bufferpool is not shared with large tables (the size of the bufferpool would as large, or nearly as large. as the sum of all the objects using it).

    Then larger tables (and maybe indexes for extremely large tables) would be placed in another bufferpool that would have a lower bufferpool hit ratio, since it is not feasible to make the bufferpool large enough to hold all of the larger tables.

    However, if you have a small database, and everything will fit into one bufferpool, then it doesn't matter and you can put everything in the default bufferpool (and you can use SMS).

    In order to have indexes seperated from tables in different bufferpools, you need to use DMS tablespaces and put indexes in a different tablespces in the create table statement (INDEXES IN xxxxxx clause).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Sep 2003
    Thanks Marcus. Your reply came as an eye opener for me. I understood it better.

Posting Permissions

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