SQL Analytics from EZ-Tracer/EZ-Cache to Splunk
Posted by Clive Petley on 03/07/18 @ 11:48 AM
EZ-DB2 SQL Analytics to Splunk!
With APAR CX910323, EZ-Tracer/EZ-Cache can now send detailed SQL metrics via Syncsort Ironstream, directly off-mainframe to Splunk (also Elasticsearch).
This allows for detailed analytics to be performed on your EZ-DB2 workload metrics, down to the individual SQL statement level.
Traditionally, analytics/reporting may have been performed using Db2 SMF Accounting Data, but these analytics only allow reporting to the package/thread level. A single thread or package may issue hundreds, maybe thousands of SQL statements - how do you know which statement(s) are causing the problems?
EZ-Tracer/EZ-Cache has several 'emit' points, where SQL events can be output to Syncsort Ironstream, to be ingested into Splunk (or Elasticsearch). These emit points allow the user to control the volume and frequency of data being sent to Splunk:
- EZ-Tracer can send an SQL event for every SQL execution - these allow for detailed analysis, including the ability to 'follow' the SQL executed by a single thread. EZ-Tracer events can be 'correlated' with additional SMF data such as SMF110 (CICS) or SMF101 (DB2 Accounting), that can be collected by Ironstream.
- EZ-Cache can send cache read interval cumulative events. This provides a low-cost mechanism to analyse SQL events over a period of time. SQL executions for the 'same' SQL statement are accumulated and output at each cache read interval, which would typically be every few minutes.
- EZ-Tracer/EZ-Cache can send cumulative trace level events. A cumulative event is sent for each distinct SQL seen during the full trace. These events allow SQL trends to be reported on and monitored over an extended period of time.
- Additional events to include SQL Text and DBName/Tablename cross referencing can also be output.
Below are some Splunk dashboard examples, showing the type of analytics that are possible with EZ-DB2 data.
For full details on how to implement sending SQL metric data to Splunk, via syncsort Ironstream, please review the attached User Guide, or contact us for further details.