Two Worlds of DB2 LUW Performance Monitoring

I generally suggest both my readers and my clients turn on the monitor switches using the DFT_MON* parameters in the DBM CFG. However, I find myself using traditional snapshots less and less. The main time I still use them is when I’m panicked and my older training kicks in. But thinking back today, the only time in the last month that I used a “GET SNAPSHOT” was when working on a 8.2 database (which is still supported by IBM when it is in conjunction with WebSphere Commerce).

Two Worlds

You can think of the two monitoring methods as the “old” way and the “new” way of accessing performance monitoring data. Starting in earnest in DB2 9.7, IBM started to introduce the MON_* table functions and views as a lighter weight methodology for monitoring DB2. The IBM DB2 Info Center refers to them as simply “Monitoring Routines and Views”. IBM describes them as having less impact on the database being monitored, and also as IBM’s strategic direction for database monitoring.

The older methodology is referred to as “Snapshot Monitoring” – you used to have no choice but the GET SNAPSHOT command. SQL methods have been introduced over the years, so there are other ways to get to the data. I fully expect that in some version IBM will announce the deprecation and finally discontinue snapshot monitoring.

Today is what it looks like while we’re in transition between the methods – there is more functionality added to the monitoring routines and views with Fixpacks of 9.7. And it takes those of us with significant experience with the older method some time to move over to fully using them. We try to make old stuff work with the new methodologies. If there was just a “RESET MONITOR SWITCHES” function, some would take it up quicker. I imagine there are technical reasons that there isn’t.

Also, I’m slow to the game. Maybe SAP certifies a new version of DB2 within 9 weeks, but IBM WebSphere Commerce? I’m lucky if they do within TWO YEARS, and even luckier if they don’t require my clients to buy expensive separate DB2 licenses to do it. I hear they’re working on that, but have yet to see DB2 10.1 be allowed with any version of WebSphere Commerce, and 10.1 has been out for well over a year now. If you are experienced and already know all this new monitoring methodology inside and out, just comment and help a fellow DBA out. Perhaps something more cutting edge should be on my wishlist the next time I change jobs.

Monitor Switches and Snapshot Monitoring

The monitor switches control what data db2 collects for the older snapshot monitoring interface. Yes, they can be turned on at the command line for a particular session, but if you set the default settings for them in the DBM CFG, then you’ll have DB2 collecting the data you’ll need if you run into a performance problem. To check them:

$ db2 get dbm cfg |grep DFT_MON
Buffer pool                         (DFT_MON_BUFPOOL) = ON
Lock                                   (DFT_MON_LOCK) = ON
Sort                                   (DFT_MON_SORT) = ON
Statement                              (DFT_MON_STMT) = ON
Table                                 (DFT_MON_TABLE) = ON
Timestamp                         (DFT_MON_TIMESTAMP) = ON
Unit of work                            (DFT_MON_UOW) = ON

To set them, use:


I included the setting for turning the Health Monitor off. I don’t use it. If you don’t use it, turn it off – it can cause performance problems.

Changes to these parameters take place online, as long as you are attached to the instance and use the “immediate” keyword.

What does turning these on get you? It gets you data for all fields in the “GET SNAPSHOT” output, for all SYSIBMADM views, and for most monitoring table functions that do not start with “MON_”. That includes things like SYSIBMADM.SNAPDB. These can be useful as a transition.

With this older methodology, you can issue the command “RESET MONITOR SWITCHES” and reset the counters for a particular session. The most useful aspect of this is to have a script that connects to a database, resets the monitor switches, sleeps for an hour (or some other amount of time)and then takes snapshots to files. This lets us get data that we know is only for a very specific period of time – though the dynamic SQL snapshot was always exempt from that methodology. I still capture data that way on most of my databases – as an emergency backup since some of my newer scripts still have bugs that I’m working out.

Looking at Data Elements to Determine Which Switch Must be on for Data to be Collected

Sometimes you have a particular area or a particular metric that you are especially interested in. Using the IBM DB2 Info Center, it is easy to see which switch must be on to collect data for a particular metric. Simply search to find the page on that metric, and you’ll get something that looks like this:
Click on the image to go to that page in the IBM DB2 Info Center if you like. Notice in Table 2, the right hand column lists the name of the monitor switch that must be turned on for this metric to be collected.

Remember, that the monitor switch must be turned on before any issue happens for DB2 to collect data about that issue. If you plan on making extensive use or even backup emergency use of the snapshot monitors, it is a good idea to have all of the monitor switches on by default.

Newer MON_* Monitoring Routines and Views

I’ve written a number of posts on these:
My New Best Friend – mon_ Part 1: Table Functions
My New Best Friend – mon_ Part 2: Views
My love affair with them continues.

One of their chief disadvantages has been that they always record data since the last database restart, and there is no way to reset monitor switches or limit data to a specific time period. I’ve said it before, and I’ll say it again, my favorite developerWorks article in recent years is: Monitoring in DB2 9.7, Part 1: Emulating data reset with the new DB2 9.7 monitoring table functions. This excellent article includes the scripts you need to implement an emulation of “RESET MONITOR SWITCHES” with the lightweight monitoring routines and views in DB2 9.7 and above. I have extended the methodology for my own personal use – to include the package cache and some other tidbits, and also to keep history tables of data so my old scripts that took snapshots hourly – instead they write to tables, and it is so easy to look through that data with SQL for performance trends or to pinpoint issues.

There are actually some configuration parameters that control what they collect, too. Unlike the old snapshot monitoring interface, these parameters are in the DB cfg. They look like this:

$ db2 get db cfg for wc005p01 |grep MON_
 Request metrics                       (MON_REQ_METRICS) = BASE
 Activity metrics                      (MON_ACT_METRICS) = BASE
 Object metrics                        (MON_OBJ_METRICS) = BASE
 Unit of work events                      (MON_UOW_DATA) = NONE
 Lock timeout events                   (MON_LOCKTIMEOUT) = WITHOUT_HIST
 Deadlock events                          (MON_DEADLOCK) = WITHOUT_HIST
 Lock wait events                         (MON_LOCKWAIT) = NONE
 Lock wait event threshold               (MON_LW_THRESH) = 5000000
 Number of package list entries         (MON_PKGLIST_SZ) = 32
 Lock event notification level         (MON_LCK_MSG_LVL) = 2

Like the DFT monitor switches for the old snapshot monitoring, changes to these take place online. However, they take effect for new connections only – existing connections are not affected. This could be problematic for an application that retains the same connections for long periods of time. Also, there is no way to turn them on for only a particular session like monitor switches – they’re either on or they’re not.

The same information is available in the IBM DB2 Info Center for each monitor element. To look at the same one as in the previous section:
Click on the image to go to that page in the IBM DB2 Info Center if you like. Notice Table 1 – the right hand column tells us what parameter and setting we need to collect data for this element. In the case of the example here, to get the data for this monitoring element, MON_OBJ_METRICS must be set to BASE or higher.

Most of these parameters allow a setting of “BASE”, “NONE”, or “EXTENDED” and default to “BASE” – which I am much happier about than the default settings for the snapshot monitoring interface that I have always disagreed with. Unlike the old snapshot monitoring, some of these settings can affect what event monitors collect too. See my post on Analyzing Deadlocks – the new way to see an example of how that works.

The info center on each of these parameters tells us what information they pertain to. The ones to focus on that roughly equate to the same kind of data as the old snapshot monitoring interface are:



Monitoring Request Metrics
The default for databases migrated from previous DB2 versions is NONE. For newly created databases, the default is BASE.
The possible values are NONE, BASE, and EXTENDED
Setting the parameter to BASE or EXTENDED will cause data for the following to be collected:

  • Statistics event monitor (you can only access this data if event monitor exists)
  • Unit of work event monitor (you can only access this data if event monitor exists)

MON_GET_WORKLOAD is actually the one I use in place of a database snapshot, so this is an important one


Monitoring Activity Metrics
The default for databases migrated from previous DB2 versions is NONE. For newly created databases, the default is BASE.
The possible values are NONE, BASE, and EXTENDED
Setting the parameter to BASE or EXTENDED will cause data for the following to be collected:

  • Activity event monitor (you can only access this data if event monitor exists)

MON_GET_PKG_CACHE_STMT is probably my favorite table function, so this one is critical for me.


Monitoring Object Metrics
The default for databases migrated from previous DB2 versions is NONE. For newly created databases, the default is BASE.
The possible values are NONE, BASE, and EXTENDED
Setting the parameter to BASE or EXTENDED will cause data for the following to be collected:


This one would be critical if you were tuning I/O or memory – pretty critical areas, too.

The recommendation for all three of these parameters would be to set them at BASE.


In DB2 9.7 there are two worlds of performance monitoring, and a transition is occurring from the old snapshot monitoring to the new monitoring routines and views. Many of the same system monitor elements are available in each method. In the IBM DB2 Info Center page that we looked at above, in the left hand column of tables 1 and 2 you will see what table functions or snapshots you can use to see a particular element. Every row in a snapshot and every column in the table functions and views are represented with a similar page in the IBM DB2 Info Center.

New functionality is being added to the new monitoring routines and views – there are things there that you can’t get otherwise. Things like static SQL from the package cache – something with the old method that you had to use an event monitor for – which has far more database impact and also a lot of data to parse through. I’m not sure if IBM is intentionally removing some monitor elements that were in the old snapshots, or if there are some they just haven’t gotten around to, or if they have a different approach in mind, but there are also elements that are simply missing from the new monitoring routines and views. Ones that I’ve looked for lately and found missing include “connections_top” and “x_lock_escals”.

If you’ve been a DBA for a while or support particularly old versions of DB2, it’s best to know both of them. If you are a new DBA, focus on the newer methodology.

Ember Crooks
Ember Crooks

Ember is always curious and thrives on change. She has built internationally recognized expertise in IBM Db2, spent a year working with high-volume MySQL, and is now learning Snowflake. Ember shares both posts about her core skill sets and her journey learning Snowflake.

Ember lives in Denver and work from home

Articles: 554


  1. Good morning Mrs. Crooks,
    let me add my personal experiences with “new” monitoring settings, used with DB2LUW v9.7, 10.1 and 10.5 FixPax 7

    My usual settings are:

    for DBM:

    db2 get dbm cfg | grep -i mon

    Transaction processor monitor name (TP_MON_NAME) =
    Default database monitor switches
    Buffer pool (DFT_MON_BUFPOOL) = ON
    Lock (DFT_MON_LOCK) = ON
    Sort (DFT_MON_SORT) = ON
    Statement (DFT_MON_STMT) = ON
    Table (DFT_MON_TABLE) = ON
    Timestamp (DFT_MON_TIMESTAMP) = ON
    Unit of work (DFT_MON_UOW) = ON
    Monitor health of instance and databases (HEALTH_MON) = OFF
    SYSMON group name (SYSMON_GROUP) = DB2MON
    Database monitor heap size (4KB) (MON_HEAP_SZ) = AUTOMATIC(90)

    for DB:

    db2 get db cfg | grep -i mon

    Monitor Collect Settings
    Request metrics (MON_REQ_METRICS) = EXTENDED
    Activity metrics (MON_ACT_METRICS) = EXTENDED
    Object metrics (MON_OBJ_METRICS) = EXTENDED
    Routine data (MON_RTN_DATA) = BASE
    Routine executable list (MON_RTN_EXECLIST) = ON
    Unit of work events (MON_UOW_DATA) = BASE
    UOW events with package list (MON_UOW_PKGLIST) = ON
    UOW events with executable list (MON_UOW_EXECLIST) = ON
    Lock timeout events (MON_LOCKTIMEOUT) = HIST_AND_VALUES
    Deadlock events (MON_DEADLOCK) = HIST_AND_VALUES
    Lock wait events (MON_LOCKWAIT) = HIST_AND_VALUES
    Lock wait event threshold (MON_LW_THRESH) = 5000000
    Number of package list entries (MON_PKGLIST_SZ) = 64
    Lock event notification level (MON_LCK_MSG_LVL) = 3


    Always set (HEALTH_MON) = OFF >>> due to high overhead, usually more than 15% CPU utilization

    And, remove from your mind the old “Get Snapshot” not easy to use

    – – – – – –
    Cordiali Saluti, Best Regards

    Gianfranco Casati
    Certified Senior IBM System Engineer
    for zOS, DB2/zOS, Vtam, TCPIP, RACF, CICS, MQ, TWS, DB2LUW, Linux

    IT Security is a Smart Investment
    – – – – – –
    The human mind is our fundamental resource.
    John Fitzgerald Kennedy
    USA President

  2. I love when I find your blog posts. Thanks for being a constant resource for advice for a newby DBA like myself. This is yet another one that I’ve marked to save and keep around for future reference.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.