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

这期内容当中小编将会给大家带来有关DB2搜集数据脚本怎样用于数据库hang或性能问题的分析，文章内容丰富且以专业的角度为大家分析和叙述，阅读完这篇文章希望大家可以有所收获。

    由于产品的bug，经常会发生一些无法解释的问题。在这种情况下我们就需要搜集相关的数据给产品部门分析原因并改进。

DB2也不例外，IBM把这些bug称为APAR。IBM官方提供的搜集数据的相关命令有：

db2support，用于各种场景，特别是数据库实例crash的情况，用法：

db2support . -d -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

上述就是小编为大家分享的DB2搜集数据脚本怎样用于数据库hang或性能问题的分析了，如果刚好有类似的疑惑，不妨参照上述分析进行理解。如果想知道更多相关知识，欢迎关注行业资讯频道。