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:
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 TSContainerCreate ( 'sensor_cont', 'rootdbs', 'sensor_t', 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.
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);
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.