Overview of IBM DB2 Analytics Accelerator for z/OS
IBM DB2 Analytics Accelerator for z/OS is a high-performance appliance that integrates IBM Netezza and zEnterprise technologies. This combination delivers extremely fast results for complex and data-intensive DB2 queries on data warehousing, business intelligence, and analytic workloads.
Figure 1 shows an overview of the query acceleration workflow. When an application submits an SQL query, DB2 will analyze it. If query acceleration is enabled and the query qualifies for acceleration, DB2 will convert the query into Netezza syntax and route it to Analytics Accelerator through the internal distributed relational database architecture (DRDA) communication channel. Analytics Accelerator runs the query and sends the result back to DB2. DB2 then sends the query out to the application. This integration is transparent to users. No application changes are required to accelerate the query.
Figure 1. Overview of query acceleration with DB2 Analytics Accelerator
When you set up and enable query acceleration, the DB2 optimizer can offload a query to Analytics Accelerator when determining the access path of a query. When evaluating whether a query is a good candidate for acceleration, you could use the EXPLAIN tool. By using the access path, you can determine whether the query qualifies for acceleration and why. You can populate the EXPLAIN tables with this information even if no accelerator is connected to DB2. This virtual accelerator feature is useful when, for example, long-running queries cannot run due to resource constraints and you want to determine if these queries can be accelerated. You can also use the EXPLAIN information to analyze queries that cannot be accelerated. If a query cannot be accelerated, you can rewrite the query so that it is eligible.
Using InfoSphere Optim Query Workload Tuner to plan and implement query acceleration
If you wanted to evaluate a large number of queries, analyzing the EXPLAIN information for each query would require a lot of time and effort. The new Workload Analytics Accelerator Advisor (WAAA) in InfoSphere Optim Query Workload Tuner (IOQWT) can evaluate large numbers of queries quickly.
IOQWT helps database administrators (DBA) and SQL developers optimize the performance of SQL statements and query workloads in applications that query DB2 for Linux, UNIX, and Windows databases and DB2 for z/OS subsystems.
IOQWT for z/OS version 126.96.36.199 is enhanced with the new WAAA, which analyzes a given query workload and lists the SQL statements that are eligible for acceleration. The WAAA:
- Lists the associated tables that must be added to an accelerator.
- Shows the estimated CPU savings from accelerating the SQL statements.
- Declares all other statements in a query workload as either ineligible for acceleration or as candidates for acceleration if they are rewritten.
- Gives reasons for declaring statements ineligible.
The next three sections describe typical scenarios in which the WAAA tool can help.
Scenario 1. Offloading queries for a data warehouse
In this scenario, a data warehouse is set up on a DB2 for z/OS version 10 instance. An accelerator is set up for query acceleration. The DBA wants to use Analytics Accelerator to improve performance for a TPC-D workload, but does not know which SQL statements should be offloaded. The DBA would like recommendations from the WAAA.
Capturing the workload and creating a query workload in IOQWT
The DBA runs the TPC-D workload and the statements are cached in the statement cache. After the statements are cached, the DBA can pull the statements together into a query workload in IOQWT by capturing them from the cache.
The DBA could capture the statements from other locations, of course. DB2 Query Monitor for z/OS (QM) can push monitored statements into IOQWT as a new query workload. IOQWT can also pull statements from QM. For example, the DBA could:
- Run the TPC-D workload while monitoring it with QM to get a performance baseline.
- Run the WAAA on the workload in IOQWT and follow the advice of the WAAA.
- Offload statements to an accelerator.
- Run the workload and monitor it again with QM to see the difference in performance compared to the baseline.
DBAs can also capture from catalog plans and packages, as described in Scenario 2.
After running the workload, the DBA starts the IBM Data Studio client version 188.8.131.52 and captures the query workload by taking the following steps.
- Connect to the subsystem by right clicking the connection to it and select
Connect in the Data Source Explorer, as in Figure
Figure 2. Connecting to the database
- Create a new tuning workflow by opening the Query Tuner Workflow Assistant.
Right click the database and select Analyze and Tune >
Start Tuning, as in Figure 3.
Figure 3. Opening the Workflow Assistant
- Create a new filter:
- In the Capture section, select the Statement Cache
source type then click New... in the Filter
pane, as in Figure 4.
Figure 4. Creating a new filter
- Name the new filter. In this case, the DBA names the filter
- Specify criteria for the filter. In the PROGRAM_NAME
field the DBA specifies
DSNTEP4as the name of the package in which the statements are bound, as in Figure 5. By specifying this value, the DBA causes all SQL statements except those that come from that program to be filtered out.
Figure 5. Filtering out the SQL statements
- Click Finish.
- In the Capture section, select the Statement Cache source type then click New... in the Filter pane, as in Figure 4.
- In the Capture SQL from Statement Cache editor, shown in Figure 6, enable statistics collection to get detailed runtime statistics from IFCID 318 trace. The statistics contain elapsed and CPU times.
- Capture the SQL by clicking Capture Now.
Figure 6. Capturing the SQL statements
SQL statements from the DSNTEP4 package are listed, including detailed information about each statement.
- Create the query workload:
- Click Create Query Workload from All
Statements..., as in Figure 7.
Figure 7. Creating a workload from all SQL statements
- Name the workload. In this case, the DBA names the workload
TPCD_sample_workload, as in Figure 8.
Figure 8. Naming the workload
- Click Create Query Workload from All Statements..., as in Figure 7.
Collecting the latest statistics for objects referenced by the workload
The DBA runs the Workload Statistics Advisor (WSA) to generate RUNSTATS statements to update the statistics of the objects that the SQL statements reference. The WAAA relies on current statistics because it bases its analysis on EXPLAIN information. (To learn how to run the WSA, see the Data Studio 184.108.40.206 Information Center.)
Running the WAAA
After running the recommended RUNSTATS statements, the DBA can run the WAAA using the following steps.
- In the Query Tuner Workload Assistant, open the Manage section.
- In the Manage Workloads editor, click Invoke
Advisors, as in Figure 9.
Figure 9. Invoking the workload advisors for the workload list
- In the Run Workload Advisors editor, check the box to Re-collect EXPLAIN information before running workload advisors, as in Figure 10. When you select this option, updated statistics are used in the EXPLAIN information that the WAAA uses to analyze the query workload.
- Click Select What To
Figure 10. Opening the Select Activities window
The Select Activities window opens.
- Select the Analytics Acceleration option then click
OK, as in Figure 11.
Figure 11. Selecting Analytics Acceleration activity
The Collect EXPLAIN Information window opens so that you can collect the statistics.
- Click Start EXPLAIN, as in Figure 12.
Figure 12. Starting the EXPLAIN tool
After the EXPLAIN tool runs and the WAAA finishes its analysis, IOQWT opens the Review section in the Workflow Assistant and indicates that the WAAA has recommendations, as in Figure 13.
Figure 13. Reviewing recommendations provided by the advisor
Reviewing the advisor's recommendations
The DBA opens the Analytics Acceleration section in the Review Workload Advisor Recommendations editor to view the WAAA analysis, as in Figure 14.
Figure 14. Review the Analytics Acceleration recommendations
The Analytics Acceleration section contains three important areas of information, as shown in Figure 15 and Figure 16.
Figure 15. Estimated performance improvements
The estimated total cost savings from query acceleration is calculated by determining how many seconds would be saved by offloading all of the eligible statements to an accelerator.
The cumulative CPU time savings from query offloading is the total amount of seconds saved when running eligible queries with Analytics Accelerator instead of DB2. The cost is shifted to Analytics Accelerator instead of DB2, saving the DBA MIPS costs on DB2.
Figure 16. List of tables referenced by the workload
The columns highlighted in red in Figure 16 are:
- On Accelerator? - specifies whether a table is already on an accelerator. If you want to determine what accelerators are hosting the selected table, right click the table and select List Accelerators Hosting the Selected Table.
- References to Table - indicates how many times the table is referenced by eligible statements.
- Cumulative Estimated Cost - specifies the sum of all eligible statements that reference the selected table. These values help the DBA understand the weight of the table in the workload. The larger the numbers are in these columns, the more the DBA should consider adding the table to an accelerator.
By default, the list shows all of the tables that the advisor recommends to offload to Analytics Accelerator to maximize the benefit. The DBA can filter to see tables that are not recommended to be offloaded and tables that are recommended to be removed from the accelerator, as shown in Figure 17.
Figure 17. Filter to view other tables
The Eligible Statements section lists all of the statements in the workload that are eligible to be offloaded to Analytics Accelerator, as in Figure 18. The sum of the values in the Total Estimated Cost column equals the value for the Estimated cost savings from query acceleration (sec) selection in the editor. The sum of the values in the STAT_CPU column equals the value for the Cumulative CPU time (STAT_CPU) savings from the Query Offloading (sec) selection in the editor.
Figure 18. SQL statements in the workload
The Ineligible Statements section lists the statements that cannot be offloaded to Analytics Accelerator, as in Figure 19. The Reason for Ineligibility column specifies why the statements are not eligible.
Figure 19. Statements that cannot be offloaded to Analytics Accelerator
The Rewritable Statements section lists the statements that are currently ineligible but can be rewritten, as in Figure 20. The Reason for Ineligibility column specifies why the statement is ineligible. The DBA can rewrite these statements, run the workload again, and then run the workload advisor again to learn whether the statements are now eligible for offloading.
Figure 20. Statements that can be rewritten to be eligible for offloading
Running a what-if analysis
The DBA sees that the WAAA recommends adding a large number of tables to an accelerator. However, some of the tables are not referenced as often as other tables, and the cumulative CPU cost for accessing them is much smaller relative to the CPU costs associated with the tables that are referenced more often. The DBA wonders whether the estimated CPU savings will decrease significantly if the less important tables are not added to an accelerator.
The DBA clicks Test Candidate Analytics Acceleration in the Workload Test Candidate Analytics Acceleration editor. IOQWT opens a list of tables that the advisor recommends to add to an accelerator. The DBA can, and does, deselect the less important tables then clicks a button to run the WAAA. The results of the test appear in the Review section of IOQWT, just as the WAAA's original recommendations appeared. The test results are organized exactly like the original recommendations. The DBA sees that the estimated savings are close enough to the estimated original savings to exclude the less important tables. The performance would still improve almost as much as the original estimated savings.
Implementing the revised recommendations
The DBA clicks Add Recommended Tables to Accelerator in the editor view, as in Figure 21.
Figure 21. Adding tables to the accelerator
The Add Tables to Accelerator window opens, as in Figure 22, and the DBA selects the accelerator that should run the queries.
Figure 22. Selecting the accelerator to offload the queries
Only one accelerator is configured in the DBA's environment. Even if multiple accelerators are configured, the DBA can select only one accelerator for all of the tables listed. In other words, for one set of recommendations you cannot select some tables to be offloaded to one accelerator and the rest of the tables for another accelerator.
The DBA clicks Add Tables to Accelerator. A confirmation message lets the DBA know when the action is complete. IOQWT created the tables on the accelerator, but the tables are empty. The DBA must load data into them and enable them for query acceleration before the DB2 optimizer will use the tables when calculating access paths.
For example, you can use the Analytics Accelerator Studio (AAS) to load data into the tables and verify that the queries are offloaded and run successfully on the accelerator. This process is described in Using AAS in Data Studio to load tables and enable acceleration.
Scenario 2. Offloading SQL queries from a mixed environment
In this scenario, the DBA wants to help an SQL developer improve an application that is not mission-critical and that has a mixture of OLTP and BI queries. The DBA wants to offload as much CPU usage as possible to an accelerator.
The DBA can capture the workload from catalog plans or packages and set the preference option to allow maximum query offloading in DB2. Then, the DBA can run WAAA as in the previous scenario.
The DBA captures the workload from the plan or packages associated with the application. As shown in Figure 23, the DBA opens the Catalog Plan or Package source type.
Figure 23. Capturing a workload from a package
The DBA creates a new query workload in IOQWT. As in the previous procedure, the DBA clicks Invoke Advisors to load the query workload into the Invoke section of the workflow assistant. The DBA then selects Set Advisor Options in the Invoke section, opens the Workload Analytics Accelerator Advisor section in the editor view, and sets the Current query acceleration option to ELIGIBLE, as in Figure 24.
Figure 24. Setting the current query acceleration to ELIGIBLE
When the DBA sets the query acceleration option to ELIGIBLE, the WAAA will not take the cost of execution into consideration when it determines whether a statement is eligible for offloading. The WAAA can then consider all short-running statements to be eligible for offloading if they do not violate any of the eligibility criteria. However, because the WAAA does not consider whether offloading short-running queries would lower their cost of execution, those queries can take longer to run after they are offloaded to an accelerator.
After setting this option, the DBA updates the statistics for the query workload then runs the WAAA, as described in Running the WAAA.
Scenario 3. Removing tables from an accelerator
In this scenario, which is unrelated to the previous scenarios, assume that the DBA has offloaded all of the tables in the TPC schema from DB2 to Analytics Accelerator. The DBA captures a small number of queries as a query workload, runs the WAAA, and finds that only one query is eligible to be offloaded. The rest of the queries are ineligible.
Because the one eligible query references only four of the tables, the WAAA recommends that the other tables be removed from the accelerator in which they are located, as in Figure 25.
Figure 25. Referenced tables removed from the accelerator
The DBA can export a list of these tables, open AAS in Data Studio, and remove the tables in the exported list.
Using AAS to load tables and enable acceleration
At the end of scenarios 1 and 2, the DBA defined tables on an accelerator. In this section, the DBA uses the AAS plugin for Data Studio to load the tables with data and enable acceleration of the statements in the workload that reference those tables. The DBA has already installed AAS for Data Studio. Resources has information about downloading and installing the plugin.
The DBA takes the following steps to load the tables.
- Open the Accelerator perspective:
- Click Window > Open Perspective > Other. The Open Perspective window opens.
- Select Accelerator, then click OK.
- Load and enable the tables for acceleration:
- In the Administration Explorer, expand the connected subsystem and select the Accelerators folder, as in Figure 26. A list of accelerators is displayed.
- Select the accelerator that you want to use, then click the
Figure 26. Managing the selected accelerator
- In the Accelerator editor, expand the TPC schema.
- Select all of the tables, right click, and select
Load..., as in Figure 27.
Figure 27. Loading the tables onto the accelerator
The Load Tables window opens, as in Figure 28. In this example, one of the tables is partitioned. Detailed information about the partition is shown.
Figure 28. Tables and partitions that will be loaded onto the accelerator
- Accept the defaults then click OK.
Figure 29 shows that the tables are loaded and are automatically enabled for acceleration.
Figure 29. Reviewing the tables enabled for acceleration
- Verify that all offloaded statements run by running the workload again. The results are displayed in the Query Monitoring section of the editor.
After running the workload, the DBA verifies that the performance has improved by running a monitoring tool. There are several monitoring tools that the DBA can use to verify the performance, such DB2 Query Monitor for z/OS or OMEGAMON for Performance Expert for z/OS. (Discussion of the verification process is outside the scope of this article.)
In this article, you followed a fictional DBA to learn about offloading queries to Analytics Accelerator using the Workload Analytics Acceleration Advisor and Analytics Accelerator Studio. The DBA celebrates with colleagues after improving the performance for the workload, beyond expectations, in only a fraction of the estimated required time for the process. We hope that working with Analytics Accelerator, the WAAA, and AAS will give you cause to celebrate, too.
- InfoSphere Optim Query Workload Tuner Information Center: Find what you need to get started, install, configure, and tune IOQWT.
- "IBMDataMag.com: The What, How, and Why of DB2 Accelerators" (IBM data magazine, 2013): Direct answers to the most important questions about DB2 accelerators.
- "Gear up with Analytics Accelerator: IBM DB2 Analytics Accelerator" (IBM data magazine, 2012): Speeding up insights from complex queries.
- Get more detail in these Redbooks®:
- IBM DB2 Analytics Accelerator for z/OS Information Center: Find information that you need to install, configure, maintain, and use IBM DB2 Analytics Accelerator for z/OS Version 4.1.
- Visit the developerWorks Information Management zone to find more resources for DB2 developers and administrators.