etc/zbxdb_checks/db2/primary.11.cfg

# vim: syntax=sql
# db2 shared by https://github.com/tiankola
[check_1m]
minutes: 1
db2.uptime: select 'db2[' ||(SELECT INST_NAME FROM SYSIBMADM.ENV_INST_INFO) ||',uptime]' key, (current timestamp - DB2START_TIME) val from TABLE(MON_GET_INSTANCE(-2))

#db2diag
#error sql
db2diag: select 'db2[' ||(SELECT INST_NAME FROM SYSIBMADM.ENV_INST_INFO) ||',db2diag]' key,path val from TABLE(SYSPROC.AUDIT_ARCHIVE('', -2)) AS T1

#实例是否active状态和启动时间"Whether the instance is active and when it was started"
DB2_STATUS: select 'inst[db2_status]' key,db2_status val from sysibmadm.snapdbm
DB2START_TIME: select 'inst[db2start_time]' key,db2start_time val from sysibmadm.snapdbm

#数据库是否active状态和启动时间"Whether the database is active and when it is started"
DB_STSTUS: select 'db[db_status]' key,db_status val from sysibmadm.snapdb
DB_CONN_TIME: select 'db[db_conn_time]' key,db_conn_time val from sysibmadm.snapdb

#数据库大小db2    "CALL GET_DBSIZE_INFO(?, ?, ?, 0)" #Database size

#应用连接情况"Application connection"
#数据库级别TOTAL_CONS,TOTAL_SEC_CONS,APPLS_CUR_CONS,APPLS_IN_DB2 # Database level
DB_CONS: select 'db[total_cons]' key,TOTAL_CONS val from sysibmadm.snapdb union all
			select 'db[total_sec_cons]' key,TOTAL_SEC_CONS val from sysibmadm.snapdb union all
			select 'db[appls_cur_cons]' key,APPLS_CUR_CONS val from sysibmadm.snapdb union all
			select 'db[appls_in_db2]' key,APPLS_IN_DB2 val from sysibmadm.snapdb

#实例级别REM_CONS_IN,REM_CONS_IN_EXEC,LOCAL_CONS,LOCAL_CONS_IN_EXEC,GW_TOTAL_CONS,GW_CUR_CONS,GW_CUR_CONS,GW_CUR_CONS "Instance level"
INST_CONS: select 'inst[rem_cons_in]' key,REM_CONS_IN from sysibmadm.snapdbm union all
			select 'inst[rem_cons_in_exec]' key,REM_CONS_IN_EXEC from sysibmadm.snapdbm union all
			select 'inst[local_cons]' key,LOCAL_CONS from sysibmadm.snapdbm union all
			select 'inst[local_cons_in_exec]' key,LOCAL_CONS_IN_EXEC from sysibmadm.snapdbm union all
			select 'inst[gw_total_cons]' key,GW_TOTAL_CONS from sysibmadm.snapdbm union all
			select 'inst[gw_cur_cons]' key,GW_CUR_CONS from sysibmadm.snapdbm

#表空间状态和使用情况（MON_GET_TABLESPACE、MON_GET_CONTAINER 、MON_TBSP_UTILIZATION ）#Table space status and usage
#表空间状态：tbsp_state除了NORMAL、Backup in Progress、Reorg in Progress外，需要告警"Table space status Need alert"
#db2 "SELECT varchar(tbsp_name, 30) as tbsp_name,member,tbsp_type,tbsp_state FROM TABLE(MON_GET_TABLESPACE('',-2)) AS t"
TABLESPACE_MEMBER: SELECT 'tbsp[' || tbsp_name ||',member]' key,member FROM TABLE(MON_GET_TABLESPACE('',-2))
TABLESPACE_TBSP_TYPE: SELECT 'tbsp[' || tbsp_name ||',tbsp_type]' key,tbsp_type FROM TABLE(MON_GET_TABLESPACE('',-2)) 
TABLEAPACE_TBSP_STATE: SELECT 'tbsp[' || tbsp_name ||',tbsp_state]' key,tbsp_state FROM TABLE(MON_GET_TABLESPACE('',-2))
#表空间大小和使用率：tbsp_type为DMS，TBSP_AUTO_RESIZE_ENABLED为0，tbsp_utilization_percent超高80%需要告警# Table space size and usage Ultra-high 80% requires alarm
TABLESPACE_TBSP_PAGE_SIZE: select 'tbsp[' || tbsp_name ||',TBSP_PAGE_SIZE]' key,tbsp_page_size from sysibmadm.tbsp_utilization
TABLESPACE_TBSP_AUTO_RESIZE_ENABLED: select 'tbsp[' || tbsp_name ||',TBSP_AUTO_RESIZE_ENABLED]' key,TBSP_AUTO_RESIZE_ENABLED from sysibmadm.tbsp_utilization
TABLESPACE_TBSP_TOTAL_SIZE_KB: select 'tbsp[' || tbsp_name ||',TBSP_TOTAL_SIZE_KB]' key,tbsp_total_size_kb from sysibmadm.tbsp_utilization 
TABLESPACE_TBSP_FREE_SIZE_KB: select 'tbsp[' || tbsp_name ||',TBSP_FREE_SIZE_KB]' key,tbsp_free_size_kb from sysibmadm.tbsp_utilization
TABLESPACE_TBSP_USED_SIZE_KB: select 'tbsp[' || tbsp_name ||',TBSP_USED_SIZE_KB]' key,tbsp_used_size_kb from sysibmadm.tbsp_utilization 
TABLESPACE_TBSP_UUTILIZETION_PERCENT: select 'tbsp[' || tbsp_name ||',TBSP_UTILIZATION_PERCENT]' key,tbsp_utilization_percent from sysibmadm.tbsp_utilization 

#缓冲池大小和命中率"Buffer pool size and hit rate"
#缓冲池大小：NPAGES为-2，表示自动扩展 "Buffer pool size: NPAGES is -2, which means automatic expansion"
BUFFERPOOL_BUFFERPOOLID: select 'bf[' || BPNAME ||',BUFFERPOOLID]' key,BUFFERPOOLID from syscat.bufferpools 
BUFFERPOOL_PAGESIZE: select 'bf[' || BPNAME ||',PAGESIZE]' key,PAGESIZE from syscat.bufferpools
BUFFERPOOL_NPAGES: select 'bf[' || BPNAME ||',NPAGES]' key,NPAGES from syscat.bufferpools
#缓冲池命中率"Buffer pool hit rate"
BUFFERPOOL_LOGICAL_READS: WITH BPMETRICS AS (
    SELECT bp_name,
           pool_data_l_reads + pool_temp_data_l_reads + pool_index_l_reads + pool_temp_index_l_reads + pool_xda_l_reads + pool_temp_xda_l_reads as logical_reads,
           pool_data_p_reads + pool_temp_data_p_reads + pool_index_p_reads + pool_temp_index_p_reads + pool_xda_p_reads + pool_temp_xda_p_reads as physical_reads,
           member
    FROM TABLE(MON_GET_BUFFERPOOL('',-2)) AS METRICS)
    SELECT 'bf[' || BP_NAME ||',logical_reads]' key, logical_reads
    FROM BPMETRICS
BUFFERPOOL_PHYSICAL_READS: WITH BPMETRICS AS (
    SELECT bp_name,
           pool_data_l_reads + pool_temp_data_l_reads + pool_index_l_reads + pool_temp_index_l_reads + pool_xda_l_reads + pool_temp_xda_l_reads as logical_reads,
           pool_data_p_reads + pool_temp_data_p_reads + pool_index_p_reads + pool_temp_index_p_reads + pool_xda_p_reads + pool_temp_xda_p_reads as physical_reads,
           member
    FROM TABLE(MON_GET_BUFFERPOOL('',-2)) AS METRICS)
    SELECT 'bf[' || BP_NAME ||',physical_reads]' key, physical_reads
    FROM BPMETRICS
BUFFERPOOL_HIT_RATIO: WITH BPMETRICS AS (
    SELECT bp_name,
           pool_data_l_reads + pool_temp_data_l_reads + pool_index_l_reads + pool_temp_index_l_reads + pool_xda_l_reads + pool_temp_xda_l_reads as logical_reads,
           pool_data_p_reads + pool_temp_data_p_reads + pool_index_p_reads + pool_temp_index_p_reads + pool_xda_p_reads + pool_temp_xda_p_reads as physical_reads,
           member
    FROM TABLE(MON_GET_BUFFERPOOL('',-2)) AS METRICS)
    SELECT 'bf[' || BP_NAME ||',HIT_RATIO]' key, CASE WHEN logical_reads > 0 THEN DEC((1 - (FLOAT(physical_reads) / FLOAT(logical_reads))) * 100,5,2) ELSE NULL END AS HIT_RATIO
    FROM BPMETRICS
BUFFERPOOL_MEMBER: WITH BPMETRICS AS (
    SELECT bp_name,
           pool_data_l_reads + pool_temp_data_l_reads + pool_index_l_reads + pool_temp_index_l_reads + pool_xda_l_reads + pool_temp_xda_l_reads as logical_reads,
           pool_data_p_reads + pool_temp_data_p_reads + pool_index_p_reads + pool_temp_index_p_reads + pool_xda_p_reads + pool_temp_xda_p_reads as physical_reads,
           member
    FROM TABLE(MON_GET_BUFFERPOOL('',-2)) AS METRICS)
    SELECT 'bf[' || BP_NAME ||',member]' key, member
    FROM BPMETRICS

#活动日志使用率"Activity log usage"
LOG_LOG_UTILIZATION_PERCENT: select 'log['|| DB_NAME ||',LOG_UTILIZATION_PERCENT]' key,LOG_UTILIZATION_PERCENT val from sysibmadm.LOG_UTILIZATION
LOG_TOTAL_LOG_USED: select 'log['|| DB_NAME ||',TOTAL_LOG_USED]' key,TOTAL_LOG_USED_KB val from sysibmadm.LOG_UTILIZATION
LOG_TOTAL_LOG_AVAILABLE: select 'log['|| DB_NAME ||',TOTAL_LOG_AVAILABLE]' key,TOTAL_LOG_AVAILABLE_KB val from sysibmadm.LOG_UTILIZATION
LOG_TOTAL_LOG_USED_TOP:	select 'log['|| DB_NAME ||',TOTAL_LOG_USED_TOP]' key,TOTAL_LOG_USED_TOP_KB val from sysibmadm.LOG_UTILIZATION
	
LOG_APP: select 'log[member]' key,MEMBER val from table(mon_get_transaction_log(-1)) as t union all
			Select 'log[sec_logs_allocated]' key,SEC_LOGS_ALLOCATED val from table(mon_get_transaction_log(-1)) as t union all
			Select 'log[sec_log_used_top]' key,SEC_LOG_USED_TOP val from table(mon_get_transaction_log(-1)) as t union all
			Select 'log[tot_log_used_top]' key,TOT_LOG_USED_TOP val from table(mon_get_transaction_log(-1)) as t union all
			Select 'log[current_lsn]' key,CURRENT_LSN val from table(mon_get_transaction_log(-1)) as t union all
			Select 'log[oldest_tx_lsn]' key,OLDEST_TX_LSN val from table(mon_get_transaction_log(-1)) as t union all
			Select 'log[current_active_log]' key,CURRENT_ACTIVE_LOG val from table(mon_get_transaction_log(-1)) as t union all
			Select 'log[applid_holding_oldest_xact]' key,APPLID_HOLDING_OLDEST_XACT val from table(mon_get_transaction_log(-1)) as t

#锁LOCKS_HELD和LOCKS_WAITING为当前锁情况，其他为历史锁情况"Locks LOCKS_HELD and LOCKS_WAITING are the current lock conditions, and the others are historical lock conditions"
LOCK: select 'lock[locks_held]' key,LOCKS_HELD val from SYSIBMADM.SNAPDB union all
		select 'lock[lock_waits]' key,LOCK_WAITS val from SYSIBMADM.SNAPDB union all
		select 'lock[lock_wait_time]' key,LOCK_WAIT_TIME val from SYSIBMADM.SNAPDB union all
		select 'lock[lock_list_in_use]' key,LOCK_LIST_IN_USE val from SYSIBMADM.SNAPDB union all
		select 'lock[deadlocks]' key,DEADLOCKS val from SYSIBMADM.SNAPDB union all
		select 'lock[lock_escals]' key,LOCK_ESCALS val from SYSIBMADM.SNAPDB union all
		select 'lock[x_lock_escals]' key,X_LOCK_ESCALS val from SYSIBMADM.SNAPDB union all
		select 'lock[locks_waiting]' key,LOCKS_WAITING val from SYSIBMADM.SNAPDB union all
		select 'lock[lock_timeouts]' key,LOCK_TIMEOUTS val from SYSIBMADM.SNAPDB

#备份上次备份时间，为0表示没有备份过db2 "select LAST_BACKUP from sysibmadm.snapdb" #The last backup time of the backup, 0 means no backup has been made
BACKUP: select 'backup[last_backup]' key,LAST_BACKUP from sysibmadm.snapdb

#SQL
SQLNOW: select 'sql[now]' key,STMT_TEXT from sysibmadm.snapstmt where STMT_TEXT is not null order by STMT_USR_CPU_TIME_S desc fetch first 20 row only
SQLHISTORY: select 'sql[history]' key,STMT_TEXT from table (mon_get_pkg_cache_stmt( null, null, null, -2) ) order by total_cpu_time desc fetch first 10 row only with ur

#HADR
#####

[auto_discovery_60]
minutes: 60
db.lld: select current_server "{#PDB}" from sysibm.sysdummy1
inst.lld: SELECT INST_NAME "{#INST_NAME}" FROM SYSIBMADM.ENV_INST_INFO
tbsp.lld: SELECT TBSP_NAME "{#TABLESPACE_NAME}",TBSP_CONTENT_TYPE "{#TABLESPACE_TYPE}" FROM SYSIBMADM.TBSP_UTILIZATION
u_ts.lld: SELECT current_server "{#PDB}", NAME "{#TS_NAME}" FROM SYSIBM.SYSTABLES WHERE (CREATOR=UPPER((SELECT INST_NAME FROM SYSIBMADM.ENV_INST_INFO)) and TYPE = 'T')
bf.lld: SELECT bp_name "{#BP_NAME}" FROM TABLE(MON_GET_BUFFERPOOL('',-2))
schema.lld:select schemaname "{#PDB}",owner "{#SCHEMA}" from syscat.schemata where DEFINER=UPPER((SELECT INST_NAME FROM SYSIBMADM.ENV_INST_INFO))
p_ts.lld: select current_server "{#PDB}", tbspace "{#TS_NAME}" from sysibm.systablespaces where tbspacetype = 'F'
t_ts.lld: select current_server "{#PDB}", tbspace "{#TS_NAME}" from sysibm.systablespaces where tbspacetype = 'S'
rman.lld: select distinct 'part:' ||dbpartitionnum ||',oper:' ||operation "{#OBJ_TYPE}" from SYSIBMADM.DB_HISTORY where operation in ('B', 'X') and entry_status = 'A'


[checks_20m]
minutes: 20
rman: with data as (
			SELECT dbpartitionnum, max(to_date(start_time,'yyyymmddhh24miss')) start_time
			, max(to_date(end_time,'yyyymmddhh24miss')) end_time,operation, sqlcode
			FROM SYSIBMADM.DB_HISTORY
			where operation in ('B', 'X')
			and entry_status = 'A'
			group by dbpartitionnum,operation, sqlcode
			)
			, calc_data as (
			select 'part:'||dbpartitionnum||',oper:'||operation object_type, start_time, end_time, round((end_time - start_time),0) elapsed, sqlcode
				from data
			)
			select 'rman['||object_type||',status]', nvl(sqlcode,0) from calc_data
			union all
			select 'rman['||object_type||',ela]', elapsed from calc_data
			union all
			select 'rman['||object_type||',age]', to_date(current_date||' '||current_time,'yyyy-mm-dd hh24:mi:ss') - start_time from calc_data

[checks_60m]
minutes: 60
version: select 'DB2[version]' key, PRODUCT_NAME FROM TABLE (MON_GET_INSTANCE(-2))
inst_name: select 'DB2[inst_name]' key, INST_NAME FROM SYSIBMADM.ENV_INST_INFO