ITPub博客

首页 > Linux操作系统 > Linux操作系统 > DB2_状态监视

DB2_状态监视

原创 Linux操作系统 作者:redhouser 时间:2011-07-15 13:32:00 0 删除 编辑

目的:
测试DB2状态监视,本脚本摘录自DB2安装目录admin_scripts/monitor.db2。
版本:Windows DB2 Express-C V9.7

操作步骤:
使用"db2cmd db2 -t"进入交互模式,执行后续操作。

-- Connect to SAMPLE
CONNECT TO sample;

-- List the top CPU consuming applications for the currently connected
-- database on the currently connected partition
SELECT s2.APPL_NAME,
       s2.APPL_ID,
       s1.AGENT_ID,
       s2.PRIMARY_AUTH_ID,
       ((s1.AGENT_USR_CPU_TIME_S + s1.AGENT_SYS_CPU_TIME_S)*1000000 +
       s1.AGENT_USR_CPU_TIME_MS + s1.AGENT_SYS_CPU_TIME_MS) AS TOTAL_CPU_TIME,
       (s1.AGENT_USR_CPU_TIME_S*1000000 + s1.AGENT_USR_CPU_TIME_MS) AS AGENT_USR_CPU_TIME,
       (s1.AGENT_SYS_CPU_TIME_S*1000000 + s1.AGENT_SYS_CPU_TIME_MS) AS AGENT_SYS_CPU_TIME
   FROM TABLE( SNAP_GET_APPL( '', -1 )) as s1,
        TABLE( SNAP_GET_APPL_INFO( '', -1 )) as s2
   WHERE s1.AGENT_ID = s2.AGENT_ID
   ORDER BY TOTAL_CPU_TIME DESC, s2.APPL_NAME;
--select * from table(snap_get_appl('',-1)) as s1;可以获取该应用(会话)的统计信息,包括当前持有锁,io,cpu,sort等
--select * from table(snap_get_appl_info('',-1)) as s2;可以获取应用连接信息


-- Retrieving the snapshot statistics about the progress
-- of all active utilities per partition.
SELECT u1.UTILITY_DBNAME,
       u1.DBPARTITIONNUM,
       u1.UTILITY_ID,
       u1.UTILITY_PRIORITY,
       u1.UTILITY_DESCRIPTION,
       u2.UTILITY_STATE,
       u2.PROGRESS_WORK_METRIC,
       u2.PROGRESS_COMPLETED_UNITS,
       u2.PROGRESS_TOTAL_UNITS,
       DEC( ( FLOAT( u2.PROGRESS_COMPLETED_UNITS ) / FLOAT( u2.PROGRESS_TOTAL_UNITS ) ) * 100, 4, 2 )
         AS PERCENT_SEQ_COMPLETE
  FROM SYSIBMADM.SNAPUTIL as u1, SYSIBMADM.SNAPUTIL_PROGRESS as u2
  WHERE u1.UTILITY_ID = u2.UTILITY_ID and u1.DBPARTITIONNUM = u2.DBPARTITIONNUM
  ORDER BY u1.UTILITY_DBNAME, u1.DBPARTITIONNUM, u2.PROGRESS_SEQ_NUM;
--select * from SYSIBMADM.SNAPUTIL;工具基本信息
--select * from SYSIBMADM.SNAPUTIL_PROGRESS;工具进度信息,总量,当前

-- Retrieving the snapshot statistics about total amount of space
-- used by all tablespaces per partition in the currently connected database.
SELECT SUM( TBSP_TOTAL_SIZE_KB ) AS DBPART_TBSP_TOTAL_SIZE,
       DBPARTITIONNUM  FROM SYSIBMADM.TBSP_UTILIZATION
   GROUP BY DBPARTITIONNUM  ORDER BY DBPART_TBSP_TOTAL_SIZE DESC;
--select * FROM SYSIBMADM.TBSP_UTILIZATION;表空间基本信息和使用信息,分配,已用,空闲

-- Connect reset
CONNECT RESET;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/18922393/viewspace-702288/,如需转载,请注明出处,否则将追究法律责任。

下一篇: DB2_在线装载数据
请登录后发表评论 登录
全部评论

注册时间:2011-05-26

  • 博文量
    211
  • 访问量
    810328