Db2 Accounting: Exploring Analysis by Plan or Package Name

Drilling into metrics at the plan (and package) levels which typically reflect alignment with business applications is often the preferred approach for detailed analysis of work coming from transaction managers like CICS and IMS.

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

The last level of detail for analyzing accounting data that we’re going to explore in this session is through plan and package names, which I’ll primarily refer to as Plan Name for brevity going forward here. So, whereas auth ID was particularly helpful for DDF workloads, Plan Name with its alignment with business applications will typically be more helpful for detailed analysis of work coming from transaction managers like IMS and CICS.

So, as we did earlier, we’ll begin our exploration from connection type that we’re going to likely be looking at planned data. And so we’re going to start with the CICS Call Attach work, and we’ll see where CICS business applications are the primary drivers of CPU within Db2. So let’s go ahead and drill into this data by plan name. And so here we see now the plans that are the primary drivers of CPU within Db2 and we’ll go ahead and capture that in our dashboard.

All right, and let’s go ahead now and let’s explore the time of day profile for that general purpose CP work. Turn this again into a timeline. When we do that, we see that these plans all kind of follow the typical online profile, except this top one that has a bump in the evening. We’ll go ahead and capture that one. So let’s hold on to that thought, and now we’re going to jump over to reports that are reporting on top plans by CPU from all connection types.

All right, and we said we’re going to have all connection types. I’m going to exclude the DDF work because that all falls under a single plan name. So we’re going to exclude that. Here we go. Connection type not equal to DRDA.

All right. So again, here now let’s go ahead and view this CPU Class 2 Db2 CPU data over time. And I’m going to set the Top five to kind of eliminate some of the extra noise. Okay. We’ll start from there and hang on to that one. All right. So now we again see the profile. These are the top five plans in terms of Db2 CPU consumption. For example, we can look at the profile of this work, and just from the time that it runs, we’re going to guess that this is a plan that’s used by batch. Let’s go ahead and look at the connection type and sure enough it is MS Batch.

All right. So now here again we see that same plan that we saw earlier with its high CPU and the evening bump. And so again, let’s just confirm that that is CICS work and it is indeed. Okay. So let’s go ahead and look at this by day and we’re going to again extend it to the entire week so we can see if this is kind of the evening bump as usual behavior or it’s an anomaly. And we can see here that on that day, the bump was later; those three days the bump was at the same time. And then on another day, there was a later bump. So yes, there is that evening bump that’s occurring with this plan of the CICS work.

All right. So again, we saw this is CICS work and we saw earlier that for CICS the correlation ID corresponds to, or contains the CICS transaction ID. So let’s go ahead now and drill into that. And then let’s go ahead and view this over time. So we see here, we’ve got several transactions that follow a typical online profile. We’ve got two notable exceptions here; one this transaction which is responsible for more CPU than the others. It peaks in the mid-morning. And then also we’ve got this transaction that is generating those evening CPU bumps. So by again, exploring the Db2 accounting data, we’ve learned a great deal about the plans in this environment that are driving Db2 CPU and their connection types. And in this case, even the CICS transactions that were driving the evening CPU time increase.