"shsandeep" <sa**********@gmail.comwrote in message
news:91******************************@localhost.ta lkaboutdatabases.com...
Thanks Mark. This is exactly what I was looking for.
One question out of curiosity: What is the necessity of having indexes
and tables using different bufferpools? Performance? Does it make a big
impact on a database which deals with majority of OLAP transactions?
Cheers,
San.
Because bufferpool memory should be limited to the amount of free real
system memory, and also constrained to a max of about 1.5 - 2.0 GB for
32-bit DB2 instances (if you have a 64-bit DB2 instance it is only
constrained by real memory), it has to be utilized in an efficient manner.
If you have a database which will not completely fit in bufferpool memory,
then you may want to prioritize certain objects over others and create more
than one bufferpool. For example, you may want to have the DB2 system
catalog, small lookup tables (including dimension tables), and all indexes
(for all tables) reside in memory all the time (by placing them in a single
appropriately sized bufferpool), and have the large tables (including large
fact tables) in a second bufferpool (which because of their size will never
fit completely in any bufferpool). If everything were to be placed in a
single bufferpool, then a table scan on a large table would likely flush out
all the catalog objects, indexes, and small tables from the bufferpool
memory.
Bufferpool design is a bit of an art (as well as some science) and there is
no perfect configuration, nor will 2 different people usually come up with
the exact same solution (unless there is only one bufferpool).
But generally, in a data warehouse application it is best to have 2 or 3
bufferpools in the manner I have described above.
For an OLTP application where you can fit all (or almost all) of the data,
indexes, and catalog in a single large bufferpool, then that is usually the
best solution. For a large OLTP system, then multiple bufferpools can be
used in a similar manner as a data warehouse.
But the biggest mistake most people make is creating too many bufferpools.