IBM DB2

Monitor IBM DB2 Database LUW Servers

Data Collection Setup

Metrics are collected via JDBC from DB2 Server instance performance tables.

Configuring IBM DB2 for Monitoring Metrics

A few special configurations are required on the DB2 database side in order for the Management Pack to collect metrics correctly.

  • Enable DB2 configuration parameter HEALTH_MON (i.e., turn on health indicators for instance and database objects).
  • Enable switch for monitoring statements.
  • Set up permissions for Management Pack to query the lock waits administrative view.
  • Enable table monitoring (DFT_MON_TABLE) for the database you are monitoring.

To complete the above configurations, run the following commands by Command Line Processor (CLP) at the db2 prompt:

update dbm cfg using HEALTH_MON on
update dbm cfg using DFT_MON_STMT on
update dbm cfg using DFT_MON_LOCK on
update dbm cfg using DFT_MON_TABLE on
update dbm cfg using DFT_MON_BUFPOOL on

To check if your changes are effective, run the following command:

db2==> get dbm cfg

The following is the Example output:

.....

.....

.....

Monitor health of instance and databases (HEALTH_MON) = ON

Default database monitor switches

Buffer pool (DFT_MON_BUFPOOL) = ON

Lock (DFT_MON_LOCK) = ON

Sort (DFT_MON_SORT) = OFF

Statement (DFT_MON_STMT) = ON

Table (DFT_MON_TABLE) = ON

Timestamp (DFT_MON_TIMESTAMP) = ON

Unit of work (DFT_MON_UOW) = OFF

Monitor health of instance and databases (HEALTH_MON) = ON

.....

.....

.....

Network Requirements

Port: 50000 (TCP) Default DB2 instance port.
This is configurable based on the DB2 instance configuration.

Least Privilege User

Creating an IBM DB2 Least-Privileged User

This topic outlines the process to create an IBM DB2 least-privileged user (LPU) on Linux/UNIX to use as the monitoring user for BindPlane

For the purposes of this management pack, we chose the SYSMON group as noted in this IBM Knowledge Center article because it has the least permissions of the four groups listed that provide access to snapshot monitoring data.

Procedure

1. Creating the user

Starting as the root user on Linux/UNIX, run the following command to create the group, user, and set the password for the LPU monitoring user.

groupadd <system-monitoring-group-name>
useradd -g <system-monitoring-group-name> <monitoring-user-name>
passwd <monitoring-user-name>

📘

The must be ≤ 8 characters. The must be all lowercase at the OS level: sysmong as shown in the example below.

groupadd sysmong
useradd -g sysmong db2lpu
passwd db2lpu

2. Switch to the instance master user (in the following example db2inst1 for instance 1):

su - db2inst1

3. Grant the necessary permissions by setting the OS group as the SYSMON_GROUP

Note: The must be all UPPERCASE at the DB2 level: SYSMONG

db2 update dbm cfg using SYSMON_GROUP <system-monitoring-group-name>

4. Exit from the master user back to root:

exit

5. Reboot the server, then log in again as root. Copy the .bashrc from master user to in order to get the environment variables that make DB2 work:

Example (using same variables as before):

cp -f /home/db2inst1/.bashrc /home/db2lpu/

cp: overwrite `/home/db2lpu/.bashrc'? y

chown db2lpu:sysmong /home/db2lpu/.bashrc

6. Test permissions:

Log in as .
Connect to a database.

Example (connecting to sample below):

db2 connect to sample

7. Enter the DB2 shell and run test Queries

db2

Run our queries from the DB2 shell to test that they all return successfully:

SELECT coalesce((sum(DATA_LOGICAL_READS) - sum(DATA_PHYSICAL_READS)) / nullif(cast(sum(DATA_LOGICAL_READS) as float), 0), 1) * 100 as buffer_pool_data_hit from SYSIBMADM.BP_HITRATIO group by db_name, snapshot_timestamp

SELECT COUNT(locks_waiting) as applications_waiting_on_locks from SYSIBMADM.SNAPAPPL WHERE LOCKS_WAITING > 0

SELECT LOG_UTILIZATION_PERCENT AS log_utilization from SYSIBMADM.LOG_UTILIZATION

SELECT TOTAL_CONS, DEADLOCKS, LOCK_TIMEOUTS, APPLS_IN_DB2 FROM SYSIBMADM.SNAPDB

SELECT LOCAL_CONS, REM_CONS_IN FROM SYSIBMADM.SNAPDBM

Other Security Reference

Optional: To test if your user is in the SYSMON_GROUP without queries, you can use the following OS command:

db2pd

The following are some useful permission queries that may help with debugging:

SELECT AUTHORITY, D_USER, D_GROUP, D_PUBLIC, ROLE_USER, ROLE_GROUP, ROLE_PUBLIC, D_ROLE FROM TABLE (SYSPROC.AUTH_LIST_AUTHORITIES_FOR_AUTHID ('DB2LPU', 'U') ) AS T ORDER BY AUTHORITY

SELECT AUTHORITY, D_USER, D_GROUP, D_PUBLIC, ROLE_USER, ROLE_GROUP, ROLE_PUBLIC, D_ROLE >FROM TABLE (SYSPROC.AUTH_LIST_AUTHORITIES_FOR_AUTHID ('SYSMONG', 'G') ) AS T ORDER BY AUTHORITY

Supported Versions

IBM DB2: 9.5+ LUW
Note: IBM DB2 running on i-series or z/os is currently not supported.

Connection Parameters

NameRequired?Description
HostRequiredThe IBM DB2 Database Host to connect to.
PortThe port for communication to the IBM DB2 Database.
DatabaseRequiredThe database to monitor.
UsernameRequired
PasswordRequired
Query CountThe number of queries to collect.
Order Queries By
Collect EventsControls whether events are collected and returned.

Metrics

Bufferpool

Name
Database
Host
Individual Buffer Pool Hit Ratio (%)
Logical Data Reads
Logical Index Reads
Name
Physical Data Reads
Physical Index Reads
Pool Asynchronous Data Reads
Pool Asynchronous Data Writes
Pool Asynchronous Index Reads
Pool Asynchronous Index Writes
Pool Asynchronous Read Time (Milliseconds)
Pool Asynchronous Write Time (Milliseconds)
Pool Data Writes
Pool Index Writes
Pool Read Time (Milliseconds)
Pool Write Time (Milliseconds)

Database

Name
Active Applications
Active Local Connections
Active Remote Connections
Active Sorts
Agent Waiting Top
Agents Created due to Empty Pool
Agents Created to Assigned Ratio (%)
Agents Registered
Allocated Size (Bytes)
Allocated Sort Heap Memory (Pages)
Applications Connected
Applications Waiting on Locks (%)
Applications Waiting on Locks Count
Average Direct Read Rate (Milliseconds)
Average Direct Write Rate (Milliseconds)
Average Lock Wait Time (Milliseconds)
Average Post Threshold Joins Rate
Average Post Threshold Sorts Rate
Buffer Pool Data Hit Ratio (%)
Buffer Pool Hit Ratio (%)
Buffer Pool Index Hit Ratio (%)
Catalog Cache Inserts
Catalog Cache Lookups
Catalog Cache Overflows
Cleans for Steals
Cleans for Thresholds
Commit SQL Statements
Communication Private Memory (Bytes)
Connection Time
Coordination Agents Top
Current Connections
Current Size (Bytes)
Deadlocks
Direct Read Requests
Direct Read Time (Milliseconds)
Direct Reads
Direct Write Requests
Direct Write Time (Milliseconds)
Direct Writes
Dynamic SQL Statements
Exclusive Lock Escalations
Failed SQL Statements
Host
Idle Agents
Internal Deadlock Rollbacks
Internal Rollbacks
Last Reset
Local Connections
Lock Escalations
Lock List in Use (Bytes)
Lock Timeouts
Lock Waits
Locklist (Bytes)
Locks Held
Log Reads
Log Space Utilization (%)
Log Writes
Logical Data Reads
Logical Index Reads
Logical Location
Maximum Active Applications
Maximum Package Cache Used
Name
Package Cache Inserts
Package Cache Lookups
Package Cache Overflows
Physical Data Reads
Physical Disk Location
Physical Index Reads
Piped Sorts Accepted
Piped Sorts Rejected
Piped Sorts Requested
Pool Agents
Pool Asynchronous Data Reads
Pool Asynchronous Data Writes
Pool Asynchronous Index Reads
Pool Asynchronous Index Writes
Pool Asynchronous Read Time (Milliseconds)
Pool Asynchronous Write Time (Milliseconds)
Pool Asynchronous Writes
Pool Data Writes
Pool Index Writes
Pool Read Time (Milliseconds)
Pool Synchronous Data Reads
Pool Synchronous Data Writes
Pool Synchronous Index Reads
Pool Synchronous Index Writes
Pool Write Time (Milliseconds)
Post Threshold Hash Joins
Post Threshold Sorts
Remote Connections
Rollback SQL Statements
Small Overflow Hash Joins to Total Hash Joins Ratio (%)
Sort Heap Pages Allocated (Pages)
Sort Overflows
Startup Time
Static SQL Statements
Status
Time of Last Backup
Total Connections
Total Hash Join Overflows
Total Hash Join Small Overflow
Total Hash Joins
Total Hash Loops
Total Lock Wait Time (Milliseconds)
Total Log Used (Kilobytes)
Total Sort Time (Milliseconds)
Total Sorts

HADR

Name
Connect Status
Connection Time
Database
Heartbeat
Host
Local Host
Name
Remote Host
Remote Instance
Remote Service
Role
State
Sync Mode
Timeout (Seconds)

Memory Pool

Name
Database
Host
ID
Utilization (%)

SQL Statement

Name
Average Execution Time (Microseconds)
Average System CPU Time (Microseconds)
Average User CPU Time (Microseconds)
Database
Executions
Host
Rows Read
Rows Written
Statement Hash
Statement Text

Tablespace

Name
Bufferpool Identifier
Containers
Database
Extent Size (Pages)
Free Pages (Pages)
Host
Name
Page Size (Bytes)
Prefetch Size (Pages)
Ranges
State
Total Pages (Pages)
Type
Usable Pages (Pages)
Used Pages (Pages)
Utilization (%)