There are certain limitations and restrictions associated
with Design Advisor, about indexes, materialized query tables (MQTs),
multidimensional clustering (MDC) tables, and database partitioning.
Restrictions on index
- Indexes that are suggested for MQTs are designed to improve workload
performance, not MQT refresh performance.
- A clustering RID index is for MDC tables. The Design Advisor will
include clustering RID indexes as an option rather than create an
MDC structure for the table.
- The Version 9.7 Design Advisor does not suggest you use partitioned
indexes on a partitioned table. All indexes are must be used with
an explicit NOT PARTITIONED clause.
Restrictions on MQT
- The Design Advisor does not suggest the use of incremental MQTs.
If you want to create incremental MQTs, you can convert REFRESH IMMEDIATE
MQTs into incremental MQTs with your choice of staging tables.
- Indexes that are for MQTs are designed to improve workload performance,
not MQT refresh performance.
- If update, insert, or delete operations are not included in the
workload, the performance impact of updating a REFRESH IMMEDIATE MQT
is not considered.
- It is suggested that REFRESH IMMEDIATE MQTs have unique indexes
created on the implied unique key, which is composed of the columns
in the GROUP BY clause of the MQT query definition.
Restrictions on MDC
- An existing table must be populated with sufficient data before
the Design Advisor considers MDC for the table. A minimum of twenty
to thirty megabytes of data is suggested. Tables that are smaller
than 12 extents are excluded from consideration.
- MDC requirements for new MQTs will not be considered unless the
sampling option, -r, is used with the db2advis command.
- The Design Advisor does not make MDC suggestions for typed, temporary,
or federated tables.
- Sufficient storage space (approximately 1% of the table data for
large tables) must be available for the sampling data that is used
during the execution of the db2advis command.
- Tables that have not had statistics collected are excluded from
consideration.
- The Design Advisor does not make suggestions for multicolumn dimensions.
Restrictions on database partitioning
The
Design Advisor provides advise about database partitioning only for DB2® Enterprise Server Edition.
Additional restrictions
Temporary simulation
catalog tables are created when the Design Advisor runs. An incomplete
run can result in some of these tables not being dropped. In this
situation, you can use the Design Advisor to drop these tables by
restarting the utility. To remove the simulation catalog tables, specify
both the -f option and the -n option (for -n, specifying the same
user name that was used for the incomplete execution). If you do not
specify the -f option, the Design Advisor will only generate the DROP
statements that are required to remove the tables; it will not actually
remove them.
Note: As of Version 9.5, the -f option is the default.
This means that if you run db2advis with the MQT
selection, the database manager automatically drops all local simulation
catalog tables using the same user ID as the schema name.
You
should create a separate table space on the catalog database partition
for storing these simulated catalog tables, and set the DROPPED TABLE
RECOVERY option on the CREATE or ALTER TABLESPACE statement to OFF.
This enables easier cleanup and faster Design Advisor execution.