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.
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
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