DB2搜集数据脚本怎样用于数据库hang或性能问题的分析

2024-04-02 19:55

短信预约 -IT技能 免费直播动态提醒

这期内容当中小编将会给大家带来有关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