这期内容当中小编将会给大家带来有关DB2搜集数据脚本怎样用于数据库hang或性能问题的分析，文章内容丰富且以专业的角度为大家分析和叙述，阅读完这篇文章希望大家可以有所收获。
由于产品的bug，经常会发生一些无法解释的问题。在这种情况下我们就需要搜集相关的数据给产品部门分析原因并改进。
DB2也不例外，IBM把这些bug称为APAR。IBM官方提供的搜集数据的相关命令有：
db2support，用于各种场景，特别是数据库实例crash的情况，用法：
db2support . -d
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
上述就是小编为大家分享的DB2搜集数据脚本怎样用于数据库hang或性能问题的分析了，如果刚好有类似的疑惑，不妨参照上述分析进行理解。如果想知道更多相关知识，欢迎关注创新互联-成都网站建设公司行业资讯频道。
