In
this article I want to throw some light on fundamentals of IBM Informix TimeSeries.
This shall help grasp TimeSeries concept better and help understand
applicability of the technology. With my limited exposure to market I see that
it is definitely suitable for any kind of sensor data management or Internet of
Things, but certainly there are other areas like stock market, and can be many
more where it might bring a lot of value.
To
understand theoretical concept of Informix TimeSeries implementation, I recommend
reading this article http://www.dbta.com/Editorial/Trends-and-Applications/Managing-Time-Series-Data-with-Informix-76454.aspx
And
now you can use following simple shell script to understand how the 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.
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 zip file (size 10KB) from following link:
https://drive.google.com/file/d/0BxkZj8z_XU_XajdUVXBMbDhUZlE/edit?usp=sharing
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. Execute
main.sh and just follow the instructions.
Output of shell script should
look like this :
################################################################
Welcome to TimeSeries demo.
This program has 2 sections a) setup database b) perform
data retrieval queries.
If You have already performed steps for (a) and
interested in section (b) only, please enter 1 else enter 2:
2
################################################################
Cleaning up old files...
Clean up is over. Please presss Enter to continue
################################################################
Creating dbspace dbspace1 (This should be executed by connecting to sysadmin database)
execute function
admin('create dbspace','dbspace1',
'/data/IBM/informixdemo/demo_on/dbspace1','100
MB','0');
4bede8c0 5
0x60001 5 1
2048 N
BA informix dbspace1
4c5e5078 5
5 0 51200 45611 PO-B--
/data/IBM/informixdemo/demo_on//dbspace1
dbspace creation .. passed
Please press Enter to continue
################################################################
Creating database demo_db
(This should be executed by connecting to sysadmin database)
(This should be executed by connecting to sysadmin database)
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 (From here all the queries should be performed by connecting to demo_db)
create row type row1
(
time_stamp datetime year to fraction(5),
value decimal(7,2)
);
Row creation .. passed
Please press Enter to continue
################################################################
Creating calendars cal1min and cal15min
execute procedure
ifx_allow_newline('t');
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(2011-12-01 00:00:00.00000),
pattstart(2011-12-01
00:00:00.0000),pattname(min)');
insert into CalendarTable
(
c_name,c_calendar
)
values
('cal15min','startdate(2011-12-01 00:00:00.00000),
pattstart(2011-12-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
insert into test_ts
values('tag1',
TSCreate('calmin',
'2011-12-01 00:02:00.00000',0,0,0,'tscont1'));
insert into test_ts
values('tag2',
TSCreate('calmin',
'2011-12-01 00:02:00.00000',0,0,0,'tscont1'));
insert into test_ts
values('tag3',
TSCreate('calmin',
'2011-12-01 00:02:00.00000',0,0,0,'tscont1'));
insert into test_ts
values('tag4',
TSCreate('calmin', '2011-12-01
00:02:00.00000',0,0,0,'tscont1'));
insert into test_ts
values('tag5',
TSCreate('calmin',
'2011-12-01 00:02:00.00000',0,0,0,'tscont1'));
Tags initialization .. passed
Please press Enter to continue
################################################################
Bulkload for tag1
update test_ts set series =
bulkload(series,'data.unl')
where tagid='tag1';
Data bulkload
for tag tag1 .. passed
Please press Enter to continue
################################################################
Load data for tag2 to tag5 through VTI
load from 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
'2011-12-01 01:00:00.00000' and
'2011-12-01 01:10:00.00000';
Press enter to see output
Database selected.
tagid
time_stamp
value
tag1 2011-12-01 01:00:00.00000 108.00
tag1 2011-12-01 01:01:00.00000 109.00
tag1 2011-12-01 01:02:00.00000 100.00
tag1 2011-12-01 01:03:00.00000 101.00
tag1 2011-12-01 01:04:00.00000 102.00
tag1 2011-12-01 01:05:00.00000 103.00
tag1 2011-12-01 01:06:00.00000 104.00
tag1 2011-12-01 01:07:00.00000 105.00
tag1 2011-12-01 01:08:00.00000 106.00
tag1 2011-12-01 01:09:00.00000 107.00
tag1
2011-12-01 01:10:00.00000
108.00
11 row(s) retrieved.
Database closed.
Press enter to continue
################################################################
Simple select on TimeSeries table to see range of data
select 'tag1',
clip(series,'2011-12-01
01:00:00.00000'::datetime year to fraction(5) ,
'2011-12-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(2011-12-01 01:00:00.00000), calendar(calmin), container(ts
cont1), threshold(0), regular, [(108.00
), (109.00 ), (100.00
),
(101.00 ), (102.00 ), (103.00
), (104.00 ), (105.00
),
(106.00 ), (107.00 ), (108.00
)]
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,
'2011-12-01
01:00:00.00000'::datetime year to fraction(5),
'2011-12-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
2011-12-01 01:00:00.00000 108.00
tag1 2011-12-01 01:01:00.00000 109.00
tag1 2011-12-01 01:02:00.00000 100.00
tag1 2011-12-01 01:03:00.00000 101.00
tag1 2011-12-01 01:04:00.00000 102.00
tag1 2011-12-01 01:05:00.00000 103.00
tag1 2011-12-01 01:06:00.00000 104.00
tag1 2011-12-01 01:07:00.00000 105.00
tag1 2011-12-01 01:08:00.00000 106.00
tag1 2011-12-01 01:09:00.00000 107.00
tag1 2011-12-01 01:10:00.00000 108.00
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, '2011-12-01
00:00'::datetime year to minute,
'2011-12-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
2011-12-01 00:15:00.00000 103.64
2011-12-01 00:30:00.00000 105.00
2011-12-01 00:45:00.00000 104.00
2011-12-01 01:00:00.00000 105.00
2011-12-01 01:15:00.00000 104.00
2011-12-01 01:30:00.00000 105.00
2011-12-01 01:45:00.00000 104.00
2011-12-01 02:00:00.00000 105.00
2011-12-01 02:15:00.00000 104.00
2011-12-01 02:30:00.00000 105.00
10 row(s) retrieved.
Database closed.
Press enter to continue
