Db2 Monitoring on z/OS

Latest Dynatrace
2-min read

OneAgent supports Db2 monitoring on z/OS by collecting Db2 metrics.

This feature is planned to be released in iterations. The first iteration of Db2 monitoring collects and reports the following metrics:

Db2 CPU % usage
MSTR CPU % usage
DBM1 CPU % usage
Active connections
Active outbound connections
Active inbound connections
Failed connections
EDM pool requests
Cache Hits
Db2 zIIP Time

In the second iteration, supported in Dynatrace version 1.325+, the following Db2 metrics are reported:

Deadlock count
Deadlock Timeout count
Latch suspension time
Number of SELECTS
Number of INSERTS
Number of UPDATES
Number of DELETES
Number of FETCHES
Number of OPENS
Number of CLOSES

For metrics descriptions, see the Db2 metrics section below.

Enable Db2 Monitoring on z/OS

In OneAgent version 1.317+, Db2 monitoring can be enabled via OneAgent features.

Go to Settings > Preferences > OneAgent features.
Find z/OS DB2 Metrics Collection [Opt-In], which is off by default, and turn it on.

No restart of zDC or zRemote is required following a change to this setting.

Db2 metrics collection

The Db2 metrics are collected from SMF type 100 and 101 records. zDC uses SMF U86 exit to collect the metrics from the SMF records and ingests the metrics at one-minute intervals.

Start the Statistics and Accounting traces with the following classes for Db2 to write the SMF 100 and 101 records. Statistics - Class 1 Accounting - Class 1

The CPU metrics are collected from SMF 100 IFCID 1, z/OS System Statistics Data section. The Db2 Subsystem parameter, ZOSMETRICS must be set to YES to get valid CPU metric results.

Db2 metric keys

Monitor the Db2 metrics in Data Explorer using the metric keys that follow.

Db2 Metric	Metric key
Db2 Address spaces CPU Usage %	`builtin:tech.zos.db2.cpu_usage`
MSTR CPU Usage %	`builtin:tech.zos.db2.cpu_usage_mstr`
DBM1 CPU Usage %	`builtin:tech.zos.db2.cpu_usage_dbm1`
Active connections	`builtin:tech.zos.db2.num_active_connections`
Active outbound connections	`builtin:tech.zos.db2.num_active_oubtbound_connections`
Active inbound connections	`builtin:tech.zos.db2.num_active_inbound_connections`
Failed connections	`builtin:tech.zos.db2.num_failed_connections`
EDM pool requests	`builtin:tech.zos.db2.num_edm_pool_requests`
Cache hits	`builtin:tech.zos.db2.num_cache_hits`
zIIP time	`builtin:tech.zos.db2.ziip_time`
Deadlock count	`builtin:tech.zos.db2.num_deadlocks`
Deadlock timeout count	`builtin:tech.zos.db2.num_timedout_deadlocks`
Latch suspension time	`builtin:tech.zos.db2.latch_suspension_time`
Number of SELECTS	`builtin:tech.zos.db2.num_selects`
Number of INSERTS	`builtin:tech.zos.db2.num_inserts`
Number of UPDATES	`builtin:tech.zos.db2.num_updates`
Number of DELETES	`builtin:tech.zos.db2.num_deletes`
Number of FETCHES	`builtin:tech.zos.db2.num_fetches`
Number of OPENS	`builtin:tech.zos.db2.num_opens`
Number of CLOSES	`builtin:tech.zos.db2.num_closes`

Monitor the metrics using DQL in Grail, use the following Grail key for the respective metrics.

Db2 Metric	Grail key
Db2 Address spaces CPU Usage %	`dt.process.zos.db2.cpu_usage`
MSTR CPU Usage %	`dt.process.zos.db2.cpu_usage_mstr`
DBM1 CPU Usage %	`dt.process.zos.db2.cpu_usage_dbm1`
Active connections	`dt.process.zos.db2.num_active_connections`
Active outbound connections	`dt.process.zos.db2.num_active_oubtbound_connections`
Active inbound connections	`dt.process.zos.db2.num_active_inbound_connections`
Failed connections	`dt.process.zos.db2.num_failed_connections`
EDM pool requests	`dt.process.zos.db2.num_edm_pool_requests`
Cache hits	`dt.process.zos.db2.num_cache_hits`
zIIP time	`dt.process.zos.db2.ziip_time`
Deadlock count	`dt.process.zos.db2.num_deadlocks`
Deadlock timeout count	`dt.process.zos.db2.num_timedout_deadlocks`
Latch suspension time	`dt.process.zos.db2.latch_suspension_time`
Number of SELECTS	`dt.process.zos.db2.num_selects`
Number of INSERTS	`dt.process.zos.db2.num_inserts`
Number of UPDATES	`dt.process.zos.db2.num_updates`
Number of DELETES	`dt.process.zos.db2.num_deletes`
Number of FETCHES	`dt.process.zos.db2.num_fetches`
Number of OPENS	`dt.process.zos.db2.num_opens`
Number of CLOSES	`dt.process.zos.db2.num_closes`

Db2 metrics

Db2 CPU usage %

The percentage of the CPU processor used by all Db2 address spaces of a Db2 subsystem. This metric is collected from SMF 100 record (z/OS System Statistics section) and shows the value from the SMF record collected at the minute boundary. ZOSMETRIC parameter must be set to YES to collect this metric.

MSTR CPU usage %

The percentage of the CPU processor used by the MSTR address space of a Db2 subsystem. This metric is collected from SMF 100 record (z/OS System Statistics section) and shows the value from the SMF record collected at the minute boundary. ZOSMETRIC parameter must be set to YES to collect this metric.

DBM1 CPU usage %

The percentage of the CPU processor used by the DBM1 address space of a Db2 subsystem. This metric is collected from SMF 100 record (z/OS System Statistics section) and shows the value from the SMF record collected at the minute boundary. ZOSMETRIC parameter must be set to YES to collect this metric.

Active connections

This metric shows the total number of inbound and outbound connections handled by the Db2 subsystem in the one minute interval.

Active outbound connections

This metric shows the total number of outbound connections made by the Db2 subsystem to remote server in the one minute interval.

Active inbound connections

This metric shows the total number of inbound connections received by the Db2 subsystem initiated by the remote server in the one minute interval.

Failed connections

This metric shows the total number of connections from the remote server that were terminated in the one minute interval because CONDBAT was exceeded.

EDM pool requests

Total number of EDM pool requests made for cursor table sections, database descriptors, package table sections and dynamic statement cache. This metric shows the total number of EDM pool requests collected at the one minute interval.

Cache Hits

Total number of inserts into and requests from the Dynamic Statement cache. This metric shows the total number of cache hits collected at the one minute interval.

Db2 zIIP Time

Accumulated Db2 CPU time consumed on the zIIP processor. This metric shows the aggregated zIIP time collected at the one minute interval. This metric is collected from SMF 101 record.

Deadlock count

This metric shows the number of times deadlocks were detected in the last one-minute interval.

Deadlock Timeout count

This metric shows the number of times in the last on-minute interval a unit of work was suspended for a time that exceeded the timeout value.

Latch Suspension time

This metric shows the accumulated wait time due to latch contention in the last one-minute interval.

Number of SELECTS

This metric shows the number of SELECTS made in the last one-minute interval.

Number of INSERTS

This metric shows the number of INSERTS made in the last one-minute interval.

Number of UPDATES

This metric shows the number of UPDATES made in the last one-minute interval.

Number of DELETES

This metric shows the number of DELETES made in the last one-minute interval.

Number of FETCHES

This metric shows the number of FETCHES made in the last one-minute interval.

Number of OPENS

This metric shows the number of OPENS made in the last one-minute interval.

Number of CLOSES

This metric shows the number of CLOSES made in the last one-minute interval.

Known limitations

In the Data Explorer view, DB2 metrics are labeled as Unknown Process. This is a known limitation.