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