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:
- In the Manage section, open
the Manage and Tune Workloads page.
- 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.
- 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.
- Ensure that the Re-collect EXPLAIN
information before running workload advisors option is
selected.
- Click the Select What
to Run button.
- 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
- 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.
- In the Review Workload Advisor Recommendations page,
click the Design tab, if there are new recommendations.
- 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.
- 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.