Hello,

What does it mean when npages is -1 or -2 in syscat.bufferpools?

Thank you!

According to DB2 V9.5 Information center:

-1 indicates that the buffer pool is to use the BUFFPAGE parameter as the number of buffer pool pages.

When self tuning is enabled for a buffer pool, the NPAGES field in the SYSCAT.BUFFERPOOLS catalog view for that particular buffer pool will be set to -2. When self tuning is disabled, the NPAGES field will be set to the buffer pool's current size.

Thank you. If I have one bufferpool per database, should I leave it automatic and let db2 manage it or set it to some specific value?

That depends on how much faith you have into STMM, which skills you have for tuning the system, and which workload you run. Given your questions, I would suggest that you just set everything to automatic.

We're running 9.1 on AIX with 16GB of RAM. One instance, 5 local databases. The server is dedicated to DB2, transactions are mostly OLTP.

As you suggested, I can set everything to automatic and let DB2 do the "tuning". Once DB2 finds the "optimal" numbers based on the average workload, I want to hardcode the bufferpools (one per database) and leave everything automatic.

Would you recommend this approach? What % of memory would you suggest I give to bufferpools assuming all 5 databases have the same workload? What about database_memory and instance_memory, should I hardcode them or leave automatic?

Thank you!

That's what I should do. Take care that all the 5 databases stay acitvated to prevent the other 4 to consume the resources.

Let's assume I have 4 databases active and almost all memory is consumed by them. Then I start the 5th one. Will db2 "borrow" memory from other databases and give it to my 5th database or return an error messages saying out of memory? I think DB2 should be aware of all databases.

Thank you!

Please read this: v8.2 -> v9.5.1 - self tuning vs. autoconfigure

Regardng your question: Yes, DB2 will borrow (as you call it) memory resources from the other 4. The problem is the start-up of the 5th DB. When there is no room for the bufferpools, db2 will fall back to the "hidden" ones. This is a situation you do not want. If you do not notice at first (db2diag.log) you'll notice very bad performance for that database very soon. The only way to correct this "mis-init" is to free enough space and STOP/START the database.
Another option is to avoid this situation is the use of the DB2_OVERRIDE_BPF parameter. That's why I asked you to read the thread mentioned earlier.