Home
Data warehousing
In addition to designing and implementing Informix® dimensional databases, you can use tools to create data warehouse applications and optimize your data warehouse queries.
Informix® Warehouse Accelerator
The Informix® Warehouse Accelerator Administration Guide includes information about installing and configuring the accelerator server, creating data marts, accelerators, and accelerated query tables (AQTs).
Data marts and AQTs
For efficient query processing, the accelerator server must have its own copy of the data. The data is stored in logical collections of related data, or data marts. A data mart specifies the collection of tables that are loaded into an accelerator and the relationships, or references, between these tables.
Create a data mart
After you configure the accelerator server and set up the connection between an accelerator and the database server, you need to create the data marts.
Creating data mart definitions manually by using workload analysis
Workload analysis involves two main steps, gathering information about your query workload, which is known as query probing, and analysis of the query probing data.
Removing probing data from the database
After you no longer need the probing data, you can use a SET ENVIRONMENT statement to remove it from the database that contains the warehousing data. Remove probing data when you want to start a new probing session and you want to ensure that none of the previous probing data that is stored in memory is included in the new data mart definition.

Data warehousing
In addition to designing and implementing Informix® dimensional databases, you can use tools to create data warehouse applications and optimize your data warehouse queries.
- Dimensional databases
  The Informix® Data Warehouse Guide provides information to help you design, implement, and manage dimensional databases, and describes the tools that you can use to create data warehouses and optimize your data warehouse queries.
- Informix® Warehouse Accelerator
  The Informix® Warehouse Accelerator Administration Guide includes information about installing and configuring the accelerator server, creating data marts, accelerators, and accelerated query tables (AQTs).
  - Overview of Informix® Warehouse Accelerator
    Informix® Warehouse Accelerator is a product that you use to improve the performance of your warehouse queries.
  - Installation
    You can install Informix® Warehouse Accelerator on the same computer as the Informix database server, on a separate computer, or on a cluster (including a high-availability cluster).
  - Configuration
    You must configure the accelerator server to work with the Informix® database server.
  - Create an accelerator
    You create an accelerator by connecting the Informix® database server to the accelerator server.
  - Data marts and AQTs
    For efficient query processing, the accelerator server must have its own copy of the data. The data is stored in logical collections of related data, or data marts. A data mart specifies the collection of tables that are loaded into an accelerator and the relationships, or references, between these tables.
    - Accelerated query tables
      Accelerated query tables, or AQTs, store information about data marts that the database server uses to determine which queries can be processed by Informix® Warehouse Accelerator.
    - Data marts
      Typically, data marts contain a subset of the tables in your database. The data marts can also contain a subset of the columns within a table. This configuration is advantageous when you are using the TCP/IP loopback optimization between Informix® and IWA, because it provides a seamless experience for the customer.
    - Create a data mart
      After you configure the accelerator server and set up the connection between an accelerator and the database server, you need to create the data marts.
      - Workload analysis
        Workload analysis is a process of designing an effective data mart.
      - Creating data mart definitions manually by using workload analysis
        Workload analysis involves two main steps, gathering information about your query workload, which is known as query probing, and analysis of the query probing data.
        Example: Create a data mart from the command line
        Use this step-by-step example as a guide to create, deploy, and load a data mart from the command line using workload analysis.
        Contents of query probing data
        The query probing data contains a set of records when SQL tracing is used, or a single record when SQL tracing is not used.
        Removing probing data from the database
        After you no longer need the probing data, you can use a SET ENVIRONMENT statement to remove it from the database that contains the warehousing data. Remove probing data when you want to start a new probing session and you want to ensure that none of the previous probing data that is stored in memory is included in the new data mart definition.
    - Deploy and load the data mart
      To deploy a data mart, you specify the accelerator that will contain the data mart definition file. When you load a data mart, the related data is unloaded from the Informix® tables and transferred to the accelerator server.
    - Types of tables for acceleration
      You can load and accelerate data from tables, external tables, synonyms, views, and time series virtual tables.
    - Update a data mart
      You can refresh the data in a data mart. You can update the data mart definition when you change the warehouse schema.
    - Drop a data mart
      You can drop a data mart if you no longer need the data mart or if you need to update the data in a data mart.
    - Data marts for time series data
      You can create data marts that contain tables that represent time series data. Typically, such tables are fact tables in the data mart.
  - Turning on query acceleration
    Before queries can be routed to the accelerator server for processing, you must set the use_dwa session environment variable.
  - SQL administration routines
    You can use the Informix® Warehouse Accelerator functions and procedures from the command line or in an application to automate administration tasks.
  - Reversion requirements for Informix Warehouse Accelerator
  - Troubleshooting
  - Sample warehouse schema
    The Informix® Warehouse Accelerator examples use this sample warehouse schema.
  - Sysmaster interface (SMI) pseudo tables for query probing data
    The SMI tables provide a way to access probing data in a relational form, which is most convenient for further processing.

Removing probing data from the database

After you no longer need the probing data, you can use a SET ENVIRONMENT statement to remove it from the database that contains the warehousing data. Remove probing data when you want to start a new probing session and you want to ensure that none of the previous probing data that is stored in memory is included in the new data mart definition.

About this task

To remove all of the probing data from the database:

Procedure

Connect to the database where the warehousing data resides.
Run the SET ENVIRONMENT use_dwa 'probe cleanup' statement.
For example:
```
$ dbaccess stores_demo -
> set environment use_dwa 'probe cleanup';
Environment set.
```
To check if the probing data is removed, run the onstat -g probe command.
Important: The probing data is stored in memory. The data is automatically removed when the database server is shut down.