Example: Accelerating time series data

Use this step-by-step example as a guide to accelerate Informix® time series data by using Informix® Warehouse Accelerator.

The SQL statements that are used in this example are run in the DB-Access utility, and are preceded by >. Commands that are run from the shell are preceded by $.

This example uses the stores_demo database that is created by the command dbaccessdemo. To create the stores_demo database, run the following command:

$ dbaccessdemo stores_demo -log

The workload in this example consists of the following two queries:

select min(tstamp), max(tstamp) from ts_data_v;
select first 3 state,avg(value) average
  from ts_data_v v, Customer_ts_data l, customer c
  where v.loc_esi_id = l.loc_esi_id and l.customer_num = c.customer_num
  group by 1 order by 2 desc;

The first query selects the minimum and maximum time stamp from the ts_data_v table. The second query selects the top three states and the average of the 'value' column.

Step 1: Connect to database stores_demo

$ dbaccess - -
> database stores_demo;
Database selected.

Step 2: Run and analyze workload on Informix®

> set environment use_dwa 'probe cleanup';
Environment set.
> set environment use_dwa 'probe start';
Environment set.
> select min(tstamp), max(tstamp) from ts_data_v;
(min)                     (max)                     
2012-11-10 00:00:00.00000 2011-02-07 23:45:00.00000
1 row(s) retrieved.
> select first 3 state,avg(value) average
  from ts_data_v v, Customer_ts_data l, customer c
  where v.loc_esi_id = l.loc_esi_id and l.customer_num = c.customer_num
  group by 1 order by 2 desc;
state          average 
DE    0.70850671296296
NY    0.47548564814815
FL    0.42491203703704
3 row(s) retrieved.
> set environment use_dwa 'probe stop';
Environment set.

Step 3: Convert results of workload analysis to data mart definition, create and load the data mart

> execute procedure ifx_probe2Mart('stores_demo','demo_mart');
Routine executed.
> execute function ifx_createMart('demo_dwa','demo_mart');
(expression)  The operation was completed successfully. 
1 row(s) retrieved.
> execute function ifx_loadMart('demo_dwa','demo_mart','NONE');
(expression)   The operation was completed successfully. 
1 row(s) retrieved.

Step 4: Run workload on Informix® Warehouse Accelerator

> set environment use_dwa 'accelerate on';
Environment set.
> select min(tstamp), max(tstamp) from ts_data_v;
(min)                     (max)                     
2012-11-10 00:00:00.00000 2011-02-07 23:45:00.00000
1 row(s) retrieved.
> select first 3 state,avg(value) average
  from ts_data_v v, Customer_ts_data l, customer c
  where v.loc_esi_id = l.loc_esi_id and l.customer_num = c.customer_num
  group by 1 order by 2 desc;
state          average 
DE    0.70850671296296
NY    0.47548564814814
FL    0.42491203703704
3 row(s) retrieved.

Step 5: Drop the data mart

> execute function ifx_dropMart('demo_dwa','demo_mart');
(expression)   The operation was completed successfully. 
1 row(s) retrieved.

Step 6: Create the data mart again

> execute function ifx_createMart('demo_dwa','demo_mart');
(expression)  The operation was completed successfully. 
1 row(s) retrieved.

Step 7: Assign calendar 'ts_1month' to table 'ts_data_v'

> execute function ifx_TSDW_setCalendar(
  'demo_dwa','demo_mart','informix','ts_data_v','ts_1month');
(expression)  The operation was completed successfully. 
1 row(s) retrieved.

Step 8: Create time window for January 2011

> execute function ifx_TSDW_createWindow(
  'demo_dwa','demo_mart','informix','ts_data_v',
  '2011-01'::datetime year to month, '2011-02'::datetime year to month);
(expression)  The operation was completed successfully. 
1 row(s) retrieved.

Step 9: Load the data mart

> execute function ifx_loadMart('demo_dwa','demo_mart','NONE');
(expression)   The operation was completed successfully. 
1 row(s) retrieved.

Step 10: Run workload on Informix® Warehouse Accelerator

> select min(tstamp), max(tstamp) from ts_data_v;
(min)                     (max)                     
2011-01-01 00:00:00.00000 2011-01-31 23:45:00.00000
1 row(s) retrieved.
> select first 3 state,avg(value) average
  from ts_data_v v, Customer_ts_data l, customer c
  where v.loc_esi_id = l.loc_esi_id and l.customer_num = c.customer_num
  group by 1 order by 2 desc;
state          average 
DE    1.36793313172043
NY    0.51537264784946
OK    0.51153326612903
3 row(s) retrieved.

Step 11: Move time window forward one month from January to February

> execute function ifx_TSDW_moveWindows(
  'demo_dwa','demo_mart','informix','ts_data_v');
(expression)  The operation was completed successfully. 
1 row(s) retrieved.

Step 12: Run workload on Informix® Warehouse Accelerator

> select min(tstamp), max(tstamp) from ts_data_v;
(min)                     (max)                     
2011-02-01 00:00:00.00000 2011-02-07 23:45:00.00000
1 row(s) retrieved.
> select first 3 state,avg(value) average
  from ts_data_v v, Customer_ts_data l, customer c
  where v.loc_esi_id = l.loc_esi_id and l.customer_num = c.customer_num
  group by 1 order by 2 desc;
state          average 
MA    0.92973660714286
NY    0.46185416666667
NJ    0.38778348214286
3 row(s) retrieved.

Step 13: Add a new measurement reading for a customer in NY

> insert into ts_data_v(loc_esi_id,measure_unit,direction,tstamp,value) values
  ('4727354321757220','KWH','P',
   '2011-02-08 00:15'::datetime year to minute, 500);
1 row(s) inserted.

Step 14: Update the virtual partition of February 2011

> execute function ifx_TSDW_updatePartition(
  'demo_dwa','demo_mart','informix','ts_data_v',
  '2011-02'::datetime year to month);
(expression)  The operation was completed successfully. 
1 row(s) retrieved.

Step 15: Run workload on Informix® Warehouse Accelerator

> select min(tstamp), max(tstamp) from ts_data_v;
(min)                     (max)                     
2011-02-01 00:00:00.00000 2011-02-08 00:15:00.00000
1 row(s) retrieved.
> select first 3 state,avg(value) average
  from ts_data_v v, Customer_ts_data l, customer c
  where v.loc_esi_id = l.loc_esi_id and l.customer_num = c.customer_num
  group by 1 order by 2 desc;
state          average 
NY    1.20410995542348
MA    0.92973660714286
NJ    0.38778348214286
3 row(s) retrieved.

Step 16: Remove time window for February 2011

> execute function ifx_TSDW_dropWindow(
  'demo_dwa','demo_mart','informix','ts_data_v',
  '2011-02'::datetime year to month);
(expression)  The operation was completed successfully. 
1 row(s) retrieved.

Step 17: Run workload on Informix® Warehouse Accelerator

> select min(tstamp), max(tstamp) from ts_data_v;
(min)                     (max)                     
                                                   
1 row(s) retrieved.
> select first 3 state,avg(value) average
  from ts_data_v v, Customer_ts_data l, customer c
  where v.loc_esi_id = l.loc_esi_id and l.customer_num = c.customer_num
  group by 1 order by 2 desc;
state          average 
No rows found.

Step 18: Drop the data mart

> execute function ifx_dropMart('demo_dwa','demo_mart');
(expression)   The operation was completed successfully. 
1 row(s) retrieved.
Database closed.