June 03, 2014

DB2 Accelerator V4.1 Reaches a New Level

In my previous post I noted how much I got out of the opening keynote session at the recent IDUG NA conference. Another presentation I enjoyed was IBM's Peter Bendel discussing the latest enhancements to the IBM DB2 Analytics Accelerator for z/OS V4.1 (DB2 Accelerator).

I've touted the benefits of DB2 Accelerator numerous times, but I was amazed by what I heard about this latest version. We were told that customers are seeing 2,000X improvement in query performance. Yes, that's 2,000, with three zeros. In one specific example, a customer query that had required three hours of processing could now be run in 6 seconds.

One feature many of us have been waiting for is static SQL support. V4.1 brings some new BIND parameters, and I find some of the options quite intriguing. The first is QUERYACCELERATION, which has values of NONE, ENABLE, ENABLE WITH FAILBACK, ELIGIBLE and ALL. If you're wondering how ENABLE WITH FAILBACK works, it's pretty cool. With this setting, DB2 Accelerator will rebind the statement if it fails during execution on the accelerator and will route it back to run on DB2 without returning an error to the application.

Regardless of which bind option you choose, DB2 will report total failures at the accelerator level for each subsystem using IFCID 002. If enabled, IFCID 378/379 will record any SQL request routed to the DB2 Accelerator. These records will not contain SQL statement text. My understanding is more details are coming soon through maintenance for V4.1. You can also view information about past queries or currently running queries by using the SYSPROC.ACCEL_GET_QUERIES stored procedure. If you don’t want the workload to failback and run on DB2 due to resource constraints, then use BIND option ENABLE. This will force DB2 to fail the execution when an error occurs and return an error status back to the application.

Another new bind parameter is GETACCELARCHIVE (with values of NO and YES).  When set to yes and all the requirements for accessing partitioned archived data are met, the statement will retrieve archived data.

With V4.1, the workload is now balanced across multiple accelerators running on machines connected to z/OS and DB2. The accelerators notify DB2 of its capacity and DB2 will route the query to the most optimal accelerator. This is not a smooth migration. The accelerators must be at V4.1 and the tables need to be redefined to the accelerator.

When replicating data you no longer have to stop all tables when reloading a table. The log reader no longer need to read all log records for all tables, but instead can now filter on relevant log records which reduces overall CPU usage on the mainframe.

V4.1 also includes changes to the high-performance storage server, which allows you to store full tables or table partitions on the DB2 Accelerator rather than a dedicated host. The big change in this release is integrity of the data: when the data is archived and moved to the accelerator, the partition archived is put into a PRO state which will restrict modifications such as inserts and deletes.

There's much more I could cover if I had the time and space, but what do you think? If you've had a chance to use DB2 Analytics Accelerator for z/OS with your static SQL applications, please share in comments your impressions of the new V4.1 features.