Query Acceleration for Business Using IBM Informix Warehouse Accelerator

Query Acceleration for Business Using IBM Informix Warehouse Accelerator

Released November 2013
Publisher(s): IBM Redbooks
ISBN: 9780738438924

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® Informix® Warehouse Accelerator is a state-of-the-art in-memory database that uses affordable innovations in memory and processor technology and trends in novel ways to boost query performance. It is a disruptive technology that changes how organizations provide analytics to its operational and historical data. Informix Warehouse Accelerator uses columnar, in-memory approach to accelerate even the most complex warehouse and operational queries without application changes or tuning.

This IBM Redbooks® publication provides a comprehensive look at the technology and architecture behind the system. It contains information about the tools, data synchronization, and query processing capabilities of Informix Warehouse Accelerator, and provides steps to implement data analysis by using Informix Warehouse Accelerator within an organization.

This book is intended for IBM Business Partners and clients who are looking for low-cost solutions to boost data warehouse query performance.

Table of contents

  1. Front cover
  2. Notices
    1. Trademarks
  3. Preface
    1. Authors
    2. Now you can become a published author, too!
    3. Comments welcome
    4. Stay connected to IBM Redbooks
  4. Chapter 1. Introduction to IBM Informix Warehouse Accelerator
    1. 1.1 Problems that Informix Warehouse Accelerator solves for its customers
    2. 1.2 History of Informix Warehouse Accelerator
    3. 1.3 Key Informix Warehouse Accelerator technologies
      1. 1.3.1 Informix Warehouse Accelerator architecture
      2. 1.3.2 Informix and Informix Warehouse Accelerator data mart definition and data loading
      3. 1.3.3 Query execution in Informix and Informix Warehouse Accelerator
      4. 1.3.4 Data synchronization in both Informix and Informix Warehouse Accelerator
    4. 1.4 Informix Warehouse Accelerator architectural options
    5. 1.5 Components of Informix Warehouse Accelerator
      1. 1.5.1 Coordinator node
      2. 1.5.2 Worker node
    6. 1.6 Informix Warehouse Accelerator tools
    7. 1.7 Business intelligence tools that are used with Informix and Informix Warehouse Accelerator
    8. 1.8 Informix Warehouse Accelerator editions
    9. 1.9 Informix Warehouse Accelerator positioning
  5. Chapter 2. Designing data marts
    1. 2.1 Relational database design overview
      1. 2.1.1 Designs for OLTP
      2. 2.1.2 Designs for OLAP and dimensional modeling
    2. 2.2 Dimensional modeling for Informix Warehouse Accelerator
      1. 2.2.1 Degree of denormalization
      2. 2.2.2 Composing a data mart
      3. 2.2.3 Subsets in a snowflake schema
      4. 2.2.4 Overlapping snowflake schemas
      5. 2.2.5 Types of relationships between tables
    3. 2.3 Designing Informix Warehouse Accelerator data marts
      1. 2.3.1 Supported data types
      2. 2.3.2 Data compression
      3. 2.3.3 Dimension tables and multiple Informix Warehouse Accelerator worker nodes
      4. 2.3.4 Data mart metadata in the Informix server
  6. Chapter 3. Designing and sizing an IBM Informix Warehouse Accelerator environment
    1. 3.1 Architectural considerations
      1. 3.1.1 SMP environment
      2. 3.1.2 Cluster environment
      3. 3.1.3 Virtual machine environment
      4. 3.1.4 Changing an existing configuration
    2. 3.2 Continuous data mart availability
    3. 3.3 Sizing
      1. 3.3.1 General resource considerations
      2. 3.3.2 Sizing for an SMP environment
      3. 3.3.3 Sizing for a cluster environment
      4. 3.3.4 Sizing for a VM environment
      5. 3.3.5 Network connectivity
  7. Chapter 4. IBM Informix Warehouse Accelerator installation and configuration
    1. 4.1 Installation of the Informix Warehouse Accelerator server
      1. 4.1.1 Installation of Informix Warehouse Accelerator: Single computer
      2. 4.1.2 Installation of Informix Warehouse Accelerator: Cluster
    2. 4.2 Configuration tasks
      1. 4.2.1 Configuring Informix Warehouse Accelerator
      2. 4.2.2 Operating system setup requirements
      3. 4.2.3 Configuring the Informix server
    3. 4.3 Setting up and starting the accelerator server
      1. 4.3.1 Setting up Informix Warehouse Accelerator
      2. 4.3.2 Starting Informix Warehouse Accelerator
      3. 4.3.3 Connecting Informix server and Informix Warehouse Accelerator
  8. Chapter 5. Creating IBM Informix Warehouse Accelerator data marts
    1. 5.1 Data marts as objects in Informix Warehouse Accelerator
    2. 5.2 Identifying workloads in your business environments
    3. 5.3 Data mart creation by using workload analysis
      1. 5.3.1 Workload analysis methods
      2. 5.3.2 Workload Analysis by using OAT
      3. 5.3.3 Data mart deployment by using OAT
    4. 5.4 Interactive data mart design with IBM Smart Analytics Optimizer Studio
    5. 5.5 Embedded data mart creation by using sysdbopen()
    6. 5.6 Designing a data mart that is based on SQL trace filtering
    7. 5.7 Using Informix TimeSeries data in an Informix Warehouse Accelerator environment
  9. Chapter 6. Query execution
    1. 6.1 Overview
    2. 6.2 Query execution flow
      1. 6.2.1 Step 1: Submitting SQL on the application or BI tools
      2. 6.2.2 Step 2: Optimizing and matching the query
      3. 6.2.3 Step 3: Local execution
      4. 6.2.4 Step 4: Accelerating SQL
      5. 6.2.5 Step 5: Query processing on the coordinator node
      6. 6.2.6 Step 6: Query processing on worker nodes
      7. 6.2.7 Step 7: Results sent to Informix and DRDA over TCP/IP
      8. 6.2.8 Step 8: Post processing on the Informix server
      9. 6.2.9 Step 9: Returning results
    3. 6.3 Enabling query execution
    4. 6.4 Query matching
      1. 6.4.1 Checklist for query (query block) qualification
      2. 6.4.2 Examples for query qualification
    5. 6.5 Monitoring query execution
    6. 6.6 Summary
  10. Chapter 7. Managing and refreshing an IBM Informix Warehouse Accelerator data mart
    1. 7.1 Overview
      1. 7.1.1 IBM Informix Open Admin Tool
      2. 7.1.2 The Informix and Informix Warehouse Accelerator Stored Procedure API
      3. 7.1.3 IBM Smart Analytics Optimizer Studio
    2. 7.2 Data synchronization methods
    3. 7.3 Data mart states and transitions
      1. 7.3.1 Stage A: Informix tables or data marts
      2. 7.3.2 Stage B: Data mart design and definition
      3. 7.3.3 Stage C: Loading the pending data mart (disabled)
      4. 7.3.4 Stage D: (Enabled) Loaded data mart in use
      5. 7.3.5 Stage E: Data mart disabled
      6. 7.3.6 Stage F: Data mart deleted
    4. 7.4 Schema changes on Informix
  11. Chapter 8. IBM Informix Warehouse Accelerator server: Administration tasks
    1. 8.1 Administering the Informix Warehouse Accelerator server by using the ondwa utility
      1. 8.1.1 ondwa setup
      2. 8.1.2 ondwa start
      3. 8.1.3 ondwa stop
      4. 8.1.4 ondwa reset
      5. 8.1.5 ondwa clean
    2. 8.2 Access token generation
    3. 8.3 Monitoring Informix Warehouse Accelerator
    4. 8.4 Monitoring operating system usage
      1. 8.4.1 Monitoring memory usage
      2. 8.4.2 Monitoring processor usage
      3. 8.4.3 Monitoring the network
      4. 8.4.4 Monitoring disk space usage
    5. 8.5 Informix database server administration objectives
      1. 8.5.1 Accelerated query tables
      2. 8.5.2 Cleanup of data mart metadata
  12. Chapter 9. Use of IBM Cognos Business Intelligence with IBM Informix Warehouse Accelerator
    1. 9.1 IBM Cognos Business Intelligence
    2. 9.2 Metadata model
    3. 9.3 Relationships
      1. 9.3.1 Dimensions and member preservation
    4. 9.4 Multiple fact queries
    5. 9.5 Filtering data
    6. 9.6 Data types
    7. 9.7 Data driven prompts and dimension browses
    8. 9.8 Connection command blocks
    9. 9.9 Monitoring SQL statements
  13. Chapter 10. IBM Informix Warehouse Accelerator proof of concept
    1. 10.1 Assessing qualification and compatibility for Informix Warehouse Accelerator
    2. 10.2 Assessing customer pain for analytic queries
      1. 10.2.1 Feasibility assessment
      2. 10.2.2 Business value assessment
    3. 10.3 Document of understanding
    4. 10.4 Operating system, environment, and client application readiness
    5. 10.5 Preparing the server for Informix Warehouse Accelerator
    6. 10.6 Sizing the environment
      1. 10.6.1 Informix database server requirements
    7. 10.7 Defining an accelerator and creating a data mart
      1. 10.7.1 Probing queries
    8. 10.8 Accelerating SQL queries
      1. 10.8.1 Controlling query acceleration
      2. 10.8.2 Query performance
      3. 10.8.3 Compiling results
    9. 10.9 Proof of technology
  14. Appendix A. Tools for IBM Informix Warehouse Accelerator
    1. A.1 Quick guide to Informix Warehouse Accelerator tools and interfaces
    2. A.2 The ondwa utility
    3. A.3 The ondwachk utility
    4. A.4 Stored procedure API
    5. A.5 IBM OpenAdmin Tool for Informix (OAT)
    6. A.6 IBM Smart Analytics Optimizer Studio
  15. Related publications
    1. Other publications
    2. Online resources
    3. Help from IBM
  16. Back cover

Product information

  • Title: Query Acceleration for Business Using IBM Informix Warehouse Accelerator
  • Author(s):
  • Release date: November 2013
  • Publisher(s): IBM Redbooks
  • ISBN: 9780738438924