Generating and acting on recommendations for adding tables to accelerators

The Workload Analytics Acceleration Advisor enables database administrators to decide which tables that are referenced in a workload should be added to accelerators in IBM DB2 Analytics Accelerator.

Before you begin

About this task

This advisor analyzes the tables that are referenced in a workload of SQL statements and determines which tables should be added to an accelerator in order to improve the performance of SQL statements in the collection that reference those tables. The advisor also reports estimated CPU savings from its recommendations.

For a summary of the types of workloads that can benefit from DB2 Analytics Acceleration Advisor, see section 1.4 Analytics Workloads in the IBM Redbook Optimizing DB2 Queries with IBM DB2 Analytics Accelerator for z/OS at http://www.redbooks.ibm.com/abstracts/sg248005.html.

Procedure

To generate and act on recommendations for adding tables to accelerators:

  1. In the Manage section of the workflow assistant, open the Manage Workloads page.
  2. Select the query workload and click the Invoke Advisors 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 Analytics Acceleration Advisor. Click Set Advisor Options under Workload on the left side of the Invoke section. Then, click the Workload Analytics Acceleration Advisor tab to display the options that you can modify. 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-explain the query workload check box is selected, if either or both of the following conditions are true:
    • You are running this advisor after implementing recommendations from another advisor.
    • You want to collect EXPLAIN information that you can compare with EXPLAIN information taken at a different time for this query workload.
  5. Click the Select What to Run button.
  6. In the Select Activities window, select the Analytics Acceleration check box and click OK.

    The Review Workload Advisor Recommendations page of the Review section opens. The Summary section on this page shows whether there are recommendations for adding tables to accelerators.

  7. In the Summary section, double-click the Analytics Acceleration row. The Analytics Acceleration section opens to display the findings and recommendations of the Workload Analytics Acceleration Advisor.

Example

The following use cases describe examples of when running the Workload Analytics Acceleration Advisor can be helpful.

Use case 1:

A DBA wants to deploy DB2 Analytics Accelerator to speed up performance for applications, yet does not know which tables should be added to accelerators and which SQL statements should be offloaded.
Without the Workload Analytics Acceleration Advisor
The DBA analyzes queries one at a time by using virtual accelerators. This manual process is tedious for large numbers of statements.
With the Workload Analytics Acceleration Advisor
The DBA can get advice for an entire workload at one time. The advisor not only shows which tables can be added to an accelerator, but also shows the estimated CPU savings for the system where the tables are currently located. Moreover, the advisor shows which statements in the workload can have their processing offloaded to an accelerator, which statements cannot, and which can be rewritten so as to be eligible for offloading.

Use case 2:

Tables have been offloaded to DB2 Analytics Accelerator, and a DBA wants to know whether all of these tables need to be there. There is a sizable maintenance overhead in keeping too many tables both in DB2 and in an accelerator.
Without the Workload Analytics Acceleration Advisor
The DBA has to look manually at the benefits of having each table in an accelerator. Such a task can be very difficult when a workload is large.
With the Workload Analytics Acceleration Advisor
The advisor can analyze the entire workload at one time and make recommendations for which tables can be removed from accelerators.

Use case 3:

A DBA has received recommendations from the advisor. The workload involves a large number of tables and the advisor recommends adding most of them to an accelerator. The DBA wants to add only a subset of them to an accelerator, but still see a substantial performance improvement.

Above the recommendations from the advisor, the DBA can click the Test Candidate Analytics Acceleration button. In the page that opens, the DBA can see a list of the tables that are recommended to be offloaded, unselect a subset of these tables, and then run the advisor to see the performance improvement from adding the selected tables to an accelerator.

What to do next

You can click the Test Candidate Analytics Acceleration button to use the Workload Test Candidate Analytics Acceleration feature to modify the recommendations and see the estimated CPU savings. If any tables that the workload references are already on an accelerator, you cannot unselect them before running a test.

When you want to implement recommendations, click Add Recommended Tables to Accelerator.

After adding the tables, you must load them and enable them for use.

.