Blogs

Accelerating Analytics Queries

Facilitate immediate insight using DB2 Analytics Accelerator for z/OS with DB2 and InfoSphere Optim database tools

The preponderance and variety of data available to today’s enterprises open up tremendous opportunities across many industries. Organizations increasingly have access to a treasure trove of structured and unstructured data that can lead to keen insights on aspects of the business to help them remain competitive. Moreover, the capability to derive the insight necessary to rapidly make critical business decisions is where analytics come into play. And an essential component of analytics processing is query workload execution. Getting information increasingly faster than was possible before and enhancing the performance of query execution to meet immediate demands is creating new challenges.

The IBM® DB2® Analytics Accelerator for z/OS® high-performance appliance enables offloading certain types of SQL queries on behalf of IBM DB2 for z/OS. DB2 Analytics Accelerator for z/OS is designed to deliver extremely fast results for complex and data-intensive queries for data warehouse, business intelligence (BI), and analytics workloads.

Many organizations face the challenges associated with having to manually analyze a workload and its query access paths to determine which set of SQL statements should be offloaded along with the related tables that should be added to an analytics accelerator. In addition, organizations need to estimate processor cost savings and performance improvement that can be achieved before implementing the offload. DB2 Analytics Accelerator for z/OS can be deployed to accelerate query execution, and organizations can also take advantage of this acceleration with integration of IBM DB2 and InfoSphere® Optim™ data lifecycle management tools.

Tuning queries

IBM InfoSphere Optim Query Workload Tuner for DB2 for z/OS Version 4.1.0.1 offers expert tuning advice for query workloads across DB2 for z/OS environments, including advanced expert advice for accelerating queries. Optim Query Workload Tuner can be used to capture a workload from various sources, run the accelerator advisor for analysis, and then receive its recommendations. The accelerator advisor provides the list of tables to be added or deleted from the accelerator and the SQL statements that are eligible for offload along with the estimated cost savings from query acceleration (see Figure 1).

Accelerating analytics queries – figure 1
 
Figure 1. Recommendations from the analytics accelerator advisor
 
The advisor also provides a list of statements and the reasons they’re not eligible for acceleration, along with statements that could be rewritten for acceleration eligibility. It can also be used to iteratively run a what-if analysis by removing some tables from the recommendations and assessing the impact of changes based on the estimated cost savings.

After tables are added to the accelerator through the accelerator advisor in Optim Query Workload Tuner, IBM DB2 Analytics Accelerator Studio can be used to load data to the tables. Analytics Accelerator Studio is a complimentary add-in for IBM Data Studio, an Optim integrated development tool that helps database developers and administrators create and manage database environments for increased productivity and collaboration. It can also be used for some basic administration for the accelerator and to verify that the statements are routed and run successfully on the accelerator (see Figure 2).

Accelerating analytics queries – figure 2
 
Figure 2. Information on the DB2 Analytics Accelerator Studio panel

Part of the steps for enabling acceleration in DB2 requires setting the ACCEL zParm to turn on the accelerator and also setting the special register CURRENT QUERY ACCELERATION to allow DB2 to offload the queries. With the capabilities of centralized management of database and client configurations, InfoSphere Optim Configuration Manager allows DBAs to easily change this zParm and dynamically set this special register for a particular application to control the granularity of the setting’s impact. These settings enable DBAs to isolate and validate the acceleration to an individual application before making it effective globally, and without making any changes to the applications.

Optimizing analytics acceleration

In addition to InfoSphere Optim database tools, DB2 data management tools can be used to further maximize analytics acceleration. The DB2 Object Comparison Tool for z/OS, an extension to the DB2 Administration Tool for z/OS Version 11.1, provides comprehensive accelerator management capabilities, including adding and removing accelerators and an option to reload data through change management.

DB2 Query Monitor for z/OS provides capabilities to monitor, identify, and segregate accelerated and non-accelerated queries. This tool can be used to measure and compare performance for the same queries—with and without acceleration.

Beyond monitoring and analysis capabilities for DB2 for z/OS and applications, the IBM Tivoli® OMEGAMON® XE for DB2 Performance Expert on z/OS tool can be used to generate extensive performance reports. This information offers insight on the behavior of the accelerated queries and appliance utilization, which helps validate the return on investment for acceleration and identify performance trends.

In addition, the recent IBM Analytics Accelerator Loader for z/OS provides flexible capabilities and options to quickly load or refresh DB2 or non-DB2 data, either directly into the DB2 Analytics Accelerator or DB2 for z/OS, or both in parallel. Either way, the load or refresh takes place with minimal application impact while helping reduce processor consumption and elapsed time.

Database administrators can capitalize on DB2 Analytics Accelerator for z/OS, DB2, and InfoSphere Optim database tools to help optimize and accelerate analytic queries to meet the ever-increasing velocity of business insight needs. Please share any thoughts or questions in the comments.

[followbutton username='IBMdatamag' count='false' lang='en' theme='light']