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.

grep -p (paragrah) on linux using awk

AIX grep has a pretty nifty way of matching a paragraph using grep -p. It does not exist on my linux box, making parsing with ksh and bash a little irritating.

Here is an awk file that will simulate what we have on AIX. (just replace the "Dynamic SQL Statements:" part)

#-- -----
#-- dynamicSQL.awk
#-- -----
BEGIN {
FS="\n";
RS="";
}
/Dynamic SQL Statements:/ { print }
#-- -----

Here is how to get the Dynamic SQL Statements paragraph on a db2pd output.
db2pd -d sample -dynamic sql | awk -f dynamicSQL.awk
or you could do it this way too:
db2pd -d sample -dynamic sql | awk 'BEGIN { FS="\n"; RS=""; } /Dynamic SQL Statements:/ { print }'

producing this output:
Dynamic SQL Statements:
Address AnchID StmtUID NumEnv NumVar NumRef NumExe Text
0xA7C62B50 76 2 1 1 1 1 select count(*) from employee
0xA7C627B0 76 1 0 0 0 0 SET CURRENT LOCALE LC_CTYPE = 'en_US'

parse away!