Identifying multiple unused indexes in DB2 automatically

db2pd utility could be used to identify unused indexes in db2. Nevertheless, when the amount of unused index is significant, manually querying index information could be tedious. Here is how to retrieve unused index automatically.

# retrieve db2pd tcbstats information

db2pd -d cmsdb -full -tcbstats all file=/tmp/db2pd_tcb.txt

# generate tables with index and not used index list
# note: you may need to change the line numbers accordingly

sed -n '2192,3752 p' /tmp/db2pd_tcb.txt > table_index_full_list.txt
sed -n '3754,5306 p' /tmp/db2pd_tcb.txt | awk '$10=="0" {print $1,$2,$3}' | grep -v SYS | sed '/^$/d' | sort | uniq > not_used_index_tablename_list.txt

# generate sql statements to retrive index information in syscat.indexes

while read -r addr name iid
awk -v ad="$addr" -v n="$name" -v i="$iid" '$1==ad && $9==n && $11==i {print "select varchar(INDNAME,60), varchar(TABSCHEMA,15), varchar(TABNAME,60), IID from syscat.indexes where TABNAME=" "'\''" $9 "'\''" " and IID=" $11 " and owner=" "'\''" $10 "'\''" ";"}' table_index_full_list.txt
done < not_used_index_tablename_list.txt > not_used_index.sql

db2 connect to cmsdb
db2 -tf not_used_index.sql | grep -vE "\-\-\-|1*2*3*IID|record" | sed '/^$/d' | more



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s