Tuning single SQL statements with the no-charge query-tuning features in the workflow assistant

You can use the no-charge query-tuning features improve the performance of SQL statements in applications that access DB2® for Linux, UNIX, and Windows and DB2 for z/OS®. You can format SQL statements so that they are easier to read, generate visual representations of access plans, and get recommendations for collecting statistics on the objects that a statement references. You can also generate a report that summarizes information about the access plan and includes the recommendations.

Before you begin

About this task

The no-charge features are so-called because they do not require an active license for IBM® InfoSphere® Optim™ Query Workload Tuner, Version 3.1 or 3.1.1 on the DB2 databases and subsystems that you tune SQL statements for. These features are a subset of the features that are available in InfoSphere Optim Query Workload Tuner. For a comparison of the no-charge features and the features that are in that product, see Features by product.

Procedure

In most cases, to tune an SQL statement with the no-charge query-tuning features in the Query Tuner Workflow Assistant, you can follow these steps.

  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. Format the SQL statement to make it easier to read and comprehend. Seeing a formatted version of the SQL statement can lead to insights that are not as apparent with the unformatted version. See Formatting SQL statements.
  3. Analyze the access plan graph for the SQL statement to find out how DB2 accesses data in the objects that the statement references. See Generating visual representations of access plans.
  4. Run the Statistics Advisor and follow its recommendations to ensure that the DB2 optimizer has the most current and most important statistics. See Collecting statistics.

Feedback