Db2 for z/OS and its ecosystem

 View Only

Another step forward in the modernization of the Db2 for z/OS ecosystem

By Stan Wong posted Tue February 21, 2023 12:22 PM


We are excited to announce the availability of several new offerings that work together to extend the ability to analyze and tune SQL statements within your Db2 for z/OS environment and that also deliver significant usability improvements to help database administrators and application developers.

  • IBM SQL Tuning Services 2.1
  • IBM Db2 Query Workload Tuner for z/OS 6.1
  • IBM Db2 Administration Foundation 1.2
  • IBM Db2 for z/OS Developer Extension 2.1

The main focus of these releases is SQL performance, which is one of the key priorities for both Db2 for z/OS database administrators who are tasked with keeping their systems running optimally and for application developers who strive to write code that runs quickly and that consumes as little resource as possible. SQL statements that don’t take advantage of the most efficient access path, that don’t leverage indexes properly, or that could be improved simply by rewriting them can prevent DBAs and application developers from reaching their performance goals.

To help alleviate these types of SQL performance problems, these new releases can be used in conjunction with one another to provide a comprehensive SQL analysis and tuning solution.

IBM SQL Tuning Services 2.1

IBM SQL Tuning Services is the foundation of our SQL performance solution. It provides SQL analysis and tuning capabilities that can be used on their own or from within other products.

This newest version of IBM SQL Tuning Services delivers significant new capabilities that provide database administrators and application developers with the tools they need to achieve their SQL performance goals. The following table lists all of the features that SQL Tuning Services 2.1 now has to offer:

  • Access Path Advisor*
  • Access Path Comparison*
  • Capture Query Environment
  • Index Advisor*
  • Query Rewrite Advisor*
  • SQL Annotator*
  • SQL Capture
  • SQL Formatter
  • Statistics Advisor
  • Visual Explain
  • Workload Access Path Advisor*
  • Workload Access Path Comparison*
  • Workload Explain*
  • Workload Index Advisor*
  • Workload Management*
  • Workload Query Rewrite Advisor*
  • Workload Statistics Advisor*

*Available through Db2 Query Workload Tuner for z/OS 6.1, described below.

One of the key characteristics of SQL Tuning Services is its flexible design. All of these features are delivered as RESTful APIs, which makes it easy to implement them into a development environment or CI/CD DevOps pipeline to automate SQL performance validation. Additionally, many of these features are also integrated directly in the newest versions of Db2 Administration Foundation and Db2 for z/OS Developer Extension, both of which are described below. (Workload-level features are available for use only as standalone APIs currently.)

Integrating SQL Tuning Services functionality into these two GUI-based products provides DBAs and application developers with easy access to a powerful set of tools for maintaining a high level of SQL performance across Db2 for z/OS environments.

As with the previous SQL Tuning Services offerings, some of the tuning features are available at no charge to licensed users of Db2 for z/OS via the Database Services Expansion Pack component of Db2 Accessories Suite. The new expanded set of tuning features requires Db2 Query Workload Tuner for z/OS 6.1, which is available as a standalone product and is also included with IBM Db2 Performance Solution Pack for z/OS 2.1.

See Overview of SQL Tuning Services for more information.

IBM Db2 Query Workload Tuner for z/OS 6.1

  • Extends your ability to analyze and tune SQL statements
  • Can be used as a standalone set of RESTful APIs by leveraging the SQL Tuning Services 2.1, or can be used from within the user interfaces of supporting products
  • Simplifies the deployment of SQL tuning capabilities in Db2 Administration Foundation

Db2 Query Workload Tuner 6.1 is a licensed product that provides access to the new extended set of SQL analysis and tuning features of SQL Tuning Services 2.1 to deliver a streamlined workflow to collect, analyze, and optimize single SQL statements and statements at the workload level.

Unlike previous versions of Db2 Query Workload Tuner, you have a high degree of flexibility with how you use its features. Functionality in this latest version is delivered as RESTful APIs that can be easily implemented in a CI/CD DevOps pipeline and that also have been integrated into the graphical user interfaces of Db2 Administration Foundation and Db2 for z/OS Developer Extension.

Also, Db2 Query Workload Tuner for z/OS 6.1 includes both SQL Tuning Services 2.1 and Db2 Administration Foundation 1.2 as a convenient way for organizations that are not currently using Db2 Administration Foundation to get up and running more quickly.

See Overview of Db2 Query Workload Tuner for z/OS 6.1 for more information.

IBM Db2 Administration Foundation for z/OS 1.2

  • Includes integrated support for the extended SQL analysis and tuning features
  • Expands the support for IDAA administration tasks
  • Includes numerous ease-of-use enhancements

This latest release of Db2 Administration Foundation integrates all but the workload-level SQL analysis and tuning capabilities of Db2 Query Workload Tuner directly into its user interface, which means that Db2 for z/OS administrators can now run a whole host of advisors to obtain advanced recommendations for improving SQL performance through the use of Db2 Administration Foundation’s intuitive dashboards.

By integrating these new SQL performance capabilities into Db2 Administration Foundation, DBAs can use a single consistent graphical user interface for general database administration, database accelerator administration, management of application objects in a DevOps environment, basic SQL performance tasks, and now with the extended set features provided by Db2 Query Workload Tuner, they can easily tune SQL statements for optimal performance.

In addition to the new SQL analysis and tuning capabilities, Db2 Administration Foundation 1.2 includes an expanded set of tools for managing Db2 Analytics Accelerator including the ability to add a new accelerator, to archive and restore tables, manage federated access to accelerators, and create references to a table in another subsystem.

It also takes advantage of the latest Zowe 2.3 enhancements that are available in Unified Management Server 1.2. to simplify installation, configuration, environment management and ensure access to the latest security enhancements and fixes. See What’s new in Db2 Administration Foundation for more details.

Lastly, this latest release also delivers all of the maintenance that was delivered via PTFs since initial general availability release of Db2 Administration Foundation.

IBM Db2 for z/OS Developer Extension 2.1

  • Includes integrated support for the extended SQL analysis and tuning features
  • Provides support for exploring and navigating Db2 catalog objects
  • Supports VS Code remote development

The SQL application development capabilities provided by Db2 for z/OS Developer Extension have continued to grow with each release, and 2.1 is no exception. This latest release provides SQL application developers with some significant new tools to increase their efficiency and to improve the quality and performance of their Db2 for z/OS SQL applications.

First and foremost, many of the new extended SQL analysis and tuning capabilities have been integrated into the Db2 Developer Extension 2.1 interface. Application developers can now take advantage of multiple new SQL advisors to expose potential performance issues and to generate recommendations for addressing those issues. For example, a developer can now use Index Advisor to review existing indexes and get recommendations for creating, dropping, or altering indexes to improve a query’s performance. Similar opportunities for improvement are available for access paths and syntax quality.

Another significant enhancement is the ability to navigate and explore the Db2 for z/OS catalog. As developers write SQL, they often need to explore the various objects that are associated with the databases that contain that data. Db2 Developer Extension now makes it easy to do so by using its intuitive catalog navigation support, which has been added to the extension’s side bar. Db2 Developer Extension 2.1 now supports the VS Code Remote Development capabilities, so that a developer can now create a remove Db2 Developer Extension development environment.

For more information about the new features in Db2 Developer Extension 2.1, see What’s new in this release.

Putting it all to work

Collectively, these offerings represent our commitment to the modernization of the Db2 for z/OS ecosystem and a significant step towards our goal of delivering a truly comprehensive set of tools for the administration of Db2 for z/OS and for developing high performing SQL applications. We hope you’ll take some time to explore and understand the benefits that these products can provide for your organization.