The information provided applies to the Informix Server Version 11.70.xC2 and later.
In order to demonstrate some conecpts and best practises for the Informix Warehouse Accelerator, I created an imaginary example database that constitutes a small snowflake schema. I call this database the "iwadb". It is comprised of twelve tables, with the single fact table partlist at the center of the snowflake. A simplified depiction of the schema (with the tables only) looks like this:
A more detailed picture with all the relevant column names can be found as PDF-file contained in the tar-file described below.
Note: Even though some parts of iwadb or its data may look like a real database with real data, iwadb and the contained data is completely artificial and without any connection or meaning to the real world.
Download the complete iwadb database as a gzip-compressed tar-file:
After downloading the file unpack it with a command like the following, at best to an empty directory:
% tar zxvf <file>
The database and accompanying utilities and shell scripts have been used successfully on a Linux system running both, the accelerator and Informix Server with a DBSpace of 2 KB page size.
Following is a short description on how to get started with iwadb. For more detailed information please see the file README.txt contained in the tar-file.
To build the database, a DBSpace with about 23 MB of free space is
needed. The database can be created with the shell script
iwadb_create.sh contained in the tar-file. Use a command
% iwadb_create.sh <database> <dbspace> -v
By default this will create all twelve tables, load the data, create
unique constraints on the needed columns and do
LOW. This will be sufficient to later create a complete data mart
with 1:n relationships between the tables. If primary and foreign key
constraints are desired, edit the script
before running it to set the variable CREATE_PRIMARY_FOREIGN_KEYS
To execute a query the utility
runsql can be used. With
the query contained in a file,
runsql can be used with a
command like this:
% cat <sql file> | runsql -v
The tar-file contains two sample queries, each one in a separate file named "q0?.sql". These queries show the structure of some typical analytical queries used in data warehousing.
In some of the next blog entries we will see how to use the example java utilities as command line tools to connect the Informix server to an existing Informix Warehouse Accelerator and create a data mart.
A word on performance:
First of all this database is intended as an example database to illustrate the concepts of executing OLAP-style queries using the Informix Warehouse Accelerator. Therefore the database has been designed to have a snowflake schema, but at the same time is simple enough to be understood easily and has a small footprint for quick download and creation, even in a rather small test system. As such this database does not serve well for performance comparisons of normal query execution (by the Informix server) versus accelerated query execution (by the Informix Warehouse Accelerator). It should not be expected that any realistic performance difference can be demonstrated with this database.