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.


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 for DB2 UDB v8 for Linux, Unix and Windows
Advanced DBA Certification Guide and Reference for DB2 Universal Database v8 for Linux, UNIX, and Windows
ISBN: 0130463884
EAN: 2147483647
Year: 2003
Pages: 121 © 2008-2017.
If you may any questions please contact us: