November 30, 2015

Coming up with a Hybrid Database Implementation model to fulfil a TimeSeries + NoSQL requirement


One of the previous blog works 'Simple JSON / BSON Implementation' demonstrated how simple it is to call JSON / BSON datatypes as part of the schema definition and implement a NoSQL setup to handle unstructured datasets.

Let us take another step to help understand the implementation of JSON / BSON types in a TimeSeries environment, that deals with time series datasets that are both structured and unstructured in nature. 

Consider a scenario in Sensor Data Management, where the setup has 'n' number of sensors, sending data at various intervals ( fixed or irregular intervals ). It's straight forward to create a TimeSeries table, provided we have all relevant information in hand. Now, if the number of sensors and the number of values / data elements that each sensor shares, varies over time ( either increase or decrease ), then, having a fixed schema will pose good amount of challenges. 

That's where, having a JSON / BSON implementation within TimeSeries, enables IBM Informix to tackle the challenges of Sensor Data Management, with relative ease and prove that IBM Informix a True Hybrid Database Server.

The following shell script has been designed to showcase the flow, that helps understand the basic objects of TimeSeries viz. Calendar, Container, TimeSeries, Virtual Table Interface (VTI) and SQL API, while working with Hybrid Database model. It takes no more than 10min to run the shell script and understand Informix TimeSeries syntax.

1.      The shell script is well tested on Linux and might need minor changes on Unix platform. It won’t work on Windows platform. However, you can copy paste SQL statements high lighted in blue in following section and execute on windows manually.
2.      Ensure that Informix engine is online. You can use “onstat –“  command
3.      Download the shell script available in the zip file ( Or Copy - Paste the following link on to the Browser:
https://drive.google.com/open?id=0B4mxiMcgUauaMENkNVdxZXJRQlU )
4.      Unzip the file on linux/Unix machine under some test directory
5.      You might need to execute dos2unix command for each file before execution
6.      Ensure that 'main.sh' and 'func.sh' have Execute Privileges
7.      Execute main.sh and just follow the instructions.

Output of shell script should look like this :

################################################################
Welcome to TimeSeries demo.
This program has sections that demonstrate capabilities of TimeSeries.
Flow in each section is Setup, Data Load & Data Retrieval
please enter your choice of TimeSeries demo
 1      : TimeSeries Demo
 2      : JSON Demo
 3      : TimeSeries with JSON Demo
 4      : TimeSeries with Rolling Window Concept
 5      : TimeSeries with Hertz Frequency Concept
 6      : TimeSeries with Swinging Door Compression Algorithm

3

################################################################
 You have chosen a Demo on Informix TimeSeries with NoSQL
################################################################

Please read the options carefully :-
If you are executing the TimeSeries Demo for the first time, then
        Press '1'       : To Perform the Setup and create the Demo Environment

If you have already performed the setup for TimeSeries Demo, then
        Press ANY NUMBER: To Execute Queries against the prepared Setup

1

Cleaning up old files...
Clean up is over. Please presss Enter to continue

################################################################
Creating dbspace dbspace1

execute function admin('create dbspace','dbspace1',
'/home/amprasan/IDS1210/storage/dbspace1','100 MB','0')
45107280         6        0x60001    6        1        2048     N  BA    informix dbspace1
471ba028         6      6      0          10000      7787                  PO-B-D /home/amprasan/IDS1210/storage/dbspace1

dbspace creation .. passed

Please press Enter to continue

################################################################
Creating database demo_db

drop database if exists demo_db;

create database demo_db in dbspace1 with buffered log

################################################################
Creating row type row1

create row type row1
(
 time_stamp datetime year to fraction(5),
 value bson
);

Row creation .. passed
Please press Enter to continue

################################################################
Creating calendars calsec, cal1min and cal15min

execute procedure ifx_allow_newline('t');
insert into calendarpatterns values
(
 'sec','{1 on},second'
);
insert into calendarpatterns values
(
 'min','{1 on},minute'
);
insert into calendarpatterns values
(
 'min15','{1 on ,14 off},minute'
);
insert into CalendarTable
(
 c_name,c_calendar
)
values ('calmin','startdate(2015-07-01 00:00:00.00000),
pattstart(2015-07-01 00:00:00.0000),pattname(min)');

insert into CalendarTable
(
 c_name,c_calendar
)
values ('calsec','startdate(2015-07-01 00:00:00.00000),
pattstart(2015-07-01 00:00:00.0000),pattname(sec)');

insert into CalendarTable
(
 c_name,c_calendar
)
values ('cal15min','startdate(2015-07-01 00:00:00.00000),
pattstart(2015-07-01 00:00:00.0000),pattname(min15)');

Calendar creation .. passed
Please press Enter to continue

################################################################
Creating table test_ts

create table test_ts
 (
   tagid char(20),
   series timeseries(row1),
   PRIMARY KEY (tagid)
) in dbspace1;
alter table test_ts
lock mode(row);

Table creation .. passed
Please press Enter to continue

################################################################
Creating container tscont1

execute procedure tscontainercreate('tscont1',
'dbspace1','row1',1024,1024);

Container creation .. passed
Please press Enter to continue

################################################################
Creating virtual table test_v

execute procedure TSCreateVirtualTab('test_v','test_ts');

Virtual table creation .. passed
Please press Enter to continue

################################################################
Initializing all tags tag1 to tag5

BEGIN;
insert into test_ts values('tag1','calendar(calsec),origin(2015-07-01 00:00:00.00000),container(tscont1),threshold(0),irregular,[]');
insert into test_ts values('tag2','calendar(calsec),origin(2015-07-01 00:00:00.00000),container(tscont1),threshold(0),irregular,[]');
insert into test_ts values('tag3','calendar(calsec),origin(2015-07-01 00:00:00.00000),container(tscont1),threshold(0),irregular,[]');
insert into test_ts values('tag4','calendar(calsec),origin(2015-07-01 00:00:00.00000),container(tscont1),threshold(0),irregular,[]');
insert into test_ts values('tag5','calendar(calsec),origin(2015-07-01 00:00:00.00000),container(tscont1),threshold(0),irregular,[]');
COMMIT;

Tags initialization .. passed
Please press Enter to continue

################################################################
Load data for Tags 1 to 5 through VTI

dbaccess demo_db load_bson.sql

Loading data through VTI .. passed
Please press Enter to continue

Congratulations!!! Your setup is successful. Please press Enter to refresh the screen and be ready to see certain data retrieval queries

################################################################
Simple select on VTI table to see range of data

select * from test_v
where tagid='tag1'
and time_stamp between '2015-07-01 01:00:00.00000' and
'2015-07-01 01:10:00.00000';

Press enter to see output

Database selected.

tagid       tag1
time_stamp  2015-07-01 01:00:00.00000
value       Q
            sure

tagid       tag1
time_stamp  2015-07-01 01:01:00.00000
value       Q
            sure

tagid       tag1
time_stamp  2015-07-01 01:02:00.00000
value       Q
            sure

tagid       tag1
time_stamp  2015-07-01 01:03:00.00000
value       Q
            sure

tagid       tag1
time_stamp  2015-07-01 01:04:00.00000
value       Q
            sure

tagid       tag1
time_stamp  2015-07-01 01:05:00.00000
value       Q
            sure

tagid       tag1
time_stamp  2015-07-01 01:06:00.00000
value       Q
            sure

tagid       tag1
time_stamp  2015-07-01 01:07:00.00000
value       Q
            sure

tagid       tag1
time_stamp  2015-07-01 01:08:00.00000
value       Q
            sure

tagid       tag1
time_stamp  2015-07-01 01:09:00.00000
value       Q
            sure

tagid       tag1
time_stamp  2015-07-01 01:10:00.00000
value       Q
            sure

11 row(s) retrieved.

Database closed.

Press enter to continue

################################################################
Unable to read through the JSON / BSON Document? Let's try again!
Simple select on VTI table to see range of data

select tagid, time_stamp, value::json series from test_v
where tagid='tag1'
and time_stamp between '2015-07-01 00:02:00.00000' and
'2015-07-01 00:04:00.00000';

Press enter to see output

Database selected.

tagid       tag1
time_stamp  2015-07-01 00:02:00.00000
series      {"lon":50.23487,"lat":4.87345,"temp":38.453,"humidity":20.235,"pressure":100}

tagid       tag1
time_stamp  2015-07-01 00:03:00.00000
series      {"lon":50.36746,"lat":5.12643,"temp":37.237,"humidity":21.344,"pressure":101}

tagid       tag1
time_stamp  2015-07-01 00:04:00.00000
series      {"lon":51.46758,"lat":4.87345,"temp":38.947,"humidity":22.137,"pressure":102}

3 row(s) retrieved.

Database closed.

Press enter to continue

################################################################
Simple select on TimeSeries table to see range of data

select 'tag1',
clip(series,'2015-07-01 00:02:00.00000'::datetime year to fraction(5) ,
'2015-07-01 00:08:00.00000'::datetime year to fraction(5))
from test_ts where tagid='tag1';

Press enter to see output

Database selected.

(constant)    tag1
(expression)  origin(2015-07-01 00:02:00.00000), calendar(calsec), container(tscont1), threshold(0), irregular, [('{"lon":50.23487,"lat":4.87345,"temp":38.453,"humidity":20.235,"pressure":100}')@2015-07-01 00:02:00.00000, ('{"lon":50.36746,"lat":5.12643,"temp":37.237,"humidity":21.344,"pressure":101}')@2015-07-01 00:03:00.00000, ('{"lon":51.46758,"lat":4.87345,"temp":38.947,"humidity":22.137,"pressure":102}')@2015-07-01 00:04:00.00000, ('{"lon":51.98765,"lat":4.36746,"temp":37.846,"humidity":20.936,"pressure":103}')@2015-07-01 00:05:00.00000, ('{"lon":52.12643,"lat":4.98727,"temp":38.376,"humidity":21.049,"pressure":104}')@2015-07-01 00:06:00.00000, ('{"lon":52.38765,"lat":4.76534,"temp":37.365,"humidity":22.387,"pressure":105}')@2015-07-01 00:07:00.00000, ('{"lon":53.98727,"lat":4.46758,"temp":38.376,"humidity":20.182,"pressure":106}')@2015-07-0100:08:00.00000]

1 row(s) retrieved.

Database closed.

Press enter to continue

################################################################
Select on TimeSeries table and output in traditional relational form

select 'tag1',time_stamp,value::json from
table (( select tssettolist(clip(series,
'2015-07-01 01:00:00.00000'::datetime year to fraction(5),
'2015-07-01 01:10:00.00000'::datetime year to fraction(5)))
::list(row1 not null)
from test_ts
where tagid='tag1'));

Press enter to see output

Database selected.

(constant)    tag1
time_stamp    2015-07-01 01:00:00.00000
(expression)  {"lon":54.37846,"lat":4.87345,"temp":38.387,"humidity":22.387,"pressure":108}

(constant)    tag1
time_stamp    2015-07-01 01:01:00.00000
(expression)  {"lon":54.48765,"lat":5.38765,"temp":37.145,"humidity":20.378,"pressure":109}

(constant)    tag1
time_stamp    2015-07-01 01:02:00.00000
(expression)  {"lon":50.23487,"lat":4.87345,"temp":38.453,"humidity":20.235,"pressure":100}

(constant)    tag1
time_stamp    2015-07-01 01:03:00.00000
(expression)  {"lon":50.36746,"lat":5.12643,"temp":37.237,"humidity":21.344,"pressure":101}

(constant)    tag1
time_stamp    2015-07-01 01:04:00.00000
(expression)  {"lon":51.46758,"lat":4.87345,"temp":38.947,"humidity":22.137,"pressure":102}

(constant)    tag1
time_stamp    2015-07-01 01:05:00.00000
(expression)  {"lon":51.98765,"lat":4.36746,"temp":37.846,"humidity":20.936,"pressure":103}

(constant)    tag1
time_stamp    2015-07-01 01:06:00.00000
(expression)  {"lon":52.12643,"lat":4.98727,"temp":38.376,"humidity":21.049,"pressure":104}

(constant)    tag1
time_stamp    2015-07-01 01:07:00.00000
(expression)  {"lon":52.38765,"lat":4.76534,"temp":37.365,"humidity":22.387,"pressure":105}

(constant)    tag1
time_stamp    2015-07-01 01:08:00.00000
(expression)  {"lon":53.98727,"lat":4.46758,"temp":38.376,"humidity":20.182,"pressure":106}

(constant)    tag1
time_stamp    2015-07-01 01:09:00.00000
(expression)  {"lon":53.76534,"lat":5.48765,"temp":37.298,"humidity":21.398,"pressure":107}

(constant)    tag1
time_stamp    2015-07-01 01:10:00.00000
(expression)  {"lon":54.37846,"lat":4.87345,"temp":38.387,"humidity":22.387,"pressure":108}

11 row(s) retrieved.

Database closed.

Press enter to continue

################################################################
Create & Load a Relational Table

create table test_rel
 (
   tag_id char(20),
   vendor varchar(20),
   PRIMARY KEY (tag_id)
) in dbspace1;
alter table test_ts
lock mode(row);

insert into test_rel values('tag1','IBM');
insert into test_rel values('tag2','ABC');
insert into test_rel values('tag3','XYZ');
insert into test_rel values('tag4','IBM');
insert into test_rel values('tag5','ABC');


Table creation .. passed
Please press Enter to continue

################################################################
Perform JOIN operation of Relational Table against TS + JSON Table

SELECT vendor, tagid, value::json sensors from test_rel tr, test_v ts
where ts.tagid = tr.tag_id and vendor = 'IBM'
and time_stamp between '2015-07-01 00:02:00.00000' and '2015-07-01 00:03:00.00000';

Press enter to see output

Database selected.

vendor   IBM
tagid    tag1
sensors  {"lon":50.23487,"lat":4.87345,"temp":38.453,"humidity":20.235,"pressure":100}

vendor   IBM
tagid    tag1
sensors  {"lon":50.36746,"lat":5.12643,"temp":37.237,"humidity":21.344,"pressure":101}

vendor   IBM
tagid    tag4
sensors  {"lon":50.23487,"lat":4.87345,"temp":38.453,"humidity":20.235,"pressure":100}

vendor   IBM
tagid    tag4
sensors  {"lon":50.36746,"lat":5.12643,"temp":37.237,"humidity":21.344,"pressure":101}

4 row(s) retrieved.

Database closed.

Press enter to continue

Congratulations!!! Your Demo is completed Successfully.
We hope you had a pleasant learning experience

Click Here to read more about IBM Informix JSON Compatibility

Click Here to read through an example that illustrates implementing TimeSeries through Informix Wire Listener

Simple JSON / BSON Implementation


Continuing with innovations and enhancements aligning to the Industry trends and patterns, IBM Informix has always positioned itself, as the Next Generation Database. Late in 2013, IBM Informix leaped ahead from being the Object Relational Database to a NoSQL Database ( or Not-Only SQL DBMS ).

Support for JSON & BSON as built-in datatypes helped store NoSQL (Unstructured ) datasets, while support for WIRE Listener enhanced connectivity options to the database, where MongoDB API and REST API can be used to access the NoSQL datasets, along with the Relational datasets, that co-exist, under the single table, making IBM Informix a True Hybrid Database server.

To quickly illustrate on the ease of implementation and use, we have put together a shell script that shall detail the flow of calling JSON/BSON types while designing the table and the access mechanism to read through the NoSQL table.

1.      The shell script is well tested on Linux and might need minor changes on Unix platform. It won’t work on Windows platform. However, you can copy paste SQL statements high lighted in blue in following section and execute on windows manually.
2.      Ensure that Informix engine is online. You can use “onstat –“  command
3.      Download the shell script available in the zip file ( Or Copy - Paste the following link on to the Browser:
https://drive.google.com/open?id=0B4mxiMcgUauaMENkNVdxZXJRQlU )
4.      Unzip the file on linux/Unix machine under some test directory
5.      You might need to execute dos2unix command for each file before execution
6.      Ensure that 'main.sh' and 'func.sh' have Execute Privileges
7.      Execute main.sh and just follow the instructions.

Output of shell script should look like this :

################################################################
Welcome to TimeSeries demo.
This program has sections that demonstrate capabilities of TimeSeries.
Flow in each section is Setup, Data Load & Data Retrieval
please enter your choice of TimeSeries demo
 1      : TimeSeries Demo
 2      : JSON Demo
 3      : TimeSeries with JSON Demo
 4      : TimeSeries with Rolling Window Concept
 5      : TimeSeries with Hertz Frequency Concept
 6      : TimeSeries with Swinging Door Compression Algorithm

2

################################################################
 You have chosen a Demo on Informix NoSQL
################################################################

Please read the options carefully :-
If you are executing the NoSQL Demo for the first time, then
        Press '1'       : To Perform the Setup and create the Demo Environment

If you have already performed the setup for NoSQL Demo, then
        Press ANY NUMBER: To Execute Queries against the prepared Setup

1

Cleaning up old files...
Clean up is over. Please presss Enter to continue

################################################################
Creating dbspace dbspace1

execute function admin('create dbspace','dbspace1',
'/home/amprasan/IDS1210/storage/dbspace1','100 MB','0')
45107280         6        0x60001    6        1        2048     N  BA    informix dbspace1
471ba028         6      6      0          10000      5371                  PO-B-D /home/amprasan/IDS1210/storage/dbspace1

dbspace creation .. passed

Please press Enter to continue

################################################################
Creating database demo_db

drop database if exists demo_db;
create database demo_db in dbspace1 with buffered log

database creation .. passed

Please press Enter to continue

################################################################
Creating table test_ts

create table test_ts
 (
   tagid char(20),
   time_stamp datetime year to fraction(5),
   series bson
) in dbspace1;
alter table test_ts
lock mode(row);

Table creation .. passed

Please press Enter to continue

################################################################
Load data for all tags into the table test_ts

dbaccess demo_db load_bson2.sql

Loading data into NoSQL (JSON/BSON) table .. passed

Please press Enter to continue

Congratulations!!! Your setup is successful. Please press Enter to refresh the screen and be ready to see certain data retrieval queries

################################################################
Simple select on NoSQL (JSON/BSON) table to see range of data

select * from test_ts
where tagid='tag1'
and time_stamp between '2015-07-01 00:02:00.00000' and
'2015-07-01 00:04:00.00000';

Press enter to see output

Database selected.

tagid       tag1
time_stamp  2015-07-01 00:02:00.00000
series      L

tagid       tag1
time_stamp  2015-07-01 00:03:00.00000
series      L

tagid       tag1
time_stamp  2015-07-01 00:04:00.00000
series      L

3 row(s) retrieved.

Database closed.

Press enter to continue

################################################################
Not Happy with the result set returned? Let's Try again
Simple select on NoSQL (JSON/BSON) table to see range of data

select tagid, time_stamp, series::json series from test_ts
where tagid='tag1'
and time_stamp between '2015-07-01 00:02:00.00000' and
'2015-07-01 00:04:00.00000';

Press enter to see output

Database selected.

tagid       tag1
time_stamp  2015-07-01 00:02:00.00000
series      {"lon":50.23487,"lat":4.87345,"temp":38.453,"humidity":20.235,"val":100}

tagid       tag1
time_stamp  2015-07-01 00:03:00.00000
series      {"lon":50.36746,"lat":5.12643,"temp":37.237,"humidity":21.344,"val":101}

tagid       tag1
time_stamp  2015-07-01 00:04:00.00000
series      {"lon":51.46758,"lat":4.87345,"temp":38.947,"humidity":22.137,"val":102}

3 row(s) retrieved.

Database closed.

Press enter to continue

################################################################
Select on NoSQL (JSON/BSON) table to pick individual value from the Document Store

select tagid, time_stamp, bson_get(series,'temp')::json series from test_ts
where tagid='tag2'
and time_stamp = '2015-07-01 00:02:00.00000';

Press enter to see output

Database selected.

tagid       tag2
time_stamp  2015-07-01 00:02:00.00000
series      {"temp":38.453}

1 row(s) retrieved.

Database closed.

Press enter to continue

################################################################
Update a Document on NosQL (JSON/BSON) table

update test_ts set series = '{lon:40.234870,lat:5.873450,temp:18.453000,humidity:25.235000,val:200.000000}'::json::bson where tagid='tag1'
and time_stamp = '2015-07-01 00:02:00.00000';

Press enter to see output

Database selected.

1 row(s) updated.

Database closed.

Press enter to continue

Congratulations!!! Your Demo is completed Successfully.
We hope you had a pleasant learning experience

Click Here to read further on IBM Informix JSON Compatibility

Click Here to read through a IBM Technical Paper - Introducing NoSQL Capabilities

Handling Hertz Data Sets as part of Informix TimeSeries implementation


In one of the earlier posts 'Illustrating the implementation of IBM Informix TimeSeries Swing Door (Linear) Compression Algorithm', we discussed about compressing time series data sets using Swing Door algorithm to efficiently save disk space. IBM Informix TimeSeries technology also has a feature that helps group a set of Sub-Second data, defined as HERTZ TimeSeries and have them recorded in the database in a efficient manner. 

Say, for example, an organization is collecting time sereis data for a given tag, 5 times in a second, then, with each element containing 5 set of values, the data is recorded 5 times within a second, for each tag, in the database.

The Hertz TimeSeries needs to be defined / configured, while one is Initializing the Informix TimeSeries table, as shown below:

Example:
BEGIN;
INSERT INTO test_ts VALUES ('tag1',TSCreateIrr('calsec', '2015-07-01 00:00:00.00000',0,50,0,'tscont1'));
COMMIT;

Where:

50     - Indicates that one can create a time series that contains hertz data that is recorded 50 times a second.

A simple shell script has been put in place to help understand how the basic objects of TimeSeries viz. Calendar, Container, TimeSeries, Virtual Table Interface (VTI) and SQL API can be created in a flow, to showcase the implementation of Hertz TimeSeries. It takes no more than 10min to run the shell script and understand Informix TimeSeries syntax.

1.      The shell script is well tested on Linux and might need minor changes on Unix platform. It won’t work on Windows platform. However, you can copy paste SQL statements high lighted in blue in following section and execute on windows manually.
2.      Ensure that Informix engine is online. You can use “onstat –“  command
3.      Download the shell script available in the zip file ( Or Copy - Paste the following link on to the Browser:
https://drive.google.com/open?id=0B4mxiMcgUauaMENkNVdxZXJRQlU )
4.      Unzip the file on linux/Unix machine under some test directory
5.      You might need to execute dos2unix command for each file before execution
6.      Ensure that 'main.sh' and 'func.sh' have Execute Privileges
7.      Execute main.sh and just follow the instructions.

Output of shell script should look like this :

################################################################
Welcome to TimeSeries demo.
This program has sections that demonstrate capabilities of TimeSeries.
Flow in each section is Setup, Data Load & Data Retrieval
please enter your choice of TimeSeries demo
 1      : TimeSeries Demo
 2      : JSON Demo
 3      : TimeSeries with JSON Demo
 4      : TimeSeries with Rolling Window Concept
 5      : TimeSeries with Hertz Frequency Concept
 6      : TimeSeries with Swinging Door Compression Algorithm

5

################################################################
 You have chosen a Demo on Informix TimeSeries with Hertz Frequency Concept
################################################################

Please read the options carefully :-
If you are executing the TimeSeries Demo for the first time, then
        Press '1'       : To Perform the Setup and create the Demo Environment

If you have already performed the setup for TimeSeries Demo, then
        Press ANY NUMBER: To Execute Queries against the prepared Setup

1

Cleaning up old files...
Clean up is over. Please presss Enter to continue

################################################################
Creating dbspace dbspace1

execute function admin('create dbspace','dbspace1',
'/home/amprasan/IDS1210/storage/dbspace1','100 MB','0')
45107280         6        0x60001    6        1        2048     N  BA    informix dbspace1
471ba028         6      6      0          10000      5371                  PO-B-D /home/amprasan/IDS1210/storage/dbspace1

dbspace creation .. passed

Please press Enter to continue

################################################################
Creating database demo_db

drop database if exists demo_db;
create database demo_db in dbspace1 with buffered log

database creation .. passed

Please press Enter to continue

################################################################
Creating row type row1

create row type row1
(
 time_stamp datetime year to fraction(5),
 value float
);

Row creation .. passed

Please press Enter to continue

################################################################
Creating calendars calsec, cal1min and cal15min

execute procedure ifx_allow_newline('t');
insert into calendarpatterns values
(
 'sec','{1 on},second'
);
insert into calendarpatterns values
(
 'min','{1 on},minute'
);
insert into calendarpatterns values
(
 'min15','{1 on ,14 off},minute'
);
insert into CalendarTable
(
 c_name,c_calendar
)
values ('calmin','startdate(2015-07-01 00:00:00.00000),
pattstart(2015-07-01 00:00:00.0000),pattname(min)');

insert into CalendarTable
(
 c_name,c_calendar
)
values ('calsec','startdate(2015-07-01 00:00:00.00000),
pattstart(2015-07-01 00:00:00.0000),pattname(sec)');

insert into CalendarTable
(
 c_name,c_calendar
)
values ('cal15min','startdate(2015-07-01 00:00:00.00000),
pattstart(2015-07-01 00:00:00.0000),pattname(min15)');

Calendar creation .. passed

Please press Enter to continue

################################################################
Creating table test_ts

create table test_ts
 (
   tagid char(20),
   series timeseries(row1),
   PRIMARY KEY (tagid)
) in dbspace1;
alter table test_ts
lock mode(row);

Table creation .. passed

Please press Enter to continue

################################################################
Creating container tscont1

execute procedure tscontainercreate('tscont1', 'dbspace1','row1',1024,1024);

Container creation .. passed

Please press Enter to continue

################################################################
Creating virtual table test_v

execute procedure TSCreateVirtualTab('test_v','test_ts',4096);

Virtual table creation .. passed

Please press Enter to continue

################################################################
Condition to enable table to handle HERTZ Data is set while initializing the Tags
 of TimeSeries table. Here, the value '50' indicates that the TAG is being initialized to receive 50 Records per Second.
################################################################
Initializing all tags tag1 to tag5

BEGIN;
insert into test_ts values('tag1',TSCreateIrr('calsec', '2015-07-01 00:00:00.00000',0,50,0,'tscont1'));
insert into test_ts values('tag2',TSCreateIrr('calsec', '2015-07-01 00:00:00.00000',0,50,0,'tscont1'));
insert into test_ts values('tag3',TSCreateIrr('calsec', '2015-07-01 00:00:00.00000',0,50,0,'tscont1'));
insert into test_ts values('tag4',TSCreateIrr('calsec', '2015-07-01 00:00:00.00000',0,50,0,'tscont1'));
insert into test_ts values('tag5',TSCreateIrr('calsec', '2015-07-01 00:00:00.00000',0,50,0,'tscont1'));
COMMIT;


Tags initialization .. passed

Please press Enter to continue

################################################################
Load data for Tags 1 to 5 through VTI

load from hz_load.unl insert into test_v;

Loading data through VTI .. passed

Please press Enter to continue

Congratulations!!! Your setup is successful. Please press Enter to refresh the screen and be ready to see certain data retrieval queries

################################################################
Simple select on VTI table to see range of data

select * from test_v
where tagid='tag1'
and time_stamp between '2015-07-01 00:00:00.00000' and
'2015-07-01 00:00:00.20000';

Press enter to see output

Database selected.



tagid                time_stamp                         value

tag1                 2015-07-01 00:00:00.00000 100.0000000000
tag1                 2015-07-01 00:00:00.02000 101.0000000000
tag1                 2015-07-01 00:00:00.04000 102.0000000000
tag1                 2015-07-01 00:00:00.06000 103.0000000000
tag1                 2015-07-01 00:00:00.08000 104.0000000000
tag1                 2015-07-01 00:00:00.10000 105.0000000000
tag1                 2015-07-01 00:00:00.12000 106.0000000000
tag1                 2015-07-01 00:00:00.14000 107.0000000000
tag1                 2015-07-01 00:00:00.16000 108.0000000000
tag1                 2015-07-01 00:00:00.18000 109.0000000000
tag1                 2015-07-01 00:00:00.20000 100.0000000000

11 row(s) retrieved.

Database closed.

Press enter to continue

################################################################
Simple select on TimeSeries table to see range of data

select 'tag1',
clip(series,'2015-07-01 00:00:01.00000'::datetime year to fraction(5) ,
'2015-07-01 00:00:01.30000'::datetime year to fraction(5))
from test_ts where tagid='tag1';

Press enter to see output

Database selected.

(constant)    tag1
(expression)  origin(2015-07-01 00:00:01.00000), calendar(calsec), container(tscont1), threshold(0), irregular, [(100.0000000000)@2015-07-01 00:00:01.00000, (101.0000000000)@2015-07-01 00:00:01.02000, (102.0000000000)@2015-07-01 00:00:01.04000, (103.0000000000)@2015-07-01 00:00:01.06000, (104.0000000000)@2015-07-01 00:00:01.08000, (105.0000000000)@2015-07-01 00:00:01.10000, (106.0000000000)@2015-07-0100:00:01.12000, (107.0000000000)@2015-07-01 00:00:01.14000, (108.0000000000)@2015-07-01 00:00:01.16000, (109.0000000000)@2015-07-01 00:00:01.18000, (100.0000000000)@2015-07-01 00:00:01.20000, (101.0000000000)@2015-07-01 00:00:01.22000, (102.0000000000)@2015-07-01 00:00:01.24000, (103.0000000000)@2015-07-01 00:00:01.26000,(104.0000000000)@2015-07-01 00:00:01.28000, (105.0000000000)@2015-07-01 00:00:01.30000]

1 row(s) retrieved.

Database closed.

Press enter to continue

################################################################
Select on TimeSeries table and output in traditional relational form

select 'tag3',time_stamp,value from
table (( select tssettolist(clip(series,
'2015-07-01 00:10:00.00000'::datetime year to fraction(5),
'2015-07-01 00:10:00.30000'::datetime year to fraction(5)))
::list(row1 not null)
from test_ts
where tagid='tag1'));

Press enter to see output

Database selected.

(constant) time_stamp                         value

tag3       2015-07-01 00:10:00.00000 100.0000000000
tag3       2015-07-01 00:10:00.02000 101.0000000000
tag3       2015-07-01 00:10:00.04000 102.0000000000
tag3       2015-07-01 00:10:00.06000 103.0000000000
tag3       2015-07-01 00:10:00.08000 104.0000000000
tag3       2015-07-01 00:10:00.10000 105.0000000000
tag3       2015-07-01 00:10:00.12000 106.0000000000
tag3       2015-07-01 00:10:00.14000 107.0000000000
tag3       2015-07-01 00:10:00.16000 108.0000000000
tag3       2015-07-01 00:10:00.18000 109.0000000000
tag3       2015-07-01 00:10:00.20000 100.0000000000
tag3       2015-07-01 00:10:00.22000 101.0000000000
tag3       2015-07-01 00:10:00.24000 102.0000000000
tag3       2015-07-01 00:10:00.26000 103.0000000000
tag3       2015-07-01 00:10:00.28000 104.0000000000
tag3       2015-07-01 00:10:00.30000 105.0000000000

16 row(s) retrieved.

Database closed.

Press enter to continue

################################################################
Roll up data to 15min cycle using aggregateby function

SELECT *
FROM TABLE (( SELECT TSSetToList(AggregateBy('avg($value)', 'cal15min',
series, 0, '2015-07-01 00:00'::datetime year to minute,
'2015-07-01 00:30'::datetime year to minute))::list(row1 not null)
FROM test_ts
WHERE tagid='tag1'));

Press enter to see output

Database selected.

time_stamp                         value

2015-07-01 00:00:00.00000 104.5000000000
2015-07-01 00:15:00.00000 104.4751381215
2015-07-01 00:30:00.00000 104.4751381215

3 row(s) retrieved.

Database closed.

Press enter to continue

Congratulations!!! Your Demo is completed Successfully.
We hope you had a pleasant learning experience

Click Here to read further on Informix TimeSeries Hertz technology

Click Here to take a look at an Example illustrating the implementation of Hertz TimeSeries.

November 28, 2015

Implementing Rolling Window concept of IBM Informix TimeSeries technology


Have you been looking for a database configuration that enables you to only see the data for a given period, no matter, what's your data retention period is? 

In the process, have you been working on making a simple delete / purge query, as complex as possible, trying to identify the markers to delete based on the time stamp, calculating the difference in hours / days / months, and ended up deleting more than necessary? leading to non-compliance or other legal terms?

IBM Informix TimeSeries has the Rolling Window feature, a concept that involves Active and Dormant windows (Partitions on DBSpaces) as its key components, enables an organization to manage time series datasets based on defined time intervals, with just a minor configuration setting. 

A user can configure Rolling Window by defining the number of Active and Dormant windows, facilitating the applications to only view the data in Active window(s), while the data in the Dormant window(s) are still within the database, they are inaccessible to the queries. Thus Rolling Window feature effectively allows you to query the data belonging to a certain window ( time period ) with ease and automatically manages the data ( delete / purge), that's dormant in nature, based on the configuration settings, as set by the user.

Consider the scenario, where an organization working on Sensor Data Management and is required to:
  • Consolidating the data from various sensors, whose data insertion frequency varies from milliseconds to minutes, into the database. 
  • BI analytics is performed against these datasets, that are not older than 2 days. 
  • Any data older than 2 days, needs to be archived / deleted / purged and has to be managed on database side, reducing application efforts.

With Informix TimeSeries Rolling Window, you can plan to define 2 Active windows, to hold the data of last 2 days, then 2 Dormant windows, to hold the data for next 2 days ( as a buffer for any ad-hoc queries) and then define to delete any data older than 4 days. 

The configuration for Rolling Window needs to be defined, while creating the TimeSeries Containers:

execute procedure tscontainercreate('tscont1','dbspace1','row1',1024,1024, '2015-07-01 00:00:00.00000'::datetime year to fraction(5), 'day', 2, 2, 'dbspace1, dbspace2', 1, 16, 16);

Here:
'day'                      - Is the Rolling Window Time Interval. You can define HOUR / DAY / MONTH / YEAR as the time period for data retention
2                               - Is the number of Active Windows, whose data shall be accessible by the queries
2                               - Is the number of Dormant Windows, whose data shall be in database, but inaccessible to queries
'dbspace1,dbspace2' - The list of DBSpace(s), where the Active and Dormant window partitions reside.

The illustration is as follows:

1. Data for Day 1 gets inserted into the database and is available for BI queries


    2. Data for Day 2 gets inserted. BI queries access data from Day 1 & 2

    3. Data for Day 3 gets inserted. Data of Day 1 gets moved into the Dormant window and is inaccessible. BI queries continue to access the data that's available in last 48 hours

    4. Data for Day 4 gets inserted. Data from Day 2 gets moved into the Dormant window, as we had space for 2 Dormant windows and that Active window can accommodate data for last 48 hours.

    5. Data for Day 5 gets inserted. Data from Day 3 gets moved to Dormant window and now, the data from Day 1 gets automatically deleted from the database.


    Now, we have put together a simple shell script, that shall take you through the implementation of TimeSeries Rolling Window feature, in under 10 minutes. The shell script has been designed to help understand the basic objects of TimeSeries viz. Calendar, Container, TimeSeries, Virtual Table Interface (VTI) and SQL API and understand Informix TimeSeries syntax.

    1.      The shell script is well tested on Linux and might need minor changes on Unix platform. It won’t work on Windows platform. However, you can copy paste SQL statements high lighted in blue in following section and execute on windows manually.
    2.      Ensure that Informix engine is online. You can use “onstat –“  command
    3.      Download the shell script available in the zip file ( Or Copy - Paste the following link on to the browser:
    https://drive.google.com/open?id=0B4mxiMcgUauaMENkNVdxZXJRQlU )
    4.      Unzip the file on linux/Unix machine under some test directory
    5.      You might need to execute dos2unix command for each file before execution
    6.      Ensure that 'main.sh' and 'func.sh' have Execute Privileges
    7.      Execute main.sh and just follow the instructions.

    Output of shell script should look like this :


    ################################################################
    Welcome to TimeSeries demo.
    This program has sections that demonstrate capabilities of TimeSeries.
    Flow in each section is Setup, Data Load & Data Retrieval
    please enter your choice of TimeSeries demo
     1      : TimeSeries Demo
     2      : JSON Demo
     3      : TimeSeries with JSON Demo
     4      : TimeSeries with Rolling Window Concept
     5      : TimeSeries with Hertz Frequency Concept
     6      : TimeSeries with Swinging Door Compression Algorithm

    4

    ################################################################
     You have chosen a Demo on Informix TimeSeries with Rolling Window Concept
    ################################################################

    Please read the options carefully :-
    If you are executing the TimeSeries Demo for the first time, then
            Press '1'       : To Perform the Setup and create the Demo Environment

    If you have already performed the setup for TimeSeries Demo, then
            Press ANY NUMBER: To Execute Queries against the prepared Setup

    1

    Cleaning up old files...
    Clean up is over. Please presss Enter to continue

    ################################################################
    Creating dbspace dbspace1, dbspace2, dbspace3

    execute function admin('create dbspace','dbspace1',
    '/home/amprasan/IDS1210/storage/dbspace1','100 MB','0');
    execute function admin('create dbspace','dbspace2',
    '/home/amprasan/IDS1210/storage/dbspace2','30 MB','0');
    execute function admin('create dbspace','dbspace3',
    '/home/amprasan/IDS1210/storage/dbspace3','30 MB','0');
    45107280         6        0x60001    6        1        2048     N  BA    informix dbspace1
    479f9ce0         7        0x60001    7        1        2048     N  BA    informix dbspace2
    47de7cc8         8        0x60001    8        1        2048     N  BA    informix dbspace3
    471ba028         6      6      0          10000      4851                  PO-B-D /home/amprasan/IDS1210/storage/dbspace1
    4800f028         7      7      0          15360      14283                 PO-B-D /home/amprasan/IDS1210/storage/dbspace2
    47603028         8      8      0          15360      14795                 PO-B-D /home/amprasan/IDS1210/storage/dbspace3

    dbspace creation .. passed

    Please press Enter to continue

    ################################################################
    Creating database demo_db

    drop database if exists demo_db;
    create database demo_db in dbspace1 with buffered log

    database creation .. passed

    Please press Enter to continue

    ################################################################
    Creating row type row1

    create row type row1
    (
     time_stamp datetime year to fraction(5),
     value float
    );

    Row creation .. passed

    Please press Enter to continue

    ################################################################
    Creating calendars calsec, cal1min and cal15min

    execute procedure ifx_allow_newline('t');
    insert into calendarpatterns values
    (
     'sec','{1 on},second'
    );
    insert into calendarpatterns values
    (
     'min','{1 on},minute'
    );
    insert into calendarpatterns values
    (
     'min15','{1 on ,14 off},minute'
    );
    insert into CalendarTable
    (
     c_name,c_calendar
    )
    values ('calmin','startdate(2015-07-01 00:00:00.00000),
    pattstart(2015-07-01 00:00:00.0000),pattname(min)');

    insert into CalendarTable
    (
     c_name,c_calendar
    )
    values ('calsec','startdate(2015-07-01 00:00:00.00000),
    pattstart(2015-07-01 00:00:00.0000),pattname(sec)');

    insert into CalendarTable
    (
     c_name,c_calendar
    )
    values ('cal15min','startdate(2015-07-01 00:00:00.00000),
    pattstart(2015-07-01 00:00:00.0000),pattname(min15)');

    Calendar creation .. passed

    Please press Enter to continue

    ################################################################
    Creating table test_ts

    create table test_ts
     (
       tagid char(20),
       series timeseries(row1),
       PRIMARY KEY (tagid)
    ) in dbspace1;
    alter table test_ts
    lock mode(row);

    Table creation .. passed

    Please press Enter to continue

    ################################################################
    Enable Rolling Window, while creating the Container 'tscont1', by extending the 'TSCONTAINERCREATE' definition to include
    INTERVAL - Which is 'DAY' in the following command
    Active Window - Which is defined as numeric '4' indicating 4 Active Windows
    Dormat Window - Which is defined as numeric '2' indicating 2 Dormant Windows
    DBSpaces spread across - Which is defined by 'dbspace1, dbspace2, dbspace3'

    Thus enabling the TimeSeries Container to hold 4 Days of Active Data at any point of time and hold 2 Days of Dormat Data that will be inaccessible to queries
    ################################################################

    Creating container tscont1

    execute procedure tscontainercreate('tscont1','dbspace1','row1',1024,1024, '2015-07-01 00:00:00.00000'::datetime year to fraction(5), 'day', 4, 2, 'dbspace1, dbspace2, dbspace3', 1, 16, 16);

    Container creation .. passed

    Please press Enter to continue

    ################################################################
    Creating virtual table test_v

    execute procedure TSCreateVirtualTab('test_v','test_ts');

    Virtual table creation .. passed

    Please press Enter to continue

    ################################################################
    Initializing all tags tag1 to tag5

    BEGIN;
    insert into test_ts values('tag1',TSCreateIrr('calsec', '2015-07-01 00:00:00.00000',0,0,0,'tscont1'));
    insert into test_ts values('tag2',TSCreateIrr('calsec', '2015-07-01 00:00:00.00000',0,0,0,'tscont1'));
    insert into test_ts values('tag3',TSCreateIrr('calsec', '2015-07-01 00:00:00.00000',0,0,0,'tscont1'));
    insert into test_ts values('tag4',TSCreateIrr('calsec', '2015-07-01 00:00:00.00000',0,0,0,'tscont1'));
    insert into test_ts values('tag5',TSCreateIrr('calsec', '2015-07-01 00:00:00.00000',0,0,0,'tscont1'));
    COMMIT;

    Tags initialization .. passed

    Please press Enter to continue

    ################################################################
    Load data for Tags 1 to 5 through VTI for a duration of 4 Days

    load from rw_load.unl insert into test_v;

    Loading data through VTI .. passed

    Please press Enter to continue

    Congratulations!!! Your setup is successful. Please press Enter to refresh the screen and be ready to see certain data retrieval queries

    ################################################################
    Simple select on VTI table to see range of data

    select * from test_v
    where tagid='tag1'
    and time_stamp between '2015-07-02 00:00:00.00000' and
    '2015-07-02 00:00:00.20000';

    Press enter to see output

    Database selected.

    tagid                time_stamp                         value

    tag1                 2015-07-02 00:00:00.00000 100.0000000000
    tag1                 2015-07-02 00:00:00.02000 101.0000000000
    tag1                 2015-07-02 00:00:00.04000 102.0000000000
    tag1                 2015-07-02 00:00:00.06000 103.0000000000
    tag1                 2015-07-02 00:00:00.08000 104.0000000000
    tag1                 2015-07-02 00:00:00.10000 105.0000000000
    tag1                 2015-07-02 00:00:00.12000 106.0000000000
    tag1                 2015-07-02 00:00:00.14000 107.0000000000
    tag1                 2015-07-02 00:00:00.16000 108.0000000000
    tag1                 2015-07-02 00:00:00.18000 109.0000000000
    tag1                 2015-07-02 00:00:00.20000 100.0000000000

    11 row(s) retrieved.

    Database closed.

    Press enter to continue

    ################################################################
    Simple select on TimeSeries table to see range of data

    select 'tag1',
    clip(series,'2015-07-02 00:00:01.00000'::datetime year to fraction(5) ,
    '2015-07-02 00:00:01.30000'::datetime year to fraction(5))
    from test_ts where tagid='tag1';

    Press enter to see output

    Database selected.

    (constant)    tag1
    (expression)  origin(2015-07-02 00:00:01.00000), calendar(calsec), container(tscont1), threshold(0), irregular, [(100.0000000000)@2015-07-02 00:00:01.00000, (101.0000000000)@2015-07-02 00:00:01.02000, (102.0000000000)@2015-07-02 00:00:01.04000, (103.0000000000)@2015-07-02 00:00:01.06000, (104.0000000000)@2015-07-02 00:00:01.08000, (105.0000000000)@2015-07-02 00:00:01.10000, (106.0000000000)@2015-07-0200:00:01.12000, (107.0000000000)@2015-07-02 00:00:01.14000, (108.0000000000)@2015-07-02 00:00:01.16000, (109.0000000000)@2015-07-02 00:00:01.18000, (100.0000000000)@2015-07-02 00:00:01.20000, (101.0000000000)@2015-07-02 00:00:01.22000, (102.0000000000)@2015-07-02 00:00:01.24000, (103.0000000000)@2015-07-02 00:00:01.26000,(104.0000000000)@2015-07-02 00:00:01.28000, (105.0000000000)@2015-07-02 00:00:01.30000]

    1 row(s) retrieved.

    Database closed.

    Press enter to continue

    ################################################################
    Select on TimeSeries table and output in traditional relational form

    select 'tag3',time_stamp,value from
    table (( select tssettolist(clip(series,
    '2015-07-02 00:02:00.00000'::datetime year to fraction(5),
    '2015-07-02 00:03:00.30000'::datetime year to fraction(5)))
    ::list(row1 not null)
    from test_ts
    where tagid='tag1'));

    Press enter to see output

    Database selected.

    (constant) time_stamp                         value

    tag3       2015-07-02 00:02:00.00000 109.0000000000

    1 row(s) retrieved.

    Database closed.

    Press enter to continue

    ################################################################
    Roll up data to 15min cycle using aggregateby function

    SELECT *
    FROM TABLE (( SELECT TSSetToList(AggregateBy('avg($value)', 'cal15min',
    series, 0, '2015-07-02 00:00'::datetime year to minute,
    '2015-07-03 2:30'::datetime year to minute))::list(row1 not null)
    FROM test_ts
    WHERE tagid='tag2'));

    Press enter to see output

    Database selected.

    time_stamp                         value

    2015-07-02 00:15:00.00000 104.4751381215
    2015-07-02 00:30:00.00000 109.0000000000
    2015-07-02 00:45:00.00000 109.0000000000
    2015-07-02 01:00:00.00000 109.0000000000
    2015-07-02 01:15:00.00000 109.0000000000
    2015-07-02 01:30:00.00000 109.0000000000
    ........
    ........
    2015-07-02 23:15:00.00000 109.0000000000
    2015-07-02 23:30:00.00000 109.0000000000
    2015-07-02 23:45:00.00000 109.0000000000
    2015-07-03 00:00:00.00000 109.0000000000
    2015-07-03 00:15:00.00000 104.4751381215

    97 row(s) retrieved.

    Database closed.

    Press enter to continue

    ################################################################
    Load Data for all Tags on Day 5 through VTI

    load from rw2_load.unl insert into test_v;

    Loading data through VTI .. passed
    Please press Enter to continue

    ################################################################
    Simple select on VTI table to see range of data from Day 1
     Since the example maintained only 4 days of Active window, the following query doesn't yeild data for Day 1, once the data for Day 5 is loaded

    select * from test_v
    where tagid='tag1'
    and time_stamp between '2015-07-01 01:00:00.00000' and
    '2015-07-01 01:10:00.00000';

    Press enter to see output

    Database selected.

    tagid                time_stamp                         value

    No rows found.

    Database closed.

    Press enter to continue


    Congratulations!!! Your Demo is completed Successfully.
    We hope you had a pleasant learning experience


    November 25, 2015

    Illustrating the implementation of IBM Informix TimeSeries Swing Door (Linear) Compression Algorithm


    It's proven that, IBM Informix TimeSeries feature efficiently handles time stamped data sets when compared to other Relational databases. In best cases, the TimeSeries implementation gives you over 50% of disk space savings over other relational databases.

    IBM Informix TimeSeries allows you to further compress this time stamped data, provided that the datasets are being recorded at REGULAR intervals and are NUMERIC in nature. Say, for example, you can compress an 8-byte BIGINT value down to 1 byte, with some loss of precision. That's a huge saving, isn't it?

    One can compress TimeSeries data using one of the following algorithms:

    1. Quantization : The Quantization algorithm helps create discrete grids, representing a range of values and then divides the values into these grids, based on the upper and lower boundaries. Fewer bytes are needed to represent a grid than a numeric value, resulting in compression, however, this mode of compression can be lossy.
    2. Linear            : As against the grids maintained by Quantization method, the Linear algorithm represents the values within line segments, defined by two end points. The algorithm does not record a value if it falls within the agreed deviation. If the value deviates too much from the last recorded value, only then will the new value be recorded.

    IBM Informix TimeSeries provides two variants of Linear Compression algorithms - BoxCar and Swing Door, to achieve fast Read/Write performance and High Compression, respectively.

    In this blog work 'Illustrating the implementation of IBM Informix TimeSeries Swinging Door Compression Algorithm', as the title indicates, we shall work on implementing 'Swing Door' algorithm, a variant of Linear Compression method.

    Be informed that, the implementation of the Swing Door algorithm needs to be done while initializing the TimeSeries table. 

    Example:
    insert into test_ts values('tag1',TSCreateIrr('calsec', '2015-07-01 00:00:00.00000','tscont1', 'ls(2)'));

    Where:
    ls          - Indicates that Swing Door algorithm is being applied to the first column after the time stamp, i.e the column following the DATETIME column (In Row type), which is of numeric data type. In the sample code shared in this blog work, the algorithm is applied to a column with FLOAT type. 
    (2)    - Indicates the Accepted Deviation, indicating that, if the difference between the current value and previous value is less than 2, then the record is not stored and if the differential value exceeds 2, only then is the record stored.

    For the ease of understanding and usage, the implementation steps have been organized as part of a simple shell script to help understand the flow, in terms of working with basic objects of TimeSeries viz. Calendar, Container, TimeSeries, Virtual Table Interface (VTI) and SQL API. It takes no more than 10min to run the shell script and understand Informix TimeSeries syntax with along with the implementation of Swing Door Compression Algorithm.

    1.      The shell script is well tested on Linux and might need minor changes on Unix platform. It won’t work on Windows platform. However, you can copy paste SQL statements high lighted in blue in following section and execute on windows manually.
    2.      Ensure that Informix engine is online. You can use “onstat –“  command
    3.      Download the shell script available in the zip file (Or Copy - Paste the following link on to the browser:
    https://drive.google.com/open?id=0B4mxiMcgUauaMENkNVdxZXJRQlU )
    4.      Unzip the file on linux/Unix machine under some test directory
    5.      You might need to execute dos2unix command for each file before execution
    6.      Ensure that 'main.sh' and 'func.sh' have Execute Privileges
    7.      Execute main.sh and just follow the instructions.

    Output of shell script should look like this :

    ################################################################
    Welcome to TimeSeries demo.
    This program has sections that demonstrate capabilities of TimeSeries.
    Flow in each section is Setup, Data Load & Data Retrieval
    please enter your choice of TimeSeries demo
     1      : TimeSeries Demo
     2      : JSON Demo
     3      : TimeSeries with JSON Demo
     4      : TimeSeries with Rolling Window Concept
     5      : TimeSeries with Hertz Frequency Concept
     6      : TimeSeries with Swinging Door Compression Algorithm

    6

    #########################################################################
     You have chosen a Demo on Informix TimeSeries with Swinging Door Compression
    #########################################################################

    Please read the options carefully :-
    If you are executing the TimeSeries Demo for the first time, then
            Press '1'       : To Perform the Setup and create the Demo Environment

    If you have already performed the setup for TimeSeries Demo, then
            Press ANY NUMBER: To Execute Queries against the prepared Setup

    1

    Cleaning up old files...
    Clean up is over. Please presss Enter to continue

    ################################################################
    Creating dbspace dbspace1

    execute function admin('create dbspace','dbspace1',
    '/home/amprasan/IDS1210/storage/dbspace1','100 MB','0')
    45107280         6        0x60001    6        1        2048     N  BA    informix dbspace1
    471ba028         6      6      0          10000      4851                  PO-B-D /home/amprasan/IDS1210/storage/dbspace1

    dbspace creation .. passed
    Please press Enter to continue

    ################################################################
    Creating database demo_db

    drop database if exists demo_db;
    create database demo_db in dbspace1 with buffered log

    database creation .. passed
    Please press Enter to continue

    ################################################################
    Creating row type row1

    create row type row1
    (
     time_stamp datetime year to fraction(5),
     value float
    );

    Row creation .. passed
    Please press Enter to continue

    ################################################################
    Creating calendars calsec, cal1min and cal15min

    execute procedure ifx_allow_newline('t');
    insert into calendarpatterns values
    (
     'sec','{1 on},second'
    );
    insert into calendarpatterns values
    (
     'min','{1 on},minute'
    );
    insert into calendarpatterns values
    (
     'min15','{1 on ,14 off},minute'
    );
    insert into CalendarTable
    (
     c_name,c_calendar
    )
    values ('calmin','startdate(2015-07-01 00:00:00.00000),
    pattstart(2015-07-01 00:00:00.0000),pattname(min)');

    insert into CalendarTable
    (
     c_name,c_calendar
    )
    values ('calsec','startdate(2015-07-01 00:00:00.00000),
    pattstart(2015-07-01 00:00:00.0000),pattname(sec)');

    insert into CalendarTable
    (
     c_name,c_calendar
    )
    values ('cal15min','startdate(2015-07-01 00:00:00.00000),
    pattstart(2015-07-01 00:00:00.0000),pattname(min15)');

    Calendar creation .. passed
    Please press Enter to continue

    ################################################################
    Creating table test_ts

    create table test_ts
     (
       tagid char(20),
       series timeseries(row1),
       PRIMARY KEY (tagid)
    ) in dbspace1;
    alter table test_ts
    lock mode(row);

    Table creation .. passed
    Please press Enter to continue

    ################################################################
    Creating container tscont1

    execute procedure tscontainercreate('tscont1', 'dbspace1','row1',1024,1024);

    Container creation .. passed
    Please press Enter to continue

    ################################################################
    Creating virtual table test_v

    execute procedure TSCreateVirtualTab('test_v','test_ts',4096);

    Virtual table creation .. passed
    Please press Enter to continue


    ################################################################
    Implementation of the Swing Door algorithm needs to be done while initializing the TimeSeries table. Here, in the following statement:
    ls - Indicates usage of Swing Door algorithm. In this case, it is being applied to the first column after the time stamp, i.e the column following the DATETIME column (In Row type), which is of numeric data type.
    (2)    - Indicates the Accepted Deviation, indicating that, if the difference between the current value and previous value is less than 2, then the record is not stored and if the differential value exceeds 2, only then is the record stored.
    ################################################################
    Initializing all tags tag1 to tag5

    BEGIN;
    insert into test_ts values('tag1',TSCreateIrr('calsec', '2015-07-01 00:00:00.00000','tscont1', 'ls(2)'));
    insert into test_ts values('tag2',TSCreateIrr('calsec', '2015-07-01 00:00:00.00000','tscont1', 'ls(2)'));
    insert into test_ts values('tag3',TSCreateIrr('calsec', '2015-07-01 00:00:00.00000','tscont1', 'ls(2)'));
    insert into test_ts values('tag4',TSCreateIrr('calsec', '2015-07-01 00:00:00.00000','tscont1', 'ls(2)'));
    insert into test_ts values('tag5',TSCreateIrr('calsec', '2015-07-01 00:00:00.00000','tscont1', 'ls(2)'));
    COMMIT;


    Tags initialization .. passed
    Please press Enter to continue

    ################################################################
    Load data for Tags 1 to 5 through VTI

    load from sw_cmp_load.unl insert into test_v;

    Loading data through VTI .. passed
    Please press Enter to continue

    Congratulations!!! Your setup is successful. Please press Enter to refresh the screen and be ready to see certain data retrieval queries

    ################################################################
    Simple select on VTI table to see range of data

    select * from test_v
    where tagid='tag1'
    and time_stamp between '2015-07-01 01:00:00.00000' and
    '2015-07-01 01:10:00.00000';

    Press enter to see output

    Database selected.

    tagid                time_stamp                         value

    tag1                 2015-07-01 01:00:00.00000 108.0000000000
    tag1                 2015-07-01 01:01:00.00000 109.0000000000
    tag1                 2015-07-01 01:02:00.00000 100.0000000000
    tag1                 2015-07-01 01:03:00.00000 101.0000000000
    tag1                 2015-07-01 01:04:00.00000 102.0000000000
    tag1                 2015-07-01 01:05:00.00000 103.0000000000
    tag1                 2015-07-01 01:06:00.00000 104.0000000000
    tag1                 2015-07-01 01:07:00.00000 105.0000000000
    tag1                 2015-07-01 01:08:00.00000 106.0000000000
    tag1                 2015-07-01 01:09:00.00000 107.0000000000
    tag1                 2015-07-01 01:10:00.00000 108.0000000000

    11 row(s) retrieved.

    Database closed.

    Press enter to continue

    ################################################################
    Simple select on TimeSeries table to see range of data

    select 'tag1',
    clip(series,'2015-07-01 01:00:00.00000'::datetime year to fraction(5) ,
    '2015-07-01 01:10:00.00000'::datetime year to fraction(5))
    from test_ts where tagid='tag1';

    Press enter to see output

    Database selected.

    (constant)    tag1
    (expression)  origin(2015-07-01 01:00:00.00000), calendar(calsec), container(tscont1), threshold(0), irregular, [(108.0000000000)@2015-07-01 01:00:00.00000, (109.0000000000)@2015-07-01 01:01:00.00000, (100.0000000000)@2015-07-01 01:02:00.00000, (101.0000000000)@2015-07-01 01:03:00.00000, (102.0000000000)@2015-07-01 01:04:00.00000, (103.0000000000)@2015-07-01 01:05:00.00000, (104.0000000000)@2015-07-0101:06:00.00000, (105.0000000000)@2015-07-01 01:07:00.00000, (106.0000000000)@2015-07-01 01:08:00.00000, (107.0000000000)@2015-07-01 01:09:00.00000, (108.0000000000)@2015-07-01 01:10:00.00000]

    1 row(s) retrieved.

    Database closed.

    Press enter to continue

    ################################################################
    Select on TimeSeries table and output in traditional relational form

    select 'tag1',time_stamp,value from
    table (( select tssettolist(clip(series,
    '2015-07-01 01:00:00.00000'::datetime year to fraction(5),
    '2015-07-01 01:10:00.00000'::datetime year to fraction(5)))
    ::list(row1 not null)
    from test_ts
    where tagid='tag1'));

    Press enter to see output

    Database selected.

    (constant) time_stamp                         value

    tag1       2015-07-01 01:00:00.00000 108.0000000000
    tag1       2015-07-01 01:01:00.00000 109.0000000000
    tag1       2015-07-01 01:02:00.00000 100.0000000000
    tag1       2015-07-01 01:03:00.00000 101.0000000000
    tag1       2015-07-01 01:04:00.00000 102.0000000000
    tag1       2015-07-01 01:05:00.00000 103.0000000000
    tag1       2015-07-01 01:06:00.00000 104.0000000000
    tag1       2015-07-01 01:07:00.00000 105.0000000000
    tag1       2015-07-01 01:08:00.00000 106.0000000000
    tag1       2015-07-01 01:09:00.00000 107.0000000000
    tag1       2015-07-01 01:10:00.00000 108.0000000000

    11 row(s) retrieved.

    Database closed.

    Press enter to continue

    ################################################################
    Roll up data to 15min cycle using aggregateby function

    SELECT *
    FROM TABLE (( SELECT TSSetToList(AggregateBy('avg($value)', 'cal15min',
    series, 0, '2015-07-01 00:00'::datetime year to minute,
    '2015-07-01 2:30'::datetime year to minute))::list(row1 not null)
    FROM test_ts
    WHERE tagid='tag1'));

    Press enter to see output

    Database selected.

    time_stamp                         value

    2015-07-01 00:15:00.00000 103.4000000000
    2015-07-01 00:30:00.00000 104.9375000000
    2015-07-01 00:45:00.00000 104.3125000000
    2015-07-01 01:00:00.00000 104.9375000000
    2015-07-01 01:15:00.00000 104.3125000000
    2015-07-01 01:30:00.00000 104.9375000000
    2015-07-01 01:45:00.00000 104.3125000000
    2015-07-01 02:00:00.00000 104.9375000000
    2015-07-01 02:15:00.00000 104.3125000000
    2015-07-01 02:30:00.00000 104.9375000000

    10 row(s) retrieved.

    Database closed.

    Press enter to continue

    Congratulations!!! Your Demo is completed Successfully.
    We hope you had a pleasant learning experience

    Click Here to read further on Compression types available with Informix TimeSeries

    Click Here to take a look at an illustrated example to implement various Compression Algorithms as part of TimeSeries implementation.