Boosting Query Performance with Informix Warehouse Accelerator
IBM Redbooks Solution Guide
Published 17 October 2013
Authors: Whei-Jen Chen
The IBM Informix® Warehouse Accelerator is a breakthrough technology that changes how businesses can view and deploy analytics. Informix Warehouse Accelerator both delivers high performance for business queries and eliminates the need for query tuning, data management, and cube-building activities normally associated with enterprises seeking acceptable performance in analytics. Together with the Informix database server, it offers a single environment where traditional online transaction processing (OLTP) and current online analytical processing (OLAP) can be combined in a cost-effective manner.
This IBM® Redbooks® Solution Guide provides an overview of Informix Warehouse Accelerators. For more information about Informix Warehouse Accelerator, see Query Acceleration for Business using Informix Warehouse, SG24-8150.
IBM® Informix® Warehouse Accelerator is a state-of-the-art in-memory database designed to use affordable innovations in memory and processor technology and trends in novel ways to boost query performance (Figure 1). It is a disruptive technology that changes how organizations provide analytics to its operational and historical data. Informix Warehouse Accelerator uses a columnar, in-memory approach to accelerate even the most complex warehouse and operational queries without application changes or tuning.
Figure 1. IBM Informix Warehouse Accelerator helps boost query performance
Did you know?
Change is constant, as is the need for higher speed in business. Data analysis helps organizations understand patterns, predict trends, and quickly adjust business flow. You can gain business advantage by quickly and consistently analyzing the latest data at a low total cost of ownership (TCO). Informix Warehouse Accelerator provides the powerful solution for your goal.
The Informix Warehouse Accelerator was designed to be completely transparent to any business applications so no application changes are required to take advantage of accelerated query performance. Users can use front-end analysis and reporting tools, such as IBM Cognos®, or develop mash-ups and other dashboards.
Informix offers tremendous cost-savings because it can run on commodity hardware and can simultaneously meet or surpass service level agreements for both transactional and analytical workloads, eliminating the need for separate OLAP systems.
The innovative approaches to complex query processing taken by Informix Warehouse Accelerator can help improve the productivity of an enterprise by providing quick answers without increasing the amount of manual work or budget required. Because the accelerator is tightly integrated with the Informix database server, database administrators (DBAs) can divide the load between the database server and the accelerator, as necessary.
Fast response time means quick answers, quick insights, and an agile business. Informix Warehouse Accelerator provides breakthrough warehouse analytical query performance without constantly monitoring and tuning the system. With Informix Warehouse Accelerator, enterprises can plan to accelerate the high-value aspect of their warehouses and dynamically evolve their infrastructures to suit business needs.
Informix Warehouse Accelerator, as the name suggests, is an accelerator to analytic queries to Informix. With Informix 11.7, Informix Warehouse Accelerator was released in 2011 with the ability to transparently accelerate analytical queries processed by Informix. It requires no change to SQL queries submitted to Informix and requires no indexing, partitioning, query optimization, summary tables, and so on, to obtain the significant improvement in query time.
The Informix Warehouse Accelerator product serves the small and medium business (SMB) segment, and large enterprises, with raw data sizes that range from a few hundred gigabytes to tens of terabytes. It is a software appliance that is not tied to particular hardware vendors. Configurations for memory and processors can be customized easily. Two editions of the software are available to meet different price points in the marketplace:
- Informix Advance Enterprise Edition
- Informix Advance Workgroup Edition
New features of Informix Warehouse Accelerator are in Informix Version 12.10:
- Continuous refresh the data in data marts
If your business requires that the data in your warehouse is always synchronized with the data in your database, you can configure the continuous refreshing of data in your data marts. This continuous synchronization process is sometimes referred to as trickle feed.
- Enhanced support for SQL Elements
Informix Warehouse Accelerator includes new functions and procedures that you can use from any SQL client to perform administrative tasks. For example, you can use functions to create a data mart, to load a data mart, or to gather accelerator metrics.
- Integration with the IBM OpenAdmin Tool (OAT)
Informix Warehouse Accelerator and its data marts can now be administered using OAT Version 3.11. Tasks supported by OAT include creating and monitoring an accelerator, and creating, loading, monitoring, and dropping data marts.
- New SQL routines for Informix Warehouse Accelerator and data mart administration
Informix Warehouse Accelerator includes new procedures and functions that can be called from any SQL client application. Use of these routines is simplified; handling of CLOB data is no longer required.
- Privilege control for data mart administration
Users now are required to have certain privileges to do Informix Warehouse Accelerator and data mart administrative tasks.
- New utility: ondwachk
This new utility script is used to check whether the setup and configuration of the Informix Server instance is valid for accelerating queries using Informix Warehouse Accelerator. The utility is part of the Informix Server installation, and run from within the Informix Server instance’s environment.
Deployment of Informix Warehouse Accelerator does not require much time and effort. If you already have an Informix solution, the Informix Warehouse Accelerator simply plugs into your existing environment. For new deployments, implementation is simple and quick. You can cut complexity further by using a single data management platform for online transaction processing and analytical processing.
Because Informix Warehouse Accelerator is pre-optimized for the specific task of accelerating complex queries, it eliminates many database tuning tasks, saving your organization time and money. No worrying about indexes, materialized query tables or query plan tuning, which are tasks that would otherwise require extensive time and expensive resources.
Informix Warehouse Accelerator is transparent to your applications and to your users, resulting in a seamless experience..
- You specify which database tables are included in the data mart, and Informix automatically offloads data belonging to the mart.
- You continue to submit queries directly to the Informix Warehouse or use your existing business intelligence tools.
- Queries are run in the most efficient way, with Informix deciding which queries should be routed to the Informix Warehouse Accelerator.
- The accelerator software combines the best of row and columnar store technologies with a parallel architecture for fast results.
- Informix Warehouse Accelerator returns the answers to the Informix Warehouse, which then returns them to the user..
Users are entirely unaware of Informix Warehouse Accelerator, but they will notice that query performance is better.
Informix Warehouse Accelerator is an attachment to the Informix database server. The Informix database server runs on various platforms, for example IBM AIX®, HP-UX, Solaris, and Linux; the Informix Warehouse Accelerator always runs on a Linux system. Figure 2 shows the general architecture of the information system with Informix Warehouse Accelerator.
Notice that the disk subsystem belongs to the Informix database server itself, not Informix Warehouse Accelerator. This means that Informix maintains the database from which data is loaded into Informix Warehouse Accelerator. Depending on the workload, this Informix server is servicing, the data can be transactional data in third normal form (3NF) or higher, or it can be a data warehouse using denormalized data, that is, dimensional modeled. Notice that all external interfaces, such as business intelligence (BI) or administration tools, are directed to the Informix database server, not Informix Warehouse Accelerator.
Figure 2. Informix and Informix Warehouse Accelerator architecture
Informix Warehouse Accelerator can be installed on different hardware environments, including symmetric multiprocessor (SMP) systems or cluster systems. Additionally, you can install on a virtual machine.
Informix Warehouse Accelerator runs in a shared-everything environment of an SMP system. This is a single machine with multiple processors and a matching amount of memory. Informix Warehouse Accelerator implements a multithreaded process architecture that takes advantage of an SMP system by using a coordinator node and a single worker node. Each Informix Warehouse Accelerator node consists of a single multithreaded process on the Linux operating system. You can specify configuration parameters to set the amount of physically available processor cores that these nodes can use when running multiple threads in parallel.
Figure 3 shows Informix Warehouse Accelerator in a single worker node configuration running in an SMP environment.
Figure 3. Informix Warehouse Accelerator single worker node configuration in an SMP environment
In an SMP environment, everything is shared and accessible by everyone; the opposite is true in a cluster environment. In a cluster environment, each cluster node has its own processor cores and memory, with exclusive access to these resources. In a cluster environment, Informix Warehouse Accelerator is configured to run a single accelerator server node on each cluster node. One cluster node runs the coordinator node and the remaining cluster nodes each run one worker node. Memory and processor resources are not shared between cluster nodes. Each individual Informix Warehouse Accelerator node is given all of the resources of its respective cluster node. Figure 4 shows Informix Warehouse Accelerator in a four-node cluster environment.
Figure 4. Informix Warehouse Accelerator in a four-node cluster environment
Virtual machine environment
For Informix Warehouse Accelerator, a virtual machine environment is similar to an SMP environment in that the accelerator server is not aware of the virtual machine and Informix Warehouse Accelerator does not do anything special on the virtual machine. The virtual machine must have properties like those of a physical machine. Specifically, the virtual machine for Informix Warehouse Accelerator must be assigned a fixed number of physical resources with exclusive access. You must assign physical resources for processor cores and memory.
Informix Warehouse Accelerator provides a solution for businesses seeking “speed of thought” analysis of warehouse data or operational data.
- Accelerating data warehouse
Data warehouses contain one or more data marts, defined to handle a particular subject area. Each data mart is typically a star or snow-flake schema. You can choose to accelerate a specific high value data mart, and only the workload using this particular data mart is accelerated without changing anything for the rest of the schema or workload. Typically, data is loaded to the warehouse nightly. You can update the Informix Warehouse Accelerator data with continuous refresh or full load.
- Accelerating operational analytics
As businesses try to get more responsive, analysis of transactional data is becoming common. You can create data marts on your transactional schema and deploy them on Informix Warehouse Accelerator. Data update from the transactional system can be trickle-fed into Informix Warehouse Accelerator for near real-time analysis.
- Accelerating time series data
Time series data type and access method provide efficient storage and query processing of time series data. Use Informix Warehouse Accelerator if your analytical queries have many joins and ad hoc aggregations of this data.
- Big data analysis
The speed of query processing of Informix Warehouse Accelerator enables new aspects of big data analysis that otherwise are just not practicable or require prohibitively high investments.
Informix is compatible with most popular business intelligence tools on the market, including Cognos, Microstrategy, Oracle BI (OBI), and Pentaho. You can keep using your familiar front-end analysis and reporting tools or develop mashups and dashboards. SQL queries can be submitted to Informix either directly from SQL programs or from a business intelligence tool. Because Cognos is part of IBM, there is more opportunity to collaborate with Cognos to obtain the most optimal queries.
Informix Warehouse Accelerator is designed to run on commodity hardware, which is a high-performance Linux 64-bit operating system on an Intel processor-based single server or a cluster. It tightly integrates with the Informix database server, running on a single server or a cluster. This combination is supported when an Informix database server is on any of the following 64-bit platforms:
- Linux operating system on Intel processor–based servers
- IBM AIX® operating system on IBM POWER7® processor–based servers
- HP-UX operating system on Intel Itanium processor–based servers
- Oracle Solaris operating system on Intel processor based server or Oracle SPARC servers
The database server and the accelerator can be installed on the same or different computers.
Informix Warehouse Accelerator is a key component of the Informix advanced editions. IBM Software Subscription and Support is included in the product price for the first year.
For the product number and order information, see Informix V12.10 announcement:
For more information, see the following documents:
- IBM Offering Information page (to search on announcement letters, sales manuals, or both):
On this page, enter Informix V12.10, select the information type, and then click Search. On the next page, narrow your search results by geography and language.
- Query Acceleration for Business using Informix Warehouse Accelerator, SG24-8150:
- IBM Informix V12.10 Information Center:
- Informix Warehouse Accelerator blog at developerWorks:
Others who read this publication also read
This material has not been submitted to any formal IBM test and is published AS IS. It has not been the subject of rigorous review. IBM assumes no responsibility for its accuracy or completeness. The use of this information or the implementation of any of these techniques is a client responsibility and depends upon the client's ability to evaluate and integrate them into the client's operational environment.
Follow IBM Redbooks
Follow IBM Redbooks