-- dba.v_sync_read_pct - synchronous read percent (SRP)

-- 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_sync_read_pct as
with POOL_ASYNC_DATA_READS_DELTA_TB as
(select snap0.SNAPSHOT_TIMESTAMP,
( POOL_ASYNC_DATA_READS  -
         ( select POOL_ASYNC_DATA_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_ASYNC_DATA_READS_DELTA
from dba.snapdb as SNAP0 ),

POOL_ASYNC_INDEX_READS_DELTA_TB as
(select snap0.SNAPSHOT_TIMESTAMP,
( POOL_ASYNC_INDEX_READS  -
         ( select POOL_ASYNC_INDEX_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_ASYNC_INDEX_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 ),

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_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 )

select A.SNAPSHOT_TIMESTAMP,
CASE
WHEN (POOL_DATA_P_READS_DELTA + POOL_INDEX_P_READS_DELTA
    + POOL_TEMP_DATA_P_READS_DELTA +  POOL_TEMP_INDEX_P_READS_DELTA) = 0 then 0
ELSE 100 - (DECIMAL((DECIMAL(POOL_ASYNC_DATA_READS_DELTA + POOL_ASYNC_INDEX_READS_DELTA, 23, 4)
/ DECIMAL(POOL_DATA_P_READS_DELTA + POOL_INDEX_P_READS_DELTA
        + POOL_TEMP_DATA_P_READS_DELTA +  POOL_TEMP_INDEX_P_READS_DELTA, 23, 4)) * 100, 8, 1))
END as sync_read_pct
from POOL_ASYNC_DATA_READS_DELTA_TB A, POOL_ASYNC_INDEX_READS_DELTA_TB B, POOL_DATA_P_READS_DELTA_TB C,
POOL_INDEX_P_READS_DELTA_TB D, POOL_TEMP_DATA_P_READS_DELTA_TB E, POOL_TEMP_INDEX_P_READS_DELTA_TB F
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 ;