Parallel query processing and advanced storage-subsystem technologies, together with optimized database management systems, have significantly improved the speed by which data can be accessed and evaluated. However, physical limits with traditional approaches call for more radical steps to satisfy current and future business requirements. BI queries are complex and sometimes hard to tune because of their unpredictable design. Expression on indexes, Materialized Query Tables (MQTs), de-normalization and aggregation techniques, and a large variety of tools and features are available for DBAs’ use. The IBM Smart Analytics Optimizer reduces the need for complex query tuning. Designed to deploy and scale with ease, IBM Smart Analytics Optimizer is an integrated, sustainable solution that can take your existing BI environment to a whole new level of performance.
In today’s data processing environment, mixed with OLTP and BI workloads, some query patterns can’t be executed immediately because of excessive resource consumption. They’re scheduled for a less busy time interval. With IBM Smart Analytics Optimizer, those patterns can be reconsidered for a production environment. Real-time, slice-and-dice “cube applications” can also benefit from the IBM Smart Analytics Optimizer and speed up analysts’ tasks.
The Technical Solution
OLAP queries typically scan large amounts of data—from gigabytes to terabytes—to come up with answers to business questions. These questions have been transformed to SQL and passed to DB2 for z/OS, typically involving dynamic SQL. In traditional environments, DBAs, application programmers, IT architects, and system engineers have done a tremendous job of tuning their environments. But the challenge is still coming into your system as ad hoc queries scanning huge amounts of data and consuming large amounts of resources, both in CPU and I/O capacity.
Usually, these queries can’t be screened before they’re submitted to the system, resulting in entirely unknown resource consumption. The spinning speed of DASD devices becomes a limiting factor when scanning terabytes of data. For the moment, we can simply look at these limits as dictated by the laws of physics. The Smart Analytics Optimizer addresses this unknown resource consumption and the need for faster ad hoc OLAP queries.
A research project called Blink (see www.almaden.ibm.com/cs/projects/blink/) laid the foundation for development of the Smart Analytics Optimizer. With Blink, IBM developed a solution to provide consistent query response times for a given amount of data, regardless of the structure of a query accessing the data. Achieving that performance-oriented goal required implementing leading technology trends: hybrid row/column stores in main memory and predicate evaluation on compressed data, combined with multi-core and vector-optimized algorithms.
The IBM Smart Analytics Optimizer is like an appliance to the extent that it adds another Resource Manager to DB2 for z/OS, just like the Internal Resource Locking Manager (IRLM), Data Manager (DM), or Buffer Manager (BM). It’s a highly integrated solution and the data continues to be managed and secured by the reliable database platform, DB2 for z/OS. No changes are required to existing applications; the applications don’t have to be aware of its existence to benefit from the capabilities it offers.
Whenever queries are eligible for processing by the IBM Smart Analytics Optimizer, users will immediately benefit from shortened response times without any further actions. Both users and applications continue to connect to DB2 for z/OS while being entirely unaware of the IBM Smart Analytics Optimizer’s presence. Instead, the DB2 for z/OS optimizer is aware of an IBM Smart Analytics Optimizer’s existence in a given environment and can execute a given query either on the IBM Smart Analytics Optimizer or by using the already well-known access paths in DB2 for z/OS. Due to cost-based decisions for any query-routing, all queries are executed in their most efficient way, regardless of their type (OLAP vs. OLTP).
The IBM Smart Analytics Optimizer consists of a specific number of blades attached to a System z. Each IBM Smart Analytics Optimizer consists of a large number of processors and main memory that can hold up to thousands of gigabytes of raw data. The number of blades connected gives you the total real storage and processing capacity for the IBM Smart Analytics Optimizer. There’s no communication with the IBM Smart Analytics Optimizer from anywhere outside your System z machine; it’s fully integrated into System z technology and fenced from the outer world. Even if it’s physically located outside a System z chassis, the solution is designed to be extremely robust and to guarantee the established Service Levels Agreements (SLAs) (see Figure 1).