If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

1. The first I/O from a data page will never be in the buffer pool. It is only when retrieving the same page again can it possibly be found in the buffer pool. Did you excute the exact same SQL statements more than once?

2. If the application is doing tablespace scans (or scans of an entire partition) then the likelihood of a buffer pool hit is much lower because the entire tablespace (or even entire partition) cannot fit into the buffer pool. So that means as the tablespace is scanned, the buffer pool starts filling up, and the least recently used pages are kicked out of the buffer pool. So by the end of the scan, only the last part of the tablespace is in the buffer pool. The next query with a tablespace scan starts with the first part of the tablespace.

In order to analyze this correctly, it helps to see the SQL that go with the statistics you listed. I also noted that there is no sequential prefetch, which is not good if you are doing tablespace scans.

It is generally a bad idea to have only one table in a buffer pool. Usually a DB2 subsystem should have no more than 3 4K buffer pools. A few number of larger buffer pools work better than a large number of smaller buffer pools.

Note: Please list your DB2 version and Operating System in future posts. (DB2 for OS/390 version ?).

Each table uses the bufferpool assigned for that tablespace. For a join, the result set may be materialized (before you fetch each row) in a system temporary tables (with associated tablespace) which also has a bufferpool assigned to it.

Just to clarify the above post, system temporary (or work file) tables (such as needed for building answer sets in a declare cursor that needs to be materialized) reside in database DSNDB07. There is a bufferpool assigned at the database level for any tablespace created by DB2 in that database.