全库表runstats小脚本

字数 2833阅读 1661评论 2赞 1

<div><div><div>###简易版本###</div><div>#!/usr/bin/ksh</div><div>#Script:     runstats_on_db.sh</div><div>#Author:     goopand</div><div>#Platform:   AIX only</div><div>#Version:    1.0</div></div><div><br></div><div>for db_name in db_name1</div><div>do</div><div>   db2 connect to ${db_name}</div><div><br></div><div>   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</div><div>   while read tb_name</div><div>   do</div><div>      echo "[`date`] runstats on table ${tb_name} ... "</div><div>      db2 "runstats on table ${tb_name} with distribution and detailed index all"</div><div><span style="line-height: 1.8em;">   done < /tmp/${db_name}_all_tbls.txt</span></div><div><br></div><div>   db2 connect reset</div><div>done</div></div><div><br></div><div><br></div><div><br></div><div>###升级版本###</div><div><div>#!/usr/bin/ksh</div><div>#Script:     runstats_on_db.sh</div><div>#Author:     goopand</div><div>#Platform:   AIX only</div><div>#Version:    1.2</div><div><br></div><div>Show_Usage()</div><div>{</div><div>   echo ""</div><div>   echo "Usage:"</div><div>   echo "   runstats_on_db.sh <db_name>"</div><div>   echo "Purpose:"</div><div>   echo "   runstats on all tables of database <db_name> "</div><div>   echo ""</div><div>}</div><div><br></div><div>Script_Init()</div><div>{</div><div>   #login user name</div><div>   cur_usr_id=`id -un`</div><div>   #current date time</div><div>   cur_time=`date "+%Y-%m-%d %H:%M:%S"`</div><div><br></div><div>}</div><div><br></div><div>argsCorrectFlag=0</div><div>funArgsCorrect()</div><div>{</div><div>   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}'`</div><div>   if [ ${db_match_num} -ge 1 ] ; then</div><div>      argsCorrectFlag=1</div><div>   else</div><div>      argsCorrectFlag=0</div><div>          </div><div>      if [ ${db_match_num} -eq 0 ]; then</div><div>         echo "error: <db_name> '${db_name}' does not exist !"</div><div>      fi</div><div>   fi</div><div><br></div><div>}</div><div><br></div><div>if [ $# -ne 1 ]; then</div><div>   Show_Usage</div><div>   exit 1</div><div>else</div><div>   db_name=$1</div><div>   funArgsCorrect</div><div>   if [ ${argsCorrectFlag} -ne 1 ]; then</div><div>      Show_Usage</div><div>          exit 1</div><div>   else</div><div>      Script_Init</div><div>   fi</div><div>fi</div><div><br></div><div>runstats_DB()</div><div>{</div><div>db2 connect to ${db_name}</div><div><br></div><div>table_list_file=/tmp/${db_name}_all_tables.txt</div><div><br></div><div>#runstats on all tables of database</div><div>echo "[${cur_time}] runstats on all tables of database ${db_name} ..."</div><div>sleep 1</div><div>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}</div><div>while read tb_name</div><div>do</div><div>   echo "[${cur_time}] runstats on table ${tb_name} ..."</div><div>   db2 "runstats on table ${tb_name} with distribution and detailed index all"</div><div>done < ${table_list_file}</div><div><br></div><div>if [ -f ${table_list_file} ]; then</div><div>   rm -f ${table_list_file}</div><div>fi</div><div><br></div><div>db2 connect reset</div><div><br></div><div>}</div><div><br></div><div>#call runstats_DB()</div><div>runstats_DB</div><div><br></div><div><br></div><div>#End of script</div></div><div><br></div>

runstats 数据库脚本

著作权归作者所有

如果觉得我的文章对您有用，请点赞。您的支持将鼓励我继续创作！

添加新评论2 条评论

carlyle001其它as
2014-07-18 14:44

牛逼的脚本，收藏了。

kjmhelenkj软件开发工程师上海软件
2014-07-18 11:40

Ctrl+Enter 发表

匿名评论

全库表runstats小脚本

添加新评论2 条评论

作者其他文章

相关文章

相关问题

相关资料