Analyzing db2 snapshot and db2diag.log

# analyzing db2 snapshot
# check sql statement occurrence in a single snapshot file

sed -n '/Dynamic SQL statement text/,/Memory usage for application/p' snapshot_1.txt | sed '/Dynamic SQL statement text/d' | sed '/Memory usage for application/d' | sed '/Agent process\/thread ID/d' | sed '/^\s*$/d' | sort | uniq -c | sort -k1n | grep -v "SYSIBM.SYSTABLES"

# check sql occurrence in several snapshots

for i in `ls snapshot_[1-9].txt`
do
sed -n '/Dynamic SQL statement text/,/Memory usage for application/p' $i | sed '/Dynamic SQL statement text/d' | sed '/Memory usage for application/d' | sed '/Agent process\/thread ID/d' | sed '/^\s*$/d' | sort | uniq  | grep -v "SYSIBM.SYSTABLES" >> sql1.txt
done

# analyzing db2diag.log
# check lock escalation time for a specific timestamp

sed -n '/2016-07-18-09.43/,/DB2 is performing lock escalation/p' db2diag.log | grep -c "2016-07-18-09.43"
sed -n '/2016-07-18-09.43/,/DB2 is performing lock escalation/p' db2diag.log | grep -c "DB2 is performing lock escalation"

# lock escalation times in different timestamps

grep -B7 "DB2 is performing lock escalation" db2diag.log | grep 2016-07-18- | awk '{print $1}' | awk -F\. '{printf("%s.%s.%s\n",$1,$2,$3);}' |sort | uniq -c

# check lock escalation sql statements

sed -n '/DB2 is performing lock escalation/,/code:/p' db2diag.log| \
sed -n '/The current statement being executed/,/code:/p' | \
sed 's/.*The current statement being executed is "//g;s/"\. Reason.* code: "2"/;/g' | \
tr "\n" " " | \
tr ";" "\n" | \
sed 's/alias_sp_syn_info.c_node_id in (.*)/alias_sp_syn_info.c_node_id/' |\
sed 's/alias_sp_syn_info.c_node_id = [0-9][0-9]*/alias_sp_syn_info.c_node_id/' | \
sort  -k1 | uniq -c | sort -k1n
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s