Db2 Accounting: Exploring Analysis by Correlation ID

Db2 analysts frequently want to subset data by correlation ID so that they have easy visibility into the specific drivers of Db2 activity (e.g., by CICS transaction, batch job, etc.).

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

From the initial categorization by connection type, analysis typically proceeds along one of three primary next steps. And so first here, we’re going to look at a correlation ID. Contents of the correlation ID field differ based on the connection type, as indicated here. For CICS, it contains the transaction ID for Db2 or TSO Call Attach when it’s through batch, it’ll be the job name. For DDF, it’s the external name for the client. For IMS, it’s the PSB name and for TSO, it’s the log on ID.

All right, well, let’s begin by exploring CPU consumption. So initially here, this is a view of Class 1 and Class 2 CPU time, and we’re going to focus on the Class 2 GCP time within Db2. So let’s go ahead and customize this and remove the zip and the class one application time. And then we’ll also turn this into a line chart so we can view it by time of day by connection type.

All right, and then let’s also extend the time interval here to the entire week of data that’s in this demo database, and then I’m going to also put that in our dashboard. All right, so we can see the pattern here and notice that the CICS work and the IMS batch work is typically the primary consumer of CPU. But I do have this spike here in the TSO Call Attach work at this particular time where it’s using more than two CPs during this hour, essentially, it’s midnight on 1/18.

So, when we drill in now by correlation ID, that for TSO batch is the job name. And so, drilling into that level will enable us to identify the job that during this hour used a significant amount of CPU. And here’s the biggest consumer by far, and I look at that over time. We can see that during that one-hour interval, it used more than two GCPs and more than 8 zIIPs. So here we can leverage analysis at the correlation ID level to identify the specific job that was consuming the CPU.

All right, so go back now at the CPU, and now let’s look at correlation ID for the CICS workload, and that also enables us to now view Db2 CPU at the transaction level. Again, I’ll drill into that. So here’s the profile for the top CICS transaction drivers of Db2 CPU, that’s the red part of the bars here. And this view also is reporting the application or Class 1 CPU time in blue. And so also then just kind of at a glance, we can see which portion of the CPU time that’s being attributed to these transactions is being consumed within Db2 versus the rest of the application.