Useful DB2 commands

# collect db2 execution plan

db2 set current explain mode explain
db2 -tvf xxx.sql # execute the sql
db2 set current explain mode no
db2exfmt -d cmsdb -g TIC -w -l -n % -s % -# 0 -o exfmt_bad.txt

# check whether a table needs reorg

db2 "reorgchk current statistics on  table cms.T_NOTICE_SEARCH"

# check active statement count

db2pd -d cmsdb -appl | awk '{print $7,$8}' | sort | uniq -c | sort -k1n
db2pd -d cmsdb -dyn  | grep 

# kill session 42344

db2pd -d cmsdb -appl | awk '$8=="42344" {printf("db2 \"force application(%s)\"\n",$2);}' | sed -n '1,25p' 

# check lock wait

db2 list applications show detail | grep -i lock
db2 get snapshot for application agentid 22868 

# check if there are missing indexes

db2advis -d cmsdb -i 3.sql > db2advis.out.2

# tcp connection group by IP

netstat -an | grep 50000 | grep -i estab | awk '{print $5}' | \
awk -F\. '{printf("%s.%s.%s.%s\n",$1,$2,$3,$4);}' |sort | 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