IBM SQL Tuning Services components and architecture

In addition to the components that comprise SQL Tuning Services itself, a typical SQL Tuning Services environment can include several supporting products that make it easier to tune SQL. This topic identifies the components that comprise an SQL Tuning Services environment and illustrates the role that these components and product play and how they work together.

SQL Tuning Services components

Before you begin to install and configure SQL Tuning Services, you should understand the function of its components.

SQL Tuning Services server: The SQL Tuning Services server is a WebSphere® Liberty server that runs on the UNIX System Services (USS) layer of z/OS®. All of the SQL Tuning Services APIs reside on this server. To enable the use of the extended set of APIs, you must install the IBM® Db2® Query Workload Tuner for z/OS 6.1 license on this server.
Repository database: The SQL Tuning Services repository database is a Db2 for z/OS database that is used to store tuning metadata (for example, historical tuning results). Each SQL Tuning Services server requires its own dedicated repository database. You can use the DSN5REPO sample JCL job to create a repository database and its objects including tables, indexes, UDFs, and so on.
Db2 for z/OS tuning target subsystem: This subsystem contains the SQL that you want to analyze and tune. A single SQL Tuning Services server can tune SQL on multiple target subsystems. A target subsystem can reside on the same LPAR as the SQL Tuning Services server, or it can reside on a different LPAR. Each target subsystem requires a set of EXPLAIN tables to store information about SQL statements, access paths, and other elements that can affect SQL performance. SQL Tuning Services provides you with an API to create the EXPLAIN tables.

Supporting products

SQL Tuning Services functionality is integrated into the following products, both of which provide you with a graphical user interface to perform SQL tuning tasks:

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.
IBM Db2 for z/OS Developer Extension integrates SQL Tuning Services functionality into a Microsoft Visual Studio Code development environment so that application developers can tune their SQL applications as they are writing them.

Additionally, IBM Db2 Query Workload Tuner for z/OS is required to use many of the advanced features that are available for tuning single SQL statements and SQL statements in an SQL workload.

Architecture

The following figure illustrates the role that each SQL Tuning Services plays and how each of these components works with other components.

Step	Description
1	A Db2 Administration Foundation or Db2 Developer Extension user selects an SQL statement that they want to tune and selects one or more tuning actions. Db2 Administration Foundation users can select multiple SQL statements (workload-level tuning) if IBM Db2 Query Workload Tuner for z/OS is installed in their environment.
2	When the user initiates the selected tuning action or actions, SQL Tuning Services issues an EXPLAIN statement for the selected SQL on the target Db2 database and collects the Explain information and catalog information that it uses to analyze various aspects of the SQL statement's performance. Note: The Db2 for z/OS tuning target subsystem can reside in the same LPAR as the SQL Tuning Services server or it can reside in a different LPAR.
3	The Explain information and catalog information are returned to the SQL Tuning Services server, the SQL is analyzed, and the requested tuning actions are performed (access paths are compared, recommendations are generated, and so on).
4 and 5	Depending on the the type of tuning action that the user initiated, SQL Tuning Services might reconnect to the tuning target subsystem to perform additional tuning operations (for example, to perform virtual index analysis).
6	After SQL Tuning Services analyzes the SQL by using the information that was collected in step 2 and its built-in knowledge base, it returns recommendations that the user can implement to improve the performance of the SQL statement.
7	The results of the SQL Tuning Services actions are written to the repository database to maintain a history of tuning activity and results.