Hi ,
One of my bufferpools is showing BP hit ratio low most of times and 0% at times.

Onle one tablespace with one table is allocated to this BP. The tablespace is of 32 partitions ,each of PRIQTY 22320K & SECQTY 2232K ).

The platinum subsystem analyser data for that BP at an instant is ..

VP_ALLOC 20000K
HP_ALLOC 20000K
BUFHIT 0.00%
GETPAGE 169260
GETPAGE_SQ 0
SYNCRIO 9421
SYNCRIO_SQ 0
PF_REQ 17122
PF_IO 9799
PF_PGS 218395
PF_PIO 22.3
SPF_REQ 0
SPF_IO 0
SPF_PGS 0
SPF_PIO 0
LPF_REQ 0
LPF_IO 0
LPF_PGS 0
LPF_PIO 0
DPF_REQ 17122
DPF_IO 9799
DPF_PGS 218395
DPF_PIO 22.3
BUF_UPDATE 456
SYNCWIO 22
ASYWIO 67
ASYWIO_PGS 114
ASYWIO_PIO 1.7
PAGING_RIO 7
PAGING_WIO 0

could some one help me what is the reason for this very low HIT ratio ?

There are two probable explanations:

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 ?).

DB2 V6.1 & OS390 V2.8

Marcus ,
The highest resource consuming SQL is a fetch cursor that is not using indexes.So it should be doing TS scan.

But the SQL contains join of 3 tables.only one of the table belongs to the BP in problem. The othe rtwo table are allocated to other BP.
In such cases which bufferpool does DB2 use ??

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.

thanks marcus. That was really helpful