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.

The Workload Analytics Acceleration Advisor can use two different methods for generating recommendations:
Using a virtual accelerator
For the advisor to use this method, you must specify the name of a virtual accelerator that is running. The benefit to using this method is that you can perform "What-If" testing of different scenarios that are based on the recommendations of the advisor.
Using accelerator modeling
This method does not require a running virtual accelerator. However, if you select this method, these restrictions apply to the advisor's recommendations:
  • You cannot specify to set CURRENT QUERY ACCELERATION to ELIGIBLE. This special register is always set to ENABLE when the advisor runs. Here is a description of each of these settings:
    ENABLE
    Specifies that statements are accelerated only if DB2 determines that it is advantageous to do so. For example, a statement might be eligible for offloading because it meets one or more of the criteria for eligibility. However, it might be the case that running the statement against tables offloaded to an accelerator brings little or no performance improvement. In this case, DB2 does not run the statement on the accelerator.
    ELIGIBLE
    Specifies that statements are run on an accelerator if they are eligible for acceleration, even if there is no performance benefit. For example, you might want to conserve CPU cycles by running as many statements as possible on an accelerator. Although the performance might not improve for all of the statements, more CPU resources will be available for other processes.
  • You cannot perform "What-If" testing of different scenarios that are based on the recommendations of the advisor.
Refer to the IBM Redbook Optimizing DB2 Queries with IBM DB2 Analytics Accelerator for z/OS at http://www.redbooks.ibm.com/abstracts/sg248005.html for the following information:
  • For a summary of the types of workloads that can benefit from DB2 Analytics Acceleration Advisor, see section 1.4.
  • For a list of the criteria that determine whether a statement can be offloaded and for a list of restrictions on offloaded statements, see section 10.1.

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.
  7. Select whether to use a virtual accelerator or accelerator modeling.

    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.

  8. 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, if the DBA specified a virtual accelerator for the advisor to use, 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

If you specified a virtual accelerator for the advisor to use: 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.


Feedback