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
Name | Required? | Description |
---|---|---|
Host | Required | The IBM DB2 Database Host to connect to. |
Port | The port for communication to the IBM DB2 Database. | |
Database | Required | The database to monitor. |
Username | Required | |
Password | Required | |
Query Count | The number of queries to collect. | |
Order Queries By | ||
Collect Events | Controls 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 (%) |
Updated over 4 years ago