[Db2] MustGather: When too high CPU usage in Db2 server

Answer

1. Preparation

[Common for Linux/UNIX/Windows environment]

db2mon script for monitoring performance

• The db2mon script file depends on the version of Db2 server, therefore check the version by db2level command output and download correspond file for the Db2 version.
• db2mon script monitors database activity for 30 seconds (default) and generates report.
• From Db2 v11.1, the db2mon script file become installed under sqllib/samples/perf directory.

[Additional preparation for Windows environment]

In Windows environment, db2mon.sql file included in .tar file downloaded as above.
Following conditions need to be satisfied to use db2mon.sql.

• USER TEMPORARY table space is required. When there is no USER TEMPORARY table space, create it before db2mon.sql was run. Following statement is the sample for creating USER TEMPORARY table space.

  
  db2 create user temporary tablespace [tablespace name]

• Database configuration parameter MON_ACT_METRICS need to be at least BASE (which is default), and MON_REQ_METRICS need to be at least BASE (default is EXTENDED). Check the current setting of these parameters and modify if any of them was below the requirement, by using UPDATE DB CFG command.
   

  db2 update db cfg using mon_act_metrics base
  db2 update db cfg using mon_req_metrics base

2. Gathering materials while the problem (High CPU usage) is happening.

[Linux/UNIX environment]

In Linux/UNIX environment, login as an instance owner and issue following commands.

$ top -b -n 5 > top_`date +"%Y%m%d_%H%M%S"`.out
$ db2pd -eve > db2pd-eve_`date +"%Y%m%d_%H%M%S"`.out
$ db2pd -stack all
$ ./db2mon.sh [database name] > db2mon_`date +"%Y%m%d_%H%M%S"`.out

-- wait one minute --

$ top -b -n 5 > top_`date +"%Y%m%d_%H%M%S"`.out
$ db2pd -eve > db2pd-eve_`date +"%Y%m%d_%H%M%S"`.out
$ db2pd -stack all
$ ./db2mon.sh [database name] > db2mon_`date +"%Y%m%d_%H%M%S"`.out

-- wait one minute --

$ top -b -n 5 > top_`date +"%Y%m%d_%H%M%S"`.out
$ db2pd -eve > db2pd-eve_`date +"%Y%m%d_%H%M%S"`.out
$ db2pd -stack all
$ ./db2mon.sh [database name] > db2mon_`date +"%Y%m%d_%H%M%S"`.out

After the commands were completed, gather db2support. Please refer following technote for detail procedure.
[Db2] How to collect db2support (for UNIX/Linux platforms)

[Windows environment]

In Windows environment, login as "Administrator" and open "Db2 command Window as Administrator" and issue following commands.

> db2 connect to [database name]

> typeperf -sc 15 -si 1 "\process(*)\% User Time" "\thread(*)\% User Time" -f BIN -o typeperf_"%DATE:/=_%_%TIME::=-%".blg
> db2pd -eve > db2pd_eve_"%DATE:/=_%_%TIME::=-%".out
> db2pd -stack all
> db2 -tvf db2mon.sql > db2mon_"%DATE:/=_%_%TIME::=-%".out

-- wait one minute --

> typeperf -sc 15 -si 1 "\process(*)\% User Time" "\thread(*)\% User Time" -f BIN -o typeperf_"%DATE:/=_%_%TIME::=-%".blg
> db2pd -eve > db2pd_eve_"%DATE:/=_%_%TIME::=-%".out
> db2pd -stack all
> db2 -tvf db2mon.sql > db2mon_"%DATE:/=_%_%TIME::=-%".out

-- wait one minute --

> typeperf -sc 15 -si 1 "\process(*)\% User Time" "\thread(*)\% User Time" -f BIN -o typeperf_"%DATE:/=_%_%TIME::=-%".blg
> db2pd -eve > db2pd_eve_"%DATE:/=_%_%TIME::=-%".out
> db2pd -stack all
> db2 -tvf db2mon.sql > db2mon_"%DATE:/=_%_%TIME::=-%".out

After the commands were completed, gather db2support. Please refer following technote for detail procedure.
[Db2] How to collect db2support (for Windows platforms)

3. contact IBM technical support and provide materials.

db2pd -stack all command generates files under DIAGPATH with the name <PID>.<TID>.stack.txt (Linux/UNIX) or <PID>.<TID>.stack.bin (Windows). These files are included in db2support.zip file.
The other output file is created in current directory.
Regarding to DIAGPATH, please refer the following technote.
[Db2] output path of db2diag.log and administration notification log file

example:

db2xprt 4194320.000.stack.bin 4194320.000.stack.fmt

Other tips

• top command / typeperf command gathers information for 15 seconds, db2mon.sh shell script gathers for 30 seconds, and generates report file after completion.
• When these command / shell script failed into hang and did not complete, interrupt by pressing Ctrl+C.
• When you terminated db2mon shell script in the middle, for example by pressing Ctrl+C, CURRENT SCHEMA special register value might not be reset and left as SESSION_USER.
• When db2pd -eve command failed into hang and did not complete, use following set of db2pd command as an alternative.
   

  [Linux/UNIX environment]

  $ db2pd -edus > db2pd_edus_`date +"%Y%m%d_%H%M%S"`.out
  $ db2pd -agents > db2pd_agents_`date +"%Y%m%d_%H%M%S"`.out
  $ db2pd -db [database name] -apinfo > db2pd_apinfo_`date +"%Y%m%d_%H%M%S"`.out

  [Windows environment]

  > db2pd -edus > db2pd_edus_"%DATE:/=_%_%TIME::=-%".out
  > db2pd -agents > db2pd_agents_"%DATE:/=_%_%TIME::=-%".out
  > db2pd -db [database name] -apinfo > db2pd_apinfo_"%DATE:/=_%_%TIME::=-%".out

Reference
db2pd - Monitor and troubleshoot Db2 database command
Collecting and reporting performance monitor data with db2mon
db2xprt - Format trap file command