Lin Hong's TECH Blog! 刀不磨要生锈,人不学习要落后 - Thinking ahead

[原创]DB2 监控表的使用

2016-02-22
DB2

摘要

DB2 v10之后增加了很多MON_GET_TABLE等监控函数的使用,总共有上百个监控函数,对于这些函数如果不常使用,除了查看informaction center之后,很难全部记住并加以使用。本文汇总了通过sql来汇总有哪些监控函数和每个函数能返回的结果集有哪些,不必全部记住这些函数或者查看information center即可使用


有哪些monitoring functions?

通过下面的sql语句即可查看

db2 "select
  substr(rout.ROUTINENAME,1,48) as ROUTINENAME,
  substr(rout.SPECIFICNAME,1,48) as SPECIFICNAME
from sysibm.sysroutines rout
where 
  rout.function_type='T'
  and substr(rout.ROUTINENAME,1,4) in ('SNAP','MON_','ENV_','COMP')
  and substrb(rout.SPECIFICNAME,-3,3) not in ('V91', 'V95', 'V97', '_AP')
order by rout.ROUTINESCHEMA,rout.ROUTINENAME,rout.SPECIFICNAME";

DB2 v10.1fp5结果如下(117行):

	ROUTINENAME                                      SPECIFICNAME                                    
------------------------------------------------ ------------------------------------------------
COMPILATION_ENV                                  COMPILATION_ENV                                 
ENV_GET_CF_SYS_RESOURCES                         ENV_GET_CF_SYS_RESOURCES                        
ENV_GET_DB2_SYSTEM_RESOURCES                     ENV_GET_DB2_SYSTEM_RESOURCES                    
ENV_GET_FEATURE_INFO                             ENV_GET_FEATURE_INFO                            
ENV_GET_INST_INFO                                ENV_GET_INST_INFO                               
ENV_GET_NETWORK_RESOURCES                        ENV_GET_NETWORK_RESOURCES                       
ENV_GET_PROD_INFO                                ENV_GET_PROD_INFO                               
ENV_GET_REG_VARIABLES                            ENV_GET_REG_VARIABLES          
......
SNAP_GET_TBSP_QUIESCER                           SNAP_GET_TBSP_QUIESCER                          
SNAP_GET_TBSP_RANGE                              SNAP_GET_TBSP_RANGE                             
SNAP_GET_UTIL                                    SNAP_GET_UTIL                                   
SNAP_GET_UTIL_PROGRESS                           SNAP_GET_UTIL_PROGRESS                          

  117 record(s) selected.

db2inst1:/dbhome/db2inst1$ 

查看某个monitoring functions的结果集?

如果我想查看某个monitoring functions的返回结果集合有哪些?如何查看呢?

只要把ROUTINENAME更改为你上面想要的某个监控函数名。

db2 "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='SNAP_GET_UTIL'
order by P.ROUTINESCHEMA,P.ROUTINENAME,P.SPECIFICNAME,IN_OUT,P.ORDINAL";

例如:

db2inst1:/dbhome/db2inst1$ db2 "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='SNAP_GET_UTIL'
> order by P.ROUTINESCHEMA,P.ROUTINENAME,P.SPECIFICNAME,IN_OUT,P.ORDINAL";

ROUTINENAME                                      SPECIFICNAME                                     IN_OUT ORD PARMNAME                                 TYPE            
------------------------------------------------ ------------------------------------------------ ------ --- ---------------------------------------- ----------------
SNAP_GET_UTIL                                    SNAP_GET_UTIL                                    IN     1   MEMBER                                   INTEGER         
SNAP_GET_UTIL                                    SNAP_GET_UTIL                                    OUT    1   SNAPSHOT_TIMESTAMP                       TIMESTAMP       
SNAP_GET_UTIL                                    SNAP_GET_UTIL                                    OUT    2   UTILITY_ID                               INTEGER         
SNAP_GET_UTIL                                    SNAP_GET_UTIL                                    OUT    3   UTILITY_TYPE                             VARCHAR         
SNAP_GET_UTIL                                    SNAP_GET_UTIL                                    OUT    4   UTILITY_PRIORITY                         INTEGER         
SNAP_GET_UTIL                                    SNAP_GET_UTIL                                    OUT    5   UTILITY_DESCRIPTION                      VARCHAR         
SNAP_GET_UTIL                                    SNAP_GET_UTIL                                    OUT    6   UTILITY_DBNAME                           VARCHAR         
SNAP_GET_UTIL                                    SNAP_GET_UTIL                                    OUT    7   UTILITY_START_TIME                       TIMESTAMP       
SNAP_GET_UTIL                                    SNAP_GET_UTIL                                    OUT    8   UTILITY_STATE                            VARCHAR         
SNAP_GET_UTIL                                    SNAP_GET_UTIL                                    OUT    9   UTILITY_INVOKER_TYPE                     VARCHAR         
SNAP_GET_UTIL                                    SNAP_GET_UTIL                                    OUT    10  DBPARTITIONNUM                           SMALLINT        
SNAP_GET_UTIL                                    SNAP_GET_UTIL                                    OUT    11  PROGRESS_LIST_ATTR                       VARCHAR         
SNAP_GET_UTIL                                    SNAP_GET_UTIL                                    OUT    12  PROGRESS_LIST_CUR_SEQ_NUM                INTEGER         
SNAP_GET_UTIL                                    SNAP_GET_UTIL                                    OUT    13  MEMBER                                   SMALLINT        
SNAP_GET_UTIL                                    SNAP_GET_UTIL_AP                                 OUT    1   SNAPSHOT_TIMESTAMP                       TIMESTAMP       
SNAP_GET_UTIL                                    SNAP_GET_UTIL_AP                                 OUT    2   UTILITY_ID                               INTEGER         
SNAP_GET_UTIL                                    SNAP_GET_UTIL_AP                                 OUT    3   UTILITY_TYPE                             VARCHAR         
SNAP_GET_UTIL                                    SNAP_GET_UTIL_AP                                 OUT    4   UTILITY_PRIORITY                         INTEGER         
SNAP_GET_UTIL                                    SNAP_GET_UTIL_AP                                 OUT    5   UTILITY_DESCRIPTION                      VARCHAR         
SNAP_GET_UTIL                                    SNAP_GET_UTIL_AP                                 OUT    6   UTILITY_DBNAME                           VARCHAR         
SNAP_GET_UTIL                                    SNAP_GET_UTIL_AP                                 OUT    7   UTILITY_START_TIME                       TIMESTAMP       
SNAP_GET_UTIL                                    SNAP_GET_UTIL_AP                                 OUT    8   UTILITY_STATE                            VARCHAR         
SNAP_GET_UTIL                                    SNAP_GET_UTIL_AP                                 OUT    9   UTILITY_INVOKER_TYPE                     VARCHAR         
SNAP_GET_UTIL                                    SNAP_GET_UTIL_AP                                 OUT    10  DBPARTITIONNUM                           SMALLINT        
SNAP_GET_UTIL                                    SNAP_GET_UTIL_AP                                 OUT    11  PROGRESS_LIST_ATTR                       VARCHAR         
SNAP_GET_UTIL                                    SNAP_GET_UTIL_AP                                 OUT    12  PROGRESS_LIST_CUR_SEQ_NUM                INTEGER         
SNAP_GET_UTIL                                    SNAP_GET_UTIL_AP                                 OUT    13  MEMBER                                   SMALLINT        

  27 record(s) selected.

db2inst1:/dbhome/db2inst1$

或:

db2 "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";

查看某个MON_GET的参数列表?

如何查看某个MON_GET的参数列表呢?我们知道查用类似的如table(MON_GET_TABLE(‘’,’’,-2))来使用MON_GET函数或者table(MON_GET_TABLESPACE(‘’,-2))。

但如何查看必须的参数呢?

可以参考如下SQL问来确认(MON_GET_TABLE可以替换成你想要的函数名):

db2 "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";

如下例(MON_GET_LOCKS):

db2inst1:/dbhome/db2inst1$         db2 "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_LOCKS'
>           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_LOCKS                                    MON_GET_LOCKS                                    IN     1   SEARCH_ARGS                              CLOB            
MON_GET_LOCKS                                    MON_GET_LOCKS                                    IN     2   MEMBER                                   INTEGER         

  2 record(s) selected.

db2inst1:/dbhome/db2inst1$ 

应用

查看sales表的情况,可用下面sql语句

db2 "select * from table(mon_get_table('DB2INST1','SALES', -2)) as mgt with ur"

或者:

db2 "select * from table(mon_get_table('','', -2)) as mgt where tabschema='DB2INST1' and tabname='SALES' with ur"

参考资料

DB2 V10.5的资料如下:

Monitor procedures and functions



下一篇 Benchmark-TPC

Comments