Overview of Db2 Query Workload Tuner

Edit online

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.

Db2 Query Workload Tuner includes two main components:

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 or the SQL statements that comprise an SQL workload. 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. End of change

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 Capture

Retrieves statements from the following sources:

a user-defined local repository
A local file
For dynamic SQL, from the statement cache
For stabilized dynamic SQL and static SQL, from Db2 catalog tables
A sequential data set or a member of a partitioned data set (PDS or PDSE)

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.

Note: Workload analysis and tuning features are available only as stand-alone APIs and with Db2 Administration Foundation. Currently, they are not integrated into IBM Db2 for z/OS Developer Extension.

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.