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
- If you plan to generate recommendations for multidimensional
clustering (MDC):
- Ensure that if the connected DB2 database
is running on Windows, the
version is 9.7 FixPack 6 or 10. Recommendations for multidimensional
clustering are not available for earlier versions that run on Windows. This restriction does
not apply to DB2 databases that
run on Linux.
- Ensure that tables contain sufficient amounts of data. A minimum
of twenty to thirty megabytes of data is recommended. Tables that
are smaller than 12 extents are excluded from consideration.
- Ensure that sufficient storage space (approximately 1% of the
table data for large tables) is available for the sampling data that
the Workload Design Advisor uses.
- If you plan to generate recommendations for redistributing
data across database partitions: register the Database
Partitioning Feature (DPF) license key.
- If you want recommendations for materialized query tables and
the Database Partitioning Feature (DPF) is enabled, you must create
a table space that the Workload Design Advisor can use to simulate
data partitioning. The table space must be in the database partition
group IBMCATGROUP and managed by SYSTEM.
Set the DROPPED TABLE RECOVERY option on the CREATE or ALTER
TABLESPACE statement to OFF. After you
create the table space, specify its name in the options for the Workload
Design Advisor.
- To specify the name in the global preferences, select . In the Preferences window, expand and select the Workload Design Advisor page.
Specify the name in the Custom table space field
under Table space in which simulation catalog tables will
be created.
- To specify the name only in the Query Tuner project in which you
are working, follow step 3 in the procedure below.
- Ensure that you have the authorities
and privileges that are required for analyzing and tuning query workloads
that run on DB2 for Linux, UNIX, and Windows.
- Verify the configuration of the connected
database for query-workload tuning.
- Optional: Modify global values for the options
for the Workload Design Advisor. The advisor uses these values every
time that you run it, unless you override them.
- Run the Workload Statistics Advisor and run the RUNSTATS
commands that the advisor recommends.
- Run the Workload Statistics Advisor again to get recommendations
for new statistical views and changes to existing statistical views.
Then, follow the recommendations.
- Ensure that the local cache of the system
catalog is current.
About this task
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.