Augment db2fodc data collection with db2mon script

Body

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.

/support/pages/node/556789
https://developer.ibm.com/recipes/tutorials/db2-performance-monitoring-with-db2mon-pl/

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

e.g. :

[perfproblem]
os_config=OFF
basic_db2_config=OFF
os_monitor_info=ON
call_stacks=ON
db2pd_info=ON
db2trc=OFF
ostrc=OFF
db2_config=OFF
COLLECTION_MODE=FULL
db2pd_dump=OFF
more_db2_config=OFF
db2_monitoring=ON
extra_info=OFF
db2monitor_iterations=3
db2monitor_sleep=20
<end>

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 :

    714   db2_monitoring_section()
    715   {
    716     start_section "DB2 monitoring"

embedded in there are the snapshot monitoring calls.

We can replace this section with this :

db2_monitoring_section()
{
  start_section "DB2 monitoring"
  echo "Estimated time to completion is 10 minutes (Ctrl-C to interrupt)"
  integer i=1
  while (( i <= $db2monitor_iterations ))
  do
    for NODE in $dbpartitionnum
    do
    if [ $NODE != "X" ] ; then
      db2pd -inst -alldbs > DB2PD/db2pd.inst.alldbs.m$NODE.$i 2>&1; echo ".\c";
    else
      db2pd -inst -alldbs > DB2PD/db2pd.inst.alldbs.$i 2>&1; echo ".\c";
    fi
    done
    for DB in $databases
    do
       db2mon.sh  $DB > DB2SNAPS/$DB.db2mon.$i  2>&1; echo ".\c";
    done
    # 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..."
      sleep $db2monitor_sleep
    fi
    i=i+1
  done
  end_section
}

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. )