linux查看db2运行状态,DB2_状态监视

目的：

测试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;