Appendix D. Comparing Index Advisor Recommended Indexes with Existing Indexes

Below is a Korn shell script to compare the indexes recommended by the index advisor with the existing indexes.

NOTE

In this script, please change the following: dbname: to the name of the database SCHEMA: to the table schema And if the Index Advisor does not recommend any indexes, there will be no output.

#!/usr/bin/ksh
#
#Passed name of file containing SQL
FN=${1%.sql}
STMT="db2advis -d dbname  -i ${FN}.sql -t 0  -o ${FN}.inx > ${FN}.advis"
#echo $STMT
eval $STMT
db2  connect to dbname
echo " " > ${FN}.sug
grep CREATE ${FN}.inx | grep -v ET | cut -d"\"" -f4 | while read TN
do
#echo $TN
STMT="db2  \"export to temp of del messages /dev/null select
substr(tabname,1,20),substr(colnames,1,30) from syscat.indexes where
tabschema='SCHEMA' and tabname='$TN' \""

#echo $STMT
eval $STMT
echo "    " >> ${FN}.sug
echo "Current Existing indexes" >> ${FN}.sug
cat temp >> ${FN}.sug
echo "DB2ADVIS Recommended indexes"  >> ${FN}.sug
grep CREATE ${FN}.inx | grep -v ET  | grep $TN  >> ${FN}.sug
done