Establishing a performance tuning strategy

The Design Advisor

The DB2 Design Advisor is a tool that can help you significantly improve your workload performance. The task of selecting which indexes, materialized query tables (MQTs), clustering dimensions, or database partitions to create for a complex workload can be daunting. The Design Advisor identifies all of the objects that are needed to improve the performance of your workload.

Given a set of SQL statements in a workload, the Design Advisor generates recommendations for:

==> New indexes.
==> New clustering indexes .
==> New MQTs.
==> Conversion to multidimensional clustering (MDC) tables.
==> The redistribution of tables.

The Design Advisor can implement some or all of these recommendations immediately, or you  can schedule them to run at a later time.

Use the db2advis command to launch the Design Advisor utility.

The Design Advisor can help simplify the following tasks:

Planning for and setting up a new database

While designing your database, use the Design Advisor to generate design alternatives in a test environment for indexing, MQTs, MDC tables, or database partitioning.

In partitioned database environments, you can use the Design Advisor to:
==> Determine an appropriate database partitioning strategy before loading data into a database.
==> Assist in upgrading from a single-partition database to a multi-partition database
==> Assist in migrating from another database product to a multi-partition DB2 database Workload performance tuning

After your database is set up, you can use the Design Advisor to:
==> Improve the performance of a particular statement or workload
==> Improve general database performance, using the performance of a sample workload as a gauge
==> Improve the performance of the most frequently executed queries, as identified, for example, by the IBM InfoSphere Optim Performance Manager
==> Determine how to optimize the performance of a new query
==> Respond to IBM Data Studio Health Monitor recommendations regarding shared memory utility or sort heap problems with a sort-intensive workload
==> Find objects that are not used in a workload