Determing whether a reorg could benefit performance without using reorgchk

the scripts below can be used to determine whether over a certain interval ,
how much overflow accesses occur for  table in that interval.


I created this script after reading the blog article on reorg and reorgchk by Ember Crooks,
and the comment by Scott hayes on this article.
Ember's Article on Reorg
Scott hayes' company website where I learned the formula below


thanks to both people mentioned above, I was able to write this script that might help you.

Step1 create the baseline table
Step2 insert the baseline into the baseline table
Step3 wait the interval
Step 4 determine the delta values over the interval
Step5 evaluate the metrics
step6 reorg the table


#!/usr/bin/ksh
#
# Usage         :
#
# Description   :
#
# Pre-requisites:
#
# Author        : Albert Mukendi Raf Mathijs
#
# Version       : 1.1
#
# Date          : 2012-XX-XX
#
# Information   :
#
#
# Begin Funcions
#

function Usage
{
   echo "---------------------------------------------------------------------------------------------------------"
   echo "Usage : $(basename $0) DATABASE INTERVAL_in_seconds"
   echo
}

# Load common functions
. /db2scripts/common_script/ksh_functions

# Check unintialized variables
set -u

# Check input parameter
readonly NbInputParmReq=2
[[ $# != ${NbInputParmReq} ]] && Usage && FatalError "The script expects ${NbInputParmReq} input parameters and receives $#"

# Initialisation and declaration of variables
readonly BASEPATH=
readonly LOGFILE=$(basename $0)_${DB2INSTANCE}_$(hostname)_$(date +%Y-%m-%d-%H.%M.%S).log
integer RC_CMD=0
readonly DATABASE=$1
readonly INTERVAL=$2

# Action
{
MsgStart
db2 connect to ${DATABASE}
print "create baseline tables"
db2 -v "declare global temporary table session.mon_table_baseline as (select current timestamp as                                                                                                           /dev/null
print " delete data from baseline table ( just in case )"
db2 -x "delete from session.mon_table_baseline"  > /dev/null
print "insert baseline data into baseline table"
db2 -v "insert into session.mon_table_baseline select current timestamp as snapshot_timestamp, a.
print " wait the given interval ( ${INTERVAL} seconds )"
sleep ${INTERVAL}
print " done sleeping"
#print " collect the delta information and return the wanted information"
print " Overflow Related Metrics "
print " ------------------------ "
db2 -v " select timestampdiff(2,char(timestamp(current timestamp)-b.snapshot_timestamp)) as snaps                                                                                                            TABNAME,c.DATA_OBJECT_L_PAGES as Logical_pages_on_disk,c.OVERFLOW_ACCESSES-b.OVERFLOW_ACCESSES a                                                                                                            /( c.rows_read - b.rows_read) as PCT_OVERFLOW_ACCESSES_ROWS_READ from table(mon_get_table(NULL,N                                                                                                           R=b.MEMBER where c.rows_read-b.rows_read > 0  order by 5,6,7 desc fetch first 50 rows only"

MsgSuccess "End of the script - `date`"
#} > ${LOGFILE}
} | tee ${LOGFILE}
#}


# History:
# --------
# 1.1 2012-XX-XX Creation

# To do:
# ------