O'Reilly logo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Hybrid Analytics Solution using IBM DB2 Analytics Accelerator for z/OS V3.1

Hybrid Analytics Solution using IBM DB2 Analytics Accelerator for z/OS V3.1

by Dino Tonelli, Theresa Tai, Andy Perkins, Cristian Molaro, Ruiping Li, Ravikumar Kalyanasundaram, James Guo, Willie Favero, Paolo Bruni
Publisher: IBM Redbooks
Release Date: September 2013
ISBN: 0738438790
View table of contents
Start reading

Book Description

The IBM® DB2® Analytics Accelerator Version 3.1 for IBM z/OS® (simply called Accelerator in this book) is a union of the IBM System z® quality of service and IBM Netezza® technology to accelerate complex queries in a DB2 for z/OS highly secure and available environment. Superior performance and scalability with rapid appliance deployment provide an ideal solution for complex analysis.

In this IBM Redbooks® publication, we provide technical decision-makers with a broad understanding of the benefits of Version 3.1 of the Accelerator's major new functions. We describe their installation and the advantages to existing analytical processes as measured in our test environment. We also describe the IBM zEnterprise® Analytics System 9700, a hybrid System z solution offering that is surrounded by a complete set of optional packs to enable customers to custom tailor the system to their unique needs..

Table of Contents

  1. Front cover
  2. Figures
  3. Tables
  4. Examples
  5. Notices
    1. Trademarks
  6. Preface
    1. Authors
    2. Now you can become a published author, too!
    3. Comments welcome
    4. Stay connected to IBM Redbooks
  7. Chapter 1. The analytics lifecycle
    1. 1.1 Business analytics lifecycle
    2. 1.2 Lifecycle simplification
    3. 1.3 Overview of the solutions
  8. Chapter 2. Using the Studio client to manage the environment
    1. 2.1 DB2 Analytics Accelerator Studio V3.1 overview
      1. 2.1.1 New features
      2. 2.1.2 Hybrid solution overview
      3. 2.1.3 Data Studio client and web console architecture
    2. 2.2 Installing IBM DB2 Analytics Accelerator Studio
      1. 2.2.1 Installing Accelerator Studio using the product DVD
      2. 2.2.2 Data Studio full client download and installation
      3. 2.2.3 Accelerator plug-ins only installation
      4. 2.2.4 Dump file on Linux
      5. 2.2.5 Accelerator Studio Help menu
    3. 2.3 Configure Studio for tuning
    4. 2.4 Accelerator Studio tasks
      1. 2.4.1 Summary of common tasks and the overall process flow
      2. 2.4.2 Sample screen captures on various activities from the Studio
      3. 2.4.3 How to set the automatic refresh frequency on the Studio
      4. 2.4.4 Tracing
      5. 2.4.5 Tasks new to Accelerator Version 3.1
    5. 2.5 Stored procedures used as administrative interface
      1. 2.5.1 Components used by stored procedures
      2. 2.5.2 DB2 supplied stored procedures used by Studio
      3. 2.5.3 DB2 Accelerator stored procedures used by Studio
    6. 2.6 Stored procedure security
    7. 2.7 Data Studio web console
      1. 2.7.1 Install and configure the Data Studio web console component
  9. Chapter 3. Data latency management
    1. 3.1 Methods of copying data from DB2 to the Accelerator
    2. 3.2 Understanding the load process
      1. 3.2.1 Load streams
      2. 3.2.2 Loading partitions in parallel
      3. 3.2.3 Combining non-partitioned and partitioned table loads
      4. 3.2.4 Data refresh
    3. 3.3 Load performance considerations
      1. 3.3.1 Network considerations
      2. 3.3.2 Source considerations
      3. 3.3.3 Target considerations
    4. 3.4 Automating the load process
    5. 3.5 Summary
  10. Chapter 4. Accelerator resource management
    1. 4.1 Accelerator resource management
    2. 4.2 Accelerator prioritization within a DB2 subsystem
      1. 4.2.1 Query prioritization
      2. 4.2.2 Accelerator data maintenance task prioritization
      3. 4.2.3 Accelerator prioritization summary table
    3. 4.3 Experiments: Prioritization within a DB2 subsystem
      1. 4.3.1 Test environment and measurement preparation
      2. 4.3.2 Test scenario A: Prioritizing one user (query) higher than the others
      3. 4.3.3 Test scenario B: Query prioritization as concurrency increases
      4. 4.3.4 Test scenario C: Mixed workload query and Accelerator table loads
    4. 4.4 Allocating resources with workload isolation for a shared Accelerator
      1. 4.4.1 Workload isolation
      2. 4.4.2 Procedure for setting the workload isolation resource limits
    5. 4.5 Workload isolation experiments
      1. 4.5.1 Test environment and data collection procedures
      2. 4.5.2 Key observations
    6. 4.6 Accelerator resource management checklist
      1. 4.6.1 Resource management checklist
      2. 4.6.2 Details referenced in resource management checklist
  11. Chapter 5. Query acceleration management
    1. 5.1 Query acceleration processing flow
    2. 5.2 Query acceleration settings and enablement
      1. 5.2.1 DSNZPARM settings for accelerated queries
      2. 5.2.2 CURRENT QUERY ACCELERATION special register
      3. 5.2.3 ODBC and JDBC application query acceleration
    3. 5.3 Query acceleration criteria
      1. 5.3.1 The query types that are supported by the Accelerator
      2. 5.3.2 The query functionality limitations
    4. 5.4 How IBM DB2 Analytics Accelerator handles correlated subqueries
      1. 5.4.1 Background of the correlated subqueries
      2. 5.4.2 Correlated subqueries processing in the Accelerator
      3. 5.4.3 Circumventions
    5. 5.5 INSERT from subselect query acceleration processing
    6. 5.6 Profile tables
    7. 5.7 Accelerator EXPLAIN support
    8. 5.8 DB2 Analytics Accelerator hardware considerations
  12. Chapter 6. High-Performance Storage Saver
    1. 6.1 High-Performance Storage Saver design principles
      1. 6.1.1 HPSS overview
      2. 6.1.2 Current restrictions
    2. 6.2 Archive process and operations
      1. 6.2.1 Prepare for archiving partitions and tables
      2. 6.2.2 Batch stored procedure invocation
    3. 6.3 Query processing
      1. 6.3.1 Query execution on the Accelerator with HPSS online archive
      2. 6.3.2 Query execution on Accelerator without HPSS online archive
      3. 6.3.3 Query execution on DB2 without HPSS online archive
      4. 6.3.4 Query acceleration controls
    4. 6.4 Monitoring and instrumentation
    5. 6.5 Restore archived partition from the Accelerator
  13. Chapter 7. Incremental update
    1. 7.1 Overview of incremental update
    2. 7.2 Incremental update architecture
    3. 7.3 Installation and configuration
      1. 7.3.1 Installing the System z components for incremental update
      2. 7.3.2 Installing the Accelerator components for incremental update
    4. 7.4 Defining incremental update between a DB2 subsystem and the Accelerator
    5. 7.5 Defining tables to incremental update
    6. 7.6 Access server
    7. 7.7 z/OS capture agent
      1. 7.7.1 Reading the DB2 log
      2. 7.7.2 Staging changes
      3. 7.7.3 Capture agent considerations
    8. 7.8 Accelerator target agent
      1. 7.8.1 Staging changes
      2. 7.8.2 Applying changes
      3. 7.8.3 About delete processing
      4. 7.8.4 About latency
    9. 7.9 Incremental update scenarios
      1. 7.9.1 Insert only replication
      2. 7.9.2 Mass insert replication
      3. 7.9.3 Delete only replication
      4. 7.9.4 Mass delete replication
      5. 7.9.5 Update only replication
      6. 7.9.6 Multi-table replication
      7. 7.9.7 Concurrent query with replication
    10. 7.10 Summary
  14. Chapter 8. Impact of new Netezza hardware and software components
    1. 8.1 Hardware and software evolution
    2. 8.2 PureData System for Analytics N2001
      1. 8.2.1 PureData for Analytics benefits
      2. 8.2.2 New hardware for PureData System for Analytics
      3. 8.2.3 Scan speed
      4. 8.2.4 The S-Blade components
      5. 8.2.5 Available PureData Systems for Analytics Models
    3. 8.3 Netezza Performance System 7.0.2
      1. 8.3.1 Directed data processing
      2. 8.3.2 Page granular zone maps
    4. 8.4 System 9700 and 9710
    5. 8.5 Query performance measurements
      1. 8.5.1 Scenario #1: I/O bound query
      2. 8.5.2 Scenario #2: CPU bound query
      3. 8.5.3 Scenario #3: Mixed: I/O and CPU bound query
      4. 8.5.4 Scenario #4: Selective query with equal predicates benefitting from zonemaps
      5. 8.5.5 Scenario #5: Directed data processing
      6. 8.5.6 Query performance summary
  15. Chapter 9. Monitoring DB2 Analytics Accelerator environments
    1. 9.1 Monitoring the environment
      1. 9.1.1 The DB2 message DSNX881I
      2. 9.1.2 DB2 commands
      3. 9.1.3 Monitoring the Accelerator using SYSPROC.ACCEL_GET_QUERY_DETAILS stored procedure
    2. 9.2 Monitoring low latency updates
    3. 9.3 Reporting and monitoring using OMPE
      1. 9.3.1 Batch reports
      2. 9.3.2 OMPE classic interface
      3. 9.3.3 Online monitoring using OMPE GUI
      4. 9.3.4 The Accelerator support in the OMPE Performance Database
      5. 9.3.5 The Accelerator support using the new OMPE Spreadsheet Data Generator
    4. 9.4 Cataloging a DB2 for z/OS database as an ODBC data source
  16. Chapter 10. IBM zEnterprise Analytics System 9700
    1. 10.1 Introduction
      1. 10.1.1 zEnterprise Analytics System 9700 configuration
      2. 10.1.2 System 9710 configuration
    2. 10.2 Architectural overview
      1. 10.2.1 zEnterprise Analytics System 9700
      2. 10.2.2 zEnterprise Analytics System 9710
      3. 10.2.3 IBM System Storage DS8870
    3. 10.3 Hardware specification
    4. 10.4 Software overview
      1. 10.4.1 System z software for 9700
    5. 10.5 Network specification
    6. 10.6 Optional software components overview
      1. 10.6.1 Data Analytics Pack
      2. 10.6.2 Data Integration Pack
      3. 10.6.3 Fast Start Services Pack
      4. 10.6.4 DB2 Connect
  17. Chapter 11. High availability considerations
    1. 11.1 Loading data on multiple Accelerators for high availability
      1. 11.1.1 Shared Accelerator considerations
      2. 11.1.2 Disaster recovery considerations
    2. 11.2 High availability configuration with a hot standby Accelerator
      1. 11.2.1 Option 1: Both the Accelerators in the same physical location
      2. 11.2.2 Option 2: Accelerators not in the same physical location
      3. 11.2.3 Observations from a simulated failover
    3. 11.3 CDC considerations for high availability
      1. 11.3.1 High availability setup for the incremental update capture engine
      2. 11.3.2 Accelerator coordinator node failover considerations
  18. Appendix A. Accelerated queries
    1. A.1 Query CPU bound
    2. A.2 Selective query
    3. A.3 Other CPU bound queries
  19. Appendix B. Notes on maintenance
    1. B.1 Preventive maintenance
    2. B.2 What to do if you encounter an Accelerator problem
    3. B.3 Recent fixes
  20. Appendix C. DSNZPARM parameters setting for data warehouse
    1. C.1 Suggested DSNZPARM parameters setting
  21. Appendix D. Tools for Accelerator
    1. D.1 Tools
    2. D.2 OMEGAMON XE for DB2 Performance Expert on z/OS
    3. D.3 DB2 Query Monitor for z/OS
    4. D.4 InfoSphere Optim Query Workload Tuner for DB2 for z/OS
    5. D.5 InfoSphere Optim Configuration Manager
  22. Appendix E. Additional material
    1. Locating the Web material
    2. Using the Web material
  23. Related publications
    1. IBM Redbooks
    2. Other publications
    3. Online resources
    4. Help from IBM
  24. Back cover