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.

In a first order it is not relevant. Only when you want to eek out the
last iota of speed would you want to look at this issue.
Note that prior to DB2 9 having multiple buffer pools means also that
you have to tune each of them. Too much hassle for the typical app,
especially OLTP.

Cheers
Serge

PS-FYI: A side effect of range partitioning in DB2 9 is that range
partitioned tables can place there indexes where ever they please.
PPS: Any table can be seen as a range partitioned table with one open
ended partition ;-)