Formatting SQL statements

The Query Tuner Workflow Assistant can format an SQL statement so that you can better understand the structure of that statement.

Before you begin

About this task

In many cases, when you capture a query for tuning, the text of the query is not well formatted. The workflow assistant for query tuning can format the query so that each table reference, each column reference under in the SELECT clause, and each predicate, is shown on its own line.

You can expand and collapse sections of complex queries, such as query blocks and subqueries, to see an overview of the query and drill into parts of the query in more detail. When you click any line in the formatted query, other lines of the query that contain column or table references from the same table are also highlighted. You can also customize the formatting by ordering the predicates according to various criteria such as local predicates or join predicates, table references, and highest filter factor.

Procedure

To format an SQL statement:

  1. Capture the SQL statement that you want to tune. See Locations from which you can capture an SQL statement for single-query tuning.
  2. Select one of the captured statements and click the Invoke Advisors and Tools button. The Run Single-Query Advisors and Analysis Tools page in the Invoke section opens.
  3. If you need to set the schema to use for unqualified objects that the SQL statement references, or (if the statement runs on DB2 for z/OS®) change the SQL ID, specify these values in the Schema and SQLID fields above the SQL statement on this page. For information about setting global defaults for schemas and SQL IDs, see Setting the schema to use for unqualified objects, the SQL ID, or both.
  4. Click the Select What to Run button.
  5. In the Select Activities window, select the Format and annotate SQL statement option. Then, click OK.

Results

The Query Tuner Workflow Assistant opens to the Review section. If the Review Formatted Query page is not opened because you selected additional options in the Select Activities window, click Open Formatted Query on the left side of this section.

In the formatted query, each table reference, each column reference under the SELECT clause, and each predicate, is shown on its own line. You can expand and collapse sections of complex queries, such as query blocks and subqueries, to see an overview of the query and drill into parts of the query in more detail. When you click any line in the formatted query, other lines of the query that contain column or table references from the same table are also highlighted. You can also customize the formatting by ordering the predicates according to various criteria such as local predicates or join predicates, table references, and highest filter factor.


Feedback