Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you the analysis of how DB2 data collection scripts are used in database hang or performance problems. The article is rich in content and analyzes and describes for you from a professional point of view. I hope you can get something after reading this article.

Due to the bug of the product, some unexplained problems often occur. In this case, we need to collect relevant data to the product department to analyze the reasons and improve.

DB2 is no exception, and IBM calls these bug APAR. The relevant commands officially provided by IBM to collect data are:

Db2support, used in various scenarios, especially in the case of database instance crash, usage:

Db2support. -d-c-s

Db2fodc, for database hang residency, usage:

Db2fodc-hang-alldbs

Or:

Db2fodc-hang full

However, in the actual operation and maintenance process of the financial system, because the business is very important, the first task in the event of failure is to restore the business as soon as possible. The above commands will take a long time, especially the db2fodc command, which must run in the case of database hang (invalid afterwards), and it will take about 20 minutes, which is generally unacceptable to customers. Without the relevant data, the IBM laboratory cannot analyze the reason and cannot improve it. This is where we are often criticized by our customers.

In order to solve this problem, the lab provides lightweight commands to collect data. I encapsulate these commands into scripts. After actual testing, it takes no more than one and a half minutes for a system with a total number of customer connections of 3000 and active connections of 200. and it won't cause more serious performance problems.

The script file is named: gather_basic_data.sh, and the contents are as follows:

#! / 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 1bot gathering basic information 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 [Amurz] [Amurz] `

# 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 +% Ymuri% mmury% dmurf% 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 1180 > vmstat.txt &

If [[$OS = = "AIX"]]; then

Nohup iostat-D-l-T 1180 > iostat.txt &

Else # Linux

Nohup iostat-xtk 1180 > 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

This is how the DB2 data collection script shared by Xiaobian is used to analyze database hang or performance problems. If you happen to have similar doubts, please refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.