Summary
z/IRIS enables the performance monitoring of JBDC calls to IBM Db2 for z/OS systems in partner APM solutions like Instana APM. With z/IRIS, DevOps teams have access to performance metrics and information related to the JDBC call processing from Db2 for z/OS systems. z/IRIS also creates Instana Events when deadlocks have been detected by Db2 z/OS systems. The deadlock events inform DevOps of the affected Java applications and scope of the Db2 z/OS resources impacted.
Instana APM traces, created by z/IRIS, are enriched with custom tags to make searching and filtering traces easy for DevOps and analysis teams.
This article describes how Instana APM users with z/IRIS can monitor the performance and cost impact of JDBC calls to Db2 for z/OS.
Distributed Db2 for z/OS traces
z/IRIS allows users to monitor JDBC calls, instrumented by partner APM platforms, to Db2 for z/OS subsystems. Distributed Db2 for z/OS traces are created from IBM z/OS SMF record types 101 and 102. The following Instana APM calls are created by z/IRIS:
Db2 Accounting calls (IFCID 3)
z/IRIS processes IFCID 3 Db2 traces with Instana trace.ids and formats these traces into Instana calls. z/IRIS Db2 Accounting calls are appended to the JDBC application traces instrumented by Instana agents for z/IRIS integration. For more information about z/IRIS related instrumentation for Instana, see 'z/IRIS - How to Enable system-wide Instana instrumentation'. Db2 Accounting call tags provide resource consumption information and performance data related to the JDBC connection transactions (commits/rollbacks). CPU and zIIP processor time, elapsed time (duration), service units and more are extracted and presented within the context of the JDBC application creating the mainframe workload.
Click on the image to enlarge
Metadata
The following metadata is added to z/IRIS Db2 Accounting calls:
| duration | IFCID 3 In-Db2 elapsed time. |
|---|---|
| endpoint | Db2 subsystem name where the IFCID 3 trace was created. |
| service | Db2 |
Db2 Deadlock calls
z/IRIS signals the occurrence of a deadlock through an erroneous Db2 Deadlock call that provides the Db2 for z/OS deadlock timestamp, the number of database resources impacted by the deadlock as well as the Db2 and z/OS system names, where the deadlock occurred. A child span is created for each Db2 RNA (resource name) locked and enriched with call tags that describe the JDBC DBATs (database access threads) holding-up or waiting for the Db2 RNA. If the JDBC applications, which are involved in the deadlock, are instrumented by Instana Java agents, then their trace.ids will be provided in either the zos.db2.deadlock.holder.trace.id or the zos.db2.deadlock.waiter.trace.id tag.
Click on the image to enlarge
Metadata
The following metadata enriches z/IRIS Db2 Deadlock calls:
| duration | Not set and therefore < 1ms is observed. |
|---|---|
| endpoint | Db2 subsystem name where the IFCID 172 was created. |
| service | Db2 |
Db2 deadlock resource name (RNA) child spans
One child span is created for each Db2 RNA listed in the IFCID 172 Db2 deadlock trace.
Call name
The call name provides the RNA locked in the deadlock event. Possible values include:
- collection.id = collectionId
- package.name= packageName
- plan.name= planName
- dbid=dbid
- obid=obid
- smallResource
where smallResource could point to one of the following:- partition.number=partitionNumber
- bucket.number=bucketNumber
- xml.doc.id=xml.doc.id
- lob.row.id=lob.row.id
- lob.version.number=lob.version.number
- physical.page.number=physical.page.number
- subpage.number=subpage.number
- page.record.id=page.record.id
Click on the image to enlarge
Db2 Deadlock RNA tags provides details regarding the JDBC connections that are holding up (i.e. blocking) or waiting for the Db2 resource, including Db2 LUWIDs, network address and Instana APM trace ids.
Db2 Deadlock participants
z/IRIS will determine whether the application JDBC sessions involved in a deadlock are also instrumented by Instana Java agents. If so, z/IRIS will locate the trace.id that contains the SQL calls impacted by the deadlock and mark the trace as erroneous. Additionally, the trace.id and span.id, which can be used to locate the Db2 Deadlock trace, is provided in the call tags zos.db2.deadlock.event.trace.id and zos.db2.deadlock.event.span.id.
It is possible to have JDBC SQL calls that have participated in multiple deadlock events.
Click on the image to enlarge
Db2 Performance calls
z/IRIS creates the following Instana APM traces using the following Db2 for z/OS performance class IFCIDs:
| IFCIDs | Instana APM call.name |
|---|---|
| IFCID 58 | Db2 Performance: End Sql QW0058SN |
| IFCID 59 | Db2 Performance: Fetch start QW0059SN |
| IFCID 60 | Db2 Performance: QW0060ST start QW0060SN |
| IFCID 61 | Db2 Performance: QW0061ST start QW0061SN |
| IFCID 62 | Db2 Performance: QW0062ST start QW0062SN |
| IFCID 63 | Db2 Performance: SQL statement |
| IFCID 64 | Db2 Performance: QW0064ST start QW0064SN |
| IFCID 65 | Db2 Performance: Open Cursor start QW0065SN |
| IFCID 66 | Db2 Performance: Close Cursor start QW0066SN |
click to enlarge image
Metadata
Each Db2 Performance call is enriched with the following data:
| duration | Not set and therefore < 1ms is observed. |
|---|---|
| endpoint | Db2 subsystem name where the respective IFCID was created. |
| service | Db2 |
z/IRIS distributed Db2 for z/OS deadlock events
z/IRIS will also create an Instana event for each Db2 z/OS deadlock trace (IFCID 172).
Click on the image to enlarge
Event title
Deadlock event detected on db2-subsystem-name involving n Db2 resources at db2.zos.deadlock.timestamp, where:
| db2-subsystem-name | The Db2 Subsystem ID on z/OS, where the deadlock occurred. |
|---|---|
| n | The number of Db2 resources impacted by the deadlock. |
| db2.zos.deadlock.timestamp | The timestamp at which the deadlock was detected. |
Event details
Complete deadlock details can be found at in the following call: trace.id = trace-id.