Shell script to automate runstats in db2

had some free time today, so wrote a small script that will automatically create a runstats script for all the tables for all the databases in an instance.

Here is the script :

#————————————————————————————
# Program        :  runstats.sh
# Description   :  The script list all the database on an instance and generate
#                      :  runstats script for each of those databases and run it.
#                      :  The home directory needs to be customized for each instance
#                      :
# Author          :  Raju Pillai
# Date              :  2009-02-13
#———————————————————————————–

export PATH=$PATH:/home/db2inst1/sqllib/bin/
Datetime=”`date +%Y-%m-%d-%H:%M:%S`”
. /home/db2inst1/sqllib/db2profile              # set up DB2 environment
db2 list db directory | grep alias > dbdirectory.out
{
  while read current_line; do
       db_name=`print “$current_line” | awk ‘{ print $4 }’`
       db2 connect to $db_name;
       db2 -x “select ‘RUNSTATS ON TABLE ‘||rtrim(creator)||’.’||rtrim(name)||’ ON ALL COLUMNS WITH DISTRIBUTION ON ALL COLUMNS AND DETAILED INDEXES ALL ALLOW WRITE ACCESS;’ from sysibm.systables where type = ‘T'” > runstats_$db_name.sql
       echo “++++++++++++++++++++++++++++++++++++++++”  > runstats_$db_name.out
       echo “Runstats for $db_name starting at $Datetime” >> runstats_$db_name.out
       db2 -vtf runstats_$db_name.sql >> runstats_$db_name.out
       echo “Runstats for $db_name ended at $Datetime”  >> runstats_$db_name.out
  done
} < dbdirectory.out

# end of scrip