Avoiding database sync I/Os (achieved when getpages are satisfied from data already residing in a buffer) plays a prominent role in Db2 performance and efficiency. Dynamic navigation to explore those waits at the levels of detail made possible from the Accounting data can greatly aid analysis.
More Db2 Accounting Videos
- Exploring Analysis by Connection Type
- Exploring Analysis by Correlation ID
- Exploring Elapsed Time Profiles
- Exploring Analysis by Authorization ID
- Exploring Prefetch Activity and Suspension Events
- Exploring Analysis by Plan or Package Name
- Exploring Database Sync I/O Activity
- Case Study: Isolating Change Drivers
- Exploring Other Metrics in Accounting Data by Plan
- Accounting Data: Customized Dashboard Recap
We talked a lot in the Db2 Statistics session about seeking to avoid a database sync I/Os, by being able to satisfy get pages from data already residing in a buffer through one of the prefetch mechanisms to avoid I/Os that are synchronous with the unit of work and thus introduce delays into response times and also create CPU which is driven by the I/Os.
So the accounting data enables us to explore those sync I/O waits at the levels of detail that we’ve been discussing today. So here we see that the number of sync I/Os by plan. So again, let’s look at this data over time and I’m going to, again, shrink down the top six in this case and remove some of the noise.
So here are the plans that are generating the most sync I/O waits, and you can see here, this top plan is generating more than 10,000 per second for several different hours. All right. So again, as we’ve been doing this exploring of the accounting data, what questions come to your mind? Well, I guess what connection type is this plan? Alright. It is Db2 Call Attach. All right. And then for Db2 Call Attach, we learned the correlation ID contains the job name. So what jobs are using this plan? We can see here that this plan is widely used by many jobs, this is just the top 20 of many. So again, the accounting data gives us insight into those database sync I/Os at great levels of detail, including here connection type and correlation name.
You May Also Be Interested In:
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.
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.
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.