Reliability and Performance with IBM DB2 Analytics Accelerator V4.1

Reliability and Performance with IBM DB2 Analytics Accelerator V4.1

Released September 2014
Publisher(s): IBM Redbooks
ISBN: None

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

The IBM® DB2® Analytics Accelerator for IBM z/OS® is a high-performance appliance that integrates the IBM zEnterprise® infrastructure with IBM PureData™ for Analytics, powered by IBM Netezza® technology. With this integration, you can accelerate data-intensive and complex queries in a DB2 for z/OS highly secure and available environment.

DB2 and the Analytics Accelerator appliance form a self-managing hybrid environment running online transaction processing and online transactional analytical processing concurrently and efficiently. These online transactions run together with business intelligence and online analytic processing workloads.

DB2 Analytics Accelerator V4.1 expands the value of high-performance analytics. DB2 Analytics Accelerator V4.1 opens to static Structured Query Language (SQL) applications and row set processing, minimizes data movement, reduces latency, and improves availability.

This IBM Redbooks® publication provides technical decision-makers with an understanding of the benefits of version 4.1 of the Analytics Accelerator with DB2 11 for z/OS. It describes the installation of the new functions, and the advantages to existing analytical processes as measured in our test environment. This book also introduces the DB2 Analytics Accelerator Loader V1.1, a tool that facilitates the data population of the DB2 Analytics Accelerator.

Table of contents

  1. Front cover
  2. Tables
  3. Examples
  4. Figures
  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 Why use analytics
    2. 1.2 What analytics is
    3. 1.3 What this has to do with data and with System z
    4. 1.4 What business-critical analytics is
    5. 1.5 Why use System z for analytics
    6. 1.6 Business usage scenarios
      1. 1.6.1 Rapidly accelerate business-critical queries
      2. 1.6.2 Derive business insight from z/OS transaction systems
      3. 1.6.3 Reduce IT sprawl for analytics initiatives
      4. 1.6.4 Improve access to historical data and lower storage costs
    7. 1.7 zEnterprise Analytics Systems 9700 and 9710
  8. Chapter 2. Database design considerations
    1. 2.1 DB2 Analytics Accelerator data distribution strategy
      1. 2.1.1 Distribution and performance
      2. 2.1.2 Joins and distributions
      3. 2.1.3 General suggestions
      4. 2.1.4 Example of distribution strategy
    2. 2.2 Zone maps (automatic partitioning)
      1. 2.2.1 Organization keys (explicit clustering)
  9. Chapter 3. Overview of IBM DB2 Analytics Accelerator for z/OS V4.1
    1. 3.1 Introduction of DB2 Analytics Accelerator for z/OS V4.1
      1. 3.1.1 What's new in version 4.1
    2. 3.2 SQL enhancements
      1. 3.2.1 Static SQL
      2. 3.2.2 ROWSET query offload and MRF for local applications
      3. 3.2.3 Additional DB2 functions and data types
    3. 3.3 Workload management
      1. 3.3.1 Workload balancing across multiple accelerators
      2. 3.3.2 IBM Worlkload Manager support for local applications
      3. 3.3.3 Mapping WLM to DB2 Analytics Accelerator
    4. 3.4 Incremental update
      1. 3.4.1 New architecture
      2. 3.4.2 Improved usability (load tables while replication continues)
      3. 3.4.3 Improved performance (assured resource allocation for Incremental Update)
      4. 3.4.4 IFI filtering
    5. 3.5 High Performance Storage Saver enhancements
      1. 3.5.1 Archiving enhancements
      2. 3.5.2 Restore archived partitions
      3. 3.5.3 Archive a table on multiple accelerators
    6. 3.6 Monitoring (new counters including incremental update)
    7. 3.7 Installation, operations, and maintenance
      1. 3.7.1 Integrated NPS installation
      2. 3.7.2 Multiple code page support in DB2 Analytics Accelerator
      3. 3.7.3 Fine-grained access of DB2 Analytics Accelerator control functions
      4. 3.7.4 DB2 Analytics Accelerator Studio functional and usability enhancements
      5. 3.7.5 Sub-capacity licensing
    8. 3.8 Updates in DB2 Analytics Accelerator V4.1 PTF-2
    9. 3.9 Updates in DB2 Analytics Accelerator V4.1 PTF-3
    10. 3.10 DB2 Analytics Accelerator tools
      1. 3.10.1 DB2 Administration Tool V11.1
      2. 3.10.2 DB2 Analytics Accelerator Loader V1.1
      3. 3.10.3 InfoSphere Optim Query Workload Tuner V4.1.0.1
      4. 3.10.4 Tivoli OMEGAMON XE for DB2 Performance Expert V511 and V520
      5. 3.10.5 DB2 Query Monitor for z/OS V3.2
  10. Chapter 4. Installation and maintenance procedures
    1. 4.1 Upgrading DB2 Analytics Accelerator to version 4.1
      1. 4.1.1 Checking the prerequisites
      2. 4.1.2 Installing Netezza Host Platform and Netezza Firmware Diagnostics and Tools
      3. 4.1.3 Estimating the upgrade time window
      4. 4.1.4 Installing DB2 Analytics Accelerator
      5. 4.1.5 Rolling migration in an HA and disaster recovery environment
    2. 4.2 Optional migration steps
      1. 4.2.1 The workload management definitions for query prioritization
      2. 4.2.2 The priority setting of accelerator maintenance tasks
      3. 4.2.3 New resource management for incremental update
      4. 4.2.4 Migrating SYSACCELERATEDTABLES table
    3. 4.3 Handling error situations
      1. 4.3.1 Error messages
      2. 4.3.2 DSNX881I alert messages
      3. 4.3.3 Opening a problem management record
      4. 4.3.4 Collecting a trace archive
      5. 4.3.5 Remote support
    4. 4.4 Optimizing DB2 Analytics Accelerator load strategy
    5. 4.5 Tuning distribution and organizing keys
      1. 4.5.1 Choosing distribution keys
      2. 4.5.2 Choosing organizing keys
      3. 4.5.3 Optimizing for high query performance
      4. 4.5.4 Optimizing for low incremental update latency
  11. Chapter 5. Query enhancements
    1. 5.1 New query acceleration features
      1. 5.1.1 Support for more predicates
      2. 5.1.2 FOR BIT DATA subtype support
      3. 5.1.3 Support for a datetime 24-hour value
      4. 5.1.4 Unmatched data type support
      5. 5.1.5 Query acceleration support for more OLAP specification expressions
    2. 5.2 Query requirements
      1. 5.2.1 Query types requirements
      2. 5.2.2 Query functionality limitations
    3. 5.3 Query acceleration settings and enablement
    4. 5.4 Increase enablement of query acceleration
      1. 5.4.1 Ambiguous queries
      2. 5.4.2 Lack of selective predicates
      3. 5.4.3 Correlated subqueries
    5. 5.5 Multi-row fetch queries
      1. 5.5.1 Measurements of single versus multi-row fetch
      2. 5.5.2 Number of rows per fetch
      3. 5.5.3 Measurements of local versus remote fetch
    6. 5.6 Static SQL queries acceleration
      1. 5.6.1 The BIND options for DB2 Analytics Accelerator
      2. 5.6.2 DB2 catalog, pseudo-catalog, and EXPLAIN table
      3. 5.6.3 Which packages should be rebound for acceleration
      4. 5.6.4 Performance of accelerated static queries
      5. 5.6.5 Static query acceleration scenario # 3
    7. 5.7 Large result sets and the cost of returned rows
  12. Chapter 6. Load and incremental update
    1. 6.1 Batch load scenarios
      1. 6.1.1 Factors affecting DB2 Analytics Accelerator load throughput
      2. 6.1.2 Concurrent table load behavior
    2. 6.2 Incremental update changes
      1. 6.2.1 Architectural changes
      2. 6.2.2 Incremental update resource allocation
      3. 6.2.3 Reduced System z CPU usage for incremental update
    3. 6.3 Performance factors affecting incremental update
      1. 6.3.1 About the test environment
      2. 6.3.2 Effect of target table size on incremental update
      3. 6.3.3 Effect of change velocity on incremental update
      4. 6.3.4 Effect of number of tables on incremental update
      5. 6.3.5 One final example
    4. 6.4 Load versus incremental update
  13. Chapter 7. Online data archiving
    1. 7.1 HPSS overview
      1. 7.1.1 Archiving enhancements
      2. 7.1.2 HPSS archive data process and operations
      3. 7.1.3 Restore HPSS archived data process and restrictions
    2. 7.2 Online data archive scenarios
      1. 7.2.1 Prepare for archiving partitions and tables
      2. 7.2.2 Archive partitions on Accelerator 1 (BZA1TFIN)
      3. 7.2.3 Archive partitions on Accelerator 2 (BZA1STPR)
    3. 7.3 Online restore of archived partitions
      1. 7.3.1 Prepare the restore of archived partitions
      2. 7.3.2 Restore archived partition on Accelerator 1 (BZA1TFIN)
      3. 7.3.3 Restore archived partition on Accelerator 2 (BZA1STPR)
  14. Chapter 8. High availability, disaster recovery, and workload balancing
    1. 8.1 High availability of DB2 Analytics Accelerators
      1. 8.1.1 Redundant SMP host for high availability
      2. 8.1.2 Redundant S-Blade capacity for high availability
      3. 8.1.3 Redundant array of independent disks
      4. 8.1.4 Private network configuration for high availability
      5. 8.1.5 High availability configuration to handle failure of an entire accelerator
    2. 8.2 Disaster recovery of DB2 Analytics Accelerator
      1. 8.2.1 Rolling migration in a HA/DR environment configuration
      2. 8.2.2 Incremental update considerations
      3. 8.2.3 Options for DB2 Analytics Accelerator recovery
    3. 8.3 Environment
    4. 8.4 Basic DB2 Analytics Accelerator workload balancing
      1. 8.4.1 Workload balancing
      2. 8.4.2 Workload balancing migration considerations
      3. 8.4.3 Workload balancing experiment
    5. 8.5 High availability behavior with workload balancing
      1. 8.5.1 High availability with query workload balancing experiments
    6. 8.6 Built-in HA DB2 Analytics Accelerator host server
      1. 8.6.1 DB2 Analytics Accelerator SMP host failover experiment
    7. 8.7 Remote DB2 Analytics Accelerator experiments
      1. 8.7.1 Experiments
  15. Chapter 9. Monitoring enhancements
    1. 9.1 Performance monitoring and capacity planning
      1. 9.1.1 Monitoring with DB2 Analytics Accelerator Studio
      2. 9.1.2 Monitoring with the DB2 command -DIS ACCEL(name) DETAIL
      3. 9.1.3 Monitoring with IBM Tivoli OMEGAMON Performance Expert
      4. 9.1.4 Monitoring using query history
    2. 9.2 Approaches to capacity planning
      1. 9.2.1 Disk space usage
      2. 9.2.2 Query workload
      3. 9.2.3 Wait times
    3. 9.3 Assessing existing DB2 workloads
      1. 9.3.1 Accelerator modeling
      2. 9.3.2 Optim Query Workload Tuner
      3. 9.3.3 Manual assessment of query workloads
    4. 9.4 Measuring actual benefits of moving workloads to DB2 Analytics Accelerator
      1. 9.4.1 Measuring with OMEGAMON Performance Expert
      2. 9.4.2 Measuring using query history
      3. 9.4.3 DB2 Query Monitor
  16. Chapter 10. DB2 Analytics Accelerator Loader
    1. 10.1 DB2 Analytics Accelerator Loader overview
    2. 10.2 Customizing Accelerator Loader
      1. 10.2.1 Pre-customization steps
      2. 10.2.2 Customize the product options using TCz
      3. 10.2.3 Post-customization step
      4. 10.2.4 Data sharing considerations
    3. 10.3 DB2 Analytics Accelerator Loader intercept and started task
    4. 10.4 Group Consistent Load
      1. 10.4.1 Not logged tables
      2. 10.4.2 Using Group Consistent Load profiles
    5. 10.5 Dual load
      1. 10.5.1 Loading DB2 Analytics Accelerator only
      2. 10.5.2 Using existing DB2 LOAD Utility JCL
      3. 10.5.3 Using Dual Load profiles
    6. 10.6 Current limitations and considerations
      1. 10.6.1 Change data capture
      2. 10.6.2 Dual load-specific limitations
    7. 10.7 First steps to troubleshooting
      1. 10.7.1 Dual load or external load failures
      2. 10.7.2 Clean up after Group Consistent Load failures
      3. 10.7.3 Stored procedures
  17. Appendix A. Preparing input data for IBM DB2 Analytics Accelerator Loader
    1. Introduction
    2. Loading non-DB2 data, such as IMS and VSAM data, to DB2 Analytics Accelerator
    3. Loading Oracle data to DB2 Analytics Accelerator
  18. Related publications
    1. IBM Redbooks
    2. Other publications
    3. Online resources
    4. Help from IBM
  19. Back cover
  20. IBM System x Reference Architecture for Hadoop: IBM InfoSphere BigInsights Reference Architecture
    1. Introduction
    2. Business problem and business value
    3. Reference architecture use
    4. Requirements
    5. InfoSphere BigInsights predefined configuration
    6. InfoSphere BigInsights HBase predefined configuration
    7. Deployment considerations
    8. Customizing the predefined configurations
    9. Predefined configuration bill of materials
    10. References
    11. The team who wrote this paper
    12. Now you can become a published author, too!
    13. Stay connected to IBM Redbooks
  21. Notices
    1. Trademarks

Product information

  • Title: Reliability and Performance with IBM DB2 Analytics Accelerator V4.1
  • Author(s):
  • Release date: September 2014
  • Publisher(s): IBM Redbooks
  • ISBN: None