You can perform virtual tests that are based on the recommendations of the Workload
Analytics Acceleration Advisor, and virtual tests to help you determine which tables you can remove
from an accelerator.
About this task
Use this page for the following tasks:
- Test variations of recommendations from the Workload Analytics Acceleration Advisor
- Test how much removing tables from an accelerator will affect the performance of a query
workload
Attention: When you start a test, the advisor follows one of
these sets of steps:
- If profiles are active on the subsystem, the advisor creates and enables a profile to use
during the test, and then issues the START PROFILE command. All profiles that were deactivated are
reactivated, including the profile that the advisor created. After running the test, the advisor
removes the profile that the advisor used during the test, and then issues the START PROFILE command
again to reactivate all of the enabled profiles.
- If profiles are not active on the subsystem, the advisor creates and enables a profile to
use during the test, and then the advisor issues the START PROFILE command. All profiles for which
PROFILE_ENABLED is set to Y in the profile table are activated. After running the test, the advisor
issues the STOP PROFILE command and removes the profiles that the advisor used during the test.
Procedure
- If you want to test variations of recommendations from the Workload Analytics Acceleration
Advisor:
- Run the Workload Analytics Acceleration Advisor, as described in Generating and acting on recommendations for adding tables to accelerators.
- From the list of recommendations, click the Test Candidate Analytics Acceleration button. The test page opens, listing all of the tables that the advisor recommends to offload to an
accelerator.
- Deselect one or more tables that are in the list and run the test.
For example, suppose that the advisor recommends a large number of tables be offloaded to an
accelerator. If you are concerned about the overhead of keeping the tables in the accelerator up to
date, you might want to see how many tables you can remove from the recommendations while still
seeing a large performance improvement. You could follow these steps to perform iterative testing to
find out which tables not to offload.
- If you want to test how much removing tables from an accelerator will affect the performance of
a query workload:
- Create a query workload that references the tables that are on an accelerator.
- Ensure that the statistics for the query workload are up to date. For this step, you can run the Workload Statistics Advisor.
- Re-explain the query workload. You can do this in the Manage section of the workflow assistant.
- Run a test.
- In the Manage section of the workflow assistant, select the query workload and click Tune Query
Workload.
- Select Test Candidate Analytics Acceleration on the left side of the workflow assistant.
- Import tables from an accelerator into the test page.
- Run the test.
- Review the results.
- Remove the tables that the advisor recommends to remove from the accelerator.
For example, suppose that you offloaded all of the tables that the SQL statements in an
application reference to facilitate a quick increase in the performance of the analytical queries to
your data warehouse. Now, you want to see which tables you can remove while still keeping the
performance at or near current levels. Removing tables might also reduce the number of incremental
updates to the offloaded tables. You could follow these steps and then use IBM DB2 Analytics
Accelerator Studio to remove the tables that, according to the test results, could be removed.