What's New in IBM Optim Query Tuner and Optim Query Workload Tuner

Product documentation


Abstract

See the following documents for the new features and enhancements in IBM Optim Query Tuner and Optim Query Workload Tuner.

Content


Optim Query Tuner and Optim Query Workload Tuner, Version 2.2.1.1

Optim Query Tuner and Optim Query Workload Tuner, Version 2.2.1 provide the following enhancements.



Access Plan Explorer

This new tool displays access plans as tables or hierarchical trees.

For access plans on DB2 for Linux, UNIX, and Windows: Sort and arrange the columns in the table view to find the most costly operations in the access plan. Use the Highlight Inflow and Highlight Outflow buttons to see which operators feed information to a selected operator and which operator is sent information by the selected operator. You can also view the flows of information by using the tree view. Select an operator and view the information about it in the expandable tables in the lower portion of the Access Plan Explorer.

For access plans on DB2 for z/OS: Select a query block or mini-plan and view the information about it in the expandable tables in the lower portion of the Access Plan Explorer. For example, in looking at a mini-plan, you might find that the corresponding DB2 for z/OS table is accessed by a table-space scan. In the details for the mini-plan, you might find that the size of the table is large and the value of the filter factor for the predicate is also large. In this case, a large number of rows match the predicate and increase the cost of the mini-plan.

You can select a correlated subquery and click the Highlight Relationship of Query Blocks button to discover which mini-plan in the parent subquery the correlated subquery is bound to.



Additional sources of SQL statements

If you are using Optim Query Tuner for DB2 for z/OS or Optim Query Tuner for DB2 for z/OS, you can capture SQL statements from two new sources.


Capture from DB2 Query Monitor for z/OS

Tune SQL statements that are offloaded by DB2 Query Monitor for z/OS. Capture statements according to their runtime metrics or according to their runtime metrics and the database objects that the statements reference. Tune statements individually or in query workloads.


Capture from user-defined SQL repositories

Offload SQL statements, runtime metrics, and other information from query monitoring applications into tables in DB2 for z/OS. Capture statements according to their runtime metrics or according to their runtime metrics and the database objects that the statements reference. Tune statements individually or in query workloads.



Improvements to usability
  • Regularly check that connections to DB2 for z/OS are open
    For Optim Query Tuner for DB2 for z/OS and Optim Query Workload Tuner for DB2 for z/OS: In the global preferences, you can specify that you want the Optim Query Tuner client to check at regular intervals that the current connection to DB2 for z/OS is open.

  • Create global temporary tables directly from the workflow assistant
    If a statement that you want to tune accesses global temporary tables, you must run the statements that define the temporary tables before you run analysis tools on your statement. Run the DDL directly from the workflow assistant.

  • Refer to a tree view of the contents of a Query Tuner project within the workflow assistant
    A tree view now appears in the upper-left corner of the Invoke and Review sections of the workflow assistant. This tree view is synchronized with the Project Explorer and shows the following objects:
    • In the Invoke section: all query nodes and workload nodes that share the project that is currently open in the workflow assistant
    • In the Review section:
      • For single SQL statements: all analysis results, reports, and results from testing candidate indexes for the current SQL statement
      • For query workloads: the most recent analysis result and all reports for the current query workload
    You can switch between statements and between workloads by using the tree in the Invoke section.
  • View estimated Class 2 CPU time savings for recommendations from the Workload Index Advisor
    Above the table of recommendations from the Workload Index Advisor, you can now see these metrics:
    • The actual Class 2 CPU time for running the statements in the current query workload, if the SQL statements in the query workload are captured together with runtime metrics
    • The estimated Class 2 CPU time for running these statments should the recommendations of the Workload Index Advisor be followed
    • A percentage that shows the estimated savings in Class 2 CPU time if the recommendations of the Workload Index Advisor are followed

Version 2.2.1

Optim Query Tuner and Optim Query Workload Tuner, Version 2.2.1

Optim Query Tuner and Optim Query Workload Tuner, Version 2.2.1 provide key enhancements in the following categories:
See PMRs fixed in this release for more information about what has been fixed in Version 2.2.1.


Workflow assistant for query tuning

The new workflow assistant for query tuning guides you through capturing single SQL statements, creating query workloads, managing tasks for query workloads, performing query analyses, and tuning. The workflow assistant consists of sections that represent general steps in the process of tuning.

  1. Status, for ensuring that your database or subsystem is configured correctly for query tuning
  2. Capture, for capturing SQL statements from the supported sources
  3. Manage, for creating and managing query workloads
  4. Invoke, for running analysis tools, reports, and advisors on an SQL statement or a query workload
  5. Review, for reviewing the results of the analyses, reading the reports, and reviewing and implementing the recommendations of advisors
  6. Compare, for comparing the access plan graphs of SQL statements as you progressively tune them


Workload Data Mart Advisor

The new Workload Data Mart Advisor recommends data marts that you can create in IBM Smart Analytics Optimizer to execute with fast and predictable response times queries that are typically found in business intelligence and data warehousing applications. You can export recommended data marts as XML files that you can import into IBM Smart Analytics Optimizer Studio to edit, validate, and deploy.

For documentation on how to use the Workload Data Mart Advisor, follow this link: http://www.ibm.com/support/docview.wss?uid=swg27019567

For information about the IBM Smart Analytics Optimizer, see the product page at http://www.ibm.com/software/data/infosphere/smart-analytics-optimizer-z/.

For a detailed description of the architecture of the IBM Smart Analytics Optimizer, see the RedGuide "Using IBM System z As the Foundation for Your Information Management Architecture" at http://www.redbooks.ibm.com/redpapers/pdfs/redp4606.pdf.



Usability

  • Ease of installation and configuration
    • You can choose to start either an administrative installation or a non-administrative installation in the launchpad to install the Optim Query Tuner client.
    • For Optim Query Tuner for Linux, UNIX, and Windows, you can run an installation program on the data server to launch a wizard that guides you through activating the product license and enabling the Index Advisor stored procedure.
    • You can easily configure your data server for query and query-workload tuning from the Optim Query Tuner client. If you want to configure your DB2 for z/OS subsystem from the server, you need to modify and run only one JCL sample job.
  • Enhancements for Optim Query Tuner for DB2 for Linux, UNIX, and Windows, Optim Query Tuner for DB2 for z/OS, and Optim Query Workload Tuner for DB2 for z/OS
    The usability of Statistics Advisor and Workload Statistics Advisor is improved by the following new features:
    • Run either of two recommended RUNSTATS commands. The Complete version of the RUNSTATS command collects a full set of statistics for the objects used in access plans for the statements that you are tuning. The Repair version fixes just the problems that the advisors identified.
    • Prevent these advisors from generating RUNSTATS commands to fix statistics that you do not yet want to fix. You can specify to tolerate problems in statistics that were collected within a certain number of days.
  • Enhancements for Optim Query Tuner for DB2 for z/OS and Optim Query Workload Tuner for DB2 for z/OS
    • From the client for Optim Query Tuner for DB2 for z/OS or Optim Query Workload Tuner for DB2 for z/OS, you can migrate existing EXPLAIN and Query Tuner tables, packages, and stored procedures during the configuration process.
    • You can find out whether the access plan uses a plan hint from the description of the QUERY node in an access plan graph.
    • You can choose to exclude dynamic SQL statements issued by the capture process when you capture SQL statements or query workloads from statement caches.
  • Enhancements for Optim Query Workload Tuner for DB2 for z/OS
    • The usability of Workload Index Advisor is improved by the following new features:
      • Recommendations for modifying existing indexes to improve query performance are provided.
      • The Workload Index Advisor can show how an index would reduce the performance of INSERT, UPDATE, and DELETE operations.
      • The Workload Index Advisor can show which candidate indexes do not meet the criteria that you specify for recommended indexes.
    • You can run stored procedures on a subsystem to perform the following workload-tuning tasks:
      • Capture workloads from statement caches
      • Gather EXPLAIN information for workloads
      • Consolidate literal values in EXPLAIN information
      • Consolidate EXPLAIN information for a workload
    • STMT_TOKEN values for the SQL statements are displayed in query workloads.
    • The definition of nested views or MQTs in the service workload can be retrieved back to support workload environment analysis.


Support for DB2 Version 10 for z/OS

This release introduces support for DB2 Version 10 for z/OS. You can analyze and tune single SQL statements or query workloads that run on a DB2 Version 10 for z/OS subsystem.



Support for DB2 for Linux, UNIX, and Windows with Oracle-compatible mode enabled

This release introduces support for DB2 for Linux, UNIX, and Windows with Oracle-compatible mode enabled. You can analyze and tune DB2 SQL statements that run on a DB2 for Linux, UNIX, and Windows data server on which Oracle-compatible mode is enabled.



Performance

  • The processes of defining and managing workloads are faster.
  • The workload tuning advisors more quickly make their recommendations for large workloads.
  • The process of canceling the workload tuning activities is faster.


Integration of Optim Query Tuner for DB2 for z/OS and Optim Query Workload Tuner for DB2 for z/OS with IBM Optim Performance Manager, Version 4.1

You can use Optim Query Tuner for DB2 for z/OS or Optim Query Workload Tuner for DB2 for z/OS to tune single SQL statements or query workloads that you are monitoring with IBM Optim Performance Manager, Version 4.1. Before tuning such an SQL statement or workload, ensure that the following prerequisites are met:

  1. The browser in which you are using Optim Performance Manager is running on the same system as the Optim Query Tuner client.
  2. The DB2 for z/OS subsystem that the SQL statement or the workload runs against is configured for use with Optim Query Tuner for DB2 for z/OS or Optim Query Workload Tuner for DB2 for z/OS.
  3. The Optim Query Tuner client is running.
  4. The data bridge server is running. To start this server, click this button Data Bridge Server button in the toolbar of the Optim Query Tuner client.


Integration with IBM Tivoli OMEGAMON XE for DB2 Performance Expert on z/OS, Version 5.0

You can use Optim Query Tuner for DB2 for z/OS or Optim Query Workload Tuner for DB2 for z/OS to tune single SQL statements or query workloads that you are monitoring with IBM Tivoli OMEGAMON XE for DB2 Performance Expert on z/OS, Version 5.0. Before tuning such an SQL statement or workload, ensure that the following prerequisites are met:

  1. The client for OMEGAMON XE for DB2 Performance Expert on z/OS is running on the same system as the Optim Query Tuner client.
  2. The DB2 for z/OS subsystem that the SQL statement or the workload runs against is configured for use with Optim Query Tuner for DB2 for z/OS or Optim Query Workload Tuner for DB2 for z/OS.
  3. The Optim Query Tuner client is running.
  4. The data bridge server is running. To start this server, click this button Data Bridge Server button in the toolbar of the Optim Query Tuner client.


Help information

  • The information on the Tune page of the Task Launcher helps you quickly start the following key tuning tasks:
    • Connect to a database
    • Configure DB2 for query tuning
    • Tune SQL statements
  • You can access context-sensitive help by clicking the help icon or typing F1.



SQL and XQuery editor
  • New option to import or export SQL statements between the SQL and XQuery editor and the file system.
  • New syntax validation/parsing support in the editor for the following data sources:
    • DB2 Version 9.8 for Linux, UNIX, and Windows
    • DB2 Version 9.7 for Linux,
    • DB2 for z/OS Version 10



Database connections
  • New "Properties" context menu from database connections opens the Properties view.
  • Improved usability for the New Database Connection wizard: Better error validation for wizard fields and an improved error message display format for database connection errors including links to more information about database error codes.



Web-based help and new online product information center

In this release, there are new options for accessing the contents of the information center ( Help > Help Contents). These options are presented on a new panel in the Installation Manager Install wizard, and they can be changed at any time on the Window > Preferences > Help > Contents preferences page.
  • The help content can be accessed on the Web so that you can have the smallest installation footprint and the most up-to-date information.
  • The help content can be downloaded and accessed on your computer so that you can work disconnected from the Internet with periodic updates.
  • The help content can be deployed on an intranet server so that you can work behind a firewall with administrative updates.

As part of this enhancement, you can also choose to view the product-specific information center online, in an external browser. The new information center is located here: http://publib.boulder.ibm.com/infocenter/idmhelp/tune-v2r2/index.jsp

For more information about Web-based help, see the following topic in the information center:

"Configuring Web-based help": http://publib.boulder.ibm.com/infocenter/idmhelp/tune-v2r2/topic/com.ibm.datatools.oqt.install.doc/topics/t_postinst_webhelp_config.html

Version 2.2.0.2

Optim Query Tuner and Optim Query Workload Tuner, Version 2.2.0.2

Optim Query Tuner and Optim Query Workload Tuner, Version 2.2.0.2 provide key enhancements in the following categories:
See PMRs fixed in this release for more information about what has been fixed in Version 2.2.0.2.

Usability enhancements

The usability is improved by providing the following new features:

  • Program names are displayed in the workload statement list.
  • Semantical task names are displayed for the scheduled tuning tasks in the task list.
  • Users are able to delete workloads from a project in the Project Explorer.
  • Users are able to change the default SQL ID and save the secondary authorization ID in the Database Configuration Status window.
  • The Access Plan Graph section contains a page that displays records in the PLAN_TABLE table for the current query.
  • An option to disable dynamic statement cache traces after capturing workloads is provided.
  • Users are able to filter statements from the statement cache by STMT_TOKEN when creating workloads.
  • Users are able to filter statements by table creator when capturing new statements into an existing workload.
  • Warning messages are displayed in the following scenarios to help users understand what is happening, or what to do:
    • Removing workloads from a project in the Project Explorer.
    • Comparing access plan graphs without binding the Access Plan Comparison package.

Performance enhancements

  • The process of capturing large numbers of queries from catalog plans and packages and dynamic statement caches, and the process for gathering EXPLAIN information for large numbers or queries, are faster.
  • The workload tuning advisors more quickly make their recommendations for large workloads.


Integration with IBM Optim Performance Manager, Version 4.1

You can use Optim Query Tuner to tune queries that you are monitoring with IBM Optim Performance Manager. Before tuning such a query, ensure that the following prerequisites are met:
  1. The browser in which you are using Optim Performance Manager is running on the same system as the client for Optim Query Tuner.
  2. The data server that the query runs against is configured for use with Optim Query Tuner.
  3. The client for Optim Query Tuner is running.
  4. The data bridge server is running. To start this server, click this button in the toolbar for the client for Optim Query Tuner: Data Bridge Server button

Microsoft Windows 7 support

This release introduces support for the Microsoft Windows 7 operating system. For detailed system requirements information see the system requirements.



Database connections

Database connections that become invalid are now detected and indicated with an icon change in the Data Source Explorer. You can set preferences for this feature in the Preferences window.



SQL and XQuery editor

The SQL and XQuery editor has been enhanced so that you can view and change the database connection and parser information that is associated with the current editing session.


Version 2.2.0.1

Optim Query Tuner and Optim Query Workload Tuner, Version 2.2.0.1

Optim Query Tuner and Optim Query Workload Tuner, Version 2.2.0.1 provide key enhancements in the following categories:

Deprecated feature: The use of profiles to monitor query performance is deprecated for DB2 for z/OS database servers, and is not recommended.

See PMRs fixed in this release for more information about what has been fixed in Version 2.2.0.1.


Advanced query tuning features for DB2® for z/OS® and DB2 for Linux, UNIX®, and Windows® data servers(*)

  • Clone projects and switch project connections to validate tuning results against a production system.
  • Search and link to on-line help topics in the Optim Query Tuner client.
  • Generate the new Optim Query Tuner Summary report that summarizes recommendations from all advising and tuning tools from Optim Query Tuner or IBM Data Studio.
  • Find Query Advisor and Access Path Advisor recommendations in a simplified interface, using simplified explanations with more examples.
  • Receive new optimization recommendations for Query Advisor and Access Path Advisor.


Advanced query tuning features for DB2 for z/OS data servers(*)

  • Compare the access plan cost between sets of collections and packages to identify potential query performance problems when migrating or rebinding collections.
  • Import XML workload files from Query Monitor for query tuning. The tuning tasks that you can perform with the imported file depend on whether you have activated a database server license.
  • Reduce response time through enhanced performance of Optim Query Tuner on large scale systems.




Advanced query tuning features for DB2 for Linux, UNIX®, and Windows® data servers(*)

  • Collect and compare actual cardinality (returned rows) values with estimated cardinality values for queries in the access plan graph. This feature requires DB2 for Linux, UNIX, and Windows Version 9.7 FP1 and a license for IBM Workload Monitor (WLM).

Version 2.2


Optim Query Tuner and Optim Query Workload Tuner, Version 2.2

Optim Query Tuner and Optim Query Workload Tuner, Version 2.2 provide key enhancements in the following categories:
See PMRs fixed in this release for more information about what has been fixed in Version 2.2.



Integration with the Eclipse development environment
  • Full integration of the features of the query tuner client with the Eclipse-based Data perspective development environment.
  • The ability to extend an existing Eclipse development environment with the features of query tuner client, and integration with other Eclipse-based IBM® Optim products.



Basic tuning facilities
  • Basic query tuning facilities for queries that run on DB2® for z/OS® and now DB2 for Linux®, UNIX®, and Windows® data servers, including the following activities:
    • Capturing queries from data server sources.
    • Viewing formatted query text.
    • Analyzing access plan graphs.
    • Generating query reports that contain information that is related to query performance about statistics, tables, and predicates.
    • Capturing the environment of the query in a form that you can send to clone the environment elsewhere, or send to IBM software support.
    • Collecting and repairing missing, incomplete, or obsolete statistics with the statistics advisor.



Advanced query tuning features for DB2 for z/OS and DB2 for Linux, UNIX and Windows data servers
  • License-based data server activation that enables advanced query tuning features on DB2 for z/OS and DB2 for Linux, UNIX, and Windows data servers, including the following activities:
    • Annotating queries to facilitate analysis.
    • Creating and analyzing index design with the index advisor.
    • Rewriting queries based on query advisor recommendations.
    • Tuning your query based on access path advisor recommendations.
    • Create, validate, and deploy plan hints to fine-tune access paths (DB2 for z/OS only).
    • Define and evaluate your own virtual indexes before deploying them to the data server (DB2 for z/OS only).


Advanced query tuning features for workloads on DB2 for z/OS data servers
  • License-based activation of workload tuning functions on for query workloads that run on DB2 for z/OS data servers, including the following activities:
    • Monitoring the performance of the normal query workload of an application or data server.
    • Monitoring the performance of queries to capture poorly performing queries.
    • Capturing query workloads from various data server sources.
    • Creating workload reports about the tables that are referenced in the statements of an entire workload.
    • Capturing the environment of a query workload in a form that you can use to clone the environment elsewhere, or send to IBM software support.
    • Tuning query workloads and getting statistics, index design, and query-writing advice.


(*) - Requires activation of license on the database server

Cross Reference information
Segment Product Component Platform Version Edition
Information Management Optim Query Tuner for DB2 for z/OS z/OS 2-2-0, 2.2.0.1, 2.2.0.2, 2.2.1, 2.2.1.1
Information Management Optim Query Workload Tuner for DB2 for z/OS z/OS 2.2.0, 2.2.0.1, 2.2.0.2, 2.2.1, 2.2.1.1

Document information


More support for:

Optim Query Tuner for DB2 for Linux- UNIX and Windows

Software version:

2.2.0, 2.2.0.1, 2.2.0.2, 2.2.1, 2.2.1.1

Operating system(s):

Linux, Windows

Reference #:

7017029

Modified date:

2011-04-15

Translate my page

Content navigation