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