Db2 Accounting: Exploring Database Sync I/O Activity

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

1. Exploring Analysis by Connection Type
2. Exploring Analysis by Correlation ID
3. Exploring Elapsed Time Profiles
4. Exploring Analysis by Authorization ID
5. Exploring Prefetch Activity and Suspension Events
6. Exploring Analysis by Plan or Package Name
7. Exploring Database Sync I/O Activity
8. Case Study: Isolating Change Drivers
9. Exploring Other Metrics in Accounting Data by Plan
10. Accounting Data: Customized Dashboard Recap

Video Transcript

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.