Static SQL Support a Key Feature of DB2 Accelerators V4.1

Last fall IBM announced DB2 Analytics Accelerator (DB2 Accelerators) for z/OS Version 4.1. In contrast to previous releases, which supported a limited SQL syntax (and only for dynamic SQL), V4.1 enables a wide array of queries with static SQL support. I've written frequently about DB2 Accelerators, and from what I've seen on recent customer visits, I believe that the enhancements in V4.1 are contributing to increased adoption of this product.

Here's a quick list of other new features:

·         Support for EBCDIC and Unicode in the same DB2 system and accelerator.

·         Support for multi-row FETCH

·         Improved scalability workload balancing through automated routing, based on workload balancing information, that evenly distributes the workload across the attached accelerators.

·         Improved high availability through automated workload balancing failover support. If, for any reason, one accelerator is unavailable, queries are automatically rerouted to available accelerators.

·         Increased number of replication sources per accelerator: It's now possible to replicate from up to 10 different subsystems into a shared accelerator.

·         Better performance for incremental update through Instrumentation Facility Interface (IFI) filtering, which is designed to lower MIPS utilization and improve performance because fewer log records need to be processed by the capture component.

·         Richer monitoring: The accelerator now provides expanded information for monitoring applications with the IFI. The IFI makes this data available to software vendors who can leverage this broader information to better inform customers about the usage, execution and concurrency of queries as they are processed in the accelerator.

·         Significant enhancements to the High Performance Storage Saver (HPSS) solution:

o    Support to update historical read-only record through a stored procedure;

o    Support to restore individual partitions without requiring any outages;

o    The capability to enforce read-only state on a partition used by HPSS; and,

o    Backup image maintenance can be simplified by marking HPSS partitions as "undeletable."

·         Simplified maintenance and lower cost of operation. V4.1 now offers an automated NZKit Install that facilitates the fully automatic installation and activation of Netezza kits, with no firmware or operating system updates required.

·         Support for DB2 for z/OS version 11.

If you’re using this technology, please share some of your experiences, pros and cons, in comments.

The Impact of DB2 Analytic Accelerator

I recently learned about a new IBM Redbook titled, “Hybrid Analytics Solution using IBM DB2 Analytic Accelerator for z/OS V3.1.”

Table of Contents

Chapter 1. The analytics lifecycle
Chapter 2. Using the Studio client to manage the environment
Chapter 3. Data latency management
Chapter 4. Accelerator resource management
Chapter 5. Query acceleration management
Chapter 6. High-Performance Storage Saver
Chapter 7. Incremental update
Chapter 8. Impact of new Netezza hardware and software components
Chapter 9. Monitoring DB2 Analytics Accelerator environments
Chapter 10. IBM zEnterprise Analytics System 9700
Chapter 11. High availability considerations
Appendix A. Accelerated queries
Appendix B. Notes on maintenance
Appendix C. DSNZPARM parameters setting for data warehouse
Appendix D. Tools for Accelerator
Appendix E. Additional material

 

If you work on OLTP systems and haven't been involved with the data warehouse side of the business, you're in for a surprise. The business is changing, and tools like the DB2 accelerators and Netezza can help you do your job. Chapter 1 nicely lays out the need to push analytics closer to the source data. 

Note that Version 4.1 of the IBM DB2 Analytic Accelerator for DB2 for z/OS is set for Nov. 29 general availability. Per IBM's announcement letter, here are some key benefits of the product:

·         Significantly improves query performance and execution time

·         Minimizes data latency, enabling near real-time applications

·         Lowers the cost of storing, managing, and processing historical data

·         Reduces capacity requirements for complex query workloads

While the Redbook (which was published in late September), doesn't cover the latest version of the product, it will give you a better understanding of IBM DB2 Analytic Accelerators and their impact on applications.

Managing IDAA with Data Studio

Recently, I downloaded and installed Data Studio 4.1 with the IBM DB2 Analytics Accelerator (IDAA) plug-in. I was going to write about using this solution to create a database connection, but it turns out that this topic is already well covered by David Simpson. Check out this terrific presentation he gave to the Baltimore/Washington DB2 User Group.

While David doesn't get into the features of IDAA, you'll learn a lot about the basics of using Data Studio with DB2 for z/OS. Page 14 lists the parameters needed to create the database connection, though there are actually several ways to find this information. One option is to look at the DB2 master address started task and do a find on "DDF." You can also use the DB2 command: –DIS DDF.

Output from –DIS DDF command with values changed to match page 14.

DSNL080I  !DA0G DSNLTDDF DISPLAY DDF REPORT FOLLOWS:          

DSNL081I STATUS=STARTD                                         

DSNL082I LOCATION            LUNAME                           GENERICLU       

DSNL083I DA1B                    USILDA01.DA1BDB2       USILDA01.DA0GPTIB

DSNL084I TCPPORT=9500  SECPORT=9501  RESPORT=9502  IPNAME=-NONE

DSNL085I IPADDR=::140.212.64.35                                

DSNL086I SQL    DOMAIN=p390.themisinc.com                        

DSNL086I RESYNC DOMAIN= p390.themisinc.com                       

DSNL089I MEMBER IPADDR=::140.212.64.35                        

DSNL099I DSNLTDDF DISPLAY DDF REPORT COMPLETE

Once Data Studio is installed and a database connection to a subsystem with an accelerator is established, you can start managing the accelerators as well as DB2 tables that use the accelerator with Data Studio. Here are some relevant resources:

* The presentation, "Deep Dive into IBM DB2 Analytics Accelerator Query Acceleration."

* This blog. Over the past year-plus, I've written three posts on IDAA. They're listed here.

* Data Studio help: Just select HELP and enter ACCELERATOR for a list of topics.

Data Studio software can be downloaded here. Be sure to pick the option that includes IDAA and other third-party product extensions. This will allow you to access the accelerator features.

 

IBM Hosts a Big Day for Big Data

I was honored to be invited to IBM Almaden research center in San Jose, California, for a briefing on IBM’s Big Data launch earlier this month. Hundreds of DB2 community luminaries -- including Information Champions, analyst and DB2 Gold Consultants -- were brought together to absorb all this information and then share it with the IT industry through their blogs, articles and tweets.

I learned a lot about what is going on in the distributed world from the discussion of tools like Hadoop as well as the latest DB2 for LUW release. DB2 for LUW 10.5 features a technology called BLU, which supports data stored in columnar format rather than row format. That is, the data page contains data for a given column where row format is rows of data that are stored on a page of data. This column format can dramatically increase the speed of analytic queries.

Of course, with my focus on the mainframe, I wanted to learn more about big data on z/OS. The starting point here was the latest release of DB2 Analytics Accelerator (IDAA) for z/OS, IDAA V3.1.

Prior to IDAA V3.1, data had to be stored on the local host and replicated to the IDAA (Netezza). However, IDAA V3.1 features a High-Performance Storage Saver (HPSS), which allows you to store a DB2 table partition or full table of data solely on the accelerator, reducing the need for host storage. This feature removes the requirement for data to be replicated in DB2 storage and Accelerator storage. Tables can now be divided between traditional database resident partitions on DB2 for z/OS and HPSS on the IDAA.

What are your company's plans for big data? How do you see big data impacting your job as a DBA? Please share your thoughts 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).

 

A Closer Look at the IDAA

IBM has come out with a draft Redbook on the DB2 Analytics Accelerator (IDAA). Having written about the IDAA last year, I'm really looking forward to reading this new IBM publication.

"Optimizing DB2 Queries with IBM DB2 Analytics Accelerator for z/OS" examines the IDAA and details how to manage data warehousing queries on z/OS with Netezza. The publication features a high-level overview of the technology; then the process of installing and integrating the IDAA with your current DB2 10 for z/OS environment is explained in depth.

Here's the table of contents:

Part 1. Business analytics with DB2 for z/OS
Chapter 1. Data warehousing on System z
Chapter 2. The DB2 for z/OS integrated solution

Part 2. Sample DB2 Analytics Accelerator implementation
Chapter 3. The business scenario
Chapter 4. Feasibility study
Chapter 5. Configuration and installation
Chapter 6. WLM settings for DB2 Analytics Accelerator
Chapter 7. Monitoring DB2 Analytics Accelerator environments
Chapter 8. Operational considerations
Chapter 9. Using Studio client to define and load data
Chapter 10. Query acceleration management
Chapter 11. Latency management
Chapter 12. Performance considerations
Chapter 13. Security considerations

Part 3. Additional topics
Chapter 14. Analytics and reporting on System z
Chapter 15. Data sharing and disaster recovery
Appendix A. Recommended maintenance
Appendix B. Additional material

I’ll soon have a chance to work with this technology and plan to write about some of my findings. What do you think? What does it take to manage this environment on a daily basis? If you have experience with the IDAA, please register your thoughts in Comments.