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.
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:
- In the Manage section of the workflow assistant, open the
Manage Workloads page.
- 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.
- 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.
- 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.
- Click the Select What
to Run button.
- 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.
- 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.