Q:

What is the recommended Buffer Pool size on a 2GB RAM PC?

I installed DB2 Express-C on a PC with 2 GB RAM and running Windows Server 2008 R2 OS. While creating a new database, there is an option to set Buffer Pool size with default set to 4K.
How much is the recommended pool size for the above mentioned configuration?
I also want to know whether to use default Buffer Pool or a separate Buffer Pool for every database.

A:

The default size is 4K. You can also specify 8K, 16K, or 32K. The size of the buffer pool should really be based on the size of your tables (ie, how many bytes is it possible for one row of data to hold). If a row of your data won't fit into a 4K page, then it won't fit into a 4K buffer pool. And hence you would need larger.
The same is also true of table spaces (the "physical containers" that your tables are stored in). A table that needs an 8K buffer pool, needs to reside in an 8K table space. And actually, you would then associate that table space to the buffer pool as well....
That being said, don't just "oversize" and go to 32K table spaces and buffer pools if you really only have 4K tables. You will waste a lot of disk space and memory then.
Also, you can have as many buffer pools as you want (I'm sure there is an upper limit, though I wouldn't know off hand). But you can have any combination of them. You could have five 4K pools, one 8K pool, one 16K pool, and three 32K pools.
The biggest thing is more based on your table sizes and understanding that as mentioned before.
Although, here is the nice thing....after you create your buffer pools and table spaces, DB2 is smart enough to put the table into the correct size table space (pending it exists). You can specify it in your DDL code, but DB2 is smart enough to put an 8K table in an 8K table space (and thus it will also be associated with an 8K buffer pool).
OK...now to answer your last two questions. There is no "recommended" size really. The default of 4K is fine. Most of our tables where I work fit in a 4K table space and 4K buffer pool.
What we tend to do though, as a best practice... We set up our own 4K, 8K and 32K table spaces and buffer pools (including temporary tables spaces for reorgs). Why we don't set up 16K, I'm not sure. But we set up several sizes in all our databases. This way if any developer creates a table that will be bigger than 4K, it will automatically get placed into the appropriately bigger tables pace (and thus buffer pool).
Hopefully this was clear enough for you. I have also found this article helpful with my own understanding of buffer pools and table spaces.

