Easy IWA: Part 1 – The TPCH database, data generation and IBM Informix 12.10.FC4

In essence, the schema consists of 8 tables, 8 explicit unique indexes supporting 8 primary keys and 9 explicit indexes supporting 9 foreign keys.

The tpch dbgen utility generates, by default, a set of flat files suitable for loading into the tpch schema with the size based on the “Scale Factor” argument; a scale factor of 1 produces a complete data set of approximately 1 GB, a scale factor of 10 produces a data set of approximately 10 GB etc.

Download the following zip file http://www.tpc.org/tpch/spec/tpch_2_17_0.zip to a temporary directory and unzip.

Go to the extracted tpch_2_17_0/dbgen directory and copy makefile.suite to Makefile; within the Makefile amend the following to suit your environment:


Then run make ensuring a clean compilation!

After making sure that there is adequate filesystem disk space available (i.e. more than 1 GB!), run ./dbgen –s 1

If the following files are produced, then dbgen has been successfully built:


For completeness and readability, perform the following:

• Remove the just generated .tbl files under ../tpch_2_17_0/dbgen
• Create a new directory, /home/Informix/dbgen_article (for example)
• Copy ./tpch_2_17_0/dbgen/dists.dss to /home/Informix/dbgen_article/
• Copy ./tpch_2_17_0/dbgen/dbgen to /home/Informix/dbgen_article/

The dbgen utility can be run with various options, some examples are detailed below:

• ./dbgen —
  • Show complete usage
• ./dbgen –s 1 –f
  • Force overwrite of existing files
• ./dbgen –s 1 –T c
  • Generate just the customers (there are options for each table)

The following helper script generates all table data in parallel as flat files:


The generated data can also be placed, in parallel, on pipes with a slight amendment to the above script:


However, the data generation will not proceed until each pipe has started to be read; the following helper script can be used for flushing all data through the pipes:


Hint – wait until 100% displayed for each dbgen execution before executing this script.

With the above information, there are two approaches that can be followed to load data; one is loading the data from flat files and the second is loading the data from pipes.

Using the region table as an example, the following SQL details:

• Creation of the database
• Creation of the region table as raw
• Creation of an external “disk” table region_ext “sameas” region
• Insertion of data into the region table
• Altering the region table to standard
• The addition of a unique index and the primary key to the region table

Before running this SQL, the file /home/informix/dbgen_article/region.tbl should be created using ./dbgen -v -T r –s 1:

Copy to Clipboard
create database jj_dbgen in datadbs_01 with buffered log;
create raw table "informix".region (
r_regionkey integer not null,
r_name char(25) not null,
r_comment varchar(152) ) lock mode row;
create external table "informix".region_ext sameas region
using ( datafiles("disk:/home/informix/dbgen_article/region.tbl"));
insert into region select * from region_ext;
alter table region type (standard);
create unique index region_pk on region (r_regionkey);
alter table region add constraint (primary key (r_regionkey));

Hint – the DBDATE format is YMD4-
Hint – the database, jj_dbgen, cannot be dropped until a Level 0 archive is performed
A fake backup can be run using onbar –b –F
Alternatively, the individual tables can be dropped without a Level 0 archive

The only differences to load from a pipe as opposed to disk are:

• Remove the flat file “region.tbl” rm region.tbl
• Create the “region.tbl” as a pipe mknod region.tbl p
• Amend the external table definition for region_ext as a “pipe” create external table “informix”.region_ext sameas region using ( datafiles(“pipe:/home/informix/dbgen_article/region.tbl”));
• Prime the region pipe with data – note this will remain running ./dbgen –v –T r –s 1

Then run the preceding short SQL Hint – do not attempt to read from an external table based on pipes – IT WILL HANG – unless there is something to read. If a read is attempted then a simple echo “A” > tablename.tbl can be run and the SQL will complete.

Below is the complete SQL which is able to be run using dbaccess – jj_dbgen.sql after running jj_dbgen.sh to generate the data as flat files.

Modify the database creation statement to denote an appropriate dbspace.

In order to load the data from pipes, change the external table definitions from “disk” to “pipe” and modify the jj_dbgen_data.sh script to generate the data on pipes and run in the background ./jj_dbgen_data.sh &, then run dbaccess – jj_dbgen.sql.

It should be noted that the 12.10.xC4 feature of “NOVALIDATE” when creating foreign keys is being used; remove the “NOVALIDATE” if working with versions prior to 12.10.FC4