Posted by 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:
- 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.
3 comments