DB2 10.5 for Linux, UNIX, and Windows

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: 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

IBM InfoSphere Optim Query Workload Tuner provides tools for improving the performance of single SQL statements and the performance of groups of SQL statements, which are called query workloads. For more information about this product, see the product overview page. In Version 3.1.1 or later, you can also use the Workload Design Advisor to perform many operations that were available in the DB2 Design Advisor wizard. For more information see the documentation for the Workload Design Advisor.

Design Advisor output

Design Advisor output is written to standard output by default, and saved in the ADVISE_* tables:
  • The ADVISE_INSTANCE table is updated with one new row each time that the Design Advisor runs:
    • The START_TIME and END_TIME fields show the start and stop times for the utility.
    • The STATUS field contains a value of COMPLETED if the utility ended successfully.
    • The MODE field indicates whether the -m parameter was used on the db2advis command.
    • The COMPRESSION field indicates the type of compression that was used.
  • The USE_TABLE column in the ADVISE_TABLE table contains a value of Y if MQT, MDC table, or database partitioning strategy recommendations were made.

    MQT recommendations can be found in the ADVISE_MQT table; MDC recommendations can be found in the ADVISE_TABLE table; and database partitioning strategy recommendations can be found in the ADVISE_PARTITION table. The RUN_ID column in these tables contains a value that corresponds to the START_TIME value of a row in the ADVISE_INSTANCE table, linking it to the same Design Advisor run.

    When MQT, MDC, or database partitioning recommendations are provided, the relevant ALTER TABLE stored procedure call is placed in the ALTER_COMMAND column of the ADVISE_TABLE table. The ALTER TABLE stored procedure call might not succeed due to restrictions on the table for the ALTOBJ stored procedure.

  • The USE_INDEX column in the ADVISE_INDEX table contains a value of Y (index recommended or evaluated ) or R (an existing clustering RID index was recommended to be unclustered) if index recommendations were made.
  • The COLSTATS column in the ADVISE_MQT table contains column statistics for an MQT. These statistics are contained within an XML structure as follows:
    <?xml version=\"1.0\" encoding=\"USASCII\"?>
You can save Design Advisor recommendations to a file using the -o parameter on the db2advis command. The saved Design Advisor output consists of the following elements:
  • CREATE statements associated with any new indexes, MQTs, MDC tables, or database partitioning strategies
  • REFRESH statements for MQTs
  • RUNSTATS commands for new objects
An example of this output is as follows:
--<?xml version="1.0"?>
--<schema>ZILIO2 </schema>
--<schema>ZILIO2 </schema>
--<schema>SAMP </schema>
-- select
-- c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice,
-- sum(l_quantity) from samp.customer, samp.orders,
-- samp.lineitem where o_orderkey in( select
-- l_orderkey from samp.lineitem group by l_orderkey
-- having sum(l_quantity) > 300 ) and c_custkey
-- = o_custkey and o_orderkey = l_orderkey group by
-- c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice
-- order by o_totalprice desc, o_orderdate fetch first
-- 100 rows only
--<schema>ZILIO2 </schema>
--<schema>SAMP </schema>
--<schema>SAMP </schema>
--<schema>ZILIO2 </schema>
--<schema>ZILIO2 </schema>
--<schema>ZILIO2 </schema>

This XML structure can contain more than one column. The column cardinality (that is, the number of values in each column) is included and, optionally, the HIGH2KEY and LOW2KEY values.

The base table on which an index is defined is also included. Ranking of indexes and MQTs can be done using the benefit value. You can also rank indexes using (benefit - overhead) and MQTs using (benefit - 0.5 * overhead).

Following the list of indexes and MQTs is the list of statements in the workload, including the SQL text, the statement number for the statement, the estimated performance improvement (benefit) from the recommendations, and the list of tables, indexes, and MQTs that were used by the statement. The original spacing in the SQL text is preserved in this output example, but the SQL text is normally split into 80 character commented lines for increased readability.

Existing indexes or MQTs are included in the output if they are being used to execute a workload.

MDC and database partitioning recommendations are not explicitly shown in this XML output example.

After some minor modifications, you can run this output file as a CLP script to create the recommended objects. The modifications that you might want to perform include:
  • Combining all of the RUNSTATS commands into a single RUNSTATS invocation against the new or modified objects
  • Providing more usable object names in place of system-generated IDs
  • Removing or commenting out any data definition language (DDL) for objects that you do not want to implement immediately