Easy accessibility to key Db2 buffer metrics simplifies buffer pool tuning analyses, increasing the likelihood they will be periodically revisited using data from new time intervals. This video explores key metrics for two other buffer pool tuning methodologies.

 

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

A second buffer pool tuning approach presented at a major Db2 conference identifies page residency time as a key focus metric. Namely the average time a page is resident in a buffer pool. This is a metric that’s well suited to automated assessment, as we saw earlier in the health insights view. So by default the thresholds here, warn at 90 seconds with an exception at 60 seconds. So as we scroll down and look at the buffer pools here, the size of the red icon here on buffer pool 20 indicates that it has more exception intervals than other buffer pools indicating that under this methodology, it would again be the top candidate to receive additional memory. Or we can start with a view like this one that focuses specifically on page residency times. So starting from this basically 24 hour interval, we see buffer pools 3, 20, and 25 with lower residency time.

Well, let’s go ahead and customize this report to only include those buffer pools. When we view this data over time shows comparable residency time among these three buffer pools for non-prime intervals, but during the prime interval buffer pool 25 is much higher time. So let’s just assume our primary focus is prime shift. So let’s go ahead and remove buffer pool 25 as well, and that will then serve to expand the scale for the other two buffer pools. And again, buffer pool 20 emerges with the lowest residency time. Again, let’s go ahead and capture this in our dashboard.

So in addition to residency time, this by-time view also brings in random and sequential get ratios just for context. And we’ll say a little bit more about those hit ratios shortly. But here we observe that the random hit ratio doesn’t improve much even with relatively significant increases in the residency times. Although these non-prime intervals likely have very different workload profiles in terms of batch versus online, so that may call the significance of that observation into question. And let’s go ahead and capture this in our dashboard.

And then finally here, let’s go ahead and compare across the members of the data sharing group. And we see that the page residency times are relatively similar except for the last member, which has a separate function. A third approach also presented in a major Db2 conference focuses on buffer pools with lower random buffer hit ratios. That ratio quantifies a percentage of random get pages that are satisfied from a buffer and thereby avoiding a sync read I/O. That’s another metric that’s well-suited to automated assessment. Here we see the random buffer pool get page hit ratio, and the thresholds are set to warn at 75% and have an exception at 50%. And again, as we scroll down that column, we see that buffer pool 20 again, with a large red icon, has more exception intervals than the other buffer pools.

Or once again, we can use a view like this that focuses specifically on the random buffer pool hit ratio with a timeline view, and many buffer pools result in lots of noise. So we changed this to a stack bar. It becomes apparent that the random buffer hit ratio for buffer pool 20 is by far the lowest. And that’s true kind of over the entire day, or if we look at the prime shift also very much the case there as well. So interestingly under all three methodologies that we looked at buffer pool 20 is the top candidate for additional memory. Let’s go ahead and add this to the dashboard as well.

Learn More About Analyzing Db2 Statistics and Accounting Data

Speak to a Technical Expert Today

Whether you are conducting product research, need support on a project, are experiencing downtime, or want to learn more about how IntelliMagic can support your business, our experts are here to help.

You May Also Be Interested In:

Blog

Profiling zHyperLink Performance and Usage

In this blog, we demonstrate how to profile zHyperLink performance and usage by reviewing one mainframe site’s recent production implementation of zHyperLink for reads, for Db2.

Read more
Blog

How A Db2 Newbie Quickly Spotlights the Root Cause of a Db2 Slowdown

This blog explores how a non-Db2 expert quickly identified latch contention arising from a Data Sharing Index Split as the root cause of a Db2 delay/slowdown.

Read more
Blog

Integrating Dataset Performance (SMF 42.6) and Db2 Buffer Pools & Databases (SMF 102/IFCID 199) Data

Dataset performance data from SMF 42.6 records provides disk cache and response time data at the Db2 buffer pool and Db2 database levels when integrated with Db2 Statistics IFCID 199 data.

Read more

Explore Db2 Performance Management and Monitoring