-- dba.v_sorts_per_transaction - Total sorts per transaction over time (SRTTX) -- 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_sorts_per_transaction as with TOTAL_SORTS_DELTA_TB as (select snap0.SNAPSHOT_TIMESTAMP, ( TOTAL_SORTS - ( select TOTAL_SORTS 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 TOTAL_SORTS_DELTA from dba.snapdb as SNAP0 ), TX_DELTA_TB as (select snap0.SNAPSHOT_TIMESTAMP, ( ( COMMIT_SQL_STMTS + ROLLBACK_SQL_STMTS ) - ( select ( COMMIT_SQL_STMTS + ROLLBACK_SQL_STMTS ) 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 TX_DELTA from dba.snapdb as SNAP0 ) select B.SNAPSHOT_TIMESTAMP, CASE WHEN TX_DELTA = 0 then 0 ELSE DECIMAL( DECIMAL(TOTAL_SORTS_DELTA, 23, 4) / DECIMAL( TX_DELTA, 23, 4), 8, 2) END as sorts_per_transaction from TOTAL_SORTS_DELTA_TB B, TX_DELTA_TB D where B.SNAPSHOT_TIMESTAMP = D.SNAPSHOT_TIMESTAMP ;