Chapter 2. DB2 UDB architecture overview 93
2.6.7 Design Advisor
The Design Advisor helps with the design of suitable indexes for a given table by
finding the best indexes for a problem query, as well as the best indexes for a set
of queries that define a workload subject to optionally applied resource limits.
For a given workload, the Design Advisor will evaluate the existing indexes and
recommend additional indexes if required.
A workload in the context of the Design Advisor is a set of SQL statements which
the database manager has to process during a given period of time. The SQL
statements can include SELECT, INSERT, UPDATE, and DELETE statements.
The information in the workload identifies the type and frequency of the SQL
statements over a given period of time. For example, your database manager
may have to process 1 000 INSERTs, 10 000 UPDATEs, 10 000 SELECTs, and 1 000
DELETEs in a one month period. The Design Advisor’s advising engine uses this
workload information in conjunction with the database information (such as
statistics) to recommend indexes. The goal of the Design Advisor’s advising
engine is to minimize the total workload cost.
The Design Advisor may be invoked via the db2advis command, as shown in
Figure 2-18 on page 94, or from the Control Center.
Note: These recommendations should be validated through actual
measurements in regression test environments before committing the
changes in the production environment.

Get DB2 UDB ESE V8 non-DPF Performance Guide for High Performance OLTP and BI now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.