Generating and acting on recommendations for converting tables to column organization

The Workload Table Organization Advisor recommends converting row-organized tables to column-organized tables.

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. Its recommendations lead to the best estimated performance improvement for the query workload as a whole.

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 also view and save the DDL for implementing the changes that are necessary to convert the tables.

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.

Procedure

To generate and act on recommendations for converting tables to column organization:

  1. Create a workload of the SQL statements that you want to tune.
    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.

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 or is recommended to remain as is.
Warning
For tables that are recommended for conversion, describes changes that can occur as a result of the conversion.
Indexes will be removed.
Indexes are not allowed on column-organized tables.
Data partitioning will be lost.
You can restore data partitioning only if you convert the table back to row-organization.
ENFORCED RI constraints will change to NOT ENFORCED.
It will be possible for errant data to be inserted into the table.
ENFORCED CHECK constraints will change to NOT ENFORCED.
It will be possible for errant data to be inserted into the table.
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.
MQTs will be removed.
Even after the removal of MQTs, SQL statements will be able to access data in the table faster.
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.
The table is already column-organized.
No conversion is necessary.
Conversion would not improve performance.
The performance of the query workload would not improve if the table were converted to column-organization.
Conversion would improve performance.
The performance of the query workload would improve if the table were converted to column-organization.
Column-organization restrictions do not allow conversion.
The following table types cannot be converted to column-organized tables: MQT, declared global temporary table, created global temporary table.

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.

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.

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.

Feedback