Created:  | Updated: 


Summary

z/IRIS enables the JDBC observability for JDBC calls processed by 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

Instana Db2 Accounting screenshot

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

Instana Db2 Deadlock screenshot

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

Instana Db2 Deadlock RNA screenshot

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

Instana Db2 Deadlock participant screenshot

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

The performance class IFCIDs are expensive to activate and are therefore only used for specific analysis purposes. Please consult the relevant IBM Db2 for z/OS documentation regarding the performance impact of activating these IFCIDs.


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 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.

Use the Instana Analyze dashboard to search for traces with the trace.id provided in the deadlock event details.