This topic describes how to use the tuning features that can be integrated into Db2® Administration Foundation to tune the SQL statements that comprise an SQL workload. When you tune the SQL statements in a workload, the use of a connection profile is optional. The instructions are provided for tuning both with and without a connection profile.
Before you begin
- Ensure that IBM® Db2 Query Workload Tuner for z/OS® is installed and configured. The workload-level tuning features require a Db2 Query Workload Tuner license.
- Ensure that the SQL Tuning Services repository database has been created and that the SQL Tuning Services user ID that you are using has permission to access the repository database.
- Ensure that EXPLAIN tables have been created.
- If you intend to use a connection profile, ensure that one has been created and that you are authorized to use it.
About this task
This task illustrates the general process for using the SQL workload tuning features in Db2 Administration Foundation. It does not provide detailed instructions for running every possible workload tuning scenario but does demonstrate the frequently used workload tuning features that are available in the Db2 Administration Foundation user interface.
Procedure
-
From the navigation menu, click . The Workload manager dashboard opens.
-
Specify whether you want to use a connection profile to tune the SQL workload by setting the Enable connection profile toggle appropriately:
- If you are using a connection profile, set the Enable connection profile toggle to On and select a profile from the Connection profile drop-down. Only the profiles that are owned by you or that have been shared with you are available. The workloads associated with the selected profile are displayed.
- If you are not using a connection profile, set the Enable connection profile toggle to Off and select a subsystem from the Select subsystem drop-down. The workloads associated with the selected subsystem are displayed.
-
Click the three vertical dots at the end of the row for the workload that you want to tune and select Tune workload. The Query workload tuning page opens.
-
Select individual tuning actions from the Choose actions drop-down or click Select all to run all of the available tuning actions on the workload. If you are not using a connection profile, you can optionally specify connection parameters. Otherwise, the Db2 credentials that are associated with your UMS user ID are used.
-
Click Next and specify the parameters for the tuning actions.
-
Click Next again to run the tuning actions. The results are disputed in the Output section of the window.
-
Click the right end of each row to view the details for each tuning job.
-
To display a list of all the tuning jobs that have been run, click from the UMS navigation menu. The Tuning jobs window opens.
-
To compare the access paths that are being used by two workloads, select two jobs that have the job type of Workload Explain, then click Compare access paths. The access paths are compared, and the results are displayed in the Workload manager dashboard. Click View details to display a graphical representation of the comparison.