db2avis workload

db2avis is very helpful in recommending indexes. The utility is usually given a single sql statement for which it runs a permutation of what-if scenarios to find the best index.

Giving it a complete workload, not just the select sql, but including the deletes/inserts/updates to consider will make its trade-off calculations more complete.

Here is a simple way of generating a workload file from the dynamic SQL cache. The output can be used to feed the db2advis utility.

db2pd -d sample -dyna | \
awk -f dynamicSQL.awk | \
grep -v "NumRef NumExe Text" | \
grep -v "Dynamic SQL Statements:" | \
grep -v "^$" | \
awk 'BEGIN { FS="0x"; RS="0x"; } {print}' | \
awk 'BEGIN { RS=""; } {print "--#SET FREQUENCY " $7 "\n" substr($0,71) ";\n" }' > workload.out


db2advis -d sample -i workload.out

This should hopefully be a good starting point.
# dynamicSQL.awk can be found on the previous post.