Chapter: Appendix D. Comparing Index Advisor Recommended Indexes with Existing Indexes
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
 | Advanced dba certification guide and reference |
