In memory metrics of db2, working with baseline tables

in my previous blog entry I rewrote some scripts that scott hayes published in a blog post on the IDUG website.

my version of those scripts would give you feedback on the metrics based on the data collected since the start of the instance.

in this entry I will put the same scripts but modified so they give you the metrics based on a baseline you setup in advance.

#!/usr/bin/ksh

function Usage

{

   echo "---------------------------------------------------------------------------------------------------------"

   echo "Usage : $(basename $0) DATABASE INTERVAL_in_seconds"

   echo

}

readonly LOGFILE=$(basename $0)_${DB2INSTANCE}_$(hostname)_$(date +%Y-%m-%d-%H.%M.%S).log

readonly DATABASE=$1

readonly INTERVAL=$2

{db2 -v " declare global temporary table session.mon_get_table_baseline as (select current timestamp as snapshot_timestamp, a.*  from table(mon_get_table(null,null,-2)) a ) with no data on commit preserve rows"

db2 -v "insert into session.mon_get_table_baseline select current timestamp as snapshot_timestamp, a.*  from table(mon_get_table(null,null,-2)) a"

WAIT THE TIMEFRAME YOU WANT TO ANALYZE
sleep $INTERVAL

db2 -v "with delta_values as (
select substr(a.tabschema,1,20) as TABSCHEMA,

substr(a.tabname,1,25) as TABNAME,
--a.member,
sum(a.rows_read) - sum(b.rows_read) as delta_rows_read,
((select sum(c.rows_read) from table(mon_get_table(null,null,-2)) c ) - (select sum(d.rows_read) from session.mon_get_table_baseline d)) as delta_total_rows_read
from table(mon_get_table(null,null,-2)) a join  session.mon_get_table_baseline b on a.tabschema=b.tabschema and a.tabname=b.tabname and a.member=b.member and a.DATA_PARTITION_ID=b.DATA_PARTITION_ID
group by a.tabschema,a.tabname)

select substr(e.tabschema,1,20) as TABSCHEMA, 

substr(e.tabname,1,25) as TABNAME,
--e.member,

e.delta_rows_read,
e.delta_total_rows_read,
decimal(e.delta_rows_read,31,4)/e.delta_total_rows_read * 100 as pct_of_rows_read
from delta_values e
order by 
--pct_of_rows_read,
e.delta_rows_read desc fetch first 20 rows only"
} | tee ${LOGFILE}