AIOps: Monitoring and Observability - Group home

Business cycles are accelerating, new technologies are rapidly improving how organizations compete, and data-driven organizations are challenging and disrupting customary business approaches

Digital transformation, modernization and AI is required to keep pace and compete.

With the retirement of veteran DBAs, and with this loss of experience, there is often the request for more expertise and help, how to best observe, and manage Db2 subsystems and the Db2 applications, including SQL stmts.

Db2 administrators often lack the knowledge to set up Db2 mechanisms used to control connections, for example, setting connection controls too low can cause an application outage, or setting connection controls too high reduces their effectiveness

=> Digital transformation requires embedded data and insight

Improving customer experience and IT operations by making enterprise data readily available to applications and Db engine itself. This includes Insight when and where it’s needed and in real-time by enabling access to transactional data at its source, and the supporting IT infrastructure.

With ML technology you can build models on your platform of choice and quickly deploy those within your transactional systems and applications. Now Db2 AI for z/OS (Db2ZAI) is built on top of the Watson Machine Learning for z/OS (WMLz) stack.

With this Db2ZAI is leveraging all the services without requiring data scientist support. It allows to generate the training data, deploys, and retrains models with WMLz.

DbzAI (5698-CGN) V1.2.0.2 has 2 major features

• Db2 System Assessment
• SQL Optimization

Db2ZAI needs as pre-requisites Db2 12 FL 500 as a minimum, together with a couple of Db2 PTF as well as Db2ZAI UI PTFs, and it uses the V2.1.0.2 of WMLz as its prerequisite.

There are three major processing steps with Db2ZAI for its System Assessment

1. Publish/Collect Statistic trace data with negligible overhead, in 1-minute intervals
2. Learning tasks and Training at WMLz (happening infrequently)

3. Assessment, as scheduled, for example, daily, weekly, or on-demand.

Although Db2ZAI has shown in IBM internal benchmarks up to 25% CP reduction in CPU time and Db2ZAI System Assessment itself is zIIP eligible, customer might not want to run the System Assessment permanently.

OMEGAMON Db2 PE (OMPE) allows you to monitor permanently, with low overhead, specific Db2 system and application metrics on a warning and critical level and launch Db2ZAI System Assessment on demand if warning or critical situation was detected.

Db2 for z/OS itself provides a tremendous amount of performance metrics, over 1,500.  In my contacts with customers I hear very often, there is no time and less knowledge about which metrics and potential exception thresholds should be used (for example,  what are the upper and lower bound performance values and metrics for their specific workload), respectively how to get also early notification and alerts before something is going wrong – and even if they get exception alerts, what to do next?

While Db2ZAI System Assessment provides the customer with overall assessments and recommendations, there is very little continuous monitoring and alerting in the running system, in application, and SQL executions provided by Db2ZAI.

Often, I get asked, how and what to best monitor. As a starting point, OMPE allows you to analyze Db2 SMF trace for system and workload to get a first set of thresholds via Batch job “Exception Profiling”. This sets of thresholds can also be defined or later edited via the OMPE PE Client GUI or in OMPE ISPF dialogs. Those threshold sets can then be used in the OMPE exception processing (sampling and checking) together with specifications of a user-defined checking interval (1 - 1440 seconds). The process is running in a subtask of the Data Collector address space. Any alerts can be received online (via pop-up) and are saved to log files or they can be alternatively routed to User Exits.

These OMPE thresholds are being defined and checked based on single performance metric values. Once an exceptional situation was detected and because system and applications problems might be even more complex, those exceptional situations should be considered in context with other metrics on a broader system wide assessment. For that reason, the DBA can easily launch Db2ZAI directly from the OMPE exception log display for doing a deeper dive On-Demand System Assessment. 

In short, these are the steps you need to enable OMPE to integrate with Db2ZAI:

1. Define thresholds on OMPE and start Periodic Exception Processing
2. Define the Db2zAI URL and Port in Db2 System Properties of the PE client
3. Select a detected exception alert from the Log file and press the “Analyze Root Cause” button

Please consider and read how to setup and configure the OMPE and Db2ZAI Integration Guide - https://community.ibm.com/community/user/hybriddatamanagement/viewdocument/ompe-and-Db2zai-integration-guide

The screens below show you the PE Client threshold editor where you can define warning and critical thresholds for Statistics or Application categories/metrics including related qualifiers (Fig. 1).

Figure 2 shows how to activate the Periodic Exception Processing per Db2 subsystem by selecting the threshold set and the interval (optionally the user exit can be selected too)

If an exception was detected it pops-up or it can be seen on the exception log window.  The exception can be selected from the list of exception and reviewed on a detail panel from where the “Analyze Root Cause” button can be pressed (Figure 3).

Pressing the button leads you to the panel first where you can enter the Start and End time scheduling entry (Figure 4)

for the ON-DEMAND System Assessment before it opened the browser UI of Db2ZAI (if you are not yet logged on, you will be prompted additionally for logon to Db2ZAI with your user ID and password) The Db2ZAI System Assessment panel is invoked directly with the new system assessment status “Started”.

Further references: You may watch my 2 YouTube videos

https://youtu.be/B_dG7U5iWZA and https://youtu.be/xDza6hQg6oQ (with live demo)

About IBM OMEGAMON XE for Db2 Performance Expert on z/OS V5.4.0 (OMPE) and the IBM Db2 Performance Solution Pack V1.5:

OMPE is a full range system and application monitor with the ability to go down to SQL level and each single Db2 event details. The parts in blue in this picture are shipped and fully licensed together within the OMPE package. The other Db2 Tools (the Db2 Query monitor, the Db2 SQL PA, and the Query Workload Tuner) are packed together with OMPE in the Db2 Performance Solution Pack but can also be ordered separately.

Beside OMPE with its powerful reporting, real-time monitoring on system and application level, the SQL monitoring, the End-to-End SQL (Extended Insight), the Stored procedure monitoring, and  the Buffer Pool Expert analysis, there exist the dedicated SQL monitoring product Db2 Query monitor which allows to  go even deeper into SQL level and object monitoring coming with exception and alert notification in addition. Both OMPE and Db2 QM have an integration with the Db2 SQL Performance Analyzer and Query Workload Tuner for tuning SQL with What-if simulation capability, as well as several advisors (Stats, access path, Index, Analytics Accelerator advisor). 

The following picture shows the integration and support of OMPE within an enterprise monitoring solution. 

Not only the integration with other z/OS subsystems monitoring of the OMEGAMON family products, but also the integration with other IBM Db2 performance management tools, support of IBMs Db2 Analytics Accelerator, extended support for SAP customer’s DBA Cockpit, and finally the integration and launch capability with Db2ZAI, about which we dive a bit deeper in this presentation. . 

This range of functional support is now extended with the infusion of AI into the Db2 tool products.

Monitoring and observing your Db2 for z/OS  system and the application accessing Db2 is one thing, getting notification and alerts about exceptional situations are a second thing, but what to do as an appropriate action is very often a time consuming analysis with a lot of dependencies and interrelationships in your environment.

Db2ZAI is about to speed up, simplify, and helping you in troubleshooting coming to a faster resolution.

Author: Norbert Jenninger, OMEGAMON for Db2 Consultant

               email: njenninger@rocketsoftware.com  - Feel free to contact me.