Exploring Other Db2 Statistics Metrics

A brief introduction to some of the “other” (non-buffer pool related) metrics available in Db2 Statistics data

More Db2 Statistics Videos

1. Exploring Assessments of Key Metrics
2. Db2 Buffer Pool Tuning: Exploring Key Metrics (Part 1)
3. Db2 Buffer Pool Tuning: Exploring Key Metrics (Part 2)
4. Db2 I/O Cache Insights from IFCID 199 and SMF 42 Data
5. Buffer Efficiency from IFCID 199 and SMF 42
6. Measuring Benefits of Db2 Buffer Pool Tuning
7. Exploring Other Db2 Statistics Metrics
8. Walkthrough of Db2 Statistics Dashboard for Buffer Pool Tuning
9. Integrating with RMF Metrics (70, 71, 72, 74)
10. Integrating with RMF 70 and 72 CPU and WLM Metrics
11. Integrating with RMF 71 Memory and Large Frame Metrics
12. Integrating with RMF 74.4 Coupling Facility Metrics

Video Transcript

Now we will explore a few of the many other metrics available in the Db2 statistics data. The list of report sets in the two columns on the right here introduces us to many of the types of metrics available in the Db2 statistics records. And so we’re going to do a flyover of the listed items on the left. That’ll hopefully whet your appetite for what you can explore in your data in the future. One metric is the level of commit activity and that can be broken out by the various types of commit. So here it is for this data sharing group. Statistics data also captures many metrics relating to locking and IRLM. Here we see various lock and IRLM request counts over time. And we’ll go ahead and capture this in our dashboard.

And when we compare this to a prior interval, it’s apparent that the spikes in lock requests are a recurring pattern in this environment. Statistics data also captures the number of times a unit of work was suspended due to lock or latch conflicts. And again, if we compare across multiple time intervals, we may expect to see somewhat similar time of day patterns.

Metrics on global locking are also available. And we can explore a time and day profile of the percentage of requests that encounter global or false lock contention or P lock negotiation. Logging can play a very important role in overall Db2 performance.  Jumping ahead again, and looking at this logging throughput by connection type provides helpful insights into which types of work are driving log volumes at various times of the day. So here there’s a couple of early morning intervals where it’s being driven by Db2 utilities during the day, it’s primarily a CICS workload driving the logging. And then in the evening, much higher levels of logging driven by IMS batch BMP.

SQL statement activity is also captured in the statistics data. So here we see the volume of SQL statements by type for this data sharing group. Look at the various types of volumes of SQL statements.  Row activity data is also available. And again, we can view that over time similarly. Statistics data also contains a wealth of prefetch metrics and let’s look at the primary data sharing group here. Here we have the number of prefetch requests by the three different types, as well as the number of I/Os associated with each type. And then we can also view pages per I/O by prefetch type and not surprisingly pages per I/O are much higher for sequential prefetches for most time intervals.

Or we may want to explore this data at the buffer pool level. So again, we’ll look at our familiar buffer pool 20. And so now we can zero in on the prefetch activity for that specific buffer pool. So we only had the opportunity today to scratch the surface of these and all the statistics metrics in this session. But I hope we come away with a greater appreciation for how much you can learn about your environment from exploring the Db2 statistics data.

Learn More About Analyzing Db2 Statistics and Accounting Data