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.

Regards
Sahana

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

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).

Thanks Marcus. Your reply came as an eye opener for me. I understood it better.
thanks