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.