How to create a sensor database with IBM Informix - Part 2

[AlexKoe]

Preface:
This post is part 2 in a little how-to series on how to use the IBM Informix database on the Raspberry Pi to build an efficient sensor data data database for local sensor data analytics/processing/caching.

The other posts can be found here:

viewtopic.php?f=37&t=97199&p=674959 (Informix Intro and Installation)
viewtopic.php?f=37&t=97772&p=678497 (An Informix Sensor DB - Part 1)
viewtopic.php?f=37&t=137392&p=912405 (The Informix REST API - Part 3)
viewtopic.php?uid=131887&f=37&t=140398 (Round robin sensor data storage with IBM Informix - Part 4)

Hi,

based on my initial postings on how to download and how to install the Informix database on the Raspberry Pi and on how to create a simple, optimized sensor data storage...

...I would like to continue with some 'advanced' topics which will hopefully help you to enhance your Informix on RPi experience.

BTW, in the meantime I received my Raspberry Pi 2 and I am quite impressed with its performance so far. I just run a non academic Informix performance comparison between the RPi and the RPi 2. The following SQL Query, executed 10 times sequentially in one SQL script against the Informix 'stores_demo' database:

Code: Select all

select tstamp::datetime year to day day, sum(value) total , max(value), min(value)  from ts_data_v 
where loc_esi_id  = 4727354321000111
and tstamp < '2010-12-14'::datetime year to day
and tstamp > '2010-11-30'::datetime year to day
group by 1 order by 1;

The execution time for that SQL script was about 4.08 seconds on the Raspberry Pi and took only about 1.2 secs on the Raspberry Pi 2.
In both cases the data has been fully cached in Informix's buffer pool to avoid any SD/microSD performance side effects. So one can say that the RPi 2 really is a great foundation for some low cost sensor-/time-series data projects.

Speaking about the RPi2: My Informix installation guide for the Raspberry Pi remains the same for the new Raspberry Pi 2!

Ok, now back to my 'advanced' Informix sensor data topics.

Create a round-robin sensor- / timeseries-data store with Informix
In Part 1 of my Informix How-To I described how to setup an Informix database table which stores time series (sensor-) data. One step of that setup was the creation of a 'container' in which the actual sensor data are stored:

Code: Select all

execute procedure TSContainerCreate (
        'sensor_cont',
        'rootdbs',
        'sensor_t',
        2048,
        2048);

That container will store the sensor data and will grow over time as long as no data are eventually deleted.

If you are only interested in sensor data collected over a certain period of time like e.g. over the last 31 days, 3 months, 6 weeks or whatever, then it could make sense to setup a 'round robin storage' for your sensor data. That setup can be done very easily with Informix and its called 'rolling window container'.

So, if you want to create a sensor data storage which will hold the data for the last 31 days, then simply replace the old 'TSContainerCreate()' function call with the following:

Code: Select all

EXECUTE PROCEDURE TSContainerCreate (
        'sensor_cont',
        'rootdbs',
        'sensor_t',
        2048,
        2048,
        '2015-02-13 00:00:00.00000'::DATETIME YEAR TO FRACTION(5),   -- Start timestamp of the round robin processing
        'day',                      -- The unit can be 'day', 'week', 'month' or 'year'
        31,                         -- Number of 'units' of active data
        1,                          -- Number of 'units' of dormant data
        'rootdbs',
        1,                          -- That option triggers an auto delete of the last unit of data which will be moved out the dormant state 'window'
        8,
        8);

If would like to do something similar for 12 months of sensor data, you might want to execute the following 'TSCreateContainer()' function:

Code: Select all

EXECUTE PROCEDURE TSContainerCreate (
        'sensor_cont',
        'rootdbs',
        'sensor_t',
        2048,
        2048,
        '2015-02-13 00:00:00.00000'::DATETIME YEAR TO FRACTION(5),   -- Start timestamp of the round robin processing
        'month',                      -- The unit can be 'day', 'week', 'month' or 'year'
        12,                         -- Number of 'units' of active data
        1,                          -- Number of 'units' of dormant data
        'rootdbs',
        1,                          -- That option triggers an auto delete of the last unit of data which will be moved out the dormant state 'window'
        8,
        8);

You might have noticed that we keep currently one 'unit' of data in the dormant window. That means that the oldest active window will be first moved into a dormant state before it will be deleted during the next window cycle.
Having the data in a dormant window allows you to e.g. archive those data before they are automatically deleted. The length of that dormant window can be easily configured by defining the number of dormant units during the TSCreateContainer() execution.

So that's it for today. Watch that space for additional upcoming new topics around the Informix sensor data capabilities on the RPi.

Follow me on Twitter: http://twitter.com/AlexKoeMUC

[mike7]

Speaking about the RPi2: My Informix installation guide for the Raspberry Pi remains the same for the new Raspberry Pi 2!

Alexander, I've got an error following your guide:

Code: Select all

Error: Cannot read the license file. errno = 2.

after

Code: Select all

sudo ./ids_install

on Pi 2. I'm trying to install ids.12.10.UC5DE.Linux-ARM6.tar

[AlexKoe]

Hi Mike,

I will look into it and will try to come back with an answer.

Stay tuned.

- Alexander

[mike7]

Code: Select all

Error: Cannot read the license file. errno = 2.

It's an issue with locale settings, I've got LANG=LANG=ru_RU.UTF-8, there is no Russian license ...

UPDATE: doesn't work if LANG and LC_MESSAGES are different, if both are ru_RU.UTF-8 I see the license

[AlexKoe]

Hi Mike,

you are on the right track!

A colleague of mine (from the Informix Lab in Munich) also just mentioned that you might have to set e.g.

Code: Select all

export LANG=en_US.UTF-8

or

Code: Select all

export LANG=en_US.8859-1

before executing the ids_install script.

That should fix your install issue. I am sorry that you run into that issue.

- Alexander