AlexKoe
Posts: 25
Joined: Tue Jan 20, 2015 3:26 pm
Contact: Website

Round robin sensor data storage with IBM Informix

Wed Mar 16, 2016 12:00 pm

Hi,

this post is part 4 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=100029&p=693935 (An Informix Sensor DB - Part 2)
viewtopic.php?f=37&t=137392&p=912405 (The Informix REST API - Part 3)

Especially on a small footprint device like a Raspberry Pi it can make sense to limit the storage size for the sensor data. Quite often the Pi is used to do some local 'on-the-edge' sensor data processing before the pre-processed data is sent into the cloud and hence only the data for a limited period of time needs to be retained.

Such an approach is often used in so called round robin databases like in the very popular RRDtool.

Informix supports a feature which is called a 'rolling window container' which can be easily used to achieve a round robin storage of sensor data on the Raspberry Pi.

In my post 'How to create a sensor database with IBM Informix - Part 1' (see above), I am using a standard time series container to store the sensor data in. If one is using a standard container, the time series data will grow over time and needs to manually purged from time to time to potentially limit the growth of that container.

Just as a reference, here is the SQL statement which I used to create the standard container 'sensor_cont' in Part 1:

Code: Select all

execute procedure TSContainerCreate (
        'sensor_cont',
        'rootdbs',
        'sensor_t',
        2048,
        2048);
Assuming one needs to store the sensor data only for 90 days before the oldest entries should be automatically deleted (as soon as newer entries are inserted), let's create a rolling window container for 90 active 'partitions' as an alternative to the standard container above:

Code: Select all

execute procedure TSContainerDestroy('sensor_cont');  -- That SQL statement is optional and only required if you want to delete an existing container with the same name

execute procedure TSContainerCreate (
        'sensor_cont',
        'rootdbs',
        'sensor_t',
        2048,
        2048,
        '2016-03-01 00:00:00.00000'::datetime year to fraction(5),  -- Timestamp when the rolling window container starts
        'day',  -- Interval unit for the active partitions. Valid options are 'day', 'week', 'month' and 'year'
        90,     -- Number of active partitions (here 90 days)
        1,      -- Number of dormant partitions. The oldest active partition becomes first dormant before its eventualley deleted
        'rootdbs',
        1,      -- The required number of dormant partitions will be automatically deleted.
         2048,
         2048);
As soon as you have created that new (rolling window) time series container, it can be used like a standard time series container with the major difference that it only holds the sensor data for the last 90 days. ;)

BTW, in Informix you can define as many time series container as you want. Each time you create a new time series, you can decide in which container a new time series should reside. That way you could store more volatile sensor data in a rolling window container, while other sensor data can be stored in a standard container.

For more detailed information on Informix rolling window container, please refer to the Informix 12.10 online documentation: http://www.ibm.com/support/knowledgecen ... tm?lang=en

If you have any questions about creating and using rolling window containers. Just post your question here and I will try to do my best to answer it. ;)

- Alexander

Return to “Automation, sensing and robotics”