Generating and acting on recommendations for BLU Acceleration

The Workload Table Organization Advisor recommends converting row-organized tables to column-organized tables and creating shadow tables for row-organized tables that are used in analytical queries.

Before you begin

About this task

The advisor examines the all of the tables that are referenced by the statements that are in a query workload. The advisor presents its analysis and rationales so that you can see the tables that are recommended for conversion, as well as those tables that are not. You can see the cardinalities of the tables, how many statements in the query workload reference them, the cumulative cost of running the statements that reference a table, the statements themselves, and more.

You can view and save the DDL for implementing the changes that are necessary to convert the tables. You can even click a button to import the DDL into the Data Studio client's Convert Tables to Column Organization feature, where you can edit the DDL, and then run it or save it.

Tip: Run this advisor only after you find that recommendations from the Workload Statistics Advisor, the Workload Index Advisor, or both do not improve performance enough. You might be able to reach the required performance for the query workload by gathering statistics, creating or modifying indexes, or both. Moreover, the Workload Table Organization Advisor relies on correct statistics when it generates its recommendations.
Restriction: The advisor can recommend the creation of shadow MQTs only if the connected DB2 for Linux, UNIX, and Windows database is at version 10.5.4.

Procedure

To generate and act on recommendations for BLU Acceleration:

  1. Create a workload of the SQL statements that you want to tune together.
    1. Capture the SQL statements that you want to tune. See Locations from which you can capture SQL statements to create query workloads that run on DB2 for Linux, UNIX, and Windows.
    2. Click the Create Query Workload from All Statements button and then give the query workload a name and description when you are prompted.
    The Manage and Tune Workloads page of the Manage section opens. The new query workload appears in the table on this page. The status of the query workload is CAPTURED.
  2. Run the Workload Statistics Advisor twice on the query workload and implement its recommendations both times.
  3. In the Manage section of the workflow assistant, select the query workload and click the Invoke Advisors button.
  4. Optional: Set values for the preferences for this advisor that override the global preferences. Select Set Advisor Options on the left side of the Invoke section. Then, select the Table Organization tab.
  5. On the left side of the workflow assistant, select Run Workload Advisors.
  6. Select the option Re-collect EXPLAIN information before running workload advisors.
  7. Click Select What to Run.
  8. Select Table Organization and click OK. The Review section of the workflow assistant opens to the Review Workload Advisor Recommendations page. If there are recommendations, the Table Organization tab appears.
  9. Click the Table Organization tab.

Results

This section displays the tables that are and are not recommended for migration to column organization, as well as those tables for which shadow tables are recommended.

At the top of the section is an estimation of the improvement to the performance of the entire query workload. The advisor calculates the estimated performance improvement by following these steps:
  1. Assume that all of the recommended table conversions are performed, and that the indexes on those tables are dropped.
  2. Find the product of the following two values for each statement in a query workload: Number of executions * Statement cost
  3. Add the products together.

These columns are displayed in the table at the top of this section:

Table
The name of a row-organized table that is either recommended for conversion to column organization or is recommended to remain as is.
Creator
The creator of the table.
Current Organization
Indicates whether the table is currently row-organized or column-organized.
Recommended Organization
Indicates whether the table is recommended for conversion to column organization, for a shadow table to be defined on it, or to remain as is.
Warning
For tables that are recommended for conversion, describes changes that can occur as a result of the conversion.
A shadow table will be defined on the table
The recommendation is to define a shadow table on the table to improve the performance of analytic queries. A shadow table is an MQT that is maintained through replication.
Data partitioning will be lost.
You can restore data partitioning only if you convert the table back to row-organization.
ENFORCED CHECK constraints will change to NOT ENFORCED.
It will be possible for errant data to be inserted into the table.
ENFORCED RI constraints will change to NOT ENFORCED.
It will be possible for errant data to be inserted into the table.
Indexes will be removed.
Indexes are not allowed on column-organized tables.
MQTs will be removed.
Even after the removal of MQTs, SQL statements will be able to access data in the table faster.
User-maintained, deferred-refresh MQTs will be converted; any other MQTs will be dropped
All user-maintained deferred-refresh MQTs that are defined on the table will be converted to column-organization when the table is converted from row-organization to column-organization.
Table space will be changed.
After the conversion, the table will be in the table space that is specified in the preferences for the Workload Table Organization Advisor or in a table space that the advisor selected. You can modify the conversion script to use a different table space.
Cardinality
The number of rows that are in the table.
References to Table
The number of time that the SQL statements that are in the query workload reference the table.
Cumulative Total Cost
The sum of the CPU costs in timerons of each access to data in the table by the SQL statements that are in the query workload.
Affected Statements
The number of SQL statements in the query workload that reference the table.
Finding
Explains why the table is or is not recommended for conversion.
Adding a shadow table can help analytical queries on the table run faster.
Analytical queries against this table would be routed automatically to the shadow table to take advantage of BLU Acceleration.
Column-organization restrictions do not allow conversion.
The following table types cannot be converted to column-organized tables: MQTs (other than user-maintained, deferred-refresh MQTs), declared global temporary tables, created global temporary tables.

There are other restrictions, too. For example, tables with LOB columns cannot be converted, not can tables that are used in MERGE statements or in statements that declare cursors. For the full set of restrictions, see the documentation for DB2 10.5 for Linux, UNIX, and Windows or later.

Conversion would improve performance.
The performance of the query workload would improve if the table were converted to column-organization.
Conversion would not improve performance.
The performance of the query workload would not improve if the table were converted to column-organization.
Missing statistics do not allow conversion.
The table is missing statistics, so conversion cannot be done reliably. Run the Workload Statistics Advisor to collect statistics for the query workload, run the RUNSTATS statements that the advisor recommends, and then run the Workload Table Organization Advisor again.
The table is already column-organized.
No conversion is necessary.
The table already has a shadow table.
An MQT that is maintained by replication is already defined on this row-organized table.

These columns appear in the table SQL Statements Affected. The table lists all of the SQL statements in the query workload that would be affected by the conversion of the listed tables to column-organization.

Execution Count
The number of times that the SQL statement is run in the query workload.
Weight
The relative weight of the statement in the query workload. The value is calculated by multiplying the value in the Cost Before column by the execution count, and then dividing the product by the total "cost before" for the query workload.
Estimated Performance Gain
The estimated increase in the performance of the statement that accesses the selected table, if the recommendation for conversion is implemented.
For example, an estimated performance gain of 90.61% means that 90.61% less CPU time is required for the statement to access the selected table.
Cost Before
The cost of the statement in timerons before the conversion of the referenced table to a column-organized table.
Cost After
The cost of the statement in timerons after the conversion of the referenced table to a column-organized table.
SQL Statement
The text of the statement. To see the full text of the statement, right-click the row and select Show SQL Statement.

What to do next

Evaluate the estimated improvements in the performance of the query workload to find out whether they might help you to meet your performance requirements. If you decide to follow the recommendations, ensure that your database is backed up before you run the script that the advisor generated. Also, be aware that referential integrity constraints on tables will be removed during the conversion to column-organization.

If you follow any recommendations to create shadow tables, ensure that InfoSphere Change Data Capture for DB2 for Linux, UNIX, and Windows is installed, an instance is created, and that all shadow tables in your database are managed by a single subscription.

When you generate an access plan graph for any SQL statement that references a column-organized table or shadow MQT, the graph will include an operator with the label CTQ wherever there is a transition between column-organized processing and row-organized processing. For more information about the CTQ operator, see New explain information for column-organized tables.


Feedback