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:

db2 update dbm cfg using DFT_MON_BUFPOOL ON DFT_MON_LOCK ON DFT_MON_SORT ON DFT_MON_STMT ON DFT_MON_TABLE ON DFT_MON_UOW ON HEALTH_MON OFF IMMEDIATE

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:

• MON_REQ_METRICS
• MON_ACT_METRICS
• MON_OBJ_METRICS

MON_REQ_METRICS

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:

• MON_GET_UNIT_OF_WORK
• MON_GET_CONNECTION
• MON_GET_SERVICE_SUBCLASS
• MON_GET_WORKLOAD
• 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

MON_ACT_METRICS

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:

• MON_GET_ACTIVITY_DETAILS
• MON_GET_PKG_CACHE_STMT
• 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.

MON_OBJ_METRICS

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:

• MON_GET_BUFFERPOOL
• MON_GET_TABLESPACE
• MON_GET_CONTAINER

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.

Summary

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.