Saturday, August 30, 2014

How to get Data and index hit ration

with bpsnap as
(
select substr(bp_name,1,20) as bp_name,
float(pool_data_p_reads) as dp_read,
float(pool_data_l_reads) as dl_read,
float(pool_index_p_reads) as ip_read,
float(pool_index_l_reads) as il_read
from sysibmadm.snapbp
)
select bp_name,
dec((1 -(dp_read / dl_read))*100,4,1) as "Data Hit Ratio",
dec((1 -(ip_read / il_read))*100,4,1) as "Index Hit Ratio"
from bpsnap
where dl_read > 0 and il_read > 0
;