DB2 UDB V8.2 on the Windows Environment

86 DB2 UDB V8.2 on the Windows Environment

...

Automatic maintenance (AUTO_MAINT) = ON

Automatic database backup (AUTO_DB_BACKUP) = ON

Automatic table maintenance (AUTO_TBL_MAINT) = ON

Automatic runstats (AUTO_RUNSTATS) = ON

Automatic statistics profiling (AUTO_STATS_PROF) = OFF

Automatic profile updates (AUTO_PROF_UPD) = OFF

Automatic reorganization (AUTO_REORG) = ON

3.5 Integrated Design Advisor

The new integrated Design Advisor can assist DBAs in making optimal and

comprehensive database design decisions. Many DBAs would agree that the

decisions made with respect to the design of a database are some of the most

challenging, time consuming, and critical to make. This self-configuring tool

greatly simplifies the design process by using workload, database, and hardware

information to recommend specific performance acceleration options for routine

design tasks.

The integrated Design Advisor consists of:

򐂰 Index advisor

򐂰 Materialized Query Table advisor

򐂰 Multidimensional Clustering advisor

򐂰 Partitioning advisor

The Design Advisor can help you significantly improve your workload

performance. The common task of selecting which indexes, MQTs, clustering

dimensions, or partitions to create for a complex workload can be quite 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 will generate recommendations for:

򐂰 New indexes

򐂰 New materialized query tables (MQTs)

򐂰 Conversion to multidimensional clustering (MDC) tables

򐂰 Repartitioning of tables

򐂰 Deletion of indexes and MQTs unused by the specified workload

Also, you can order the Design Advisor to implement these recommendations

immediately or schedule some or all of them for a later time.

Chapter 3. Administration and management 87

The Design Advisor analyzes the characteristics (database and hardware

resource) of your workload to consider the costs and advantages of each feature

when formulating design recommendations. Then it weighs the performance

improvements against the costs associated with implementing the

recommendations.

Because the Design Advisor performs the cost-benefit analysis for you, all

features are selected by default for consideration. It is recommended that you

leave this default setting and allow the Design Advisor to determine which

features provide the greatest overall performance advantages for the workload

that you specify. If you do not want to implement specific performance

enhancement features, you can choose not to select those features for

evaluation. Eliminating those features will reduce the amount of time that the

Design Advisor spends evaluating different performance enhancement

possibilities for your database.

Using either the Design Advisor GUI or the db2advis command-line tool, the

Design Advisor can help simplify the following tasks:

򐂰 Planning for or setting up a new database or partitioning structure

While designing your database or database partitions, use the Design Advisor

to:

– Generate design alternatives in a test environment for partitioning,

indexes, MQTs, and MDC tables.

– Determine initial database partitioning before loading data into a

database.

– Assist in migrating from a non-partitioned DB2 database to a partitioned

DB2 database.

– Assist in migrating to DB2 in a partitioned environment from another

database product.

– Evaluate indexes, MQTs, or partitions that have been generated manually.

Note: It is very important for the Design Advisor that you provide a

representative query workload. If the workload that you submit is not

representative of the overall database activity, the recommendations might not

provide performance enhancements for queries not included in the workload.

Also, it is critical that the statistics on any object that is involved in the

workload are up-to-date. Inaccurate statistics might negatively affect the

quality of the Design Advisor recommendations.