-- dba.v_stmt_high_rr_rs_ratio -- All captured statements which match applid/timestamp with at least 80% of max RR_RS ratio. -- statement captured within 2 seconds of snapshot with max RR_RS -- 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_stmt_high_rr_rs_ratio as select A.SNAPSHOT_TIMESTAMP, decimal( decimal(A.ROWS_READ, 21, 2) / decimal(A.ROWS_SELECTED, 21, 2), 21, 2) as rr_rs_ratio, stmt_text from dba.snapappl A, dba.snapstmt B where A.AGENT_ID = B.AGENT_ID and abs(timestampdiff(2, char(A.SNAPSHOT_TIMESTAMP - B.SNAPSHOT_TIMESTAMP))) < 2 and A.rows_selected > 0 and A.ROWS_READ / A.ROWS_SELECTED > 0.8 * (select max( case when rows_selected = 0 then NULL else ROWS_READ / ROWS_SELECTED end) from dba.snapappl appl group by SNAPSHOT_TIMESTAMP having appl.SNAPSHOT_TIMESTAMP = A.SNAPSHOT_TIMESTAMP );