InfoSphere Optim Query Workload Tuner, Version 3.2 contains enhancements and new features.
New ways to compare access plans
New tool to compare SQL statement text
Format SQL statement text at the click of a button
Capture query workloads directly from DB2 Query Monitor CAE Web Client
Enhancements to tuning SQL statements that run on DB2 for Linux, UNIX, and Windows
Create, validate, and deploy new types of optimization hints for DB2 for z/OS
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.
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.
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.
|Information Management||InfoSphere Optim Query Workload Tuner for DB2 for Linux- UNIX and Windows||Linux, Windows||3.2|