A SQL database for sensor and JSON data on Raspian

[AlexKoe]

Preface:

This post is the introductionary part 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=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)
viewtopic.php?uid=131887&f=37&t=140398 (Round robin sensor data storage with IBM Informix - Part 4)


Hi,

I am not sure if the following might be appropriate to be posted here, but I suppose it might be of interest for the Raspberry Pi community:

IBM has just released a free developer edition of its Informix SQL database for ARM v6 (and ARM v7) and hence its available for the Raspberry Pi (it supports Raspian but might run on similar Linux distributions as well).

Informix has built-in optimized support for time series (sensor-) based data plus JSON plus Geospatial data. Its an object-relational SQL database and supports all major development APIs (eg. JDBC, REST API, MongoDB API, ODBC, .NET etc.).
That free developer version can be downloaded from here: https://www14.software.ibm.com/webapp/i ... rce=ifxids.

To get you started on the built-in time series/sensor data capabilities, you might want to take a look at the following IBM DeveloperWorks article: http://www.ibm.com/developerworks/data/ ... timeseries and/or take a look at the following free IBM RedBook on that topic: http://www.redbooks.ibm.com/abstracts/s ... .html?Open.

Finally, here is the link to the official documentation: http://www-01.ibm.com/support/knowledge ... tm?lang=en.

If you might have any questions, please feel free to get in touch with me by sending a PM.

- Alexander

PS: If you want to follow me on Twitter: http://twitter.com/AlexKoeMUC

[AlexKoe]

In order to provide you a kick start for the Informix database on the Raspberry Pi, I have compiled the following how-to.

If you follow the next steps, you should end up with a very simple Informix 12.10 instance which allows you to test some of Informix capabilties like optimized time series data processing, JSON document storage, Geospatial data etc.

Installation of Informix

As user pi (or any other user who has sudo permissions):

1. Copy the Informix install tar file (ids.12.10.UC4DE.Linux-ARM6.tar) to a temporary folder, e.g. /tmp
2. Create a temporary folder for the Informix install files:

   Code: Select all

   mkdir /tmp/ifxinstall

3. Change directory to that folder:

   Code: Select all

   cd /tmp/ifxinstall

4. Extract the Informix tar file into the ifxinstall folder:

   Code: Select all

   tar xvf /tmp/ids.12.10.UC4DE.Linux-ARM6.tar

5. create a new group informix:

   Code: Select all

   sudo addgroup informix

6. Create a new user informix (with the primary group informix). During this step you will be asked for a password for informix. Please take a note of that password. You will need it later.

   Code: Select all

   sudo adduser --ingroup informix informix

   Make sure to add the user informix to the /etc/sudoers file by adding the following line by using the 'sudo visudo' command:

   Code: Select all

   informix ALL=(ALL) NOPASSWD: ALL

7. Run the Informix install script. During the installation you will be asked for an install dir. You might want to use /opt/IBM/informix1210UC4DE.

   Code: Select all

   sudo ./install_ids

8. Optional: As soon as the installation has successfully finished, you can delete the ifxinstall folder and the Informix install tar file if you want.

   Code: Select all

   rm -rf /tmp/ifxinstall
   rm /tmp/ids.12.10.UC4DE.Linux-ARM6.tar
   

9. Optional, but highly recommended: create the following symbolic link:

   Code: Select all

   sudo ln -s /opt/IBM/informix1210UC4DE /opt/IBM/informix

10. Create the folder which will later contain the Informix database files:

    Code: Select all

    sudo mkdir /opt/IBM/ifxdata

11. And set its ownership and permissions:

    Code: Select all

    sudo chown informix:informix /opt/IBM/ifxdata
    sudo chmod 770 /opt/IBM/ifxdata
    

Configuration and initalization of a new Informix instance

1. Login as user informix
2. Set the $INFORMIXDIR environment variable to point to the Informix install directory (actually to the symbolic link):

   Code: Select all

   export INFORMIXDIR=/opt/IBM/informix

   Extend the $PATH environment variable:

   Code: Select all

   export PATH=$PATH:$INFORMIXDIR/bin

   Set the $INFORMIXSERVER environment variable (you can choose any name here, but let's use ol_informix1210 for now to keep it simple):

   Code: Select all

   export INFORMIXSERVER=ol_informix1210

   Note: you might want to add those three entries to the end of the .bashrc file in the /home/informix directory
   So the .bashrc file should contain the following three lines at the end:

   Code: Select all

   export INFORMIXDIR=/opt/IBM/informix
   export PATH=$PATH:$INFORMIXDIR/bin
   export INFORMIXSERVER=ol_informix1210
   

3. Create a new Informix configuration file:

   Code: Select all

   cp $INFORMIXDIR/etc/onconfig.std $INFORMIXDIR/etc/onconfig

4. Create a new Informix hosts definition file:

   Code: Select all

   cp $INFORMIXDIR/etc/sqlhosts.demo $INFORMIXDIR/etc/sqlhosts

5. Edit the file $INFORMIXDIR/etc/onconfig (with nano, vi or any other editor) and apply the following changes:
   Change the value of ROOTPATH to /opt/IBM/ifxdata/rootdbs:

   Code: Select all

   ROOTPATH	/opt/IBM/ifxdata/rootdbs

   Change the value of DBSERVERNAME to ol_informix1210

   Code: Select all

   DBSERVERNAME	ol_informix1210

   Change the value of LTAPEDEV to /dev/null

   Code: Select all

   LTAPEDEV	/dev/null

   Change the value of TAPEDEV to /dev/null

   Code: Select all

   TAPEDEV		/dev/null

   Change the value of LOGFILES to 10

   Code: Select all

   LOGFILES	10

   Save the file and exit the editor
6. Edit the file $INFORMIXDIR/etc/sqlhosts and add the following line:

   Code: Select all

   ol_informix1210	onsoctcp	localhost	9088

   Note: 9088 is the port which will be used by Informix for the client/server communication. You can choose any available port you want.
   Save the file and exit the editor.
7. Create an empty database file and set the correct access mode:

   Code: Select all

   touch /opt/IBM/ifxdata/rootdbs
   chmod 660 /opt/IBM/ifxdata/rootdbs
   

8. Now we are ready to initialize Informix for the first time:

   Code: Select all

   oninit -iv

   The first initialization will take a few minutes and it will create a few system databases automatically. You can monitor the pogress by doing the following:

   Code: Select all

   tail -f /opt/IBM/informix/tmp/online.log

   Please wait until you see the following entry in the online.log file before you continue:
   'sysadmin' database built successfully
9. Congratulations: You have just created your first Informix database instance on the Raspberry Pi!

Create an Informix demo database (w/ data)
As user informix:
Execute the following command to create the 'stores_demo' database:

Code: Select all

dbaccessdemo -log

Depending on what kind of storage you might be using for your RPi that command might take a few minutes to complete.


First steps with Informix

As user informix:

To stop an Informix instance:

Code: Select all

onmode -ky

To start an Informix instance:

Code: Select all

oninit

To check the status of Informix:

Code: Select all

onstat -

Display the last message log entries:

Code: Select all

onstat -m

Display some basic performance stats:

Code: Select all

onstat -p

As any user who has the Informix environment variables (see above) set:

Execute SQL scripts from the command line:

Code: Select all

dbaccess <database_name> <sql_script_file>

Using dbaccess interactively:

Code: Select all

dbaccess <database_name>

or simply

Code: Select all

dbaccess

Have fun!
If you might any questions/issues, just send me a PM.

- Alexander

[AlexKoe]

Just a quick modification of the newly created Informix instance.
To allow the database to automatically expand its storage area ('rootdbs') if required (e.g. while adding new data or database objects), simply execute the following command as user 'informix':

Code: Select all

echo "EXECUTE FUNCTION task('modify chunk extendable', '1')" | dbaccess sysadmin -

- Alexander

[AlexKoe]

Auto-start Informix during the boot sequence of Raspian

If you would like to auto-start the Informix DB each time you (re-)boot your Raspberry Pi, first create a new script called 'informix' in the '/etc/init.d' directory with the following content (either become user 'root' and edit the file or use the command 'sudo nano /etc/init.d/informix'):

Code: Select all

#!/bin/sh
### BEGIN INIT INFO
# Provides:          informix
# Required-Start:    $networking $netdeamons $syslog
# Required-Stop:     $networking
# Default-Start:     2 3 4 5
# Default-Stop:      0 1 6
### END INIT INFO

INFORMIXSERVER=ol_informix1210
INFORMIXDIR="/opt/IBM/informix"
ONCONFIG=onconfig
INFORMIXSQLHOSTS="/opt/IBM/informix/etc/sqlhosts"
PATH=${INFORMIXDIR}/bin:${PATH}
export INFORMIXSERVER INFORMIXDIR ONCONFIG INFORMIXSQLHOSTS PATH

if [ $# -lt 1 ]
        then
                echo "Usage: $0 {start|stop}"
        else
                case "$1" in
                'start')
                        if [ `$INFORMIXDIR/bin/onstat 2>&- | grep -c On-Line` -ne 1 ]
                        then
                                rm -f /INFORMIXTMP/*
#                               rm -f $INFORMIXDIR/tmp/online*.log
                                echo -n "Starting Informix V12.10..."
                                $INFORMIXDIR/bin/oninit
                                echo "done"
                        fi
                        ;;
                'stop')
                        if [ `$INFORMIXDIR/bin/onstat 2>&- | grep -c On-Line` -eq 1 ]
                        then
                                echo -n "Shutting down Informix V12.10..."
                                $INFORMIXDIR/bin/onmode -ky
                                echo "done"
                        fi
                        ;;
                *)
                        echo "Usage: $0 {start|stop}"
                        ;;
                esac
fi

In the next step simply run the following command

Code: Select all

sudo update-rc.d informix defaults

That's it. Now your Informix DB should automatically start up during the Raspian boot sequence and also should shut down during a controlled shutdown request.

- Alexander

[AlexKoe]

Install the 'libaio1' package for Informix under Raspian
Unfortunately I forgot to mention that Informix prefers to use kernel asynchronous I/O (KAIO) for its database operations. In order to be able to utilize KAIO for Informix on Raspian, you need to install the 'libaio1' package:

Code: Select all

sudo apt-get install libaio1

Note: Informix will definitely work w/o KAIO (as you already might have seen), but the performance with KAIO enabled will be better!

- Alexander

[DougieLawson]

Can you give some examples of why I'd want to use Informix rather than MySQL? What does it do that MySQL can't do? What does MySQL do that isn't in Informix and how can I work round the missing pieces? How does it perform if you benchmark it against MySQL? How much better is it at utilising the restricted RAM available on a Raspberry Pi? Is the Informix SQL API easy to use? Is it supported from everywhere like PHP, Java, Perl, python?

Because without that you're preaching to an empty room, everyone is down the pub having a beer with the MySQL users.

[AlexKoe]

Hi Dougie,

thanks for bringing up those very valid questions. Generally speaking every developer should choose the best tool(s) to tackle his/her development challenges.

Let's start in reverse order of your questions, the development APIs.
Informix is an object-relational, SQL database which follows the SQL99 standard and is supported by all the major programming APIs, some of them community supported and some provided and supported by IBM. Here is an incomplete overview about the available APIs (I guess some of them need to be compiled for the RPi with the Informix ClientSDK which comes with the Informix RPi server):

• PHP: Two PDO drivers available, PDO_Informix and PDO_IBM (a shared Informix and DB2 driver). http://pecl.php.net/package/PDO_INFORMIX, http://pecl.php.net/package/PDO_IBM
• Perl: There is a DBD-Informix driver: http://search.cpan.org/~johnl/DBD-Infor ... nformix.pm
• Python: http://wiki.python.org/moin/Informix
• Ruby: http://rubygems.org/gems/ruby-informix
• Java (JDBC): http://www14.software.ibm.com/webapp/do ... rs=ifxjdbc
• REST API: comes with the Informix server for the RPi
• MongoDB API: comes with the Informix server for the RPi
• ODBC: part of the Informix ClientSDK (which comes with the Informix RPi version)
• plus others

One of the strengths of Informix 12.10 is the optimized integration of time series data (for e.g. sensor data), Geospatial data, 'classic' relational data and unstructured JSON data in one hybrid database engine. All of those data types can be easily accessed either via SQL, MongoDB API and/or the REST API depending on a developer's requirement.

Unlike in MySQL you don't need to mix and match different DB/storage engines to achieve a specific behavior. So if you need HA for your application its built-in. If you need multi-site data replication its also built-in. If you need support for complex clusters over thousands of nodes, its built-in. There are no restrictions on how to combine the different technologies.
You can even join structured relational data with JSON documents and eg. time series and Geospatial data.

Informix can be configured for resource constraint environments like eg. a Raspberry Pi. If required the install footprint (w/o data) can be easily below 100 MB, maybe even below 50 MB.

The memory utilization is quite often dependent on how efficiently a database stores its data on disk, since those data pages are eventually loaded into memory.

In the case of time series/sensor data for example, the Informix approach of storing such data will provide you with storage savings on disk of about 60 - 80% (+/-, depending your data structures) compared to a pure relational approach like in MySQL. That in return means that you have more sensor data readings in one data page and hence either much more readings in the same amount of database RAM (cache) or you can reduce the memory usage to cache the same amount readings w/o any performance issues.

Also: using less storage space on disk for those time series data means significantly less I/O and CPU activity for operations on those time series data compared to a 'traditional' relational approach which is definitely very beneficial for those less powerful HW environments like a Raspberry Pi.

Simply speaking: Informix is very efficiently utilizing the precis RPi resources in such use cases.

Unfortunately I don't have a head to head comparison between MySQL and Informix in that respect, but I recently did an Informix relational vs. Informix time series comparison on a platform similar to the RPi (although ARM v7 based). Buy just moving time series data from a relational Informix table to an Informix time series, I have been able to speed up queries on the same kind of data by factor 3-7!.
The more time series based data you need to handle on a RPi or any other platforms, the more a developer will appreciate the Informix capabilities in that respect.

I nearly forgot to mention that Informix has been optimized over the last years to especially handle the tough requirements of deep embedded, low resource, zero admin environments. Informix has for example a buit-in low memory handler to automatically react on low memory situations. BTW, due to those features customers like Cisco have deep embedded Informix in many of their solutions and I believe that they do have an install base of more than 100k Informix instances worldwide, most of them invisible to their customers (Details can be found here: http://www-01.ibm.com/software/sw-libra ... 58W44.html).

Speaking of a pub and a beer: if you might be in the Munich area, give me a 'ring' (or a PM) and we could have a more in depth-discussion, very likely together with some colleagues from the Munich Informix lab and other technical teams. Alternatively I could get you in touch with a great colleague and Informix time series specialist in the greater London area.

If other Raspberry Pi developers who are reading this post might be in the Munich area and would like to chat with some colleagues from the technical Informix team(s) and/or me, just send me a PM.

- Alexander (Twitter: AlexKoeMuc)

PS: Since you are mentioning MQTT in your signature, have you already had a chance to take a look at IBM's Bluemix and IBM's IoT Foundation? Both are making intensive use of MQTT and they did integrate Informix with a focus on time series data. Links: http://www-01.ibm.com/software/bluemix, https://internetofthings.ibmcloud.com/

[DougieLawson]

Hi Alex, I'm an exIBM'er. My specialist subjects are DB2 on Z and IMS DB/DC. If we could ever get DL/I running on a RPi I'd be very happy. I got as far a Boëblingen with DB2 & San José & Melbourne with IMS. I live 20 miles from Hursley.

[edbrunelle]

Alex, the pathname given for the database download is invalid! Got another?

[AlexKoe]

Hi Ed,

please try the following link:

https://www-01.ibm.com/marketing/iwm/iw ... lang=en_US

...and choose the Informix 12.10.UC5DE ARM v6 version.

Good luck and have fun working with the product,

Alexander

[rothariger]

Hi Alex,

first i want to thank you for this tutorial... it help me a lot!

but now i want to ask you, i'm trying to connect from my desktop to the RPI instance of the informix, but i'm not able to do that.
the connection is refused, i could only telnet to localhost 9088, but i even can't telnet to the same rpi ip from the rpi.
do you know what could it be?


regards.

[AlexKoe]

Hallo rothariger,

a very simple fix to your problem would be to replace 'localhost' in step 6 of my installation tutorial either with the IP address of the RPi in your local network or with its hostname.

Before applying that change you need to shutdown Informix with an

Code: Select all

onmode -ky

first and the re-start Informix with an

Code: Select all

oninit

.

BTW, Informix can be easily configured to listen to multiple networks and different ports at the same time, if necessary.

For each additional network/port combination you need to a) add a new line to that sqlhosts file (e.g):

Code: Select all

ol_informix01 onsoctcp 192.168.1.100 9089

and b) add a new DBSERVERALIAS entry in the $ONCONFIG file (e.g.):

Code: Select all

DBSERVERALIAS ol_informix01

.

Good luck,

Alexander

[rothariger]

Thanks Alex.


Regards.

[msd]

Hi,

thanks a lot for this guide Alexander!!

Could help me setting up the REST/JSON listener?

BR
Marco

[AlexKoe]

Hi,

thanks a lot for this guide Alexander!!

Could help me setting up the REST/JSON listener?

BR
Marco

Hi Marco,

interesting coincidence, I wanted to publish a how to on that topic soon.

In a nutshell:

• Make sure that you have Java installed:

  Code: Select all

  sudo apt-get install oracle-java8-jdk

• Create a new REST listener properties file based on $INFORMIXDIR/etc/jsonListener-example.properties, eg.:

  Code: Select all

  cp $INFORMIXDIR/etc/jsonListener-example.properties $INFORMIXDIR/etc/restListener.properties

• Modify that properties file and at least set the 'url' property to point to your Informix instance, you also might want to set 'listener.hostName=*', 'database.share.close.enable=true', 'listener.idle.timeout=3000', 'listener.port=<your desired port number for the REST API>', 'listener.http.accessControlAllowOrigin="*"', 'listener.type=rest', 'pool.size.maximum=2', 'pool.connections.maximum=4', 'pool.type=advanced', 'response.documents.count.default=10000' <-- or any number higher than 100, ' pool.idle.timeout=60', 'pool.idle.timeunit=SECONDS'
• Start the REST listener with the following command:

  Code: Select all

  java -cp $INFORMIXDIR/bin/jsonListener.jar com.ibm.nosql.server.ListenerCLI -config $INFORMIXDIR/etc/restListener.properties -logfile $INFORMIXDIR/tmp/restListener.log -loglevel error -start

For additional information on the Informix REST API, please refer to http://www-01.ibm.com/support/knowledge ... tm?lang=en

- Alexander

[AlexKoe]

Hi Marco,

just FYI: I just posted a new How-To on the Informix REST API:
viewtopic.php?f=37&t=137392&p=912405

- Alexander

[nzDAVE]

Is there a version of IDS for the Raspberry Pi 3 ?

[AlexKoe]

Is there a version of IDS for the Raspberry Pi 3 ?

Hi nzDave,

you should currently use the ARM v6 port of IDS on the Raspberry Pi 3:

https://www-01.ibm.com/marketing/iwm/iw ... rce=ifxids

Please let me know if have any issues or further questions.

- Alexander