On December 12th, I’ll be presenting on the DB2 Night Show on HADR and TSAMP Advanced Topics. Sign up here: http://www.dbisoftware.com/db2nightshow/

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 actually ran across this article at least a year before I actually got to start using 9.7. http://www.ibm.com/developerworks/data/library/techarticle/dm-1009db2monitoring1/index.html?ca=drs-

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.

Calculate Key Performance Metrics

Here’s one of my favorite quick overview type queries using the mon_get table functions:

select
        DECIMAL(100*(1-((float(POOL_DATA_P_READS)/(float(POOL_DATA_L_READS)+float(POOL_DATA_P_READS))))),8,4) || ' %' as data_BPHR,
        DECIMAL(100*(1-((float(POOL_INDEX_P_READS)/(float(POOL_INDEX_L_READS)+float(POOL_INDEX_P_READS))))),8,4) || ' %' as index_BPHR,
        DECIMAL(100*(1-((float(POOL_TEMP_DATA_P_READS+POOL_TEMP_INDEX_P_READS)/(float(POOL_TEMP_DATA_L_READS+POOL_TEMP_DATA_L_READS)+float(POOL_TEMP_DATA_P_READS+POOL_TEMP_INDEX_P_READS))))),8,4) || ' %' as temp_BPHR,
        deadlocks,
        Lock_escals,
        lock_timeouts,
        lock_wait_time,
        rows_read/rows_returned as read_eff,
        sort_overflows
from table (mon_get_workload ('SYSDEFAULTUSERWORKLOAD', -2)) as t
with ur;

DATA_BPHR    INDEX_BPHR   TEMP_BPHR    DEADLOCKS            LOCK_ESCALS          LOCK_TIMEOUTS        LOCK_WAIT_TIME       READ_EFF             SORT_OVERFLOWS
------------ ------------ ------------ -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
99.9983 %    99.9457 %    99.9999 %                       1                    0                   36              1937088                 2225                 1173

  1 record(s) selected.

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.

A couple of simple queries you might use for that are:

db2 "select MEMORY_SET_TYPE, MEMORY_SET_ID, MEMORY_SET_SIZE, MEMORY_SET_COMMITTED, MEMORY_SET_USED, MEMORY_SET_USED_HWM from table(mon_get_memory_set(NULL, CURRENT_SERVER, -2)) as t with ur"

MEMORY_SET_TYPE                  MEMORY_SET_ID        MEMORY_SET_SIZE      MEMORY_SET_COMMITTED MEMORY_SET_USED      MEMORY_SET_USED_HWM
-------------------------------- -------------------- -------------------- -------------------- -------------------- --------------------
DBMS                                                0                49348                22675                16908                22675
FMP                                                 2                23134                23134                 1114                 1114
PRIVATE                                             9                69664                69664                23658               173015
DATABASE                                            1              4412407              3806724              3368484              4389404
APPLICATION                                        12               601030                21102                12910               588513

  5 record(s) selected.

db2 "select MEMORY_SET_TYPE, MEMORY_POOL_TYPE, APPLICATION_HANDLE, EDU_ID, MEMORY_POOL_USED, MEMORY_POOL_USED_HWM from table(mon_get_memory_pool(NULL, CURRENT_SERVER, -2)) as t with ur"

MEMORY_SET_TYPE                  MEMORY_POOL_TYPE                 APPLICATION_HANDLE   EDU_ID               MEMORY_POOL_USED     MEMORY_POOL_USED_HWM
-------------------------------- -------------------------------- -------------------- -------------------- -------------------- --------------------
DBMS                             FCM_LOCAL                                           -                    -                    0                    0
DBMS                             FCM_SESSION                                         -                    -              1572864              1572864
DBMS                             FCM_CHANNEL                                         -                    -               393216               393216
DBMS                             FCMBP                                               -                    -               851968               851968
DBMS                             FCM_CONTROL                                         -                    -              1703936              1703936
DBMS                             MONITOR                                             -                    -               393216              1179648
DBMS                             RESYNC                                              -                    -               262144               262144
DBMS                             APM                                                 -                    -              1572864              3080192
DBMS                             KERNEL                                              -                    -              1245184              2031616
DBMS                             BSU                                                 -                    -              1376256              4718592
DBMS                             SQL_COMPILER                                        -                    -              2686976              2686976
DBMS                             KERNEL_CONTROL                                      -                    -               196608               196608
DBMS                             EDU                                                 -                    -              4653056              4653056
FMP                              MISC                                                -                    -              1048576              1048576
PRIVATE                          PRIVATE                                             -                 2600                    0                    0
PRIVATE                                                                              -                 2594                    0                    0
PRIVATE                                                                              -                 2594                    0                    0
PRIVATE                          PRIVATE                                             -                 2594               393216              1507328
PRIVATE                          PRIVATE                                             -                 2598               327680               327680
PRIVATE                          PRIVATE                                             -                 2564               196608               327680
PRIVATE                          PRIVATE                                             -                 2577               458752              1507328
PRIVATE                          PRIVATE                                             -                 2595               196608               327680
PRIVATE                          PRIVATE                                             -                 2589               524288              1507328
PRIVATE                          PRIVATE                                             -                 2553               196608               524288
PRIVATE                          PRIVATE                                             -                 2603               720896              1507328
PRIVATE                          PRIVATE                                             -                 2596               393216               524288
PRIVATE                          PRIVATE                                             -                 2565               393216               524288
PRIVATE                          PRIVATE                                             -                 2597               786432              1507328
PRIVATE                          PRIVATE                                             -                 2602               524288               524288
PRIVATE                          PRIVATE                                             -                 2604               196608               196608
PRIVATE                          PRIVATE                                             -                 2506               131072               131072
PRIVATE                          PRIVATE                                             -                 2507               196608              4521984
PRIVATE                          PRIVATE                                             -                 2509                65536              2228224
PRIVATE                          PRIVATE                                             -                 2518               327680               458752
PRIVATE                          PRIVATE                                             -                 2517               327680               458752
PRIVATE                          PRIVATE                                             -                 2516               327680               458752
PRIVATE                          PRIVATE                                             -                 2515               327680               458752
PRIVATE                          PRIVATE                                             -                 2514               327680               458752
PRIVATE                          PRIVATE                                             -                 2513               524288               589824
PRIVATE                          PRIVATE                                             -                 2512               262144               262144
PRIVATE                          PRIVATE                                             -                 2511                65536                65536
PRIVATE                          PRIVATE                                             -                 2508               393216               393216
PRIVATE                          PRIVATE                                             -                 2386               327680               327680
PRIVATE                          PRIVATE                                             -                 2387               458752               720896
PRIVATE                          PRIVATE                                             -                 2377               196608               196608
PRIVATE                          PRIVATE                                             -                 2380               196608               196608
PRIVATE                          PRIVATE                                             -                 2357               196608               524288
PRIVATE                          PRIVATE                                             -                 2369               196608               196608
PRIVATE                          PRIVATE                                             -                 2444               196608               196608
PRIVATE                          PRIVATE                                             -                   16                    0                    0
PRIVATE                          PRIVATE                                             -                   14                65536                65536
PRIVATE                          PRIVATE                                             -                   13                65536                65536
PRIVATE                          PRIVATE                                             -                    0                65536                65536
PRIVATE                          USER_DATA                                           -                    0              3932160             16056320
PRIVATE                          PERSISTENT_PRIVATE                                  -                    0              9175040             36503552
DATABASE                         UTILITY                                             -                    -               196608             69795840
DATABASE                         PACKAGE_CACHE                                       -                    -             39976960            209518592
DATABASE                         XMLCACHE                                            -                    -               196608               196608
DATABASE                         CAT_CACHE                                           -                    -             20185088             20316160
DATABASE                         BP                                                  -                    -            441450496            574357504
DATABASE                         BP                                                  -                    -            118226944            230031360
DATABASE                         BP                                                  -                    -            129695744            201785344
DATABASE                         BP                                                  -                    -           1764425728           2673278976
DATABASE                         BP                                                  -                    -               851968               851968
DATABASE                         BP                                                  -                    -               589824               589824
DATABASE                         BP                                                  -                    -               458752               458752
DATABASE                         BP                                                  -                    -               393216               393216
DATABASE                         LOCK_MGR                                            -                    -            811663360            991756288
DATABASE                         DATABASE                                            -                    -             38731776             43843584
APPLICATION                      APPLICATION                                     60405                    -               262144             14221312
APPLICATION                      APPLICATION                                     60676                    -               131072               262144
APPLICATION                      APPLICATION                                     60657                    -               131072               131072
APPLICATION                      APPLICATION                                     60658                    -               131072               131072
APPLICATION                      APPLICATION                                     60631                    -               131072               131072
APPLICATION                      APPLICATION                                     60628                    -               131072               131072
APPLICATION                      APPLICATION                                     60548                    -               131072               131072
APPLICATION                      APPLICATION                                     60494                    -               131072               131072
APPLICATION                      APPLICATION                                     60495                    -               131072               131072
APPLICATION                      APPLICATION                                     60332                    -               131072               131072
APPLICATION                      APPLICATION                                     51226                    -                65536                65536
APPLICATION                      APPLICATION                                     51227                    -                65536                65536
APPLICATION                      APPLICATION                                     51228                    -               655360               655360
APPLICATION                      APPLICATION                                     51225                    -                65536                65536
APPLICATION                      APPLICATION                                     51224                    -                65536                65536
APPLICATION                      APPLICATION                                     51223                    -                65536                65536
APPLICATION                      APPL_SHARED                                         -                    -             10420224             31129600

  86 record(s) selected.

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:

  1. I still just love db2top for real-time monitoring
  2. 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
  3. 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.

0 comments… add one

Leave a Comment