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!

Database Configuration Versioning

The crontab takes care of grabbing a copy of my database manager and database configuration files(db2 dbm and db cfg). A regular "get dbm and get db cfg" in time will generate a lot files. Multiply these by the number of databases and environments and you have a lot more.

Making the scripts smarter will make the number of files manageable. Saving configs only when there are changes should do the trick.

Now how to implement it?

. get cfg (filter the active logs S0000nnn.LOG, not a cfg)
. if there are changes, timestamp and keep file

Using this method, one can call the script manually even if scheduled and the number of files will only grow if there are changes.

This is also very friendly with code versioning tools(cvs/svn/git diff), so you can check them in if you have them.

The timestamp on the file makes it easier to determine what changed when.

scripts:
compare.sh = diff of file, needed by getDBCFG and getDBMCFG
getDBCFG.sh = ./getDBCFG.sh DBNAME
getDBMCFG.sh = ./getDBMCFG.sh

Hello World!

This is how everything starts... Doesn't it?