Query Tuner projects for tuning single SQL statements and query workloads

The primary purpose of a Query Tuner project is to save and allow you to review the recommendations, analyses, and reports that you generate in the workflow assistant. You can start tuning without first creating a Query Tuner project. However, if you want to save your work and return to it later, you must save it in a Query Tuner project.

Objects for tuning single SQL statements include query groups, query nodes, analysis results, and reports

Query group
A query group is a folder that contains one or more query nodes.

The following image shows a single query group within a Query Tuner project.

A query group

Query node
Each query node in a Query Tuner project contains the text of an SQL statement. You create query nodes in one of these ways:
  • When you close a workflow assistant in which you captured an SQL statement, you can choose to save the SQL statement in a new query node.
  • When you close a workflow assistant after running tools and advisors on an SQL statement that you just captured, you can choose to save the SQL statement and the analysis result in a new query node.
  • When you right-click a query group in a Query Tuner project and select Tune Query.

When you double-click a query node in a Query Tuner project, the workflow assistant for query tuning opens to the Invoke section and the SQL statement appears in the Query Text field. You can run the advisors and tools, or you click the Review tab to open the Review section and view the current or saved analysis results.

The following image shows a single query node within a Query Tuner project.

A query node

When there is more than one query node in a project, you can switch between their SQL statements in the Invoke section of the workflow assistant. To switch, in the top-left corner of the Invoke section select the query node that contains the SQL statement. This corner of the Invoke section lists the query groups that are in the current project and the query nodes within those query groups.

Analysis result
Each analysis result includes the output of all of the tools and advisors that you run on an SQL statement at one time. For example, if you generate an access plan graph and run the Statistics Advisor at the same time, the analysis result contains the access plan graph and recommendations from the Statistics Advisor. Every time that you run one or more advisors or tools on a statement, an analysis result is added to the corresponding query node.

The following image shows a single analysis result within a Query Tuner project.

An analysis result

When you double-click an analysis result in a Query Tuner project, the workflow assistant for query tuning opens, so that you can review the content of that analysis result. You can also review all of the analysis results for an SQL statement for that statement over time. In the Review section, you can switch between analysis results by selecting them in the top-left corner. For example, in the next image, the Review section shows two analysis results for the current query node Query 1.

Query 1 with two analysis results
You can rename an analysis result by right-clicking it in the Project Explorer or in the workflow assistant and selecting Rename. The new name appears in both the Project Explorer and the workflow assistant.

Reports
When you generate a report, the workflow assistant saves that report within an analysis result.

The following image shows four reports that are related to an SQL statement that runs on DB2® for z/OS®.

Reports in a project

The reports that you generate are also listed in the Review section of the workflow assistant.

Reports that are listed under an analysis result in the Review section
You can rename a report by right-clicking it in the Project Explorer or in the workflow assistant and selecting Rename. The new name appears in both the Project Explorer and the workflow assistant.

Objects for tuning query workloads include workload groups, workload nodes, and reports

Workload group
A workload group is a folder that contains multiple workload objects. You can right-click a workload group and select Tune Workload to create a workload or attach an existing workload on the subsystem for the workload group.
A workload group
Workload node
Each workload node in a Query Tuner project is a resource unit that contains the information about the workload on the subsystem, including the connection alias and the workload name. The statements of a workload and the analysis results are not contained in the workload node; they are created and saved on the subsystem. When you double-click a workload node in a Query Tuner project, the workflow assistant opens to the Invoke section, so that you can run the advisors.

When there are two or more workload nodes in a project, you can switch between their query workloads in the Invoke section of the workflow assistant. To switch, in the top-left corner of the Invoke section select the workload node that represents the query workload. This corner of the Invoke section lists the workload groups that are in the current project and the workload nodes within those workload groups.

In each workload node, only a single analysis result for the corresponding query workload is available. An analysis result is represented by its corresponding workload node.

If any workload table reports are generated for a workload, the reports appear under the workload node in the Review section.

Two reports under a workload node
You can rename a workload group, workload node, or report by right-clicking it in the Project Explorer or in the workflow assistant and selecting Rename. The new name appears in both the Project Explorer and the workflow assistant.


Feedback