What's New in IBM InfoSphere Optim Query Tuner and InfoSphere Optim Query Workload Tuner Version 3.1

Product documentation


Abstract

InfoSphere Optim Query Tuner and InfoSphere Optim Query Workload Tuner, Version 3.1 includes new features and enhancements to existing ones.

Content

A new product joins the existing three products: InfoSphere Optim Query Workload Tuner for DB2 for Linux, UNIX, and Windows. There are also new features and enhancements for tuning single SQL statements and query workloads. Follow these links to learn more.


InfoSphere Optim Query Workload Tuner for DB2 for Linux, UNIX, and Windows


With this new product, you can tune query workloads that run on DB2 for Linux, UNIX, and Windows. The features of the product include:


Multiple supported sources from which to capture SQL statements


Create query workloads from SQL statements in the following locations:

  • Existing query workloads
  • Text-based files
  • IBM Optim Performance Manager
  • Package caches
  • Packages
  • SQL stored procedures
  • User-defined functions
  • Views
  • Triggers


Recommendations for tuning query workloads


Get recommendations in these categories:

Statistics

Generate RUNSTATS commands that collect the minimal, but necessary, set of statistics for the base tables, indexes, and statistical views that are in the access paths of the SQL statements in query workloads.

Choose from two different versions of the RUNSTATS commands that are recommended:

  • Merged RUNSTATS commands
    Merged RUNSTATS commands are recommended RUNSTATS commands that are merged with RUNSTATS commands that are currently used for automatic statistics collection for the selected tables.
  • Advisor-generated RUNSTATS commands
    Advisor-generated RUNSTATS commands are recommended RUNSTATS commands that are not merged with RUNSTATS commands that are currently used for automatic statistics collection for the selected tables.


Perform one of these tasks on recommended RUNSTATS commands:

  • Run the RUNSTATS commands that you selected directly from the workflow assistant, or copy and run them later.
  • Run the selected RUNSTATS commands with the SET PROFILE option specified for each. Statistics are collected, and the commands are saved into statistics profiles for the corresponding tables.
  • Run the selected RUNSTATS commands with the SET PROFILE ONLY option specified for each. No statistics are collected. The commands are saved into statistics profiles for the corresponding tables.


Statistical views

Generate and run DDL statements to create statistical views that can improve the statistical information that the DB2 optimizer uses to calculate access plans for the SQL statements in query workloads. Generate and run DDL statements to modify existing statistical views to assist the DB2 optimizer better. DDL statements to create new views and modify existing ones are accompanied by RUNSTATS commands to collect statistics for those views.

Prioritize the recommendations by referring to multiple data points about each, such as the number of tables that a statistical view is defined on, the estimated time to run all SQL statements that are affected by the view, the amount of time required to run RUNSTATS on the view, and more.

Find out whether existing statistical views are being used by the DB2 optimizer.

Perform one of these tasks:

  • Run the recommended DDL statements and RUNSTATS commands for selected views.
  • Run the recommended DDL statements and RUNSTATS commands for selected views, and save the RUNSTATS commands into statistics profiles.


Indexes

Generate DDL statements for creating or modifying indexes for the SQL statements in query workloads.

View the estimated performance improvement for the statements in a query workload for the case when all recommendations are followed. Also, view the disk space required for all recommended new indexes and the total amount of time in milliseconds to run all of the INSERT, UPDATE, and DELETE statements that are in the query workload, if all of the recommendations are followed.

Prioritize the recommendations by referring to multiple data points about each, such as the cardinality of the affected table, the number of SQL statements in the query workload that refer to the table, the estimated percentage by which the recommended index can improve the speed of execution of SQL statements against the table, and more.

Find out whether existing indexes are currently being used in access plans and whether they would be used in access plans after you created recommended indexes or modified existing ones.


Recommendations and analysis for tuning single SQL statements


Tune single SQL statements with all of the features that are available for tuning SQL statements that run on DB2 for Linux, UNIX, and Windows.


Other features


Set default options for advisors in the global preferences. You can override these defaults every time that you run the advisors.

Set custom values for special registers each time that you collect EXPLAIN information for the SQL in a query workload before running the advisors.

View statements that could not be explained and the reasons that they could not.

New features and enhancements for tuning query workloads that run on DB2 for z/OS


The following features and enhancements are new in InfoSphere Optim Query Workload Tuner for DB2 for z/OS:


Workload Access Path Advisor


Use the Workload Access Path Advisor for DB2 for z/OS systems to evaluate the access path that was chosen by the DB2 optimizer to process a query, identify suboptimal access paths, and get recommendations to resolve possible performance issues.


Capture query workloads from repositories for IBM Optim Performance Manager


The new page "Capture SQL Statements from Optim Performance Manager Repository" in the Capture section of the workflow assistant lets you connect to a repository database, so you can capture and tune groups of related SQL statements that you have been monitoring.


Improvements to advisors


The Workload Statistics Advisor now supports workloads with more than 500 tables. You can also view the Workload Statistics Advisor recommendations in a summary table that lists the table name, order of table cardinality, reference count, and weighted reference count.

For query workloads that run on DB2 Version 10 for z/OS, save settings from recommended RUNSTATS commands into RUNSTATS profiles that are used for automatic statistics collection.

The Workload Index Advisor now creates indexes under the same schema as the tables referenced by SQL statements in the workload.


Comparing access plans and costs in packages or query workloads


Compare the structures and costs of access plans for SQL statements in packages that were rebound due to SQL statement changes, environment changes, release migration, or running the RUNSTATS command.

Compare a tuned query workload to its pre-tuned version to validate that the performance of the SQL statements in the tuned version is optimized.

If there are changes, view a categorized list of the changes.

Filter the results of comparisons so that you see only the changes that are important in your environment.

Generate reports of the comparisons.

Modify changed access plans with these two options:

  • Tune statements with changed access plans in a query workload.
  • Direct the DB2 optimizer into specific access plans by creating plan hints.


Restore previous access plans if you saved the original access plans with plan management policies.


Workload summary reports


You can review, email, or print a workload summary report including states of the statistics (missing, obsolete, conflicting), recommended RUNSTATS commands, query and access path warnings, and recommended indexes. To perform in-depth analysis of problematic tables, indexes, or statements, you can use the InfoSphere Optim Query Tuner Client to examine the details interactively, tune individual statements that are in a query workload, or re-run advisors after applying the recommendations.

New features and enhancements for tuning single SQL statements that run on DB2 for Linux, UNIX, and Windows


The following features are new in both InfoSphere Optim Query Tuner and InfoSphere Optim Query Workload Tuner for DB2 for Linux, UNIX, and Windows.



Capture SQL statements from repositories for IBM Optim Performance Manager


The new page "Capture SQL Statements from Optim Performance Manager Repository" in the Capture section of the workflow assistant lets you connect to a repository database, so you can capture and tune SQL statements that you have been monitoring.


Test indexes virtually


You can use the Test Candidate Indexes support to:

  • Create virtual indexes, and edit and test virtual indexes and indexes recommended by the Index Advisor
  • Customize index statistics
  • See which indexes were used by the DB2 optimizer when it calculates an access plan
  • See the corresponding estimates of disk space and performance gain
  • Generate graphs of the virtual access plans that the DB2 optimizer calculates during tests
  • Compare graphs of virtual access plans to graphs of actual access plans


Create, validate, and deploy optimization profiles


You can use the optimization profile support for DB2 for Linux, UNIX, and Windows systems to:

  • Visually define an optimization profile for a single SQL statement
  • Validate the optimization profile and generate a validation report
  • Deploy the optimization profile on the specified database server or generate a script to deploy the operational profile on other data servers

Enhancements for tuning single SQL statements that run on DB2 for z/OS


The following features are new in both InfoSphere Optim Query Tuner and InfoSphere Optim Query Workload Tuner for DB2 for z/OS.

  • Capture SQL statements from repositories for Optim Performance Manager.
  • Review more detailed information about RUNSTATS commands that are recommended by the Statistics Advisor.
  • For SQL statements that run on DB2 Version 10 for z/OS, save settings from recommended RUNSTATS commands into RUNSTATS profiles that are used for automatic statistics collection.

Other improvements

  • The user interface for configuring DB2 for z/OS subsystems for tuning SQL statements and query workloads now produces results that by default are identical with the results of configuring by running JCL.
  • By default, the preference Always refresh catalog information from the data server is selected. Therefore, the local cache of catalog information for data servers that you are connected to is automatically refreshed whenever you choose to explain a statement or query workload when you run advisors or analysis tools. The advisors and analysis tools use a local cache of catalog information so that they do not have to query the catalog on a data server as they perform their analyses. Ensuring that the local cache is refreshed prevents problems such as the Statistics Advisor recommending RUNSTATS commands a second time even after you ran those RUNSTATS commands when they were first recommended. This preference is located on the EXPLAIN Options page under Data Management > Query Tuner in the Preferences window. To open this window, select Help in the menu bar and then select Preferences.
  • Set default values for CURRENT SQLID and CURRENT SCHEMA for individual DB2 for z/OS subsystems that you connect to. These default values will be used, unless you override them, every time that you choose to explain an SQL statement when running the advisors and analysis tools. You can set these default values on the EXPLAIN Options page under Data Management > Query Tuner in the Preferences window. To open this window, select Help in the menu bar and then select Preferences.

New information center and information roadmap



Cross reference information
Segment Product Component Platform Version Edition
Information Management InfoSphere Optim Query Workload Tuner for DB2 for Linux- UNIX and Windows AIX, HP-UX, Linux, Solaris, Windows 3.1

Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

InfoSphere Optim Query Tuner for DB2 for Linux, UNIX and Windows

Software version:

3.1

Operating system(s):

Linux, Windows

Reference #:

7021897

Modified date:

2014-01-06

Translate my page

Machine Translation

Content navigation