Bufferpool Hit Ratio

[satish mullapudi]

Hi All,
I am using DB2 UDB v 8.2 , OS: RHEL . Now, How to get the Bufferpool
Hit Ratio?

[tsmithnj]

select distinct BP_NAME,
NPAGES,
PAGESIZE,
decimal((bigint (npages)*bigint (pagesize)/(1024*1024))),

decimal(1-((decimal(pool_ data_p_reads)+d ecimal(pool_ind ex_p_reads))/

(1+decimal(pool _data_l_reads)+ decimal(pool_in dex_l_reads))), 20,5)*100
as "BP Hit Ratio"
from table( snapshot_bp( '<DBNAME>', -1 ))
as snapshot_databa se inner join syscat.bufferpo ols bp
on bp.BPNAME = snapshot_databa se.BP_NAME

Make sure you update "<DBNAME>" with your database's name.


satish mullapudi wrote:

Hi All,
I am using DB2 UDB v 8.2 , OS: RHEL . Now, How to get the Bufferpool
Hit Ratio?

[natG]

On Tue, 22 Aug 2006 05:09:06 -0700, tsmithnj wrote:

select distinct BP_NAME,
NPAGES,
PAGESIZE,
decimal((bigint (npages)*bigint (pagesize)/(1024*1024))),

decimal(1-((decimal(pool_ data_p_reads)+d ecimal(pool_ind ex_p_reads))/

(1+decimal(pool _data_l_reads)+ decimal(pool_in dex_l_reads))), 20,5)*100
as "BP Hit Ratio"
from table( snapshot_bp( '<DBNAME>', -1 ))
as snapshot_databa se inner join syscat.bufferpo ols bp
on bp.BPNAME = snapshot_databa se.BP_NAME

Make sure you update "<DBNAME>" with your database's name.

[from a db2 newbie] Thank you for the post!
Question. What is column '4'? In my case they are all 0.
nat

[tsmithnj]

It is the hit ratio. You probably need to turn on your monitor
switches.
natG wrote:

On Tue, 22 Aug 2006 05:09:06 -0700, tsmithnj wrote:

select distinct BP_NAME,
NPAGES,
PAGESIZE,
decimal((bigint (npages)*bigint (pagesize)/(1024*1024))),

decimal(1-((decimal(pool_ data_p_reads)+d ecimal(pool_ind ex_p_reads))/

(1+decimal(pool _data_l_reads)+ decimal(pool_in dex_l_reads))), 20,5)*100
as "BP Hit Ratio"
from table( snapshot_bp( '<DBNAME>', -1 ))
as snapshot_databa se inner join syscat.bufferpo ols bp
on bp.BPNAME = snapshot_databa se.BP_NAME

Make sure you update "<DBNAME>" with your database's name.

[from a db2 newbie] Thank you for the post!
Question. What is column '4'? In my case they are all 0.
nat

[natG]

On Wed, 23 Aug 2006 05:25:37 -0700, tsmithnj wrote:

It is the hit ratio. You probably need to turn on your monitor switches.

No. That's column 5. I have that. (Wadda ya think I was excited about?<g>)
But column 4 in my case is 0.

nat

[tsmithnj]

That's the size of the BP in GB. Change "1024*1024" to "1024" and
you'll have MB


natG wrote:

On Wed, 23 Aug 2006 05:25:37 -0700, tsmithnj wrote:

It is the hit ratio. You probably need to turn on your monitor switches.

No. That's column 5. I have that. (Wadda ya think I was excited about?<g>)
But column 4 in my case is 0.

nat