The many timing buckets captured in the Db2 Accounting data enable great visibility into the elapsed time profiles of work executing within Db2. This is particularly powerful when combined with the capability to easily subset this data by criteria captured in these records including connection type, correlation ID, authorization ID, and plan and package name.

 

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. Exploring Other Metrics in Accounting Data by Plan
  9. Accounting Data: Customized Dashboard Recap

 

Video Transcript

Another very common and helpful use of the Db2 accounting data is to look at the elapsed time profiles of the work. And so here we have it by connection type. Again, the difference between the CICS and IMS batch, for example, then when we drill on the CICS by correlation ID, now we will see the elapsed time profiles by transaction. And so, and this legend on the right indicates a dozen or so timing buckets that are captured in the accounting records, and the IBM SMF field names and field descriptions are captured there below. Let’s go ahead and capture this in the dashboard. So looking across the profiles of these top 20 transactions, we see elements in common, for example, the red and the yellow components of the bars, the Db2 Class 2 CPU time, and the random sync read I/O wait time are the primary components of elapsed time for most of the transactions. Also, we have observed significant variances, most notably how the elapsed times range from under 10 milliseconds per commit to almost 200 milliseconds per commit. And we haven’t mentioned it in the session until now, but Db2 member names are carried along in the accounting data. So we can view any of these metrics across members of the data sharing group. And so when we do that here, we validate our expectation that elapsed times are similar across the members of the data sharing group.
All right. So now let’s go ahead and view the elapsed time profile for this transaction across the selected interval. And in addition to the Db2 Class 2 CP time and the sync I/O wait time there’s also a third measurable component here, and that is contention for L locks. Now, rather than looking at this as a stacked area chart, which is certainly a valid way to look at it, perhaps we want to evaluate each component in relation to the others. So let’s go ahead then and turn this into a line chart, and when we do that, we see a couple of things. One is the CPU time is very consistent throughout the entire interval. Whereas the sync I/O wait time is much higher in the evening hours, when the batch is the dominant workload, likely reflecting higher I/O volumes, creating contention for data in, in the buffers.
And then also we see spikes in this, the third component here, global contention for L locks. So again, since we’re seeing those spikes, it might be interesting to compare it to another time interval. So in this case, we’ll do the previous day. So when we do that, we also observe spikes in that same metric, but not at the same times and not with the same intensity, so that could warrant some additional investigation. All right. And then finally, here’s just a quick preview of an example that we’re going to be exploring when we get into the next session. We’re talking about integrating Db2 accounting data with other types of SMF data. In this case, this view brings together on the top row data from the CICS transaction, the 110 subtype 1 records, and puts it together with the Db2 accounting data along the bottom. So you can compare the values and get the CICS and the Db2 perspective. So that’s all we’ll say on that now and we’ll spend more time on that in the next session.

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:

Whitepaper

IDC Technology Spotlight on Optimizing Mainframe Availability and Cost

This IDC Technology Spotlight, written by Research Vice President of IDC Tim Grieser, discusses how intelligent analysis of z/OS operations data optimizes mainframe availability and cost.

Download
Webinar

Implementing Asynchronous Duplexing in a Production Db2 Lock Structure - User Experience

Analyze the performance impacts of implementing asynchronous duplexing for Db2 lock structure in a high volume Db2 data sharing environment.

Watch Webinar
Whitepaper

How to Achieve Higher Availability and Lower Performance Costs with Asynchronous Db2 Lock Structure Duplexing

In this Cheryl Watson Tuning Letter Reprint, Todd Havekost and Frank Kyne detail the unexpected performance benefits gained after one site implemented Asynchronous duplexing.

Download

Explore Db2 Performance Management and Monitoring