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.
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:
- 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.
- 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.
- 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.