Creating MQTs, using multidimensional clustering, and redistributing data across database partitions

Run the Workload Design Advisor to find out how materialized query tables, multidimensional clustering, and data redistribution can improve the performance of query workloads that run on DB2® for Linux, UNIX, and Windows

Before you begin

Restriction: The Workload Design Advisor does not make MDC recommendations for typed, temporary, or federated tables. This advisor also does not make recommendations for multicolumn dimensions. This advisor ignores tables that do not have statistics.
Restriction: The Workload Design Advisor can recommend database partitioning only for DB2 Enterprise Server Edition.

Procedure

To generate and act on recommendations from the Workload Design Advisor:

  1. In the Manage section, open the Manage and Tune Workloads page.
  2. Select the query workload and click the Invoke Advisors and Tools icon, which is on the left end of the toolbar above the list of query workloads. The Run Workload Advisors page of the Invoke section opens.
  3. Optional: Modify values of the options for the Workload Design Advisor. Click Set Advisor Options under Workload on the left side of the Invoke section. Then, click the Design tab to display the options that you can modify. For help with the options, click the ? icon. After you are finished modifying the values for options, click Run Workload Advisors on the left side of the Review section.
  4. Ensure that the Re-collect EXPLAIN information before running workload advisors option is selected.
  5. Click the Select What to Run button.
  6. In the Select Activities window, select one or more of these options, then click OK.
    • Materialized query tables
    • Multidimensional clustering
    • Distributing data across database partitions
  7. In the Collect EXPLAIN Information window, specify values for the runtime environment of the SQL statements that are in the query workload.
    The workflow assistant must run the EXPLAIN statement for each of the SQL statements that are in the query workload. The Workload Design Advisor requires up-to-date information about the SQL statements. For more information, click the Help icon in the lower-left corner of the window.
  8. In the Review Workload Advisor Recommendations page, click the Design tab, if there are new recommendations.
  9. Review this information at the top of the Design section.
    Estimated performance improvement
    The percentage by which the time that is required to process the query workload is estimated to be reduced, if you create all of the objects that are recommended.
    Disk space (DASD) required
    The amount of disk space that is required to create all of the objects that are recommended.

    Table of recommendations for indexes for recommended MQTs

    These indexes are designed to improve the performance of the query workload, not the performance of the MQT refreshes.

    NAME
    Name of the index.
    CREATOR
    Qualifier of the index.
    TBNAME
    Name of the MQT on which the index is defined.
    TBCREATOR
    Qualifier of the table.
    COLNAMES
    List of columns that the index is defined on.
    COLCOUNT
    Number of columns in the key plus the number of include columns, if any.
    NLEAF
    Number of leaf pages; -1 if statistics are not gathered.
    NLEVELS
    Number of index levels; -1 if statistics are not gathered.
    FIRSTKEYCARD
    Number of distinct first key values; -1 if statistics are not gathered.
    FULLKEYCARD
    Number of distinct full key values; -1 if statistics are not gathered.
    INDEXTYPE
    Type of index. CLUS = Clustering; REG = Regular; DIM = Dimension block index; BLOK = Block index
    UNIQUERULE
    Unique rule. D = Duplicates allowed; P = Primary index; U = Unique entries only allowed
    EXISTS
    Y if the index exists in the database catalog. N if the index does not currently exist in the catalog.

    Table of recommended MQTs

    The Workload Design Advisor does not recommend incremental MQTs. If you want to create incremental MQTs, you can convert REFRESH DEFERRED MQTs into incremental MQTs with your choice of staging tables.

    If update, insert, or delete operations are not included in the workload, the performance impact of updating a recommended REFRESH IMMEDIATE MQT is not considered.

    NAME
    Name of the MQT.
    CREATOR
    Qualifier of the MQT.
    NUMROWS
    Number of estimated rows in the MQT.
    NUMCOLS
    Number of columns that are defined in the MQT.
    ROWSIZE
    Reserved for future use.
    MQT_SOURCE
    Indicates where the MQT candidate was generated. I indicates that the MQT candidate is a refresh-immediate MQT. D indicates that the MQT candidate can be created only as a full refresh-deferred MQT.
    CREATION_TEXT
    Contains the CREATE TABLE DDL for the MQT.
    TBSPACE
    Table space that is recommended for the MQT.
    REFRESH_TYPE
    Type of refresh. I = immediate; D = deferred
    EXISTS
    Y if the MQT exists in the database catalog.
    REPLICATE
    Specifies whether or not part of the MQT is distributed in a replicated database partition.

    Table of recommended multidimensional clustering (MDC) tables

    The table lists the regular tables, existing MQTs, or recommended MQTs that the Workload Design Advisor recommends be converted to MDC tables. Before running the CREATE DDL scripts to create the MDC tables, follow either of these series of steps:
    • Export the data from the regular tables, drop the regular tables, create the MDC tables, and then import the data into them.
    • Rename these regular tables, create the MDC tables, copy the data from these regular tables into the MDC tables, and drop the regular tables.
    TABLE_NAME
    Name of the table.
    TABLE_SCHEMA
    Qualifier of the table.
    TABLESPACE
    Table space in which the table is to be created.
    SELECTION_FLAG
    Indicates the recommendation type. Valid values are M for MQT, P for database partitioning and C for MDC. This field can include any subset of these values. For example, MC indicates that the table is recommended as an MQT and an MDC table.
    TABLE_EXISTS
    Y if the table exists in the database catalog.
    ORGANIZE BY
    Contains the ORGANIZE BY clause of the CREATE TABLE DDL.
    CREATION_TEXT
    Contains the CREATE TABLE DDL.

    Table of recommendations for distributing data across database partitions

    This table lists the tables that are recommended to be distributed across database partitions.

    TABLE_NAME
    Name of the table.
    TABLE_SCHEMA
    Qualifier of the table.
    TABLESPACE
    Table space in which the table is to be created.
    TABLE_EXISTS
    ’Y' if the table exists in the database catalog.
    COLNAMES
    Specifies the distribution key columns on which the table will be distributed.
    USEIT
    "Y" indicates that the database partition is used in EVALUATE PARTITION mode.
    COST
    Specifies the cost in timerons of using the database partition.
  10. Click the Run icon in the toolbar to open the Run DDL window, where you can run the recommended DDL scripts or save them.

What to do next

After you run the scripts, re-explain the SQL statements that are in the query workload. You can do so by returning to the Invoke section and selecting the Re-collect EXPLAIN information before running workload advisors check box before selecting advisors to run.

Feedback