Accelerate queries with IBM DB2 Analytics Accelerator for z/OS by using InfoSphere Optim Query Workload Tuner

The IBM® DB2® Analytics Accelerator for z/OS® is a technical solution that runs certain types of SQL queries on a high-performance appliance on behalf of DB2 for z/OS. It can be challenging to determine which tables and SQL statements should be offloaded to the Analytics Accelerator, and to estimate CPU savings and performance improvement, before implementing the offload. With detailed examples, this article explains how you can meet these challenges by using InfoSphere® Optim™ Query Workload Tuner and Analytics Accelerator Studio and quickly improve the performance of your workloads.

Thuan Bui (tqbui@us.ibm.com), Technical Enablement Specialist, IBM

Author photoThuan Bui has worked more than 25 years for IBM in development, quality assurance, customer support, performance, and management positions in database and data management technology. He currently works on the IBM Optim Solutions Technical Enablement team at Silicon Valley Lab, California.



Robert Heath (heathr@us.ibm.com), Information Developer, IBM

  Robert HeathRobert Heath has been with IBM for 13 years and is the information developer for InfoSphere Optim Query Workload Tuner.



Leif Pedersen (pedersen@dk.ibm.com), Solutions Architect, IBM

Photo of Leif pedersenLeif Pedersen, a solutions architect on the Optim Technical Enablement Team at the Silicon Valley Laboratory, has worked with database technology for more than 25 years. One of his main areas of expertise is DB2 for z/OS performance and Query optimization. Currently Leif helps customers with the Optim products such as Query Workload Tuner, pure Query, Optim Performance Manager, Optim configuration Manager, and Optim Query Capture Replay.



Chi Man Sizto (jsizto@us.ibm.com), Advisory Software Developer , IBM

Chi Man SiztoChi Man Jason Sizto is an advisory software developer in the Optim Query Workload Tuner (OQWT) team at the Silicon Valley Laboratory. He is responsible for the Statistics Advisor and IBM DB2 Analytics Accelerator Advisor under OQWT. Previously, Jason was a developer for DB2 for Linux, UNIX, and Windows (DB2). He has more than 10 years of experience in DB2 query optimization and performance.



20 March 2014

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.

Frequently used acronyms

  • AAS: Analytics Accelerator Studio
  • DBA: Database administrator
  • DRDA: Distributed relational database architecture
  • IOQWT: InfoSphere Optim Query Workload Tuner
  • QM: Query Monitor
  • SQL: Structured Query Language
  • WAAA: Workload Analytics Acceleration Advisor
  • WSA: Workload Statistics Advisor

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
Applications send queries to DB2. If query acceleration is enabled, and the query qualifies for acceleration, DB2 converts the query into Netezza syntax and routes it to Analytics Accelerator. Analytics Accelerator runs the query and sends the result to DB2, and DB2 returns the result to the application.

Learn about DB2 Analytics Accelerator for z/OS features, case studies, resources, and more.

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 4.1.0.1 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 4.1.0.1 and captures the query workload by taking the following steps.

  1. Connect to the subsystem by right clicking the connection to it and select Connect in the Data Source Explorer, as in Figure 2.
    Figure 2. Connecting to the database
    Connect to the database by right-clicking the database and selecing Connect
  2. 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
    Open the Workflow Assistant
  3. Create a new filter:
    1. 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
      Create a new filter
    2. Name the new filter. In this case, the DBA names the filter tpcd_app.
    3. Specify criteria for the filter. In the PROGRAM_NAME field the DBA specifies DSNTEP4 as 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
      Filter out other SQL statements

      Click to see larger image

      Figure 5. Filtering out the SQL statements

      Filter out other SQL statements
    4. Click Finish.
  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.
  5. Capture the SQL by clicking Capture Now.
    Figure 6. Capturing the SQL statements
    Capture the SQL statements

    SQL statements from the DSNTEP4 package are listed, including detailed information about each statement.

  6. Create the query workload:
    1. Click Create Query Workload from All Statements..., as in Figure 7.
      Figure 7. Creating a workload from all SQL statements
      Create a workload from all SQL statements
    2. Name the workload. In this case, the DBA names the workload TPCD_sample_workload, as in Figure 8.
      Figure 8. Naming the workload
      Name the workload

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 4.1.0.1 Information Center.)

Running the WAAA

After running the recommended RUNSTATS statements, the DBA can run the WAAA using the following steps.

  1. In the Query Tuner Workload Assistant, open the Manage section.
  2. In the Manage Workloads editor, click Invoke Advisors, as in Figure 9.
    Figure 9. Invoking the workload advisors for the workload list
    Invoke the workload advisors for your workload list
  3. 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.
  4. Click Select What To Run....
    Figure 10. Opening the Select Activities window
    Open the Select Activities window

    The Select Activities window opens.

  5. Select the Analytics Acceleration option then click OK, as in Figure 11.
    Figure 11. Selecting Analytics Acceleration activity
    Select the Analytics Acceleration activity

    The Collect EXPLAIN Information window opens so that you can collect the statistics.

  6. Click Start EXPLAIN, as in Figure 12.
    Figure 12. Starting the EXPLAIN tool
    Start 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
Review the recommendations that the advisor provides

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
Use the editor view to 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
Review the 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
Review the list of tables that are 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
You can filter to view other tables that are not recommended for offloading and tables that should be removed from the accelerator

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
Review the statements that run 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
Review the 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
Review the statements that you can rewrite to make them 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
Add the 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
Select the accelerator that should be used 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
Capture 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
Set 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 4 referenced tables should be removed from the accelerator, according to the workload advisor.

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.

  1. Open the Accelerator perspective:
    1. Click Window > Open Perspective > Other. The Open Perspective window opens.
    2. Select Accelerator, then click OK.
  2. Load and enable the tables for acceleration:
    1. In the Administration Explorer, expand the connected subsystem and select the Accelerators folder, as in Figure 26. A list of accelerators is displayed.
    2. Select the accelerator that you want to use, then click the Manage icon.
      Figure 26. Managing the selected accelerator
      Manage the selected accelerator
    3. In the Accelerator editor, expand the TPC schema.
    4. Select all of the tables, right click, and select Load..., as in Figure 27.
      Figure 27. Loading the tables onto the accelerator
      Load the tables that you want to load 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
      Review the tables and partitions that will be loaded onto the accelerator.
    5. 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
    Review the tables that are enabled for acceleration.
  3. 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.)


Summary

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.

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=966209
ArticleTitle=Accelerate queries with IBM DB2 Analytics Accelerator for z/OS by using InfoSphere Optim Query Workload Tuner
publish-date=03202014