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

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.

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.

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.

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.

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

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.

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.