What's New in InfoSphere Optim Query Workload Tuner, Version 3.2

Product documentation


Abstract

InfoSphere Optim Query Workload Tuner, Version 3.2 contains enhancements and new features.

Content


Enhancements to the Workload Index Advisor


Test candidate indexes for entire query workloads

You can now run tests to determine whether creating recommended indexes or indexes that you propose will improve query workload performance. These tests are supported for query workloads that run on DB2 for Linux, UNIX, and Windows or DB2 for z/OS. Tests analyze the candidate indexes together with existing indexes, create candidate indexes virtually, and then estimate the performance of the query workload. Before you run tests, you can even drop existing indexes virtually.


Virtually drop existing indexes before running the Workload Index Advisor

For query workloads that run on DB2 for z/OS, you can virtually drop all existing indexes before you run the Workload Index Advisor, enabling the advisor to generate recommendations that do not take existing indexes into account.


Other enhancements

For query workloads that run on DB2 for Linux, UNIX, and Windows or DB2 for z/OS, the Workload Index Advisor can now recommend converting existing non-unique indexes to unique indexes if those indexes include a unique key. The LAST_USED time for existing indexes is displayed in the table of existing indexes where the Workload Index Advisor displays its recommendations. For DB2 for z/OS, the advisor can recommend modifications to existing indexes or and can recommend dropping existing indexes that are already covered by other existing indexes. The performance of the Workload Index Advisor has been improved.


 

New ways to compare access plans

With the Workload Access Plan Comparison feature, which is new for DB2 for Linux, UNIX, and Windows, you can fix or compare multiple access plans in a number of different scenarios for DB2 for Linux, UNIX, and Windows and DB2 for z/OS.

For example, you can now compare the access plans of two query workloads that are created before and after DB2 release migration to know whether there are access plan changes and statement runtime metrics differences.

You can compare the access plans of two query workloads on different systems, such as a testing system and a production system, to determine whether there changes in query workload performance.

You can also compare the access plans from two EXPLAIN snapshots within the same query workload to validate workload tuning advisor recommendations.

Additionally, you can see the SQL statement runtime metrics in comparison results. The results can be filtered by access plan changes, access plan characteristics, estimated cost and runtime metrics. You can use the filtered comparison result to generate HTML reports for printing and sharing.

If you find problematic access plan changes or performance regression, you can even generate optimization profiles for DB2 for Linux, UNIX, and Windows databases and optimization hints for DB2 for z/OS subsystems to lock down the access plans of multiple SQL statements at one time.


 

New tool to compare SQL statement text

You can now compare the text of two versions of an SQL statement. Run the query formatter tool on the two versions, compare them, and view the highlighted differences.


 

Format SQL statement text at the click of a button

If you want to format an SQL statement to make it easier to read and understand, and do not need to see statistical annotations of the parts of the statement, you can now format SQL statements by clicking the new Format SQL button in the workflow assistant. In the window that opens, you can use the find/replace function to edit the text and the click the Copy button to copy the entire statement.


 

Capture query workloads directly from DB2 Query Monitor CAE Web Client

You can now capture a query workload and transfer it to InfoSphere Optim Query Workload Tuner with no size limitations, with complete metadata, and with EXPLAIN information. After you examine the monitored SQL statements with the DB2 Query Monitor CAE Web Client and store them in generic staging tables, InfoSphere Optim Workload Tuner can retrieve problematic statements programmatically for query workload tuning.


 

Enhancements to tuning SQL statements that run on DB2 for Linux, UNIX, and Windows


New capture sources for DB2 for Linux, UNIX, and Windows

You can capture SQL statements from activity event monitors and EXPLAIN tables.


Save EXPLAIN information when creating query workloads

After you capture SQL statements and click the Save All to Workload button, you can specify to save the latest EXPLAIN information for the statements.

This feature is especially helpful when section explain information is available. A section explain records the actual access plan that was used by the DB2 optimizer (if the statement was run) or will be used (if the statement is in a package). You can use the section explain information as a performance baseline against which you can measure your progress in tuning a query workload. Rather than having to re-explain a query workload to collect existing section EXPLAIN information and query plan for each SQL statement, all the information can now be collected when saving the statements to a query workload in order to establish a performance baseline.

After you tune selected SQL statements within the query workload, you can create an EXPLAIN snapshot of the current state of the database, and then compare it with the performance baseline to determine whether performance is improved. This feature applies to SQL statements captured from a EXPLAIN tables, activity event monitors, packages, stored procedures, or the package cache.


Workload summary reports for DB2 for Linux, UNIX, and Windows

You can now generate reports with detailed tuning recommendations and share them across different organizations to make it easier to collaborate. The reports provide recommendations for:

  • Improving statistics, statistical views, and indexes
  • Creating materialized query tables and indexes on materialized query tables
  • Converting to multi-dimensional clustering
  • Distributing data across partitions

 

Create, validate, and deploy new types of optimization hints for DB2 for z/OS

You can now create a statement-level access path hint or an optimization parameter hint to help the DB2 optimizer determine the optimal access path for matching SQL statements on an instance of a DB2 for z/OS Version 10 subsystem in New Function Mode.


 

Usability enhancements

The Index Advisor and the Workload Index Advisor now have a common look and feel, and many of their preferences are consolidated.

Many of the preferences for the Statistics Advisor and Workload Statistics Advisor are consolidated.

If you run the workload advisors and then run the single-query advisors on a single SQL statement within the same query workload, you can now return to the workload advisor recommendations without having to run the workload advisors again. Just click the Manage tab to go to the Manage section of the workflow assistant. In the list of query workloads, select the query workload that you ran previously and click the Review Results button.



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

Rate this page:

(0 users)Average rating

Document information


More support for:

InfoSphere Optim Query Workload Tuner for DB2 for z/OS

Software version:

3.2

Operating system(s):

z/OS

Reference #:

7036269

Modified date:

2012-12-07

Translate my page

Machine Translation

Content navigation