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

共计 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 行业资讯频道。