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.