Db2 Tools for z/OS

Db2 Tools for z/OS

Connect with Db2, Informix, Netezza, open source, and other data experts to gain value from your data, share insights, and solve problems.

 View Only

IBM Db2 Administration Foundation for z/OS now includes SQL Tuning

By CALENE JANACEK posted Mon November 01, 2021 09:41 AM

  
IBM Db2 Administration Foundation for z/OS, with PTF 2 (UI77754) enabled, now includes capabilities for analyzing and tuning Db2 for z/OS SQL statements This release marks a major milestone on the path of integrating key Data Studio and Data Server Manager functionality into Db2 for Administration Foundation for z/OS. 

PTF 2 enables you to perform SQL tuning actions, such as Visual Explain and Statistics Advisor, to help analyze query performance issues.  Db2 Administration Foundation provides one consolidated browser-based user interface, the IBM Unified Experience for z/OS, that is built on top of the open-source Zowe Virtual Desktop. You can interact with Db2 assets based on the task you want to accomplish.   This removes the complexity of individual tooling and integrates query tuning with user-driven administrative actions.

The IBM Unified Management Server for z/OS (UMS) is the platform designed for modern mainframe data management. UMS is the architecture server that provides all the common functions needed by the data management experiences that are installed and accessed, such as the Db2 Administration Foundation. UMS reduces the amount and effort of installation and maintenance of code that would otherwise be required by multiple software applications. New features and fixes are for UMS and the data management products, such as Db2 Administration Foundation, are released through separate PTFs.  To enable SQL Tuning in Db2 Administration Foundation, you must install Db2 Administration Foundation PTF 2 and the compatible UMS PTF 7 (UI77744, UI77745).

IBM SQL Tuning Services is needed to enable the SQL tuning capabilities in Db2 Administration Foundation.  SQL Tuning Services is a component of the IBM Database Services Expansion Pack, which ships with the Db2 Accessories Suite for z/OS. After IBM SQL Tuning Services is setup on UNIX System Services (USS), the UMS configuration file must be updated with SQL Tuning Services server information to enable communication between UMS and SQL Tuning Services.  Once this setup is complete, you can invoke tuning capabilities from within Db2 Administration Foundation.
In the Unified Experience for z/OS, administrators can do a one-time setup of tuning profiles (which maps the Db2 for z/OS subsystem to SQL Tuning Services).  This allows for easy sharing of the tuning profile to non-privileged IDs making SQL tuning easier to use for less experienced mainframe staff.
Figure 1. Setting up Db2 Administration Foundation tuning profile in the Unified Experience for z/OS interface

Db2
Administration Foundation users can tune their SQL queries (using Visual Explain and Statistics Advisor) from the SQL Editor and Package explorer.
Figure 2. Query tuning from SQL Editor and Package Explorer in Unified Experience for z/OS interface

PTF 7 (IBM Unified Management Server for z/OS APAR PH41007) was released on October 29 and can be ordered  on ShopZ.
The recommended level of Zowe is 1.24 or higher. Learn more about IBM Administration Foundation for z/OS.




#Db2Toolsforz/OS
0 comments
28 views

Permalink