Redbooks Examines DB2 Analytics Accelerator 4.1

Recently I shared my impressions of the latest version of IBM DB2 Analytics Accelerator. Now I'm here to let you know that there's a new Redbook that details the features found in Version 4.1.

"Reliability and Performance with IBM DB2 Analytics Accelerator 4.1" is currently a draft publication. The formal publishing date is set for the end of July. I'll list just a few highlights:

* Chapter 5 examines new query accelerator features, including static SQL support as well as support for “NOT IN <subquery>” and many other new sub-query predicates. Numerous customers I talk to believe that their current workloads aren't large enough to run cost-effectively on DB2 accelerators. However, with the recent query enhancements, it may be time to reconsider this. Use the monitoring tools and you may discover that SQL statements that previously couldn't run on the accelerator now can.

* Chapter 6 provides a look into the primary factors that affect throughput of loading data into the accelerators -- partitions and table number and sizes; processors (number and speed), and; Netezza host. The chapter goes through different scenarios to help you understand your database design and the best way to load the data.

* Chapter 7 covers online data archiving with the High-Performance Storage Saver.  When archiving occurs, the table space partition is put into a persistent read mode with a status of PRO. The process to archive and restore is handled by two separate stored procedures. However, I'm not sure how to remove the archived partition when you no longer want to save it on both DB2 and the accelerator. Hopefully the process of purging old data from the accelerator will be clarified in the final version of the Redbook.

* Chapter 9 is devoted to monitoring enhancements. Having spent most of my career tuning SQL, I find this information extremely interesting. There are several options for monitoring the accelerator: DB2 Accelerator Studio, DB2 commands, query history tables and DB2 monitoring tools from IBM or third-party vendors.

The DB2 command –DIS ACCEL(name) DETAIL provides information about active and queued queries, wait times and disk storage utilization for the named accelerator as well as totals for all subsystems connected to the named accelerator.

As for real-time monitors, IBM continues to add information to the standard interfaces it and third-party vendors use to monitor DB2 activity. The first set of information allowed customers to see what is happening on the Netezza box. The next set of information provided more insight into which statements are running on the accelerator and how much was saved from general DB2 processing. In addition to monitoring utilization levels, customers need to know that the statements they create are capable of running on the accelerator. Making this information available real-time via the monitoring tools adds considerable value to this product.

Here's the complete table of contents:

Chapter 1. The analytics lifecycle

Chapter 2. Database design considerations

Chapter 3. Overview of IBM DB2 Analytics Accelerator V4.1

Chapter 4. Installation and maintenance procedures

Chapter 5. Query enhancements

Chapter 6. Load and incremental update

Chapter 7. Online data archiving

Chapter 8. High availability, disaster recovery, and workload balancing

Chapter 9. Monitoring enhancements

Chapter 10. The Accelerator Loader

Appendix A. Preparing input data for Accelerator Loader

I feel this publication is well worth your time. If you read it, please share your thoughts in comments.

Are DBAs Still Needed?

Since IBM unveiled its big data strategy, there's been an interesting response. Some have focused on the role of the DBA, wondering if advancements like IBM Data Analytics Accelerator for z/OS (IDAA) and Netezza, which are designed to eliminate concerns over index design when storing data, make DBAs obsolete.

IBM's Susan Visser went so far as to organize a "tweetchat" on this topic: "Is the role of the DBA dead?"

As Susan notes, the debate was informative, but ultimately, it wasn't much of a debate. That's because everyone agreed that DBAs are still absolutely needed. While I wasn't able to participate in the Twitter debate myself, here's my take: We still need DBAs to understand the target system and the best physical design for the given system. Sure, since Netezza doesn't use indexes, you don’t need to worry about what index to create on the table. However, you still need to understand the physical design techniques for the Netezza system.

For more on the enduring importance of DBAs, see this post by Bjarne Nelson.

Have you tackled a performance problem on the Netezza system? If you're a DBA with experience on Netezza, I'd like to hear from you in comments.

DB2 Commands for IDAA

Last month I wrote about IBM's just-released draft Redbook on the DB2 Analytics Accelerator. Since then, I've researching the IDAA more closely.

Here's a quick list of IDAA commands:

-DISPLAY THREAD: The DISPLAY THREAD command has been enhanced to show threads that are executing on an accelerator. For example, to view all active threads running on an accelerator, issue this command:

                -DIS THREAD(*) ACCEL(*)

 

Page 45 of the draft Redbook I cited -- "Optimizing DB2 Queries with IBM DB2 Analytics Accelerator for z/OS" -- displays the output and provides a detailed explanation.

-CANCEL THREAD: The CANCEL THREAD command can now be used to stop the processing of a thread that's executing on IDAA. (Note: You must apply maintenance PM54383 to enable the CANCEL THREAD command to support IDAA.)

-DISPLAY ACCEL: The DISPLAY ACCEL command returns a list of accelerators within a DB2 subsystem. It also provides detail status for a specific member or all members of a data sharing group. 

-START ACCEL(*): The START ACCEL command is used to startup all accelerators. ACCEL(*) within a subsystem or can be used to start a specific accelerator using ACCEL(name).

-STOP ACCEL(*): The STOP ACCEL command is used to stop all accelerators ACCEL(*) defined within a subsystem or a specifically named accelerator -- e.g., ACCEL(name).

Details about all the parameters available for these commands can be found in the DB2 10 for z/OS Command Reference (SC19-2972-04).

 

IBM Introduces IDAA to Speed Analytics

Not that long ago, running a large report typically took a few hours, and that was fine. But given the critical role that business intelligence now plays in making business decisions, time is a luxury.

Reading this explanation of BI, I was reminded of some of my work in the area of tuning decision support queries. These queries eventually ran against a separate copy of the data known as a data warehouse to avoid impacting the operational data.

As the need to run analytic queries against operational data becomes greater, the fact that these queries are often still long-running (and can provide unpredictable response times) becomes a bigger issue. To streamline the process, DBAs are asked to analyze and tune the SQL, which of course, besides being time-consuming for us, doesn't always produce favorable results anyway. And when companies don't get favorable results, they may look to other platforms to provide their analytics.

Last year, IBM is came out with IBM Smart Analytics Optimizer (ISAO) for DB2 for z/OS V1.1, which ran on z/Enterprise 196 with zBX hardware. Now just 15 months later, IBM has unveiled IBM DB2 Analytics Accelerator for z/OS V2.1. IDAA runs on IBM zEnterprise 196 or 114 with IBM Netezza 1000-3 hardware. Simply put, this solution is designed to provide faster response times for long-running, unpredictable queries. IBM also touts it as a cost-saver, because it's intended to save DBAs from implementing expensive SQL tuning efforts that can require new indexes or materialized query tables (MQTs).

Like the zIIP engines, the IDAA is a workload-optimized platform designed for special purpose processing. As such, you don't need to change your application to take advantage of it.

If you have the opportunity to work with IDAA, please post a comment so others can benefit from your experience.