-- dba.v_overall_hit_ratio - overall hit ratio (OHR) -- Downloaded from http://www.ebenner.com/db2luwdba-howto -- For use only with other components from article series: -- "Effective DB2 Monitoring with Snapshot Administrative Views" -- by Jeffrey Benner, in _DB2 LUW DBA HowTo_ -- Please direct any questions to consulting@ebenner.com -- This work is licensed under the Creative Commons Attribution 3.0 United States License. To view a copy of this license, visit http://creativecommons.org/licenses/by/3.0/us/ or send a letter to Creative Commons, 171 Second Street, Suite 300, San Francisco, California, 94105, USA. create view dba.v_overall_hit_ratio as with POOL_DATA_L_READS_DELTA_TB as (select snap0.SNAPSHOT_TIMESTAMP, ( POOL_DATA_L_READS - ( select POOL_DATA_L_READS from dba.snapdb as SNAP1 where snap1.SNAPSHOT_TIMESTAMP = ( select max(snap2.SNAPSHOT_TIMESTAMP) from dba.snapdb as SNAP2 where snap2.SNAPSHOT_TIMESTAMP < snap0.SNAPSHOT_TIMESTAMP ) ) ) as POOL_DATA_L_READS_DELTA from dba.snapdb as SNAP0 ), POOL_INDEX_L_READS_DELTA_TB as (select snap0.SNAPSHOT_TIMESTAMP, ( POOL_INDEX_L_READS - ( select POOL_INDEX_L_READS from dba.snapdb as SNAP1 where snap1.SNAPSHOT_TIMESTAMP = ( select max(snap2.SNAPSHOT_TIMESTAMP) from dba.snapdb as SNAP2 where snap2.SNAPSHOT_TIMESTAMP < snap0.SNAPSHOT_TIMESTAMP ) ) ) as POOL_INDEX_L_READS_DELTA from dba.snapdb as SNAP0 ), POOL_DATA_P_READS_DELTA_TB as (select snap0.SNAPSHOT_TIMESTAMP, ( POOL_DATA_P_READS - ( select POOL_DATA_P_READS from dba.snapdb as SNAP1 where snap1.SNAPSHOT_TIMESTAMP = ( select max(snap2.SNAPSHOT_TIMESTAMP) from dba.snapdb as SNAP2 where snap2.SNAPSHOT_TIMESTAMP < snap0.SNAPSHOT_TIMESTAMP ) ) ) as POOL_DATA_P_READS_DELTA from dba.snapdb as SNAP0 ), POOL_INDEX_P_READS_DELTA_TB as (select snap0.SNAPSHOT_TIMESTAMP, ( POOL_INDEX_P_READS - ( select POOL_INDEX_P_READS from dba.snapdb as SNAP1 where snap1.SNAPSHOT_TIMESTAMP = ( select max(snap2.SNAPSHOT_TIMESTAMP) from dba.snapdb as SNAP2 where snap2.SNAPSHOT_TIMESTAMP < snap0.SNAPSHOT_TIMESTAMP ) ) ) as POOL_INDEX_P_READS_DELTA from dba.snapdb as SNAP0 ), -- New Monitor Elements Added May 2009 POOL_XDA_P_READS_DELTA_TB as (select snap0.SNAPSHOT_TIMESTAMP, ( POOL_XDA_P_READS - ( select POOL_XDA_P_READS from dba.snapdb as SNAP1 where snap1.SNAPSHOT_TIMESTAMP = ( select max(snap2.SNAPSHOT_TIMESTAMP) from dba.snapdb as SNAP2 where snap2.SNAPSHOT_TIMESTAMP < snap0.SNAPSHOT_TIMESTAMP ) ) ) as POOL_XDA_P_READS_DELTA from dba.snapdb as SNAP0 ), POOL_TEMP_DATA_P_READS_DELTA_TB as (select snap0.SNAPSHOT_TIMESTAMP, ( POOL_TEMP_DATA_P_READS - ( select POOL_TEMP_DATA_P_READS from dba.snapdb as SNAP1 where snap1.SNAPSHOT_TIMESTAMP = ( select max(snap2.SNAPSHOT_TIMESTAMP) from dba.snapdb as SNAP2 where snap2.SNAPSHOT_TIMESTAMP < snap0.SNAPSHOT_TIMESTAMP ) ) ) as POOL_TEMP_DATA_P_READS_DELTA from dba.snapdb as SNAP0 ), POOL_TEMP_XDA_P_READS_DELTA_TB as (select snap0.SNAPSHOT_TIMESTAMP, ( POOL_TEMP_XDA_P_READS - ( select POOL_TEMP_XDA_P_READS from dba.snapdb as SNAP1 where snap1.SNAPSHOT_TIMESTAMP = ( select max(snap2.SNAPSHOT_TIMESTAMP) from dba.snapdb as SNAP2 where snap2.SNAPSHOT_TIMESTAMP < snap0.SNAPSHOT_TIMESTAMP ) ) ) as POOL_TEMP_XDA_P_READS_DELTA from dba.snapdb as SNAP0 ), POOL_TEMP_INDEX_P_READS_DELTA_TB as (select snap0.SNAPSHOT_TIMESTAMP, ( POOL_TEMP_INDEX_P_READS - ( select POOL_TEMP_INDEX_P_READS from dba.snapdb as SNAP1 where snap1.SNAPSHOT_TIMESTAMP = ( select max(snap2.SNAPSHOT_TIMESTAMP) from dba.snapdb as SNAP2 where snap2.SNAPSHOT_TIMESTAMP < snap0.SNAPSHOT_TIMESTAMP ) ) ) as POOL_TEMP_INDEX_P_READS_DELTA from dba.snapdb as SNAP0 ), POOL_XDA_L_READS_DELTA_TB as (select snap0.SNAPSHOT_TIMESTAMP, ( POOL_XDA_L_READS - ( select POOL_XDA_L_READS from dba.snapdb as SNAP1 where snap1.SNAPSHOT_TIMESTAMP = ( select max(snap2.SNAPSHOT_TIMESTAMP) from dba.snapdb as SNAP2 where snap2.SNAPSHOT_TIMESTAMP < snap0.SNAPSHOT_TIMESTAMP ) ) ) as POOL_XDA_L_READS_DELTA from dba.snapdb as SNAP0 ), POOL_TEMP_DATA_L_READS_DELTA_TB as (select snap0.SNAPSHOT_TIMESTAMP, ( POOL_TEMP_DATA_L_READS - ( select POOL_TEMP_DATA_L_READS from dba.snapdb as SNAP1 where snap1.SNAPSHOT_TIMESTAMP = ( select max(snap2.SNAPSHOT_TIMESTAMP) from dba.snapdb as SNAP2 where snap2.SNAPSHOT_TIMESTAMP < snap0.SNAPSHOT_TIMESTAMP ) ) ) as POOL_TEMP_DATA_L_READS_DELTA from dba.snapdb as SNAP0 ), POOL_TEMP_XDA_L_READS_DELTA_TB as (select snap0.SNAPSHOT_TIMESTAMP, ( POOL_TEMP_XDA_L_READS - ( select POOL_TEMP_XDA_L_READS from dba.snapdb as SNAP1 where snap1.SNAPSHOT_TIMESTAMP = ( select max(snap2.SNAPSHOT_TIMESTAMP) from dba.snapdb as SNAP2 where snap2.SNAPSHOT_TIMESTAMP < snap0.SNAPSHOT_TIMESTAMP ) ) ) as POOL_TEMP_XDA_L_READS_DELTA from dba.snapdb as SNAP0 ), POOL_TEMP_INDEX_L_READS_DELTA_TB as (select snap0.SNAPSHOT_TIMESTAMP, ( POOL_TEMP_INDEX_L_READS - ( select POOL_TEMP_INDEX_L_READS from dba.snapdb as SNAP1 where snap1.SNAPSHOT_TIMESTAMP = ( select max(snap2.SNAPSHOT_TIMESTAMP) from dba.snapdb as SNAP2 where snap2.SNAPSHOT_TIMESTAMP < snap0.SNAPSHOT_TIMESTAMP ) ) ) as POOL_TEMP_INDEX_L_READS_DELTA from dba.snapdb as SNAP0 ) -- End New Monitor Elements May 2009 select A.SNAPSHOT_TIMESTAMP, CASE WHEN (POOL_DATA_L_READS_DELTA + POOL_INDEX_L_READS_DELTA + POOL_XDA_L_READS_DELTA + POOL_TEMP_DATA_L_READS_DELTA + POOL_TEMP_XDA_L_READS_DELTA + POOL_TEMP_INDEX_L_READS_DELTA )= 0 then 0 ELSE 100 - (DECIMAL((DECIMAL(POOL_DATA_P_READS_DELTA + POOL_INDEX_P_READS_DELTA + POOL_XDA_P_READS_DELTA + POOL_TEMP_DATA_P_READS_DELTA + POOL_TEMP_XDA_P_READS_DELTA + POOL_TEMP_INDEX_P_READS_DELTA, 23, 4) / DECIMAL( POOL_DATA_L_READS_DELTA + POOL_INDEX_L_READS_DELTA + POOL_XDA_L_READS_DELTA + POOL_TEMP_DATA_L_READS_DELTA + POOL_TEMP_XDA_L_READS_DELTA + POOL_TEMP_INDEX_L_READS_DELTA, 23, 4)) * 100, 8, 1)) END as overall_hit_ratio from POOL_DATA_L_READS_DELTA_TB A, POOL_INDEX_L_READS_DELTA_TB B, POOL_DATA_P_READS_DELTA_TB C, POOL_INDEX_P_READS_DELTA_TB D, POOL_XDA_P_READS_DELTA_TB E, POOL_TEMP_DATA_P_READS_DELTA_TB F, POOL_TEMP_XDA_P_READS_DELTA_TB G, POOL_TEMP_INDEX_P_READS_DELTA_TB H, POOL_XDA_L_READS_DELTA_TB I, POOL_TEMP_DATA_L_READS_DELTA_TB J, POOL_TEMP_XDA_L_READS_DELTA_TB K, POOL_TEMP_INDEX_L_READS_DELTA_TB L where A.SNAPSHOT_TIMESTAMP = B.SNAPSHOT_TIMESTAMP and B.SNAPSHOT_TIMESTAMP = C.SNAPSHOT_TIMESTAMP and C.SNAPSHOT_TIMESTAMP = D.SNAPSHOT_TIMESTAMP and D.SNAPSHOT_TIMESTAMP = E.SNAPSHOT_TIMESTAMP and E.SNAPSHOT_TIMESTAMP = F.SNAPSHOT_TIMESTAMP and F.SNAPSHOT_TIMESTAMP = G.SNAPSHOT_TIMESTAMP and G.SNAPSHOT_TIMESTAMP = H.SNAPSHOT_TIMESTAMP and H.SNAPSHOT_TIMESTAMP = I.SNAPSHOT_TIMESTAMP and I.SNAPSHOT_TIMESTAMP = J.SNAPSHOT_TIMESTAMP and J.SNAPSHOT_TIMESTAMP = K.SNAPSHOT_TIMESTAMP and K.SNAPSHOT_TIMESTAMP = L.SNAPSHOT_TIMESTAMP ;