This third article in the Easy IWA series provides specific worked examples of creating two Data Marts under IBM Informix Dynamic Server and IBM Informix Warehouse Accelerator 12.10.FC4. Both examples use the “smart mart” utility, with the first example loading from external tables into IBM Informix tables and subsequently into an IWA Data Mart; the second loads directly into an IWA Data Mart from external tables. Both examples use the TPCH data generation utility and schema as detailed in a previous article, with the addition of the removal of unsuitable “*_comment” columns via a simple in-line pipe and sed or awk approach.
The creation and loading of a Data Mart under IBM Informix can at first appear convoluted; however, it is a straight forward set of activities that can be placed in a single, repeatable SQL script.
The process described here is based on the following premises (the scripts are written so that they can be re-run; the first run will report an error regarding the dropping of the source database and the dropping of the Data Mart):
An IBM Informix instance with an IWA Accelerator (JJDWA1) is present and running
No databases exist for the source of data for the Accelerator (“jj_tpch_internal” and “jj_tpch_external”)
Data will be generated using the TPCH data generation utility with redundant “*_comment” column data removed in-line (jj_tpch_data_stripped.sh)
The scripts for this article can be found in the resources section at the end of this article
If using Scale Factor 1, then both Data Marts are able to loaded, but this is running close to the limit imposed by having 2,560 MB RAM allocated to the WORKERS and 512 allocated to the CO-ORDINATOR
Two approaches will be described:
- Creation and loading a Data Mart from IBM Informix standard tables (jj_tpch_internal_stripped.sql)
- Creation of a TPCH database schema (excluding “*_comment” columns) and defining external tables defined as pipes based on the standard tables
- Loading IBM Informix tables with TPCH data from the external tables using the TPCH data generation utility (jj_tpch_data_stripped.sh)
- Loading the IWA Data Mart from IBM Informix tables
- Creation and loading a Data Mart from IBM Informix external tables (jj_tpch_external_stripped.sql)
- Creation of a TPCH database schema (excludes “*_comment” columns) and defining external tables based on the standard tables
- Loading IBM IWA directly from the external tables using the TPCH data generation utility (jj_tpch_data_stripped.sh)
As the data generation is the same for both approaches, the first issue to address is removal of the last “*_comment” field from the data generation scripts to ensure effective use of available resources.
Removal of “*_comment” columns from data generation
The following script achieves this, using awk or sed to remove in-line the last “|” delimited field, placing all data on respective pipes for subsequent usage by external tables:
This article, and the attached files, demonstrate the complete process of creation and loading of an IBM Informix Warehouse Accelerator, using both internal table data (standard tables) and external tables ( based on pipes) as the source of data. The process is straight forward and the provided examples provide a clear initial basis to build upon. Using external tables, any data source can potentially be transformed into a data mart within IWA enabling high performance “what if” analysis in a discrete environment.
The code fix suggested above is provided “as is” without warranty of any kind, either express or implied, including without limitation any implied warranties of condition, uninterrupted use, merchantability, fitness for a particular purpose, or non-infringement.
If you have any questions regarding data marts and IBM Informix Warehouse Accelerator and would like to find out more, simply contact us.