Architecting and Deploying DB2 with BLU Acceleration

Architecting and Deploying DB2 with BLU Acceleration

by Whei-Jen Chen, Brigitte Blaser, Marco Bonezzi, Polly Lau, Jean Cristie Pacanaro, Martin Schlegel, Ayesha Zaka, Alexander Zietlow
Released October 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

IBM® DB2® with BLU Acceleration is a revolutionary technology that is delivered in DB2 for Linux, UNIX, and Windows Release 10.5. BLU Acceleration delivers breakthrough performance improvements for analytic queries by using dynamic in-memory columnar technologies. Different from other vendor solutions, BLU Acceleration allows the unified computing of OLTP and analytics data inside a single database, therefore, removing barriers and accelerating results for users. With observed hundredfold improvement in query response time, BLU Acceleration provides a simple, fast, and easy-to-use solution for the needs of today's organizations; quick access to business answers can be used to gain a competitive edge, lower costs, and more.

This IBM Redbooks® publication introduces the concepts of DB2 with BLU Acceleration. It discusses the steps to move from a relational database to using BLU Acceleration, optimizing BLU usage, and deploying BLU into existing analytic solutions today, with an example of IBM Cognos®.

This book also describes integration of DB2 with BLU Acceleration into SAP Business Warehouse (SAP BW) and SAP's near-line storage solution on DB2. This publication is intended to be helpful to a wide-ranging audience, including those readers who want to understand the technologies and those who have planning, deployment, and support responsibilities.

Table of contents

  1. Front cover
  2. Notices
    1. Trademarks
  3. Preface
    1. Authors
    2. Acknowledgment
    3. Now you can become a published author, too!
    4. Comments welcome
    5. Stay connected to IBM Redbooks
  4. Summary of changes
    1. October 2014, Second Edition
  5. Chapter 1. Introducing DB2 BLU Acceleration
    1. 1.1 DB2 with BLU Acceleration
    2. 1.2 BLU Acceleration: Seven Big Ideas
      1. 1.2.1 Big Idea 1: Simplicity and ease of use
      2. 1.2.2 Big Idea 2: Column store
      3. 1.2.3 Big Idea 3: Adaptive compression
      4. 1.2.4 Big Idea 4: Parallel vector processing
      5. 1.2.5 Big Idea 5: Core-friendly parallelism
      6. 1.2.6 Big Idea 6: Scan-friendly memory caching
      7. 1.2.7 Big Idea 7: Data skipping
      8. 1.2.8 The seven big ideas in action
    3. 1.3 Next generation analytics: Cognos BI and DB2 with BLU Acceleration
    4. 1.4 IBM DB2 with BLU Acceleration offerings
      1. 1.4.1 Simplified IBM Business Intelligence with BLU Acceleration Pattern deployment on IBM PureApplication Systems
      2. 1.4.2 IBM BLU Acceleration for Cloud
      3. 1.4.3 IBM BLU Acceleration Solution – Power Systems Edition
    5. 1.5 Obtaining DB2 with BLU Acceleration
      1. 1.5.1 IBM DB2 BLU Acceleration Kit for Trial
      2. 1.5.2 IBM BLU Acceleration for Cloud trial option
      3. 1.5.3 DB2 with BLU Acceleration trial software
      4. 1.5.4 IBM DB2 BLU bootcamps and education
  6. Chapter 2.  Planning and deployment of BLU Acceleration
    1. 2.1 BLU Acceleration deployment made easy
    2. 2.2 Data environments targeted for analytic workloads
    3. 2.3 Data environments with mixed workloads
    4. 2.4 Prerequisites
      1. 2.4.1 DB2 system requirements
      2. 2.4.2 DB2 license requirements and functionality
      3. 2.4.3 Capacity planning
      4. 2.4.4 Storage requirements
    5. 2.5 Deployment
      1. 2.5.1 Single tuning parameter for analytical workloads
      2. 2.5.2 New database deployments
      3. 2.5.3 Multiple mixed-workload databases in a single instance
      4. 2.5.4 Existing database deployments
      5. 2.5.5 Upgrade from a previous release to DB2 10.5
    6. 2.6 Configuration preferred practices for BLU Acceleration deployment
      1. 2.6.1 Changes applied with DB2_WORKLOAD=ANALYTICS
      2. 2.6.2 Memory distribution
      3. 2.6.3 Creating column-organized tables
      4. 2.6.4 Using insert and ingest with column-organized tables
      5. 2.6.5 Converting tables to column-organized tables
    7. 2.7 Configuration preferred practices for HADR
      1. 2.7.1 Column-organized tables now support high availability and disaster recovery
      2. 2.7.2 Column-organized tables and HADR synchronization modes
      3. 2.7.3 Configuring primary and standby databases
      4. 2.7.4 Automatic Client Reroute and HADR
      5. 2.7.5 Switching the roles in your HADR environment
      6. 2.7.6 Checking HADR status
      7. 2.7.7 Considerations for fix pack upgrades in an HADR environment
      8. 2.7.8 HADR best practices
  7. Chapter 3. Planning and deployment of BLU Acceleration shadow tables for mixed workload environments
    1. 3.1 Overview
      1. 3.1.1 Data environments with mixed workloads
      2. 3.1.2 DB2 with BLU Acceleration and shadow tables
    2. 3.2 Prerequisites
      1. 3.2.1 Supported versions, platforms, and system requirements
      2. 3.2.2 User accounts and groups
      3. 3.2.3 Paths, storage space requirements, and permissions
      4. 3.2.4 Selecting candidates for shadow tables
    3. 3.3 Deployment
      1. 3.3.1 Roadmap
      2. 3.3.2 Installation
      3. 3.3.3 Database and database manager configuration for shadow tables
      4. 3.3.4 Shadow table creation
      5. 3.3.5 Replication setup
      6. 3.3.6 Subscriptions and table mappings
      7. 3.3.7 InfoSphere CDC event notification setup
      8. 3.3.8 Connection setup
      9. 3.3.9 Setup validation
    4. 3.4 Configuration preferred practices
      1. 3.4.1 DB2 and InfoSphere CDC sizing guidelines
      2. 3.4.2 DB2 configuration parameters
      3. 3.4.3 InfoSphere CDC instance configuration parameters
      4. 3.4.4 Startup and shutdown procedures
      5. 3.4.5 Buffer pool and storage considerations
      6. 3.4.6 Enabling latency based routing
      7. 3.4.7 Optimizing compression dictionaries
      8. 3.4.8 Latency and throughput
    5. 3.5 Operational preferred practices
      1. 3.5.1 Backup and recovery
      2. 3.5.2 Restoring table space backups for shadow tables
      3. 3.5.3 Moving InfoSphere CDC metadata tables to a specific table space
      4. 3.5.4 Reorganizing shadow tables
      5. 3.5.5 Table statistics
      6. 3.5.6 Monitoring replication
      7. 3.5.7 Troubleshooting shadow tables
      8. 3.5.8 Restoring replication when restricted DDL is applied to a table
      9. 3.5.9 Preventing disruption to replication with restrictive DDL applied to the source table
    6. 3.6 Shadow tables and HADR
      1. 3.6.1 Shadow tables configuration
      2. 3.6.2 HADR considerations
      3. 3.6.3 InfoSphere CDC considerations
      4. 3.6.4 Standby server considerations
      5. 3.6.5 Installation and configuration
      6. 3.6.6 Shadow tables configuration and metadata
      7. 3.6.7 Considerations for an HADR role switch
      8. 3.6.8 Considerations for InfoSphere CDC Access Server after a failover
  8. Chapter 4. Optim Query Workload Tuner and BLU Acceleration
    1. 4.1 Planning and testing BLU Acceleration with IBM InfoSphere Optim Query Workload
      1. 4.1.1 Use case 1: Databases with mixed workloads
      2. 4.1.2 Use case 2: DB2 10.5 with BLU Acceleration upgrade
    2. 4.2 How the Workload Table Organization Advisor works
      1. 4.2.1 Scenario used in this chapter
    3. 4.3 Prerequisites
    4. 4.4 Preparing an empty DB2 10.5 database with current objects and statistics using db2look
    5. 4.5 Step 1: Capturing existing workloads for analysis
    6. 4.6 Step 2: Managing a list of captured workloads
      1. 4.6.1 Exporting and importing captured workloads
      2. 4.6.2 Invoking Workload Table Organization Advisor
    7. 4.7 Step 3: Running the Workload Table Organization Advisor
    8. 4.8 Step 4: Reviewing the table organization summary
    9. 4.9 Running the conversion recommendations from the advisor
    10. 4.10 Optional: Selecting your own candidate tables for conversion analysis
  9. Chapter 5. Performance test with a Cognos BI example
    1. 5.1 Testing your new column-organized tables
      1. 5.1.1 Scenario environment
    2. 5.2 DB2 benchmark tool: db2batch command
      1. 5.2.1 Before BLU-conversion results
      2. 5.2.2 After BLU-conversion results
    3. 5.3 Cognos Dynamic Query Analyzer
      1. 5.3.1 Quick configuration of Cognos Dynamic Query Analyzer
      2. 5.3.2 Opening query execution trace logs in DQA
      3. 5.3.3 Log summary before BLU Acceleration deployment
      4. 5.3.4 Log summary after BLU Acceleration deployment
    4. 5.4 Conclusion
  10. Chapter 6. Post-deployment of DB2 with BLU Acceleration
    1. 6.1 Post-deployment of BLU Acceleration
    2. 6.2 Table organization catalog information
    3. 6.3 BLU Acceleration metadata objects
      1. 6.3.1 Synopsis tables
      2. 6.3.2 Pagemap indexes
    4. 6.4 Storage savings
      1. 6.4.1 Table-level compression rates
      2. 6.4.2 Column-level compression rates
      3. 6.4.3 Automatic space reclamation
    5. 6.5 Memory utilization for column data processing
      1. 6.5.1 Column-organized hit ratio in buffer pools
      2. 6.5.2 Prefetcher performance
      3. 6.5.3 Monitoring sort memory usage
    6. 6.6 Workload management
      1. 6.6.1 Automatic workload management
    7. 6.7 Query optimization
      1. 6.7.1 CTQ operator
      2. 6.7.2 Time spent on column-organized table processing
      3. 6.7.3 Observing query performance
      4. 6.7.4 Average number of columns referenced in workload
      5. 6.7.5 The MONREPORT module
  11. Chapter 7. Oracle compatibility for BLU Acceleration
    1. 7.1 DB2 SQL Compatibility feature overview
      1. 7.1.1 Data types
      2. 7.1.2 SQL standard
      3. 7.1.3 PL/SQL
      4. 7.1.4 Concurrency management
      5. 7.1.5 Built-in functions and packages
      6. 7.1.6 SQL*Plus scripting
      7. 7.1.7 Oracle Call Interface (OCI) and Pro*C
      8. 7.1.8 Miscellaneous features and capabilities
    2. 7.2 Deploying DB2 BLU Acceleration with SQL Compatibility
      1. 7.2.1 Enabling SQL Compatibility for BLU Acceleration
      2. 7.2.2 Verifying SQL Compatibility set up
    3. 7.3 Migrating Oracle to DB2 with BLU Acceleration
      1. 7.3.1 A general conversion process overview
      2. 7.3.2 IBM Data Conversion Workbench tools
      3. 7.3.3 A migration scenario using DCW
      4. 7.3.4 Conclusion
  12. Chapter 8. DB2 with BLU Acceleration and SAP integration
    1. 8.1 Introduction to SAP Business Warehouse (BW)
      1. 8.1.1 Persistent Staging Area (PSA)
      2. 8.1.2 InfoObjects
      3. 8.1.3 DataStore Objects (DSOs)
      4. 8.1.4 InfoCubes
      5. 8.1.5 BLU Acceleration benefits for SAP BW
    2. 8.2 Prerequisites and restrictions for using BLU Acceleration in SAP BW
      1. 8.2.1 Important SAP documentation and SAP notes
      2. 8.2.2 Prerequisites and restrictions for using DB2 BLU Acceleration
      3. 8.2.3 Required Support Packages and SAP DBSL Patches
      4. 8.2.4 Required SAP kernel parameter settings
    3. 8.3 BLU Acceleration support in the ABAP Dictionary
    4. 8.4 BLU Acceleration support in the DBA Cockpit
      1. 8.4.1 Checking whether individual tables in SAP database are column-organized
      2. 8.4.2 Checking if SAP database contains column-organized tables
      3. 8.4.3 Monitoring columnar data processing time in the SAP database
      4. 8.4.4 Monitoring columnar processing-related prefetcher and buffer pool activity in the SAP database
    5. 8.5 BLU Acceleration support in SAP BW
      1. 8.5.1 Column-organized standard InfoCubes in SAP BW
      2. 8.5.2 Flat InfoCubes in SAP BW 7.40
      3. 8.5.3 Column-Organized DataStore Objects (DSOs) in SAP BW
      4. 8.5.4 Column-Organized InfoObjects in SAP BW
      5. 8.5.5 Column-organized PSA tables in SAP BW
      6. 8.5.6 Column-organized temporary tables in SAP BW
    6. 8.6 Conversion of SAP BW objects to column-organized tables
      1. 8.6.1 Conversion of SAP BW objects to column-organized tables with DB2 Cancun Release 10.5.0.4 and DB6CONV V6
      2. 8.6.2 Conversion of InfoCubes to column-organized tables before DB2 Cancun Release 10.5.0.4
    7. 8.7 Deployment
      1. 8.7.1 Upgrading an SAP BW system to DB2 10.5
      2. 8.7.2 Installing a new SAP BW system on DB2 10.5
      3. 8.7.3 Migrating an SAP BW system to DB2 10.5 with BLU Acceleration InfoCubes
    8. 8.8 Performance of SAP BW with BLU Acceleration
      1. 8.8.1 Example environment
      2. 8.8.2 Storage consumption and compression rates
      3. 8.8.3 Query performance
      4. 8.8.4 Discussion
    9. 8.9 BLU Acceleration for SAP near-line storage solution on DB2 (NLS)
      1. 8.9.1 Overview of NLS
      2. 8.9.2 BLU Acceleration for NLS storage objects
      3. 8.9.3 Configuration for BLU Acceleration on NLS
      4. 8.9.4 NLS specific limitations
      5. 8.9.5 Benefits of BLU Acceleration for SAP NLS on DB2
  13. Appendix A. BLU Acceleration monitor elements
    1. A.1 Sample Monreport output
  14. Related publications
    1. IBM Redbooks publications
    2. Other publications
    3. Online resources
    4. Help from IBM
  15. Back cover
  16. 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
  17. Notices
    1. Trademarks

Product information

  • Title: Architecting and Deploying DB2 with BLU Acceleration
  • Author(s): Whei-Jen Chen, Brigitte Blaser, Marco Bonezzi, Polly Lau, Jean Cristie Pacanaro, Martin Schlegel, Ayesha Zaka, Alexander Zietlow
  • Release date: October 2014
  • Publisher(s): IBM Redbooks
  • ISBN: None