Architecting and Deploying IBM DB2 with BLU Acceleration in Your Analytical Environment

Architecting and Deploying IBM DB2 with BLU Acceleration in Your Analytical Environment

by Whei-Jen Chen, Brigitte Blaser, Aidan Craddock, Polly Lau, Cong Lin, Kushal Munir, Martin Schlegel, Alexander Zietlow
Released June 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 online transaction processing (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 readers 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. 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
  5. 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
      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 Optimizing data loads with column-organized tables
      5. 2.6.5 Converting tables to column-organized tables
  6. Chapter 3. Optim Query Workload Tuner and BLU Acceleration
    1. 3.1 Common use cases for IBM InfoSphere Optim Query Workload Tuner
      1. 3.1.1 Use case 1: DB2 10.5 with BLU Acceleration upgrade
      2. 3.1.2 Use case 2: Databases with mixed workloads
      3. 3.1.3 Scenario used in this chapter
    2. 3.2 Prerequisites
    3. 3.3 Prepare an empty DB2 10.5 database with current objects and statistics using db2look
    4. 3.4 Step 1: Capture existing workloads for analysis
    5. 3.5 Step 2: Manage a list of captured workloads
      1. 3.5.1 Exporting and importing captured workloads
      2. 3.5.2 Invoking Workload Table Organization Advisor
    6. 3.6 Step 3: Run Workload Table Organization Advisor
    7. 3.7 Step 4: Review the table organization summary
    8. 3.8 Running the conversion recommendations from the Advisor
    9. 3.9 Optional: Selecting your own candidate tables for conversion analysis
  7. Chapter 4. Performance test with a Cognos BI example
    1. 4.1 Testing your new column-organized tables
      1. 4.1.1 Scenario environment
    2. 4.2 DB2 benchmark tool: db2batch command
      1. 4.2.1 Before BLU-conversion results
      2. 4.2.2 After BLU-conversion results
    3. 4.3 Cognos Dynamic Query Analyzer
      1. 4.3.1 Quick configuration of Cognos Dynamic Query Analyzer
      2. 4.3.2 Opening query execution trace logs in DQA
      3. 4.3.3 Log summary before BLU Acceleration deployment
      4. 4.3.4 Log summary after BLU Acceleration deployment
    4. 4.4 Conclusion
  8. Chapter 5. Post-deployment of DB2 with BLU Acceleration
    1. 5.1 Post-deployment of BLU Acceleration
    2. 5.2 Table organization catalog information
    3. 5.3 BLU Acceleration metadata objects
      1. 5.3.1 Synopsis tables
      2. 5.3.2 Pagemap indexes
    4. 5.4 Storage savings
      1. 5.4.1 Table-level compression rates
      2. 5.4.2 Column-level compression rates
      3. 5.4.3 Automatic space reclamation
    5. 5.5 Memory utilization for column data processing
      1. 5.5.1 Column-organized hit ratio in buffer pools
      2. 5.5.2 Prefetcher performance
    6. 5.6 Workload management
      1. 5.6.1 Automatic workload management
    7. 5.7 Query optimization
      1. 5.7.1 CTQ operator
      2. 5.7.2 Time spent on column-organized table processing
      3. 5.7.3 Observing query performance
      4. 5.7.4 Average number of columns referenced in workload
      5. 5.7.5 Monreport module
  9. Chapter 6. DB2 with BLU Acceleration and SAP integration
    1. 6.1 Introduction to SAP Business Warehouse (BW)
      1. 6.1.1 Persistent Staging Area (PSA)
      2. 6.1.2 InfoObjects
      3. 6.1.3 DataStore Objects (DSOs)
      4. 6.1.4 InfoCubes
      5. 6.1.5 BLU Acceleration benefits for SAP BW
    2. 6.2 Prerequisites and restrictions for using BLU Acceleration in SAP BW
    3. 6.3 BLU Acceleration support in the ABAP Dictionary
    4. 6.4 BLU Acceleration support in the DBA Cockpit
      1. 6.4.1 Checking whether individual tables in SAP database are column-organized
      2. 6.4.2 Checking if SAP database contains column-organized tables
      3. 6.4.3 Monitoring columnar data processing time in the SAP database
      4. 6.4.4 Monitoring columnar processing-related prefetcher and buffer pool activity in the SAP database
    5. 6.5 BLU Acceleration support in SAP BW
      1. 6.5.1 Column-organized InfoCubes in SAP BW
      2. 6.5.2 Conversion of InfoCubes to column-organized tables
      3. 6.5.3 Column-organized temporary tables in SAP BW
    6. 6.6 Deployment
      1. 6.6.1 Upgrading an SAP BW system to DB2 10.5
      2. 6.6.2 Installing a new SAP BW system on DB2 10.5
      3. 6.6.3 Migrating an SAP BW system to DB2 10.5 with BLU Acceleration InfoCubes
    7. 6.7 Preferred practices: SAP BW on BLU Acceleration
      1. 6.7.1 Example environment
      2. 6.7.2 Storage consumption and compression rates
      3. 6.7.3 Query performance
      4. 6.7.4 Discussion
    8. 6.8 BLU Acceleration for SAP near-line storage solution on DB2 (NLS)
      1. 6.8.1 Overview of NLS
      2. 6.8.2 BLU Acceleration for NLS storage objects
      3. 6.8.3 Configuration for BLU acceleration on NLS
      4. 6.8.4 NLS specific limitations
  10. Appendix A. New BLU Acceleration monitor elements
    1. A.1 Sample Monreport output
  11. Related publications
    1. IBM Redbooks
    2. Other publications
    3. Online resources
    4. Help from IBM
  12. Back cover
  13. 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
  14. Notices
    1. Trademarks

Product information

  • Title: Architecting and Deploying IBM DB2 with BLU Acceleration in Your Analytical Environment
  • Author(s): Whei-Jen Chen, Brigitte Blaser, Aidan Craddock, Polly Lau, Cong Lin, Kushal Munir, Martin Schlegel, Alexander Zietlow
  • Release date: June 2014
  • Publisher(s): IBM Redbooks
  • ISBN: None