Quick & easy way to find the monitoring table function you need - ask the catalog!

I really love the 'in-memory' monitoring interface that was created in v9.7, and added to ever since.   You've seen them - MON_GET_BUFFERPOOL, MON_GET_WORKLOAD - all those.    With each release, and even each fixpak, you get new table functions and new metrics to find out what's going on in DB2.   Better information makes performance tuning of DB2 that much easier.

However, one minor but persistent sticking point remains - how do I find out what table function I need to provide a particular metric, or even just a general type of performance data?   For example - which table functions give me CPU information, and what are those columns called?   How many input parameters to MON_GET_BUFFERPOOL?   There are dozens of table functions and hundreds of metrics to keep straight.  The Information Center will tell me (grudgingly, it seems, sometimes :-) ) what I want, but to be honest, I don't really have the patience for that.

Being a basically lazy guy, I wrote a query that looks into the catalogs for all table functions that start with 'MON_GET' or 'SNAP', etc., and which lists out both the input and output parameters (plus names & types, etc., for both).  I typically run this from the command line, piped into 'more' so I can page down and find what I want.   If I'm looking for something really specific, I might just pipe it into 'grep' for things like 'CPU', or whatever I'm interested in.

It's really simple, but no kidding, I use it every day.  I hope you find it helpful too.

ps - don't forget, when you move to a new DB2 fixpak level, running the update command (e.g., db2updv105) on your existing databases is what gets you the new table function definitions that came in that fixpak.

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 substr(r.ROUTINENAME,1,4) in ('SNAP','MON_','ENV_','WLM_','COMP')
order by P.ROUTINESCHEMA,P.ROUTINENAME,P.SPECIFICNAME,IN_OUT,P.ORDINAL;

And some sample output -


ROUTINENAME        SPECIFICNAME       IN_OUT ORD PARMNAME                 TYPE
------------------ ------------------ ------ --- ------------------------ ---------
MON_GET_BUFFERPOOL MON_GET_BUFFERPOOL IN     1   BP_NAME                  VARCHAR
MON_GET_BUFFERPOOL MON_GET_BUFFERPOOL IN     2   MEMBER                   INTEGER
MON_GET_BUFFERPOOL MON_GET_BUFFERPOOL OUT    1   BP_NAME                  VARCHAR
MON_GET_BUFFERPOOL MON_GET_BUFFERPOOL OUT    2   MEMBER                   SMALLINT
MON_GET_BUFFERPOOL MON_GET_BUFFERPOOL OUT    3   AUTOMATIC                SMALLINT
MON_GET_BUFFERPOOL MON_GET_BUFFERPOOL OUT    4   DIRECT_READS             BIGINT
MON_GET_BUFFERPOOL MON_GET_BUFFERPOOL OUT    5   DIRECT_READ_REQS         BIGINT
MON_GET_BUFFERPOOL MON_GET_BUFFERPOOL OUT    6   DIRECT_WRITES            BIGINT
MON_GET_BUFFERPOOL MON_GET_BUFFERPOOL OUT    7   DIRECT_WRITE_REQS        BIGINT
MON_GET_BUFFERPOOL MON_GET_BUFFERPOOL OUT    8   POOL_DATA_L_READS        BIGINT
MON_GET_BUFFERPOOL MON_GET_BUFFERPOOL OUT    9   POOL_TEMP_DATA_L_READS   BIGINT
MON_GET_BUFFERPOOL MON_GET_BUFFERPOOL OUT    10  POOL_XDA_L_READS         BIGINT
MON_GET_BUFFERPOOL MON_GET_BUFFERPOOL OUT    11  POOL_TEMP_XDA_L_READS    BIGINT
MON_GET_BUFFERPOOL MON_GET_BUFFERPOOL OUT    12  POOL_INDEX_L_READS       BIGINT
MON_GET_BUFFERPOOL MON_GET_BUFFERPOOL OUT    13  POOL_TEMP_INDEX_L_READS  BIGINT
MON_GET_BUFFERPOOL MON_GET_BUFFERPOOL OUT    14  POOL_DATA_P_READS        BIGINT
MON_GET_BUFFERPOOL MON_GET_BUFFERPOOL OUT    15  POOL_TEMP_DATA_P_READS   BIGINT

:

 

Recent Stories
Tips for getting the best INSERT performance

Statistics Event Monitors - terrible name, terrific feature

Measuring OVERHEAD and TRANSFERRATE on DB2 LUW - the why & how