DB2 & IMS

Data warehousing and Business Intelligence (BI) have been around as long as DB2 and both concepts have presented implementation challenges. IT professionals have struggled with query performance due to the increasing size of the queries. Sometimes, simply having a query complete was considered a success.

Today, business analytics are no longer considered a “nice to have” with low performance expectations. Data warehousing and business analytics are becoming mission-critical applications with the same Quality of Service (QoS) requirements as traditional Online Transaction Processing (OLTP) workloads. The applications must be reliable, available, and secure—attributes synonymous with System z. Sometimes, there’s also a need to have the analytics workload coexist with traditional OLTP in a mixed workload environment. The ability to run mixed workloads is a major differentiator when determining a warehouse hosting platform.

IBM last year delivered what many thought was an excellent query performance solution for warehousing and analytics, the IBM Smart Analytics Optimizer for DB2 for z/OS Version 1. Although it gave the improved query performance targeted, the developers knew they could do better. The result of this effort is IBM’s new DB2 Analytics Accelerator for z/OS Version 2.1. This new version of the accelerator delivers many requested improvements to the Smart Analytics Optimizer from which it evolved.

The backbone of the Version 2 accelerator, and maybe the most noticeable difference between this new version and the previous accelerator, is its use of the Netezza 1000, an appliance that delivers easy-to-use warehouse agility and performance. The Netezza 1000 also enables the Version 2 accelerator to increase its overall data capacity to 32TB of uncompressed raw data, with the ability to easily expand to a petabyte. However, when a Netezza 1000 is implemented as part of DB2’s query accelerator, the Netezza 1000 “loses” its identity by becoming a component available only for DB2 for z/OS use. This happens because the accelerator appears as a virtual component of DB2 for z/OS.

Connecting With DB2

DB2 consists of multiple resource managers (e.g., Data Manager [DM], Buffer Manager [BM], Relational Data System [RDS], etc.) that perform its various functions. When the accelerator is installed, it essentially becomes one more resource. The difference is that these other resource managers exist as part of the DB2 subsystem and run in DB2 while the accelerator runs on the Netezza 1000.

The accelerator’s subcomponent status is key to how the accelerator processes a query. When a query arrives at DB2, the optimizer determines the best access path. With the accelerator attached, the accelerator becomes an additional access path. If DB2’s optimizer decides the query qualifies for offload to the accelerator, the query is moved to the accelerator and executed. Use of the accelerator is transparent to any task, creating a dynamic SQL statement that could eventually qualify to run on the accelerator. If DB2 decides the query will run more effectively natively on DB2, then the query runs the way dynamic SQL has always executed in DB2. Because the optimizer makes the routing decision, failure of the accelerator isn’t a showstopper. The accelerator is just no longer considered an access path resource and all queries execute natively in DB2 on z/OS without any outage. This is why no program changes are required to take advantage of the accelerator; it’s always the optimizer’s decision (not the application’s) where to run the query.

A significant change in how the accelerator processes SQL from Version 1 is that query blocks are no longer the unit of execution on the accelerator. The entire query will run on the accelerator or natively in the DB2 subsystem and queuing query blocks for single threaded execution are eliminated. The Version 2 accelerator can run multiple queries simultaneously.

Some DB2 SQL query components and characteristics are incompatible with the accelerator and will prevent certain query types from ever being eligible to run on the accelerator. Only read-only dynamic SQL (SELECT SQL) statements are qualified to run on the accelerator. In addition, the SELECT statement can use a cursor defined as scrollable or for rowset processing (multi-row SELECT). Only the DB2 optimizer decides what queries are actually routed to the accelerator.

The Query Data

3 Pages