My New Best Friend – mon_ Part 1: Table Functions

I started to write this post and realized that there was too much to cover in just one post, so I’ve split it up into multiple posts. In this post, I’ll focus on the mon_get table functions. The next post will focus on the mon_ views, and I may or may not cover the mon_format stuff as I dig deeper into it.

Introduced with DB2 9.7 were a large number of table functions starting with ‘mon_’. And a few views and other interesting tidbits. The more I use them, the more I become addicted to them.

History

One thing has always bugged me as they introduced SNAPDB and all the other interesting views in the SYSIBMADM schema – there’s no way to “reset monitor switches” to control the time period that the data covers. Don’t get me wrong – “since the last activation” is nice and all, but I’ve had databases literally activated for more than a year, and I’m usually not interested in having performance data that old in the mix. As I believe that being a detail oriented control freak makes a good dba, I am detail-oriented and a control freak.

What that has always meant in snapshot monitoring is that I have a script that runs once an hour, resets monitor switches, sleeps, and then takes a full set of snapshots. I store that data for 30-90 days depending on how space constrained a particular system is.

Now, I’m actually a bit of a late comer to 9.7. I’m mostly dependent on whatever version IBM has certified with WebSphere Commerce, and they also don’t always allow you to upgrade without buying separate DB2 licenses. Seeing that many clients use DB2 almost solely because it is bundled in the price for WebSphere Commerce, that’s a pretty significant block to upgrading. I’m sure hoping IBM doesn’t do that to me for 10.1, but I’ve only heard a couple of vague rumors that IBM is even working on certifying 10.1 with WebSphere Commerce.

I took the methodology described in that article, and took it a step farther – I extended it to include a set of history tables where data is written periodically so I can look for performance trends or spikes. I have to check with my employer about possibly writing a post or article on that and sharing the actual code there.

Anyway, I’m now a complete addict for using the mon_get table functions – now that I can actually emulate that reset capability.

Dynamic SQL

The second way I started to use the mon_get table functions was for looking at dynamic SQL. I used to have a whole process for parsing the data in a dynamic SQL snapshot, and then pulling it into Excel so I could sort on various things. Now I can very easily do all of that with the mon_get_pkg_cache_stmt table function. I even wrote an article for developer works about that: http://www.ibm.com/developerworks/data/library/techarticle/dm-1211packagecache/index.html

Why Use the mon_ Table Functions?

Why should you use the mon_ stuff instead of other views or snapshots?

Their main advantage over plain text snapshots is obvious – you can access the data via SQL. Most DBAs are going to prefer accessing data via SQL since they’re used to it. Accessing the data using SQL allows us to sort, filter and overall just come up with a report of only the data we need without having to look through or parse a bunch of data that we don’t need. And using SQL to access it just makes sense for DBAs because we already know the language.

Additionally, there is some overhead with taking snapshots. The mon_get table functions are lighter weight and have less impact on the database being monitored. IBM’s strategic direction is to use the mon_ table functions and views going forward, and they’ve already depricated some of the views starting with ‘SNAP’. It is generally a good idea to be working with something you know your vendor is planning to enhance and continue to develop.

I’ve heard that this (along with db2pd and really all of the in-memory snapshot stuff) has come from Informix, though that’s unofficial, and I don’t have anything to back it up.

What Else Can the mon_get Table Functions Do?

At the base of it, any information that you used to get out of a snapshot, you can now get from the mon_get table functions. Actually, there’s even more in the mon_get table functions than there is in the traditional ‘get snapshot’ interface. One example of this is static SQL – traditionally you couldn’t get static SQL from the snapshot monitors, but now it’s all there in MON_GET_PKG_CACHE_STMT, static and dynamic.

One of the confusing things when I started working with these was that there is no real equivalent to the database snapshot or the SNAPDB view – there are a few mon_ views that may help with this, but when you’re looking strictly at the mon_get table functions, you end up going to mon_get_workload, and if you’re like me, and don’t have the WLM feature, then you’ll see all user activity represented in the ‘SYSDEFAULTUSERWORKLOAD’ workload. So for my database the numbers above are database-wide. And someone will probably notice that the Read Efficiency is pretty bad above, but the system I’m taking these numbers from is a fairly new development system, so I’m not too worried about some of the numbers above just yet.

This is just one example obviously. I could use the mon_get table functions to dig further into that read efficiency (what tables? even what SQL?), and I could also dig into buffer pool hit ratios by buffer pool or any other of the million things you used to use the snapshots for.

Find Memory Information

If you’ve ever investigated memory issues in detail, you probably used either db2pd (with the -memsets or -mempools keywords) or db2mtrk. All of that information, too, is available in the mon_get table functions.

Dang, I like that better than the older methods. It even allows me to track a connection that is using a particularly large amount of memory.

I have not (yet) completely replaced all other monitoring methods with use of the mon_get table functions. There are several reasons for this:

I still just love db2top for real-time monitoring

When I panic, or when the pressure is really on, capturing a snapshot to a file is a skill I learned a long time ago, so I tend to fall back on it

I still have to think through and look up the SQL

But the reasons are dwindling and I’m making more and more use of these. Give them a try and let me know what you think. Next week, I’ll be posting a part two where I cover some of the interesting mon_ views.

[…] databases. I’m a big fan of some of this new functionality, particularly the mon_ stuff (see My New Best Friend – mon_ Part 1: Table Functions and My New Best Friend – mon_ Part 2: Views). Many of the things I’ve been enjoying are 9.7 […]

WebSphere Commerce Reference and Education

Disclaimer

The posts here represent my personal views and not those of my employer. Any technical advice or instructions are based on my own personal knowledge and experience, and should only be followed by an expert after a careful analysis or consultation with IBM support. Please test any actions before performing them in a critical or nonrecoverable environment. Any actions taken based on my experiences should be done with extreme caution. I am not responsible for any adverse results. DB2 is a trademark of IBM. I am not an employee or representative of IBM.