Features for tuning single SQL statements
You can use these features in the IBM Data Studio client, Version 4.1.1 only if the full set of tuning features for InfoSphere Optim Query Workload Tuner is activated on the DB2 system where your SQL statements run.| Feature | InfoSphere Optim Query Workload Tuner DB2 for Linux, UNIX, and Windows | InfoSphere Optim Query Workload Tuner DB2 for z/OS® |
|---|---|---|
| Annotate formatted SQL statements with statistics | ✓ | ✓ |
| Create, validate, and deploy optimization profiles to modify access plans directly | ✓ | |
| Drill down into the details of access plans with the Access Plan Explorer | ✓ | ✓ |
| Generate recommendations for creating indexes | ✓ | ✓ |
| Generate recommendations for improving access paths | ✓ | ✓ |
| Generate recommendations for rewriting SQL statements | ✓ | ✓ |
| Virtually test recommended indexes and indexes that you propose | ✓ | ✓ |
| Create, validate, and deploy plan hints to modify access plans directly | ✓ | |
| Override the selectivity (filter factors) of predicates | ✓ (DB2 11 for z/OS new-function mode only) |
Features for tuning query workloads
You can use these features in the IBM Data Studio client, Version 4.1.1 only if the full set of tuning features for InfoSphere Optim Query Workload Tuner is activated on the DB2 system where your SQL statements run.| Feature | InfoSphere Optim Query Workload Tuner DB2 for Linux, UNIX, and Windows | InfoSphere Optim Query Workload Tuner DB2 for z/OS |
|---|---|---|
| Tune SQL statements that are captured from repositories in InfoSphere Optim Performance Manager | ✓ | ✓ |
| Generate recommendations and DDL for creating and modifying indexes | ✓ | ✓ |
| Generate RUNSTATS commands for fixing problems with statistics | ✓ | ✓ |
| Generate recommendations and DDL for creating or modifying statistical views | ✓ | |
| Generate recommendations and DDL for MQTs, multi-dimensional clustering, and redistributing data across partitions | ✓ | |
| Generate recommendations and scripts for BLU acceleration | ✓ | |
| Compare, annotate, and generate reports on the access plans and estimated cost changes for SQL statements in different packages or workloads | ✓ | |
| Create reports on the catalog information for tables that SQL statements reference | ✓ | |
| Generate recommendations for improving access paths | ✓ | |
| Generate recommendations for rewriting SQL statements | ✓ | |
| Capture the environments of query workloads; send representations of the environments to IBM Support or re-create the environments on another subsystem | ✓ | |
| Generate recommendations for offloading tables to DB2 Analytics Accelerator | ✓ | |
| Virtually test the offloading of tables to DB2 Analytics Accelerator |