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
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
Generate design alternatives in a test environment for partitioning,
indexes, MQTs, and MDC tables.
Determine initial database partitioning before loading data into a
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.

Get DB2 UDB V8.2 on the Windows Environment now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.