Db2 I/O Cache Insights from IFCID 199 and SMF 42 Data

Integrating IFCID 199 and SMF 42 data provides extensive insights into I/O cache characteristics and response time data at the Db2 buffer pool and Db2 database levels.

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

We can expand our insights into buffer hit ratios and buffer efficiency by leveraging Db2 IFCID 199 data that captures buffer pool statistics at the dataset level. The state is very powerful and becomes even more so when integrated with data set I/O performance metrics, which are provided by the SMF 42 records.

When a get page cannot be satisfied from a buffer causing the sync read I/O, this linkage with the SMF 42 data provides visibility into the response time and cache characteristics of that I/O at the database and buffer pool levels. These insights can be leveraged to enhance our buffer pool tuning methodology, which we will come back to shortly.

Starting with data by buffer pool, here we see several metrics of interest. For context, the sync I/Os by buffer pool, which are available in the statistics data, are shown here which quantify the I/Os that we’re seeking to avoid through buffer hits. And again, no surprise, buffer pool 20 is again at the top here. Now here in the middle, we have visibility into disk cache statistics, which are made possible by that integration with the SMF 42 data, namely, how many of the I/Os associated with each buffer pool were cache hits in this top row report and how many more cache misses in this bottom row.

So let’s go ahead and look at the cache hits and look at buffer pool 20 here over time. So, these are the cache hits, and perhaps we would also like to kind of see how the hits and misses compare over time. So we can bring both of those together and see the correlation on this chart for buffer pool 20. And then let’s go ahead and capture that in our dashboard.

Another set of insights available from this integration with SMF 42 data is disk response times by buffer pool separated out by the traditional z/OS response time components. So here we see for buffer pool 20, when a get page cannot be satisfied from the buffer and a sync I/O to disk takes place, the average I/Os queue time, pen disconnect, and connect times for those I/Os. And this drill-down shows consistency in those disk response times across the interval. And we might be interested in comparing that with the I/O rate, which we’ll put on a secondary axis because it’s got a different unit of measure. And again, so despite significant differences in the I/O rate over time, the response time is quite consistent. Again, let’s go ahead and capture this in our dashboard.

Now, perhaps we want to explore those response times for I/Os out of buffer pool 20 across the various databases that are being accessed through that buffer pool. And when we do that, we observe widely differing values. So I think the next thing we want to do here is compare those response times over time across the databases. So to do that I need to have a single variable. So this will be total response time and turn it into a line chart. And then we’ll go with the top 10 databases. So when we do that, we see there are a couple of databases with much higher response times during the day. Again, let’s go ahead and capture that in our dashboard. Then if we wanted to explore these response times for the higher databases, perhaps across the members of the data sharing group. In this case, we see quite differing values, which may indicate different work profiles on some of the members of the data sharing group. We’ve seen that this, IFCID 199 data is available at the buffer pool and database levels. So now let’s look at some views that start by database, which are seen across the bottom row here. So we can begin here. This is the mix of types of I/Os for the databases, with the highest I/O activities. And the sync reads are further broken out by cache hits and misses.

We can also explore how the I/Os for a database are distributed by buffer pool type and how those map to buffer pools. So almost all of the group buffer pool dependent data buffers for this database map to buffer pool 20, whereas the index buffers map to buffer pool 21. Also here, we can see the disk response times for the 20 busiest databases. Again, armed with what we just learned when we drill into this by buffer pool, as we compare the response times, we see between the data buffers on the first bar and the index buffers on the second bar for this database.

Also if analysis warrants, we can further explore this data by criteria, such as table space which will come into some of our accounting analysis that we’ll be doing in the subsequent session. And also we can even view this data by partition.

Learn More About Analyzing Db2 Statistics and Accounting Data