DB2 Monitoring Prerequisites InfluxDB (the host is the same as Grafana) Separated Linux host Install and running telegraf Install DB2 Client Implement custom scripts InfluxDB 新增 database > create database db2_mon with duration 180d > create user mon with password 'thisispassword' > grant read on db2_mon to mon mon 唯讀帳號是給 Grafana 的 datasource 使用。 Telegraf 建立新的設定檔 telegraf-db2.conf telegraf --input-filter exec --output-filter influxdb config > /etc/telegraf/telegraf-db2.conf 修改設定檔 [agent] ... ... logfile = "/var/log/telegraf/telegraf-db2.log" # Configuration for sending metrics to InfluxDB [[outputs.influxdb]] urls = ["http://10.10.2.209:8086"] database = "db2_mon" timeout = "0s" username = "admin" password = "Thispassword" [[inputs.exec]] interval = "300s" ## Commands array #commands = [ # "/tmp/test.sh", # "/usr/bin/mycollector --foo=bar", # "/tmp/collect_*.sh" #] commands = ["sudo -u db2mon sh -c '/home/db2mon/bin/collect_db2.v2.sh -d dcdb -a b_dcdb -u dbuser -p dbpass'"] ## Timeout for each command to complete. timeout = "5s" ## measurement name suffix (for separating different commands) #name_suffix = "_mycollector" ## Data format to consume. ## Each data format has its own unique set of configuration options, read ## more about them here: ## https://github.com/influxdata/telegraf/blob/master/docs/DATA_FORMATS_INPUT.md data_format = "influx" [[inputs.exec]] interval = "1h" commands = ["sudo -u db2mon sh -c '/home/db2mon/bin/collect_db2x1h.sh -d dcdb -a b_dcdb -u dbuser -p dbpass'"] timeout = "5s" data_format = "influx" ### CENTDB [[inputs.exec]] interval = "300s" commands = ["sudo -u db2mon sh -c '/home/db2mon/bin/collect_db2.v2.sh -d centdb -a b_centdb -u dbuser -p dbpass'"] timeout = "5s" data_format = "influx" 建立一個新的服務啟動檔 telegraf-db2.service NOTE: 由於 telegraf 只能有一個對應的設定檔,如果這部主機需要多個 Output ,則必需啟動多個不同的 telegraf 服務。 cp /usr/lib/systemd/system/telegraf.service /etc/systemd/system/telegraf-db2.service 修改設定檔 // 修改這一行 ExecStart=/usr/bin/telegraf -config /etc/telegraf/telegraf-db2.conf $TELEGRAF_OPTS 檢查服務啟動列表有無新增 telegraf-db2,如果沒有,執行 systemctl list-unit-files --type service systemctl daemon-reload DB2 Metrics Bufferpool -- DATA_HIT_RATIO_PERCENT: Data hit ratio, that is, the percentage of time that the database manager did not need to load a page from disk to service a data page request. -- INDEX_HIT_RATIO_PERCENT: Index hit ratio, that is, the percentage of time that the database manager did not need to load a page from disk to service an index data page request. select BP_NAME, DATA_HIT_RATIO_PERCENT, INDEX_HIT_RATIO_PERCENT from sysibmadm.MON_BP_UTILIZATION where BP_NAME not like 'IBMSYS%' and BP_NAME<>'IBMDEFAULTBP' Logs (active log & archive log) -- LOG_UTILIZATION_PERCENT: Percent utilization of total log space. (active logs) -- TOTAL_LOG_USED_KB: The total amount of active log space currently used in the database -- TOTAL_LOG_AVAILABLE_KB: The amount of active log space in the database that is not being used by uncommitted transactions. select DB_NAME, LOG_UTILIZATION_PERCENT, TOTAL_LOG_USED_KB, TOTAL_LOG_AVAILABLE_KB from sysibmadm.log_utilization Connections -- APPLS_CUR_CONS: Indicates the number of applications that are currently connected to the database. -- LOCKS_WAITING: Indicates the number of agents waiting on a lock. -- NUM_INDOUBT_TRANS: The number of outstanding indoubt transactions in the database. select DB_NAME,APPLS_CUR_CONS,LOCKS_WAITING,NUM_INDOUBT_TRANS from sysibmadm.snapdb Transactions -- 這是累計的數值 -- COMMIT_SQL_STMTS: The total number of SQL COMMIT statements that have been attempted. -- ROLLBACK_SQL_STMTS: The total number of SQL ROLLBACK statements that have been attempted. select COMMIT_SQL_STMTS, ROLLBACK_SQL_STMTS from sysibmadm.snapdb Custom scripts Learning Archive Log Monitor Generating Log Archive Activity Histograms