Overview of Db2 Query Workload Tuner
IBM® Db2® Query Workload Tuner for z/OS® 6.1 (Db2 Query Workload Tuner) is a collection of components that enables database administrators and application developers to optimize the performance of SQL statements that query data in Db2 for z/OS databases and to maintain that high level of performance over time.
SQL optimization is critical to maintaining a high level of application performance in a Db2 for z/OS environment. When SQL queries and workloads are not tuned for optimal performance, application degradation can put revenue at risk, reduce customer satisfaction, and increase infrastructure costs.
- IBM SQL Tuning Services for z/OS
- IBM Db2 Administration Foundation for z/OS
Db2 Query Workload Tuner also includes a license for enabling the extended tuning features of SQL Tuning Services.
The following sections describe each of the Db2 Query Workload Tuner components.
SQL Tuning Services
The core SQL analysis and tuning functionality of Db2 Query Workload Tuner is provided by IBM SQL Tuning Services for z/OS. SQL Tuning Services is a set of RESTful APIs that enable you to improve the performance of single SQL statements and SQL statements that comprise a workload by running the following SQL analysis and tuning tools:
- Access Path Advisor
- Generates access path recommendations that can improve the performance of a single query.
- Access Path Comparison
- Compares two access paths and displays the results of the comparison. A graphical representation of the access path flow is provided for each access path along with any meaningful differences in the plan table records for each access path.
- Capture Query Environment
- Captures details about the environment in which you are running an SQL statement. These details are saved to a file that you can provide to IBM Support when you are trying to resolve a performance problem with an SQL statement or that you can use to re-create an environment on another subsystem.
- Index Advisor
- Generates recommendations for creating, altering, or dropping indexes that can improve the performance of a single query. This feature provides the DDL scripts that you can run and information about the existing indexes from the query that you’re tuning.
- Index Impact Analyzer
- Generates a report that shows the impact of applying the index changes that are recommended by Index Advisor and Workload Index Advisor.
- Job Management
- Enables you to query information about SQL Tuning Services jobs and cancel, delete, and view the results of SQL Tuning Services jobs.
- Query Rewrite Advisor
- Evaluates how efficiently a query is written and generates best-practice recommendations for rewriting it to improve its performance.
- SQL Annotator
- Gathers statistical information about database objects that are used for access path selection and provides a cost estimate for running a particular query. This information can help you understand how a query is processed by the Db2 optimizer so that you can determine which other SQL tuning tools might be able to improve that query's performance.
- SQL Formatter
- Formats an SQL statement for a single query so that you can more easily read and understand the structure of the statement.
- Statistics Advisor
- Generates a recommended set of RUNSTATS commands to improve query performance.
- Visual Explain
- Generates a graphical representation and a grid plan table view of the access paths for an SQL statement. This information is useful for tuning queries for better performance.
- Virtual Index Analyzer
- Virtually tests indexes to determine if the performance of a single query can be improved by creating or dropping the indexes.
- Workload Access Path Advisor
- Generates recommendations for improving the access paths that are used by an SQL workload.
- Workload Access Path Comparison
- Compares, annotates, and generates reports on the access plans and estimated cost changes for SQL statements in different packages or workloads.
- Workload Analytics Acceleration Advisor
- Generates a report that shows the eligible and ineligible statements for offloading to IBM Db2 Analytics Accelerator for z/OS and quantifies the CPU savings that can be made by offloading.
- Workload Candidate Acceleration Analyzer
- Generates a report that shows the eligible and ineligible statements for offloading to IBM Db2 Analytics Accelerator for z/OS and quantifies the CPU savings that can be made by offloading with user input candidate tables.
- Workload Explain
- Gathers explain information for all statements in a workload and stores this information in the repository database. This information is used by other workload advisors to analyze these statements.
- Workload Index Advisor
- Generates recommendations for creating, altering, or dropping indexes that can improve the performance an SQL workload. This feature provides the DDL scripts that you can run and information about the existing indexes from the workload that you’re tuning.
- Workload Management
- Provides a set of APIs for performing common workload-related tasks such as creating, updating, and deleting workloads, listing the statements in a workload, and refining workload results by applying filter criteria.
- Workload Query Environment Collector
- Generates diagnostic information for a workload, which is needed by IBM Support, and return a job ID as an asynchronous request.
- Workload Query Rewrite Advisor
- Generates recommendations for rewriting statements in an SQL workload to improve its overall performance.
- Workload Statistics Advisor
- Generates recommendations and RUNSTATS DDL for creating or modifying statistical views.
- Workload Virtual Index Analyzer
- Virtually tests indexes to determine if the performance of a workload can be improved by creating or dropping the indexes.
Additional APIs are provided for performing tasks that support SQL analysis and tuning such as setting up an SQL Tuning Services environment, managing EXPLAIN tables, and retrieving SQL statements from a variety of sources.
Because SQL Tuning Services functionality is delivered as RESTful APIs, you can call them directly from an application or development environment (for example, from within a CI/CD DevOps pipeline).
Additionally, SQL Tuning Services functionality is integrated into the user interfaces of the following products:
- IBM Db2 Administration Foundation for z/OS, which is included with Db2 Query Workload Tuner, leverages the SQL Tuning Services APIs to improve the performance of SQL applications that are running on your system. A database administrator can identify, evaluate, and improve SQL queries that affect system performance.
- IBM Db2 for z/OS Developer Extension integrates the SQL Tuning Services APIs into a Microsoft Visual Studio Code development environment so that application developers can tune their SQL applications as they are writing them.
Db2 Administration Foundation
IBM Db2 Administration Foundation for z/OS is a browser-based graphical user interface for managing Db2 for z/OS databases. It runs within an IBM Unified Management Server for z/OS environment, which is based on Zowe™ and provides an architectural framework for various tools that you can use to manage data on IBM Z® systems.
For more information, see Using Db2 Administration Foundation.
Enablement license for the extended tuning features
After you successfully install and configure SQL Tuning Services, you'll use the Db2 Query Workload Tuner license to enable the extended tuning features.