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

A SQL database for sensor and JSON data on Raspian

Tue Jan 20, 2015 4:27 pm

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
Last edited by AlexKoe on Wed Mar 16, 2016 12:04 pm, edited 2 times in total.

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

Re: A SQL database for sensor and JSON data on Raspian

Fri Jan 23, 2015 10:27 pm

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
Posts: 25
Joined: Tue Jan 20, 2015 3:26 pm
Contact: Website

Re: A SQL database for sensor and JSON data on Raspian

Sat Jan 24, 2015 9:43 pm

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
Posts: 25
Joined: Tue Jan 20, 2015 3:26 pm
Contact: Website

Re: A SQL database for sensor and JSON data on Raspian

Wed Jan 28, 2015 3:35 pm

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
Posts: 25
Joined: Tue Jan 20, 2015 3:26 pm
Contact: Website

Re: A SQL database for sensor and JSON data on Raspian

Thu Jan 29, 2015 12:02 pm

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

User avatar
DougieLawson
Posts: 30448
Joined: Sun Jun 16, 2013 11:19 pm
Location: Basingstoke, UK
Contact: Website

Re: A SQL database for sensor and JSON data on Raspian

Thu Jan 29, 2015 12:21 pm

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.
Microprocessor, Raspberry Pi & Arduino Hacker
Mainframe database troubleshooter
MQTT Evangelist
Twitter: @DougieLawson

Since 2012: 1B*5, 2B*2, B+, A+, Zero*2, 3B*3

Please post ALL technical questions on the forum. Do not send private messages.

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

Re: A SQL database for sensor and JSON data on Raspian

Thu Jan 29, 2015 6:05 pm

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):
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/

User avatar
DougieLawson
Posts: 30448
Joined: Sun Jun 16, 2013 11:19 pm
Location: Basingstoke, UK
Contact: Website

Re: A SQL database for sensor and JSON data on Raspian

Thu Jan 29, 2015 6:45 pm

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.
Microprocessor, Raspberry Pi & Arduino Hacker
Mainframe database troubleshooter
MQTT Evangelist
Twitter: @DougieLawson

Since 2012: 1B*5, 2B*2, B+, A+, Zero*2, 3B*3

Please post ALL technical questions on the forum. Do not send private messages.

edbrunelle
Posts: 13
Joined: Mon Feb 03, 2014 8:31 pm
Location: Phoenix, AZ USA

Re: A SQL database for sensor and JSON data on Raspian

Sun Aug 16, 2015 8:59 am

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

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

Re: A SQL database for sensor and JSON data on Raspian

Sun Aug 16, 2015 9:12 pm

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
Posts: 8
Joined: Mon May 20, 2013 1:16 pm

Re: A SQL database for sensor and JSON data on Raspian

Tue Aug 18, 2015 6:04 pm

Hi Alex,

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

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
Posts: 25
Joined: Tue Jan 20, 2015 3:26 pm
Contact: Website

Re: A SQL database for sensor and JSON data on Raspian

Wed Aug 19, 2015 6:50 pm

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
Posts: 8
Joined: Mon May 20, 2013 1:16 pm

Re: A SQL database for sensor and JSON data on Raspian

Wed Aug 19, 2015 7:21 pm

Thanks Alex.


Regards.

msd
Posts: 6
Joined: Sun Feb 21, 2016 8:29 am

Re: A SQL database for sensor and JSON data on Raspian

Sun Feb 21, 2016 10:03 am

Hi,

thanks a lot for this guide Alexander!!

Could help me setting up the REST/JSON listener?

BR
Marco

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

Re: A SQL database for sensor and JSON data on Raspian

Mon Feb 22, 2016 9:44 am

msd wrote: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
Posts: 25
Joined: Tue Jan 20, 2015 3:26 pm
Contact: Website

Re: A SQL database for sensor and JSON data on Raspian

Wed Feb 24, 2016 6:20 pm

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
Posts: 1
Joined: Thu May 26, 2016 3:24 am

Re: A SQL database for sensor and JSON data on Raspian

Thu May 26, 2016 3:28 am

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

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

Re: A SQL database for sensor and JSON data on Raspian

Mon May 30, 2016 11:42 am

nzDAVE wrote: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

Return to “Automation, sensing and robotics”

Who is online

Users browsing this forum: ajc3 and 10 guests