When a performance problem is perceived within DB2, one of the most popular and easiest data collection approaches is to run db2fodc -perf
This creates a DB2FODC_Perf... directory in the diagnostic data directory.
However this data collection is often not sufficient to troubleshoot the issue.
The reason is that the collection is really geared towards high cpu situations.
It is lightweight for this reason, but this means that some more meaningful data can be left out.
For instance if the data collection is not because of a high cpu situation, but because of an I/O bottleneck or a contention on another resource e.g. a latch then some data might be missed.
The db2fodc -hang data collection on the other hand is much more comprehensive and has a lot of value in generic performance collection .
However, it can be intrusive to run this collection.
For instance it turns on a short db2trc and if cpu usage is already high, then the data collection itself can grind things to a complete halt.
( in more extreme cases, sensitive HA monitoring might actually decide to failover )
Both the db2fodc -hang and -perf option use the somewhat antiquated snapshot monitoring.
A more up to date approach uses the monitoring table functions.
A very good tool to use for this is the db2mon.sh script as documented in these links.
In order to augment db2fodc -perf for more generic data collection, without having to resort to the heavy data collection of db2fodc -hang
we can create a hybrid solution to call the db2mon script for collection information.
The first step is to create a data collection profile which can be invoked by the db2fodc -profile option.
Let's call this profile "perfproblem"
There is a file in ~sqllib/cfg called db2fodc.profile which defines these profiles.
We can add such a stanza and the end of this file
A few remarks.
1. The db2fodc -profile option will share usage with the same script as db2fodc -hang. So we have to explicitly specify OFF for some of the options, otherwise it will revert to the default usage.
2. Specify the options without double quotes ( they are incorrectly enclosed with " " in the examples )
3. Call stacks can be very useful in seeing what is going on, so I left this option as ON in the example. ( db2fodc -perf will only selectively collect call stacks. )
This is the first step. It can already be invoked using :
db2fodc -profile perfproblem
However, this will still revert to the default snapshot data collection.
This is because it will invoke ~/sqllib/bin/db2cos_hang with these options and will hence call the default "db2monitor" section, using the snapshots.
The next step is to change db2cos_hang, any changed db2cos script should be put into ~/sqllib/adm rather than ~/sqllib/bin,
so we copy the one from ~/sqllib/bin to ~/sqllib/adm and work with that one.
$ cp ~/sqllib/bin/db2cos_hang ~/sqllib/adm
now open the ~/sqllib/adm/db2cos_hang file and around line 713 you will see :
716 start_section "DB2 monitoring"
embedded in there are the snapshot monitoring calls.
We can replace this section with this :
start_section "DB2 monitoring"
echo "Estimated time to completion is 10 minutes (Ctrl-C to interrupt)"
while (( i <= $db2monitor_iterations ))
for NODE in $dbpartitionnum
if [ $NODE != "X" ] ; then
db2pd -inst -alldbs > DB2PD/db2pd.inst.alldbs.m$NODE.$i 2>&1; echo ".\c";
db2pd -inst -alldbs > DB2PD/db2pd.inst.alldbs.$i 2>&1; echo ".\c";
for DB in $databases
db2mon.sh $DB > DB2SNAPS/$DB.db2mon.$i 2>&1; echo ".\c";
# only sleep if it is not the last iteration
if [ $i -ne $db2monitor_iterations ] ; then
echo "Waiting `expr $db2monitor_sleep / 60` minutes and `expr $db2monitor_sleep % 60` secs before starting the next iteration..."
see that the db2mon.sh call is now used.
For convenience the output directory DB2SNAPS is still used.
The final step is to make sure that the db2mon.sh script and the db2mon.sql script are accessible, the easiest approach is to copy these into ~/sqllib/adm as well.
( db2mon.sh and db2mon.sql can be obtained from either of the links pasted above. )