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
- 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
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:
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.
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.
Evaluating and Tuning Db2 Subsystem Wait (Suspension) Times
Understanding what causes wait (suspension) time can be instrumental in improving your subsystem and application performance.