目的：
测试DB2使用表函数获取健康监视器快照，本脚本摘录自DB2安装目录admin_scripts/healthmon.db2。
版本：Windows DB2 Express-C V9.7
操作步骤：
使用"db2cmd db2 -t"进入交互模式,执行后续操作。
-- HEALTH_MON parameter allows you to specify whether you want to monitor an
-- instance, its associated databases, and database objects according to
-- various health indicators. This parameter has to be set to ON.
UPDATE DBM CFG USING HEALTH_MON ON IMMEDIATE;
-- For each logical group (namely DBM, DB2, Tablespace and Container), there
-- are three types of UDFs: INFO, HI (Health Indicator) and HI_HIS (Health
-- Indicator HIStory)
-- CREATE FUNCTION statement is used to register a UDF or function template
-- with application server. It has been included here to depict the prototype
-- of the UDFs and the table each of them return.
-- Usage of UDFs:
-- select *|
-- from table(
-- where partition has the following values
-- 0..n, with n>0 partition number
-- -1 means currently connected partition
-- -2 means all partitions
-- Snapshot monitor UDF for HMon Snapshot DBM header table
CREATE FUNCTION HEALTH_DBM_INFO ( INTEGER )
RETURNS TABLE (
SNAPSHOT_TIMESTAMP TIMESTAMP,
SERVER_INSTANCE_NAME VARCHAR(8),
ROLLED_UP_ALERT_STATE BIGINT,
ROLLED_UP_ALERT_STATE_DETAIL VARCHAR(20),
DB2START_TIME TIMESTAMP,
LAST_RESET TIMESTAMP,
NUM_NODES_IN_DB2_INSTANCE INT
)
SPECIFIC HEALTH_DBM_INFO
EXTERNAL NAME 'db2dbappext!health_dbm_info'
LANGUAGE C
PARAMETER STYLE. db2sql
DETERMINISTIC
FENCED
CALLED ON NULL INPUT
NO SQL
EXTERNAL ACTION
SCRATCHPAD
FINAL CALL
DISALLOW PARALLEL;
SELECT SERVER_INSTANCE_NAME,
DB2START_TIME
FROM TABLE (HEALTH_DBM_INFO (CAST(NULL AS INTEGER)) )
AS HEALTH_DBM_INFO;
-- Snapshot monitor UDF for HMon Snapshot DBM Health Indicator table
CREATE FUNCTION HEALTH_DBM_HI ( INTEGER )
RETURNS TABLE (
SNAPSHOT_TIMESTAMP TIMESTAMP,
HI_ID BIGINT,
SERVER_INSTANCE_NAME VARCHAR(8),
HI_VALUE SMALLINT,
HI_TIMESTAMP TIMESTAMP,
HI_ALERT_STATE BIGINT,
HI_ALERT_STATE_DETAIL VARCHAR(20),
HI_FORMULA VARCHAR(2048),
HI_ADDITIONAL_INFO VARCHAR(4096)
)
SPECIFIC HEALTH_DBM_HI
EXTERNAL NAME 'db2dbappext!health_dbm_hi'
LANGUAGE C
PARAMETER STYLE. db2sql
DETERMINISTIC
FENCED
CALLED ON NULL INPUT
NO SQL
EXTERNAL ACTION
SCRATCHPAD
FINAL CALL
DISALLOW PARALLEL;
SELECT SNAPSHOT_TIMESTAMP,
HI_ID,
SERVER_INSTANCE_NAME,
HI_VALUE,
HI_ALERT_STATE
FROM TABLE (HEALTH_DBM_HI (CAST(NULL AS INTEGER)) )
AS HEALTH_DBM_HI;
-- Snapshot monitor UDF for HMon Snapshot DBM Health Indicator History table
CREATE FUNCTION HEALTH_DBM_HI_HIS ( INTEGER )
RETURNS TABLE (
SNAPSHOT_TIMESTAMP TIMESTAMP,
HI_ID BIGINT,
SERVER_INSTANCE_NAME VARCHAR(8),
HI_VALUE SMALLINT,
HI_TIMESTAMP TIMESTAMP,
HI_ALERT_STATE BIGINT,
HI_ALERT_STATE_DETAIL VARCHAR(20),
HI_FORMULA VARCHAR(2048),
HI_ADDITIONAL_INFO VARCHAR(4096)
)
SPECIFIC HEALTH_DBM_HI_HIS
EXTERNAL NAME 'db2dbappext!health_dbm_hi_his'
LANGUAGE C
PARAMETER STYLE. db2sql
DETERMINISTIC
FENCED
CALLED ON NULL INPUT
NO SQL
EXTERNAL ACTION
SCRATCHPAD
FINAL CALL
DISALLOW PARALLEL;
SELECT SNAPSHOT_TIMESTAMP,
HI_ID,
SERVER_INSTANCE_NAME,
HI_VALUE,
HI_ALERT_STATE
FROM TABLE (HEALTH_DBM_HI_HIS (CAST(NULL AS INTEGER)) )
AS HEALTH_DBM_HI_HIS;
-- Snapshot monitor UDF for HMon Snapshot DB header table
CREATE FUNCTION HEALTH_DB_INFO ( VARCHAR(255), INTEGER )
RETURNS TABLE (
SNAPSHOT_TIMESTAMP TIMESTAMP,
DB_NAME VARCHAR(8),
INPUT_DB_ALIAS VARCHAR(8),
DB_PATH VARCHAR(256),
DB_LOCATION INT,
SERVER_PLATFORM INT,
ROLLED_UP_ALERT_STATE BIGINT,
ROLLED_UP_ALERT_STATE_DETAIL VARCHAR(20)
)
SPECIFIC HEALTH_DB_INFO
EXTERNAL NAME 'db2dbappext!health_db_info'
LANGUAGE C
PARAMETER STYLE. db2sql
DETERMINISTIC
FENCED
CALLED ON NULL INPUT
NO SQL
EXTERNAL ACTION
SCRATCHPAD
FINAL CALL
DISALLOW PARALLEL;
SELECT SNAPSHOT_TIMESTAMP,
DB_NAME,
INPUT_DB_ALIAS,
DB_LOCATION,
SERVER_PLATFORM
FROM TABLE (HEALTH_DB_INFO('SAMPLE', 0 )) AS HEALTH_DB_INFO;
-- Snapshot monitor UDF for HMon Snapshot Tablespace Health Indicator table
CREATE FUNCTION HEALTH_TBS_HI ( VARCHAR(255), INTEGER )
RETURNS TABLE (
SNAPSHOT_TIMESTAMP TIMESTAMP,
TABLESPACE_NAME VARCHAR(18),
HI_ID BIGINT,
HI_VALUE SMALLINT,
HI_TIMESTAMP TIMESTAMP,
HI_ALERT_STATE BIGINT,
HI_ALERT_STATE_DETAIL VARCHAR(20),
HI_FORMULA VARCHAR(2048),
HI_ADDITIONAL_INFO VARCHAR(4096)
)
SPECIFIC HEALTH_TBS_HI
EXTERNAL NAME 'db2dbappext!health_tbs_hi'
LANGUAGE C
PARAMETER STYLE. db2sql
DETERMINISTIC
FENCED
CALLED ON NULL INPUT
NO SQL
EXTERNAL ACTION
SCRATCHPAD
FINAL CALL
DISALLOW PARALLEL;
SELECT TABLESPACE_NAME,
HI_ID,
HI_VALUE,
HI_ALERT_STATE
FROM TABLE (HEALTH_TBS_HI( 'SAMPLE', 0 )) AS HEALTH_TBS_HI;
-- Snapshot monitor UDF for HMon Snapshot Container Health Indicator History
-- table
CREATE FUNCTION HEALTH_CONT_HI_HIS( VARCHAR(255), INTEGER )
RETURNS TABLE (
SNAPSHOT_TIMESTAMP TIMESTAMP,
CONTAINER_NAME VARCHAR(256),
NODE_NUMBER INTEGER,
HI_ID BIGINT,
HI_VALUE SMALLINT,
HI_TIMESTAMP TIMESTAMP,
HI_ALERT_STATE BIGINT,
HI_ALERT_STATE_DETAIL VARCHAR(20),
HI_FORMULA VARCHAR(2048),
HI_ADDITIONAL_INFO VARCHAR(4096)
)
SPECIFIC HEALTH_CONT_HI_HIS
EXTERNAL NAME 'db2dbappext!health_cont_hi_his'
LANGUAGE C
PARAMETER STYLE. db2sql
DETERMINISTIC
FENCED
CALLED ON NULL INPUT
NO SQL
EXTERNAL ACTION
SCRATCHPAD
FINAL CALL
DISALLOW PARALLEL;
SELECT CONTAINER_NAME,
HI_VALUE,
HI_TIMESTAMP,
HI_VALUE
FROM TABLE (HEALTH_CONT_HI_HIS( 'SAMPLE', 0 )) AS HEALTH_CONT_HI_HIS;
ROLLBACK;
-- TERMINATE;
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/18922393/viewspace-702189/，如需转载，请注明出处，否则将追究法律责任。
