###简易版本###
#!/usr/bin/ksh
#Script: runstats_on_db.sh
#Author: goopand
#Platform: AIX only
#Version: 1.0
for db_name in db_01 db_02
do
db2 connect to ${db_name}
db2 -x "select lower(ltrim(rtrim(tabschema)))||'.'||lower(ltrim(rtrim(tabname))) tb_name from syscat.tables where type in ('T','S') order by 1 with ur" > /tmp/${db_name}_all_tbls.txt
while read tb_name
do
echo "[`date`] runstats on table ${tb_name} ... "
db2 "runstats on table ${tb_name} with distribution and detailed index all"
done < /tmp/${db_name}_all_tbls.txt
db2 connect reset
done
###升级版本###
#!/usr/bin/ksh
#Script: runstats_on_db.sh
#Author: goopand
#Platform: AIX only
#Version: 1.2
Show_Usage()
{
echo ""
echo "Usage:"
echo " runstats_on_db.sh <db_name>"
echo "Purpose:"
echo " runstats on all tables of database <db_name> "
echo ""
}
Script_Init()
{
#login user name
cur_usr_id=`id -un`
#current date time
cur_time=`date "+%Y-%m-%d %H:%M:%S"`
}
argsCorrectFlag=0
funArgsCorrect()
{
db_match_num=`db2 list active databases | grep -i "Database name" | awk -F"=" '{print $2}' | awk '{print $1}' | grep -i ${db_name} | wc -l | awk '{print $1}'`
if [ ${db_match_num} -ge 1 ] ; then
argsCorrectFlag=1
else
argsCorrectFlag=0
if [ ${db_match_num} -eq 0 ]; then
echo "error: <db_name> '${db_name}' does not exist !"
fi
fi
}
if [ $# -ne 1 ]; then
Show_Usage
exit 1
else
db_name=$1
funArgsCorrect
if [ ${argsCorrectFlag} -ne 1 ]; then
Show_Usage
exit 1
else
Script_Init
fi
fi
runstats_DB()
{
db2 connect to ${db_name}
table_list_file=/tmp/${db_name}_all_tables.txt
#runstats on all tables of database
echo "[${cur_time}] runstats on all tables of database ${db_name} ...\n"
sleep 1
db2 -x "select lower(ltrim(rtrim(tabschema)))||'.'||lower(ltrim(rtrim(tabname))) from syscat.tables where type in ('T','S') order by 1 with ur" > ${table_list_file}
while read tb_name
do
echo "[${cur_time}] runstats on table ${tb_name} ..."
db2 "runstats on table ${tb_name} with distribution and detailed index all"
done < ${table_list_file}
if [ -f ${table_list_file} ]; then
rm -f ${table_list_file}
fi
db2 connect reset
}
#call runstats_DB()
runstats_DB
#End of script