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(
-- 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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- PostgreSQL 函式獲取表DDLSQL函式
- [譯] 使用 closest() 函式獲取正確的 DOM 元素函式
- Python input()函式:獲取使用者輸入的字串Python函式字串
- 使用 PHP 的 Filter 函式(過濾器)高效、安全地獲取請求引數PHPFilter函式過濾器
- hive視窗函式使用Hive函式
- shell指令碼獲取函式返回值指令碼函式
- 織夢DedeCMS獲取文章連結的函式GetOneArchive使用方法函式Hive
- QT槽函式獲取訊號傳送物件QT函式物件
- C++ 獲取指定的過載函式地址C++函式
- 原生js和jQ獲取視窗寬高及捲軸的方法和函式JS函式
- 快取函式的簡單使用快取函式
- zblog獲取GET/POST等值函式“GetVars”引數和使用方法介紹函式
- c語言獲取陣列長度的函式C語言陣列函式
- VBA中使用EXCEL工作表函式Excel函式
- hive視窗分析函式使用詳解系列二之分組排序視窗函式Hive函式排序
- PostgreSQL 原始碼解讀(117)- MVCC#2(獲取快照#2)SQL原始碼MVCC#
- PostgreSQL 原始碼解讀(116)- MVCC#1(獲取快照#1)SQL原始碼MVCC#
- sys_context 獲取環境上下文的函式Context函式
- 透過Lambda函式的方式獲取屬性名稱函式
- Rust 問答之如何獲取 main 函式的引數RustAI函式
- WordPress自定義欄位獲取get_post_meta函式函式
- PostgreSQL 視窗函式 ( Window Functions ) 如何使用?SQL函式Function
- 如何使用python中的取整floor函式?Python函式
- SQL視窗分析函式使用詳解系列三之偏移量類視窗函式SQL函式
- 虛擬函式,虛擬函式表函式
- 使用javacv 獲取視訊第一幀 做封面Java
- render函式處理動態獲取img的src屬性函式
- 語音訊號處理獲取 NFFT 的自定義函式音訊FFT函式
- Hyperf 獲取 Swoole\Server 的物件以及函式類的封裝Server物件函式封裝
- 關於 Date 函式獲取各類時間/日期/天數函式
- 如何透過AST樹去獲取JS函式引數名ASTJS函式
- Oracle分析函式與視窗函式Oracle函式
- 為爬蟲獲取登入cookies: 使用browsercookie從瀏覽器獲取cookies爬蟲Cookie瀏覽器
- 瞭解使用mysql 的檢視、儲存過程、觸發器、函式....MySql儲存過程觸發器函式
- MySQL視窗函式的具體使用TOCSMySql函式
- 獲取表空間DDL
- PostgreSQL/MogDB/openGauss怎樣獲取表上依賴於該表的檢視SQL
- upptime:使用GitHub Actions監控你的網站健康監控Github網站
- Mysql視窗函式MySql函式