We have DB2 on AIX, but I didn't find any information about negative bufferpool ratio in the LUW manual. DB2 for z/OS manual states:

A negative hit ratio means that prefetch has brought pages into the buffer pool that are not subsequently referenced. The pages are not referenced because either the query stops before it reaches the end of the table space or DB2® must take the pages away to make room for newer ones before the query can access them.


Does it mean DB2 reads more pages than needed, table scan instead of index scan? Do I only need to be concerned about negative bufferpool ratio for tables with random access? Should we increase the bufferpool size? Any other suggestions on how to increase the bufferpool hit ratio?

One example where a page can be read from disk and placed in a bufferpool (but never used) is if a tablespace scan is being done (usually to read the entire table) but the query stops short for some reason. Or if a cursor is opened and fetching of rows begins but not all the rows in the cursor are actually fetched before the cursor is closed. I would not worry too much about these situations unless you have encountered a specific problem.

In order to recommend bufferpool size(s) the following information is needed:

• Total physical memory on your machine.
• DB2 Edition (ESE, WSE, Express-C, etc)
• Size of each database on the server
• Description and type of application (OLTP, data warehouse, etc)
• Number of typical connections to the application
• Any other applications running on the same server (application code, web server, etc).

One thing you never want to do is use more memory for DB2 than actually exists on the machine. DB2 does not like virtual memory and can crash in some cases if all the real memory is used up. However, many operating systems will use any free memory for disk caching (but release it when needed) so the amount of "free" memory reported by the OS can be very misleading.

We have ESE Version 9.1 with 16GB of RAM.

Two instances, one database in each:
First - OLTP, ~100GB, number of connection around 200-300
Second - mixed (50% OLTP and 50% DW), database size and number of connection is appr the same

The server is dedicated to DB2.

Thank you in advance.

Without knowing the details, I would allocate a total of 6 GB of memory for the OLTP instance, and about 4 GB for the combo database. I would put the indexes and small tables in a separate BP from the large tables (which you can only do if they are in separate tablespaces). I would not exceed 10 GB for the total of all bufferpools on this machine, so if you add more databases you will need to adjust the other bufferpools as appropriate.