In October 2011, we released a new product: InfoSphere Optim Query Workload Tuner for DB2 for Linux, UNIX, and Windows. As with InfoSphere Optim Query Workload Tuner for DB2 for z/OS, you can capture and save a set of queries into a workload, and then launch advisors to get expert advice for tuning the workload as a whole. The advisors are the Workload Statistics Advisor, which can recommend RUNSTATS control statements for collecting statistics on database objects that are used by the workload, as well as scripts for creating statistical views and modifying existing ones; and the Workload Index Advisor, which can recommend new indexes and changes to existing ones.
We released this product as version 3.1, to coincide with the 3.1 release of the DB2 for z/OS product. Earlier this year, we delivered InfoSphere Optim Query Workload Tuner for DB2 for Linux, UNIX, and Windows, Version 3.1.1, which contains a number of enhancements that make use of new features in DB2 V10 for Linux, UNIX, and Windows.
- Access plan graphs can display three new operators that are newly introduced by DB2 for advanced query processing:
- REBAL, which indicates the rebalancing of data processing among SMP subagents
- MGSTRM, which indicates multi-distinct processing
- ZZJOIN, which indicates zigzag joins
- The editor for creating and deploying optimization profiles lets you make use of DB2 registry variables and options for federated servers.
- The Workload Index Advisor can recommend indexes that take into account zigzag joins and use jumpscans for data access.
- The Workload Statistical Advisor can fully exploit the new capabilities for statistical views. It can recommend fewer statistical views because these views can now join more tables. When you have fewer statistical views to maintain, fewer system resources are required to collect statistics on them. Furthermore, query optimization can be done faster.
- We introduce the Workload Design Advisor, which can giver ecommendations for creating MQTs, creating indexes on MQTs, converting tables to multidimensional clustering tables, and distributing data across partitions.
Apart from exploiting new features in DB2 V10 for Linux, UNIX, and Windows, we further enhanced InfoSphere Optim Query Workload Tuner by
- Including the full set of tuning features in the Data Studio full client and administration client. When you connect to a database that has a license for InfoSphere Optim Query Workload Tuner on it, you can use this full set of features. When you connect to a database where no such license is active, you can tune by using a small subset of tuning features.
- If you use Optim Performance Manager as your performance monitoring tool, you can select one or more queries for tuning. In V3.1.1, we offer a better integration between the two products.