共计 6228 个字符，预计需要花费 16 分钟才能阅读完成。
这期内容当中丸趣 TV 小编将会给大家带来有关 DB2 搜集数据脚本怎样用于数据库 hang 或性能问题的分析，文章内容丰富且以专业的角度为大家分析和叙述，阅读完这篇文章希望大家可以有所收获。
由于产品的 bug，经常会发生一些无法解释的问题。在这种情况下我们就需要搜集相关的数据给产品部门分析原因并改进。
DB2 也不例外，IBM 把这些 bug 称为 APAR。IBM 官方提供的搜集数据的相关命令有：
db2support，用于各种场景，特别是数据库实例 crash 的情况，用法：
db2support . -d dbname -c -s
db2fodc，用于数据库 hang 住的情况，用法：
db2fodc -hang -alldbs
或：
db2fodc -hang full
但在金融系统实际运维过程中，由于业务非常重要，发生故障时的首要任务是尽快恢复业务。而以上命令都会耗费比较长的时间，特别是 db2fodc 命令，必须在数据库 hang 的情况下跑（事后跑无效），且需要 20 分钟左右，客户一般来说无法接受。而没有相关数据，IBM 实验室无法分析出原因，也就无法改进。这也是我们经常被客户诟病的地方。
为了解决此问题，实验室提供了轻量级的搜集数据命令，我把这些命令封装成脚本，经过实际测试，在客户总连接数 3000，活跃连接数 200 左右的系统跑完需要不超过 1 分半钟，而且不会导致更严重的性能问题。
脚本文件名为：gather_basic_data.sh，内容如下：
#!/bin/ksh
# David Shen 2018/05/24 V1.0
# Used to gather necessary information(Stack,Trace) when database hang or have performance issues
#
# Functions
Usage ()
{
echo
echo Usage: $0 [-d dbname], [-t 0|1] [-f 0|1] [-p outputpath]
-d parameter is optional, if not specified, gather all db info under current db2 instance
-t parameter indicate if db2 trace info is needed, default is 0 – not needed
-f parameter indicate if db2 trace output need to be formated, default is 0 – not needed
-p parameter indicate where the output data should locate,default is instance db2diag path
Example: gather_basic_data.sh;gather_basic_data.sh -d testdb;gather_basic_data.sh -d testdb -t 1;
gather_basic_data.sh -d testdb -t 1 -f 1;gather_basic_data.sh -d testdb -t 1 -p /db/dbdata/
echo
exit 1
}
# Main function
# initial parameter
# The NeedDB2Trace variable indicates whether we need DB2 trace data!
# The NeedFmtTrace variable indicates whether we need to format trace data in the script!
# The NeedStack variable indicates whether we need DB2 stack files data!
TraceSleepTime=2
StackTraceInterval=5
NeedDB2Stack=1
OS=`uname -s|tr [a-z] [A-Z]`
#Read parameter
while getopts :d:t:f:p: opt
do
case ${opt} in
d ) DBName=${OPTARG} ;;
t ) NeedDB2Trace=$OPTARG ;;
f ) NeedFmtTrace=${OPTARG} ;;
p ) ParentDir=${OPTARG} ;;
esac
done
if [[-z $DBName]]; then
DBName=
fi
if [[-z $NeedDB2Trace]]; then
NeedDB2Trace=0
fi
if [[-z $NeedFmtTrace]]; then
NeedFmtTrace=0
fi
if [[-z $ParentDir]]; then
ParentDir=$(db2 get dbm cfg | grep Diagnostic data directory path | awk {print $NF} )
fi
#—————————————————————————
# Parm Checks
#—————————————————————————
if [$DBName !=] ; then
#DBName is valid and db is acitve?
if ((`db2 list active databases|grep -i $DBName|wc -l` == 0)) ; then
echo No active db named $DBName under this instance!
Usage
fi
fi
if [$NeedDB2Trace != 0] [$NeedDB2Trace != 1] ; then
echo -t Parameter not correct!
Usage
fi
if [$NeedDB2Trace == 1] ; then
if [$NeedFmtTrace != 0] [$NeedFmtTrace != 1] ; then
echo -f Parameter not correct!
Usage
fi
fi
if [! -d $ParentDir] ; then
echo Error: $ParentDir,no this directory!
Usage
else
#if there is enough space(1G)?
Freespace=$(df -m $ParentDir|tail -1|awk {print $3} )
if [${Freespace} -lt 1024 ] ; then
echo There is no enough space under $ParentDir,at least 1G space needed!
exit -1
fi
fi
##### Prepare for the directory that output files will be generated to.
CurrentTime=`date +%Y-%m-%d-%H.%M.%S`
DataPath= ${ParentDir}/${0}.${CurrentTime}
mkdir ${DataPath}
if [$? -ne 0]
then
echo `date` —– Failed to make directory ${DataPath}! Exiting …\n
exit -1
else
echo `date` —– Current working directory is $PWD \n
cd ${DataPath}
echo `date` —– Changed working directory to $DataPath \n
mkdir StackFiles
if [$? -ne 0]
then
echo `date` —– Failed to make directory ./StackFiles! Exiting …\n
exit -1
else
StackFilePath= ${PWD}/StackFiles
echo `date` —– Stack files are going to be put in $StackFilePath!\n
fi
LogFile= ${0}.log
echo `date` —– Starting script ${0}, log file is ${LogFile}\n\n | tee ${LogFile}
fi
##### Start gathering data
# Start gathering some OS data in background
echo `date` —– Start gathering some OS data in background …\n | tee -a ${LogFile}
nohup vmstat -tw 1 180 vmstat.txt
if [[$OS == AIX]]; then
nohup iostat -D -l -T 1 180 iostat.txt
else #Linux
nohup iostat -xtk 1 180 iostat.txt
fi
echo `date` —– OS data was submitted to background!\n\n | tee -a ${LogFile}
for i in 1 2 3
do
if [$NeedDB2Stack -eq 1]
then
# Stack files by db2pd -stack command
echo `date` —– Start generating stack files for the $i time …\n | tee -a ${LogFile}
# db2pd -stack all db2pd_stack_all_$i.txt
db2pd -stack all dumpdir=${StackFilePath} db2pd_stack_all_$i.txt
sleep $StackTraceInterval
echo `date` —– Stack files for the $i time is done!\n\n | tee -a ${LogFile}
else
echo `date` —– Not going to collect stack files!\n\n | tee -a ${LogFile}
fi
# db2pd data
echo `date` —– Start gathering db2pd data for the $i time …\n | tee -a ${LogFile}
if [$DBName ==]
then
db2pd -alldbs -appl -trans -apinfo db2pd_appl_$i.txt
db2pd -alldbs -locks wait db2pd_locks_$i.txt
db2pd -alldbs -logs db2pd_logs_$i.txt
db2pd -edu -agent db2pd_edu_$i.txt
db2pd -latch db2pd_latch_$i.txt
db2pd -dbptnmem -memset -mempool -inst -alldbs db2pd_mem_$i.txt
db2pd -alldbs -tcb db2pd_tcb_$i.txt
else
db2pd -db ${DBName} -appl -trans -apinfo db2pd_appl_$i.txt
db2pd -db ${DBName} -locks wait db2pd_locks_$i.txt
db2pd -db ${DBName} -logs db2pd_logs_$i.txt
db2pd -edu -agent db2pd_edu_$i.txt
db2pd -latch db2pd_latch_$i.txt
db2pd -dbptnmem -memset -mempool -inst -db ${DBName} db2pd_mem_$i.txt
db2pd -db ${DBName} -tcb db2pd_tcb_$i.txt
fi
echo `date` —– db2pd data for the $i time is done!\n\n | tee -a ${LogFile}
# DB2 trace (db2trc) data
if [$i -eq 2 -a $NeedDB2Trace -eq 1]
then
echo `date` —– Start gathering DB2 trace data, which will take at least $TraceSleepTime seconds …\n | tee -a ${LogFile}
db2trc on -t -i 128M
db2trc info db2trc_info.out
echo `date` —– DB2 trace data is turned on!\n | tee -a ${LogFile}
sleep $TraceSleepTime
db2trc stop ${LogFile} 2 1
db2trc dump db2trc.dmp ${LogFile} 2 1
db2trc off ${LogFile} 2 1
echo `date` —– Binary DB2 trace data is dumped out!\n\n | tee -a ${LogFile}
if [$NeedFmtTrace -eq 1]
then
db2trc flw -t db2trc.dmp db2trc.flw
db2trc fmt db2trc.dmp db2trc.fmt
else
echo `date` —– Not going to format binary trace data, please format the data manually after the script is finished!\n\n | tee -a ${LogFile}
fi
fi
# Some OS data
echo `date` —– Start gathering some OS data …\n | tee -a ${LogFile}
ps auxw ps_auxw_$i.txt
ipcs -a ipcs_$i.txt
if [[$OS == AIX]]; then
ps -kefl ps_kefl_$i.txt
svmon -G svmon_G_$i.txt
else #Linux
ps -elf ps_elf_$i.txt
netstat -v netstat_v_$i.txt
swapon -s swapon_$i.txt
free free_$i.txt
top -b -n 1 top_$i.txt
fi
echo `date` —– OS data is done!\n\n | tee -a ${LogFile}
done
# Other DB2 data
echo `date` —– Start gathering some other DB2 data …\n | tee -a ${LogFile}
db2set -all db2set.txt
db2pd -dbmcfg -dbcfg -alldbs db2pd_cfg.txt
echo `date` —– The other DB2 data is done!\n\n | tee -a ${LogFile}
echo `date` —– All data gathered, exiting …\n\n | tee -a ${LogFile}
exit 0
上述就是丸趣 TV 小编为大家分享的 DB2 搜集数据脚本怎样用于数据库 hang 或性能问题的分析了，如果刚好有类似的疑惑，不妨参照上述分析进行理解。如果想知道更多相关知识，欢迎关注丸趣 TV 行业资讯频道。
