Db2 Accounting: Exploring Elapsed Time Profiles

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. Case Study: Isolating Change Drivers
9. Exploring Other Metrics in Accounting Data by Plan
10. 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.