db2数据库监控脚本

db2数据库监控脚本

主要用途

1、监控db2数据库性能；
2、按照监控日期生成文件夹，存放监控日志文件；
3、自动清理过期日志；
4、可以设定监控时间间隔与监控次数；
5、结合crontab计划任务，可以设定何时调用脚本；

注意事项

需要根据业务需要，适当设置监控频率与监控周期；

版本移植性

因Linux与AIX在脚本编写上有少许差异，所以分为两个脚本。

Linux下db2监控脚本

Linux版本

Oracle Linux Server release 6.7

脚本文件：snaps.sh

#!/bin/sh
# usage: monitor the db2 health on linux;
# std:
# 1:log dir : snaps.%Y-%m-%d 
# 2:log retains : only retain the last 7 days logs;
#
# set temp path;
export PATH=/home/db2inst1/sqllib/bin:$PATH

# set the max monitor count;
maxCount=5
sleeptime=600
intervalCount=1

# set base dir;
base_dir=/home/db2inst1/tmp/snaps
out_dir=$base_dir/snaps.$(date +%Y-%m-%d)

# show cmd usage;
if [ $# -eq 0 ]; then 
    echo "Usage:sh snaps.sh <database>"
    exit
fi

# clear old logs;
old_dir=$base_dir/snaps.$(date -d "7 days ago" "+%Y-%m-%d")
if [ -d "$old_dir" ]; then 
    echo rmdir $old_dir
    rm -r $old_dir
fi

# make new dir;
if [ ! -d "$out_dir" ]; then 
    echo mkdir $out_dir
    mkdir $out_dir
fi

# exit for test;
# exit 0

# data monitor;
DB=$1
db2 terminate 2>&1
db2 connect to $DB
db2 reset monitor all
db2 update monitor switches using bufferpool on lock on sort on statement on table on uow on timestamp on
while [ $intervalCount -le $maxCount ] ; do
    echo $(date +%H:%M:%S) : $intervalCount/$maxCount
    suffix=$(date +%H:%M:%S.log)

    db2 get snapshot for database manager > $out_dir/dbmsnap.$suffix 2>&1
    db2 get snapshot for database on $DB > $out_dir/$DB.dbsnap.$suffix 2>&1
    db2 get snapshot for applications on $DB > $out_dir/$DB.appsnap.$suffix 2>&1
    db2 get snapshot for tables on $DB > $out_dir/$DB.tablessnap.$suffix 2>&1
    db2 get snapshot for tablespaces on $DB > $out_dir/$DB.tbspsnap.$suffix 2>&1
    db2 get snapshot for locks on $DB > $out_dir/$DB.locksnap.$suffix 2>&1
    db2 get snapshot for bufferpools on $DB > $out_dir/$DB.bpsnap.$suffix 2>&1
    db2 get snapshot for dynamic sql on $DB > $out_dir/$DB.dynsql.$suffix 2>&1

    let intervalCount++
    sleep $sleeptime
done
db2 terminate

exit 0

AIX下db2监控脚本

Aix版本

Aix 6.1.4.0

脚本文件：snaps.sh

#!/bin/sh
# usage: monitor the db2 health on aix;
# std:
# 1:log dir : snaps.%Y-%m-%d 
# 2:log retains : only retain the last 3 days logs;
#
# set temp path;
export PATH=/home/db2inst1/sqllib/bin:$PATH

# set the max monitor count;
maxCount=6
sleeptime=600
intervalCount=1

# set base dir;
base_dir=/home/db2inst1/tmp/snaps
out_dir=$base_dir/snaps.$(date +%Y-%m-%d)

# show cmd usage;
if [ $# -eq 0 ]; then 
    echo "Usage:sh snaps.sh <database>"
    exit
fi

# clear old logs;
old_dir=$base_dir/snaps.$(TZ=aaa$(expr 24 \* 3) date +%Y-%m-%d)
if [ -d "$old_dir" ]; then 
    echo rmdir $old_dir
    rm -r $old_dir
fi

# make new dir;
if [ ! -d "$out_dir" ]; then 
    echo mkdir $out_dir
    mkdir $out_dir
fi

# exit for test;
#exit 0

# data monitor;
DB=$1
db2 terminate 2>&1
db2 connect to $DB
db2 reset monitor all
db2 update monitor switches using bufferpool on lock on sort on statement on table on uow on timestamp on
while [ $intervalCount -le $maxCount ] ; do
    echo $(date +%H:%M:%S) : $intervalCount/$maxCount
    suffix=$(date +%H:%M:%S.log)

    db2 get snapshot for database manager > $out_dir/dbmsnap.$suffix 2>&1
    db2 get snapshot for database on $DB > $out_dir/$DB.dbsnap.$suffix 2>&1
    db2 get snapshot for applications on $DB > $out_dir/$DB.appsnap.$suffix 2>&1
    db2 get snapshot for tables on $DB > $out_dir/$DB.tablessnap.$suffix 2>&1
    db2 get snapshot for tablespaces on $DB > $out_dir/$DB.tbspsnap.$suffix 2>&1
    db2 get snapshot for locks on $DB > $out_dir/$DB.locksnap.$suffix 2>&1
    db2 get snapshot for bufferpools on $DB > $out_dir/$DB.bpsnap.$suffix 2>&1
    db2 get snapshot for dynamic sql on $DB > $out_dir/$DB.dynsql.$suffix 2>&1

    let intervalCount=intervalCount+1
    sleep $sleeptime
done
db2 terminate
exit 0

计划任务

0 7-19 * * * nohup sh /home/db2inst1/tmp/snaps/snaps.sh 数据库名称 &

参考

Expr算数运算
详细讲解算数运算方式，并且因跨平台移植问题，建议使用expr进行算数运算;
参考：http://blog.chinaunix.net/uid-209416-id-2410742.html

Aix 计算前一天
参考：http://blog.163.com/squall_smile/blog/static/60349840201431593851292/
详细讲解aix中使用TZ计算日期的方式

linux Date日期
参考：http://blog.chinaunix.net/uid-9370128-id-271932.html

调度服务cron
参考：http://www.cnblogs.com/itech/archive/2011/02/09/1950226.html

判断文件目录是否存在
参考：http://www.cnblogs.com/emanlee/p/3583769.html