Bindplane

The Bindplane Developer Hub

Welcome to the Bindplane developer hub. You'll find comprehensive guides and documentation to help you start working with Bindplane as quickly as possible, as well as support if you get stuck. Let's jump right in!

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 <monitoring-user-name> must be ≤ 8 characters. The <system-monitoring-group-name> 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 <system-monitoring-group-name> 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 <monitoring-user-name> 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 <monitoring-user-name>.
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 14 days ago

IBM DB2


Monitor IBM DB2 Database LUW Servers

Suggested Edits are limited on API Reference Pages

You can only suggest edits to Markdown body content, but not to the API spec.