DB2_使用表函式獲取健康監視器快照

目的：
測試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( ( [,] )) as
-- 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;