Posted by When I started working with DB2 (on versions 5 and 7), the only option for seeing what was going on in the database was snapshots. I spent a fair amount of time parsing the data in snapshots with Perl scripts to filter out only the data I wanted to see. Slowly, methods of using SQL to access snapshot data were introduced. First table functions for accessing snapshot data, then SYSIBMADM views, and finally we have the MON_GET family of table functions. I’m going to focus on the use of these MON_GET interfaces in this post.
I’ve blogged about MON_GET goodies before – including these posts:
Two Worlds of DB2 LUW Performance Monitoring
My New Best Friend – mon_ Part 1: Table Functions
My New Best Friend – mon_ Part 2: Views
But in the last couple of years, I’ve focused a lot of my learning attention on sharpening my SQL skills – both so I can help clients with SQL and so I can write SQL to get exactly the data I want out of the various query-able sources.
I still love db2top, but use it mostly for overview and high-level real-time monitoring. I need more for in-depth investigation and ongoing data collection.
Compared to Using Snapshot Data
One of the things I loved about snapshot data is that it covered or could easily cover a discrete time period. By default, that time period was since database activation, but I could reset monitor switches and only see data since that reset. The reset was specific to the session I was in, so other sessions would have other reset points. In fact, I ran scripts that would reset the monitor switches, sleep for an hour, and then grab a set of snapshots. Keeping this data for a week or a month, I then had a basis for comparison for any numbers I was looking at. Granted, this meant that I had a bunch of text files out there to parse through, but grep can work wonders, and when it’s not enough there’s always Perl’s powerful text-parsing capabilities.
There is no reset with data in SYSIBMADM views or monitoring table functions. The data is always since the last database restart. This takes some getting used to, but my favorite developerWorks article of all time describes how to emulate the reset function. The code from that article is getting a bit dated, and you’ll have to add quite a few elements for 10.5, but it is worth your time to work through it.
One advantage of using the mon_get monitoring functions is that the collection of data is “in-memory” and considered more lightweight than snapshots. It is also the strategic direction for IBM going forward, and new elements and even table functions are constantly being added.
“Monitoring”
If you collect or take action on the data from these monitoring functions, you might use it for actual monitoring. But simply querying it doesn’t really qualify as monitoring to me. I may use the word monitoring, but without collection or action, it’s just investigation in my book. You can use it for monitoring, but just because you’re using these table functions doesn’t mean you are truly monitoring a database.
How to Find what Table Functions are Available
More than once in the last year, I have stumbled upon a monitoring function that I had never previously heard of, and I like to think I do a half-way decent job of keeping up with what’s new in DB2 and how to monitor. How can you make yourself aware of what’s out there? First, as always is the IBM DB2 Knowledge Center. A comprehensive list of monitor functions and procedures is available here: http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.sql.rtn.doc/doc/c0053963.html?lang=en
But I’m a command line gal. Give me a command line method any time. The excellent SQL from Steve Rees fits the bill nicely to see anything available. Check it out at that link. It gives extremely comprehensive output, though, listing basically every monitored element in every table function or snapshot view. That’s nearly 8,000 rows returned in my 10.5 sample database. If I simply want a list of the available monitoring functions, I use this SQL, which gives me only 109 rows:
select
substr(r.ROUTINENAME,1,48) as ROUTINENAME,
substr(r.SPECIFICNAME,1,48) as SPECIFICNAME
from sysibm.sysroutines r
where
r.function_type='T'
and substr(r.ROUTINENAME,1,4) in ('SNAP','MON_','ENV_','COMP')
and substrb(r.SPECIFICNAME,-3,3) not in ('V91', 'V95', 'V97', '_AP')
order by r.ROUTINESCHEMA,r.ROUTINENAME,r.SPECIFICNAME;
Once I have an idea of what I really am looking for, then I use SQL like this to list all of the elements available in a particular function:
select
substr(P.ROUTINENAME,1,48) as ROUTINENAME,
substr(P.SPECIFICNAME,1,48) as SPECIFICNAME,
case when P.ROWTYPE in ('B','O','P') then CHAR('IN',3) else CHAR('OUT',3) end as IN_OUT,
cast(p.ORDINAL as char(3)) as ORD,
substr(P.PARMNAME,1,40) as PARMNAME,
substr(P.TYPENAME,1,16) as TYPE
from sysibm.sysroutines r, sysibm.sysroutineparms p
where p.routineschema=r.routineschema
and p.routinename=r.routinename
and p.specificname=r.specificname
and r.function_type='T'
and r.ROUTINENAME='MON_GET_TABLE'
order by P.ROUTINESCHEMA,P.ROUTINENAME,P.SPECIFICNAME,IN_OUT,P.ORDINAL;
That gives me output that looks like this:
ROUTINENAME SPECIFICNAME IN_OUT ORD PARMNAME TYPE ------------------------------------------------ ------------------------------------------------ ------ --- ---------------------------------------- ---------------- MON_GET_TABLE MON_GET_TABLE IN 1 TABSCHEMA VARCHAR MON_GET_TABLE MON_GET_TABLE IN 2 TABNAME VARCHAR MON_GET_TABLE MON_GET_TABLE IN 3 MEMBER INTEGER MON_GET_TABLE MON_GET_TABLE OUT 1 TABSCHEMA VARCHAR MON_GET_TABLE MON_GET_TABLE OUT 2 TABNAME VARCHAR MON_GET_TABLE MON_GET_TABLE OUT 3 MEMBER SMALLINT MON_GET_TABLE MON_GET_TABLE OUT 4 TAB_TYPE VARCHAR MON_GET_TABLE MON_GET_TABLE OUT 5 TAB_FILE_ID BIGINT MON_GET_TABLE MON_GET_TABLE OUT 6 DATA_PARTITION_ID INTEGER MON_GET_TABLE MON_GET_TABLE OUT 7 TBSP_ID BIGINT MON_GET_TABLE MON_GET_TABLE OUT 8 INDEX_TBSP_ID BIGINT MON_GET_TABLE MON_GET_TABLE OUT 9 LONG_TBSP_ID BIGINT MON_GET_TABLE MON_GET_TABLE OUT 10 TABLE_SCANS BIGINT MON_GET_TABLE MON_GET_TABLE OUT 11 ROWS_READ BIGINT MON_GET_TABLE MON_GET_TABLE OUT 12 ROWS_INSERTED BIGINT MON_GET_TABLE MON_GET_TABLE OUT 13 ROWS_UPDATED BIGINT MON_GET_TABLE MON_GET_TABLE OUT 14 ROWS_DELETED BIGINT MON_GET_TABLE MON_GET_TABLE OUT 15 OVERFLOW_ACCESSES BIGINT MON_GET_TABLE MON_GET_TABLE OUT 16 OVERFLOW_CREATES BIGINT MON_GET_TABLE MON_GET_TABLE OUT 17 PAGE_REORGS BIGINT MON_GET_TABLE MON_GET_TABLE OUT 18 DATA_OBJECT_L_PAGES BIGINT MON_GET_TABLE MON_GET_TABLE OUT 19 LOB_OBJECT_L_PAGES BIGINT MON_GET_TABLE MON_GET_TABLE OUT 20 LONG_OBJECT_L_PAGES BIGINT MON_GET_TABLE MON_GET_TABLE OUT 21 INDEX_OBJECT_L_PAGES BIGINT MON_GET_TABLE MON_GET_TABLE OUT 22 XDA_OBJECT_L_PAGES BIGINT MON_GET_TABLE MON_GET_TABLE OUT 23 DBPARTITIONNUM SMALLINT MON_GET_TABLE MON_GET_TABLE OUT 24 NO_CHANGE_UPDATES BIGINT MON_GET_TABLE MON_GET_TABLE OUT 25 LOCK_WAIT_TIME BIGINT MON_GET_TABLE MON_GET_TABLE OUT 26 LOCK_WAIT_TIME_GLOBAL BIGINT MON_GET_TABLE MON_GET_TABLE OUT 27 LOCK_WAITS BIGINT MON_GET_TABLE MON_GET_TABLE OUT 28 LOCK_WAITS_GLOBAL BIGINT MON_GET_TABLE MON_GET_TABLE OUT 29 LOCK_ESCALS BIGINT MON_GET_TABLE MON_GET_TABLE OUT 30 LOCK_ESCALS_GLOBAL BIGINT MON_GET_TABLE MON_GET_TABLE OUT 31 DATA_SHARING_STATE VARCHAR MON_GET_TABLE MON_GET_TABLE OUT 32 DATA_SHARING_STATE_CHANGE_TIME TIMESTAMP MON_GET_TABLE MON_GET_TABLE OUT 33 DATA_SHARING_REMOTE_LOCKWAIT_COUNT BIGINT MON_GET_TABLE MON_GET_TABLE OUT 34 DATA_SHARING_REMOTE_LOCKWAIT_TIME BIGINT MON_GET_TABLE MON_GET_TABLE OUT 35 DIRECT_WRITES BIGINT MON_GET_TABLE MON_GET_TABLE OUT 36 DIRECT_WRITE_REQS BIGINT MON_GET_TABLE MON_GET_TABLE OUT 37 DIRECT_READS BIGINT MON_GET_TABLE MON_GET_TABLE OUT 38 DIRECT_READ_REQS BIGINT MON_GET_TABLE MON_GET_TABLE OUT 39 OBJECT_DATA_L_READS BIGINT MON_GET_TABLE MON_GET_TABLE OUT 40 OBJECT_DATA_P_READS BIGINT MON_GET_TABLE MON_GET_TABLE OUT 41 OBJECT_DATA_GBP_L_READS BIGINT MON_GET_TABLE MON_GET_TABLE OUT 42 OBJECT_DATA_GBP_P_READS BIGINT MON_GET_TABLE MON_GET_TABLE OUT 43 OBJECT_DATA_GBP_INVALID_PAGES BIGINT MON_GET_TABLE MON_GET_TABLE OUT 44 OBJECT_DATA_LBP_PAGES_FOUND BIGINT MON_GET_TABLE MON_GET_TABLE OUT 45 OBJECT_DATA_GBP_INDEP_PAGES_FOUND_IN_LBP BIGINT MON_GET_TABLE MON_GET_TABLE OUT 46 OBJECT_XDA_L_READS BIGINT MON_GET_TABLE MON_GET_TABLE OUT 47 OBJECT_XDA_P_READS BIGINT MON_GET_TABLE MON_GET_TABLE OUT 48 OBJECT_XDA_GBP_L_READS BIGINT MON_GET_TABLE MON_GET_TABLE OUT 49 OBJECT_XDA_GBP_P_READS BIGINT MON_GET_TABLE MON_GET_TABLE OUT 50 OBJECT_XDA_GBP_INVALID_PAGES BIGINT MON_GET_TABLE MON_GET_TABLE OUT 51 OBJECT_XDA_LBP_PAGES_FOUND BIGINT MON_GET_TABLE MON_GET_TABLE OUT 52 OBJECT_XDA_GBP_INDEP_PAGES_FOUND_IN_LBP BIGINT MON_GET_TABLE MON_GET_TABLE OUT 53 NUM_PAGE_DICT_BUILT BIGINT MON_GET_TABLE MON_GET_TABLE OUT 54 STATS_ROWS_MODIFIED BIGINT MON_GET_TABLE MON_GET_TABLE OUT 55 RTS_ROWS_MODIFIED BIGINT MON_GET_TABLE MON_GET_TABLE OUT 56 COL_OBJECT_L_PAGES BIGINT MON_GET_TABLE MON_GET_TABLE OUT 57 TAB_ORGANIZATION CHARACTER MON_GET_TABLE MON_GET_TABLE OUT 58 OBJECT_COL_L_READS BIGINT MON_GET_TABLE MON_GET_TABLE OUT 59 OBJECT_COL_P_READS BIGINT MON_GET_TABLE MON_GET_TABLE OUT 60 OBJECT_COL_GBP_L_READS BIGINT MON_GET_TABLE MON_GET_TABLE OUT 61 OBJECT_COL_GBP_P_READS BIGINT MON_GET_TABLE MON_GET_TABLE OUT 62 OBJECT_COL_GBP_INVALID_PAGES BIGINT MON_GET_TABLE MON_GET_TABLE OUT 63 OBJECT_COL_LBP_PAGES_FOUND BIGINT MON_GET_TABLE MON_GET_TABLE OUT 64 OBJECT_COL_GBP_INDEP_PAGES_FOUND_IN_LBP BIGINT MON_GET_TABLE MON_GET_TABLE OUT 65 NUM_COLUMNS_REFERENCED BIGINT MON_GET_TABLE MON_GET_TABLE OUT 66 SECTION_EXEC_WITH_COL_REFERENCES BIGINT MON_GET_TABLE MON_GET_TABLE OUT 67 OBJECT_DATA_CACHING_TIER_L_READS BIGINT MON_GET_TABLE MON_GET_TABLE OUT 68 OBJECT_DATA_CACHING_TIER_PAGES_FOUND BIGINT MON_GET_TABLE MON_GET_TABLE OUT 69 OBJECT_DATA_CACHING_TIER_GBP_INVALID_PAG BIGINT MON_GET_TABLE MON_GET_TABLE OUT 70 OBJECT_DATA_CACHING_TIER_GBP_INDEP_PAGES BIGINT MON_GET_TABLE MON_GET_TABLE OUT 71 OBJECT_XDA_CACHING_TIER_L_READS BIGINT MON_GET_TABLE MON_GET_TABLE OUT 72 OBJECT_XDA_CACHING_TIER_PAGES_FOUND BIGINT MON_GET_TABLE MON_GET_TABLE OUT 73 OBJECT_XDA_CACHING_TIER_GBP_INVALID_PAGE BIGINT MON_GET_TABLE MON_GET_TABLE OUT 74 OBJECT_XDA_CACHING_TIER_GBP_INDEP_PAGES_ BIGINT MON_GET_TABLE MON_GET_TABLE OUT 75 OBJECT_COL_CACHING_TIER_L_READS BIGINT MON_GET_TABLE MON_GET_TABLE OUT 76 OBJECT_COL_CACHING_TIER_PAGES_FOUND BIGINT MON_GET_TABLE MON_GET_TABLE OUT 77 OBJECT_COL_CACHING_TIER_GBP_INVALID_PAGE BIGINT MON_GET_TABLE MON_GET_TABLE OUT 78 OBJECT_COL_CACHING_TIER_GBP_INDEP_PAGES_ BIGINT 81 record(s) selected.
This is where I am tempted to make use of CLPPLUS – as I could have this SQL file prompt me for the function name I want using CLPPLUS. But I’m testing for this blog entry on Express-C, where CLPPLUS is not available.
There is another way to go about finding what you’re looking for in the monitoring functions, and I have used this several times when I am trying to look at one or two elements in depth, and I want to know which views they appear in. Sometimes names change, so this doesn’t always work, but if I were looking for information on ROWS_RETURNED and where it is reported, then I could use something like this:
select
substr(P.ROUTINENAME,1,48) as ROUTINENAME,
substr(P.SPECIFICNAME,1,48) as SPECIFICNAME,
case when P.ROWTYPE in ('B','O','P') then CHAR('IN',3) else CHAR('OUT',3) end as IN_OUT,
cast(p.ORDINAL as char(3)) as ORD,
substr(P.PARMNAME,1,40) as PARMNAME,
substr(P.TYPENAME,1,16) as TYPE
from sysibm.sysroutines r, sysibm.sysroutineparms p
where p.routineschema=r.routineschema
and p.routinename=r.routinename
and p.specificname=r.specificname
and r.function_type='T'
and p.parmname='ROWS_RETURNED'
order by P.ROUTINESCHEMA,P.ROUTINENAME,P.SPECIFICNAME,IN_OUT,P.ORDINAL;
This returns results like this:
ROUTINENAME SPECIFICNAME IN_OUT ORD PARMNAME TYPE ------------------------------------------------ ------------------------------------------------ ------ --- ---------------------------------------- ---------------- MON_GET_ACTIVITY MON_GET_ACTIVITY OUT 23 ROWS_RETURNED BIGINT MON_GET_CONNECTION MON_GET_CONNECTION OUT 71 ROWS_RETURNED BIGINT MON_GET_DATABASE MON_GET_DATABASE OUT 76 ROWS_RETURNED BIGINT MON_GET_PKG_CACHE_STMT MON_GET_PKG_CACHE_STMT OUT 28 ROWS_RETURNED BIGINT MON_GET_ROUTINE MON_GET_ROUTINE OUT 60 ROWS_RETURNED BIGINT MON_GET_SERVICE_SUBCLASS MON_GET_SERVICE_SUBCLASS OUT 58 ROWS_RETURNED BIGINT MON_GET_UNIT_OF_WORK MON_GET_UNIT_OF_WORK OUT 72 ROWS_RETURNED BIGINT MON_GET_WORKLOAD MON_GET_WORKLOAD OUT 57 ROWS_RETURNED BIGINT WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES OUT 23 ROWS_RETURNED BIGINT WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES_V97 WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES_V97 OUT 23 ROWS_RETURNED BIGINT 10 record(s) selected.
That can be very useful for finding all the places I might be able to use the ROWS_RETURNED element. However, there is a problem. There are similar elements to rows returned in other contexts – for example in SNAP_GET_STMT, this same element is referred to as “FETCH_COUNT”. Names can change, particularly between the MON_GET functions and the snap_functions.
General Structure of a MON_GET Table Function
I clearly remember the first time I encountered a table function. It was within my first 4 or 5 years of being a DBA, when I worked in a role where I hardly did any SQL more complicated than SELECT * FROM SYSCAT.BUFFERPOOLS. I remember just looking at it and thinking there was no way I would ever get used to using that thing and my snapshots worked just fine thank you.
Years later, I’m so used to the MON_GET table functions that I can come up with the basic syntax for many of them cold. They all take as input object names, sometimes in multiple parts, along with a member number. The member number is easy in single-member implementations – it is always -2. There are sometimes other input parameters.
But for something like MON_GET_TABLE, I know that I need two things to uniquely identify a table – the schema name and the table name. Therefore, I know that MON_GET_TABLE will require three inputs. So I can easily write the table function part of the query table(MON_GET_TABLE('','',-2)). Some are even easier. I know for tablespaces that I can uniquely identify a tablespace with just one identifier, so it would be table(MON_GET_TABLESPACE('',-2)). Until you start to think this way, you can use a query to get the required inputs for any function:
select
substr(P.ROUTINENAME,1,48) as ROUTINENAME,
substr(P.SPECIFICNAME,1,48) as SPECIFICNAME,
'IN' as IN_OUT,
cast(p.ORDINAL as char(3)) as ORD,
substr(P.PARMNAME,1,40) as PARMNAME,
substr(P.TYPENAME,1,16) as TYPE
from sysibm.sysroutines r, sysibm.sysroutineparms p
where p.routineschema=r.routineschema
and p.routinename=r.routinename
and p.specificname=r.specificname
and r.function_type='T'
and r.ROUTINENAME='MON_GET_TABLE'
and P.ROWTYPE in ('B','O','P')
order by P.ROUTINESCHEMA,P.ROUTINENAME,P.SPECIFICNAME,IN_OUT,P.ORDINAL;
For MON_GET_TABLE, this gives me:
ROUTINENAME SPECIFICNAME IN_OUT ORD PARMNAME TYPE ------------------------------------------------ ------------------------------------------------ ------ --- ---------------------------------------- ---------------- MON_GET_TABLE MON_GET_TABLE IN 1 TABSCHEMA VARCHAR MON_GET_TABLE MON_GET_TABLE IN 2 TABNAME VARCHAR MON_GET_TABLE MON_GET_TABLE IN 3 MEMBER INTEGER 3 record(s) selected.
I mentioned that there are a few that require more data than just the number of identifiers required to uniquely identify the items the data is being gathered on. One of my favorite table functions, MON_GET_PKG_CACHE_STMT, is one of these:
select
substr(P.ROUTINENAME,1,48) as ROUTINENAME,
substr(P.SPECIFICNAME,1,48) as SPECIFICNAME,
'IN' as IN_OUT,
cast(p.ORDINAL as char(3)) as ORD,
substr(P.PARMNAME,1,40) as PARMNAME,
substr(P.TYPENAME,1,16) as TYPE
from sysibm.sysroutines r, sysibm.sysroutineparms p
where p.routineschema=r.routineschema
and p.routinename=r.routinename
and p.specificname=r.specificname
and r.function_type='T'
and r.ROUTINENAME='MON_GET_PKG_CACHE_STMT'
and P.ROWTYPE in ('B','O','P')
order by P.ROUTINESCHEMA,P.ROUTINENAME,P.SPECIFICNAME,IN_OUT,P.ORDINAL;
ROUTINENAME SPECIFICNAME IN_OUT ORD PARMNAME TYPE
------------------------------------------------ ------------------------------------------------ ------ --- ---------------------------------------- ----------------
MON_GET_PKG_CACHE_STMT MON_GET_PKG_CACHE_STMT IN 1 SECTION_TYPE CHARACTER
MON_GET_PKG_CACHE_STMT MON_GET_PKG_CACHE_STMT IN 2 EXECUTABLE_ID VARCHAR
MON_GET_PKG_CACHE_STMT MON_GET_PKG_CACHE_STMT IN 3 SEARCH_ARGS CLOB
MON_GET_PKG_CACHE_STMT MON_GET_PKG_CACHE_STMT IN 4 MEMBER INTEGER
4 record(s) selected.
Using the input parameters, we could limit output to only dynamic or static SQL (SECTION_TYPE), a specific EXECUTABLE_ID or oddly enough, specify search arguments right there instead of in the where clause. This SQL for listing the input parameters is particularly useful for functions like this that don’t follow the pattern of the rest of them. These input parameters are also listed at the top of the output from the previous SQL that listed all the output parameters for a function as well.
Limiting Output
Given the general syntax of calling a table function, there are two diferent ways we can do certain kinds of filtering on the output. If we only want output from MON_GET_TABLE for a single table, we can either specify the table schema and name as input values for the table function OR we can leave those blank, and specify the table schema and name in the where clause.
Specifying the table schema and name as input parameters looks something like this:
db2 "select * from table(mon_get_table('DB2INST1','SALES', -2)) as mgt with ur"
The exact same results are returned by this query, specifying the table schema and the table name in the where clause instead:
db2 "select * from table(mon_get_table('','', -2)) as mgt where tabschema='DB2INST1' and tabname='SALES' with ur"
Which of the above is more efficient? While the answer to that question is usually the famous “It Depends …”, in this case the first one is nearly always the better choice. I tested that in my sample database, and found that the first statement above took 0.00426385 timerons while the second statement took 0.11944 timerons. Both very small numbers in my very small database, but they would be larger in a larger database. Additionally if you’re using statements like these very frequently as a part of a monitoring solution, you want to be as efficient as possible since the statements may be executing very frequently. In my silly little sample database, specifying the parameters as input to the table function took just 3% of the time/resources as specifying them in the where clause – a very significant difference. If there is ever a choice to apply filtering in the input parameters for the table function or in the where clause, always choose to apply them as input parameters.
Is there any way to only record queries (INSERT/UPDATE/DELETE/SELECT/EXECUTE) that run over a certain time? In SQL Server and MySQL there are functions to capture any query that runs for more than x seconds (normally 5 or 10). I set up a event monitor for statements but on my test system (which is not real busy) I captured 2,000 statements in 15 seconds. On my production server that would be much, much, much higher. Thanks!!!
If you have an edition of Db2 that allows you to use WLM, you can use it to do this.