Examples of the benefits of dynamic navigation through Db2 Accounting data applied to metrics such as (a) prefetch activity by type and (2) counts of suspension (“wait”) events.

 

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

All right, earlier when we viewed the various types of prefetch activity by connection type, we saw some increases in sequential prefetch activity. And so now that we have talked about exploring at the auth ID level, let’s do that for that early morning spike. And we can see that this auth ID is generating most of the sequential prefetch requests, which happens to be the same auth ID that we just saw earlier with those CPU spikes. So again, let’s view this over time and broaden the time interval out to the entire week. And so again, we see this process on a daily basis generating significant volumes of sequential and dynamic prefetch activity and those early morning hours on a daily basis. So, again, this is all kind of correlating together for this activity driven by this auth ID.

Another metric that’s present in the accounting data is the count of suspension events. Db2 performance experts like John Campbell, advise analysts to be aware of both the number of suspensions and the suspension time. And they’re both captured in the accounting records. We looked earlier at the elapsed time, and now we’re going to look at the counts. So, you can see here that Db2 captures approximately 30 types of waits here. And so let’s go ahead and look at this over time. You can see in this environment, the DDF work, the two primary suspension events are waits for database sync I/O and global contention for child L-locks. But notice this pocket of waits for other read I/Os which as we talked about earlier often mean waits for prefetch I/Os. And notice the timeframe here. Does any possible correlation come to mind?
Well, let’s go back and look at the suspension events by auth ID, and lo and behold, we’ve got that same auth ID generating lots of waits for other read I/Os or lots of prefetch activity. And again, if we view these suspensions over the entire week, we see them occurring at the same times as the CPU spikes and the prefetch spikes. Again, one of the things we learn as we explore the data is we can begin to correlate different types of activities and how they relate together. In this case, it’s all being driven by this same auth ID.

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

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
Video

Db2 GETPAGE Efficiency Integrating Dataset Performance (42.6) and Db2 IFCID 199 Data

Integrating Db2 IFCID 199 and SMF 42 data can be leveraged to create a view of overall Db2 GETPAGE efficiency along with several other key GETPAGE views and data.

Watch video
Blog

Evaluating and Tuning Db2 Subsystem Wait (Suspension) Times

Understanding what causes wait (suspension) time can be instrumental in improving your subsystem and application performance.

Read more

Explore Db2 Performance Management and Monitoring