IBM Db2 AI for z/OS — Improving Application Performance using Machine Learning
Performance is Key
There continues to be considerable focus on performance improvements as one of the main themes in recent IBM Db2® releases. With the high-value data retained on Db2 for z/OS and the Z platform, organizations strive to extract value from that data for competitive advantage.
As mentioned in my previous blog the IBM Z platform has become a hybrid transaction and analytical processing (HTAP) environment being able to deliver high-speed processing transactions as well as for complex, resource-intensive Db2 queries to support business-critical reporting and analytic workloads.
The query processing engine of Db2 is generally referred to as the optimizer or the Relational Data Services (RDS) components, which encompasses the query transformation, access path selection, run time, and parallelism. The job of the Optimizer is to determine the best way to return data results from a query in Db2.
The access paths formulated by the Db2 optimizer during the BIND process are critical to application performance. It is these access paths that determine how efficiently Db2 data is read and delivered to an organization’s programs.
The stability of access paths is important. And with customers executing thousands of distinct SQL statements millions of times per day, there can be a disruption to their system performance if the optimizer is not choosing the most effective access path for any single SQL.
There are many events that can occur that might cause Db2 to change from a good access path to the bad access path, or where a suboptimal path is initially chosen. Customers desire 100% perfection in access path selection, which has eluded commercial DBMSs. With the mission critical nature of Db2 for z/OS customers, any single poor optimizer access path choice is undesirable.
Based on customer engagements our own distinguished engineer estimates that 25–30% of customer DBA time is spent managing access paths — where even minor performance regressions can cause disruption to their business and increase the impact to IBM support. Getting applications to run efficiently can help reduce CPU costs and meet the service level agreements expected by the business. This can be a constant battle that requires DBAs to possess acute analysis and tuning skills.
Machine learning based applications co-located with data and other necessary services running natively on z/OS or Linux for System z can operate more efficiently than if running on separate / remote machines because of reduced infrastructure latencies . Co-located applications, data and services can also benefit from consistent security and governance policies being applied across those resources.
Introducing IBM Db2 AI for z/OS
IBM Db2 AI for z/OS (architecture shown in figure #1) is a new offering that adds AI capabilities to the Optimizer, leveraging native machine learning on the Z platform for the benefit of Db2 for z/OS. It helps create, learn and tune optimized access paths using continuous ML feedback loops and capabilities already present in IBM Machine Learning for z/OS — faster and more accurately. It collects query execution history and feeds information into ML models which are then used by the Optimizer at next bind/rebind/prepare.
Use case scenario — Access Path Selection and REBIND
Db2 AI for z/OS constructs ML models to assist the Db2 Optimizer in choosing the most efficient access paths by learning key inputs from a customer’s workload. Inputs that are currently “estimated” because they are unknown and unavailable to the optimizer include host variable/parameter marker values and the number of rows required by the application. These two inputs can have a significant impact on the optimizer’s ability to choose the most efficient access path.
Models are based on actual execution of queries that are executed on the target system. It learns the patterns from your collected data from workloads in an organization’s unique operating environment and determines the correct inputs for the optimizer for SQL statements entering Db2 for z/OS.
Db2 AI for z/OS creates, deploys and retrains the models — therefore encapsulating the data science within Db2 AI for z/OS. DBAs and System Programmers can therefore utilize Db2 AI for z/OS in their Db2 subsystem without requiring data science experience.
The solution also comes with built-in dash boards to help monitor SQL performance, connections and models.
Summary and Next Steps
The goal of IBM Db2 AI for z/OS is to provide self-tuning / self-managing capabilities to Db2 for z/OS. In this blog we have focused on the Optimizer component, as missing inputs to query optimization can result in higher CPU costs and query response times that do not meet expected service level agreements.
There are many other opportunities where AI and machine learning could be used to help further optimize Db2 such as determining when to rebind, providing targeted query level and subsystem memory allocations, IBM Db2 Analytics Analyzer (IDAA) offload decisioning, Fast Traversal Block (FTB) usage, REORG minimization, RUNSTATS/optimizer integration, intelligently applying maintenance and optimizing system health in general.
For more information on IBM Db2 AI for z/OS and how to obtain it click this link ibm.com/analytics/db2/zos
Register also for the webcast on Db2 AI for z/OS.
Steven Astorino,
VP of Development, Hybrid Cloud, z Analytics and Canada Lab Director
IBM Analytics
Follow me: @astorino_steven
