raspberry pi and informix

A SQL database for sensor and JSON data on Raspian

Post Reply18 posts • Page 1 of 1AlexKoePosts:25Joined: Tue Jan 20, 2015 3:26 pmContact:Website 

A SQL database for sensor and JSON data on Raspian

• Quote

Tue Jan 20, 2015 4:27 pmPreface:

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/AlexKoeMUCLast edited by AlexKoe on Wed Mar 16, 2016 12:04 pm, edited 2 times in total.AlexKoePosts:25Joined: Tue Jan 20, 2015 3:26 pmContact:Website 

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

• Quote

Fri Jan 23, 2015 10:27 pmIn 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 allmkdir /tmp/ifxinstall
3. Change directory to that folder:Code: Select allcd /tmp/ifxinstall
4. Extract the Informix tar file into the ifxinstall folder:Code: Select alltar xvf /tmp/ids.12.10.UC4DE.Linux-ARM6.tar
5. create a new group informix:Code: Select allsudo 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 allsudo adduser --ingroup informix informixMake sure to add the user informix to the /etc/sudoers file by adding the following line by using the ‘sudo visudo’ command:Code: Select allinformix 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 allsudo ./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 allrm -rf /tmp/ifxinstall rm /tmp/ids.12.10.UC4DE.Linux-ARM6.tar
9. Optional, but highly recommended: create the following symbolic link:Code: Select allsudo ln -s /opt/IBM/informix1210UC4DE /opt/IBM/informix
10. Create the folder which will later contain the Informix database files:Code: Select allsudo mkdir /opt/IBM/ifxdata
11. And set its ownership and permissions:Code: Select allsudo 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 allexport INFORMIXDIR=/opt/IBM/informixExtend the $PATH environment variable:Code: Select allexport PATH=$PATH:$INFORMIXDIR/binSet the $INFORMIXSERVER environment variable (you can choose any name here, but let’s use ol_informix1210 for now to keep it simple):Code: Select allexport INFORMIXSERVER=ol_informix1210Note: 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 allexport INFORMIXDIR=/opt/IBM/informix export PATH=$PATH:$INFORMIXDIR/bin export INFORMIXSERVER=ol_informix1210
3. Create a new Informix configuration file:Code: Select allcp $INFORMIXDIR/etc/onconfig.std $INFORMIXDIR/etc/onconfig
4. Create a new Informix hosts definition file:Code: Select allcp $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 allROOTPATH /opt/IBM/ifxdata/rootdbsChange the value of DBSERVERNAME to ol_informix1210Code: Select allDBSERVERNAME ol_informix1210Change the value of LTAPEDEV to /dev/nullCode: Select allLTAPEDEV /dev/nullChange the value of TAPEDEV to /dev/nullCode: Select allTAPEDEV /dev/nullChange the value of LOGFILES to 10Code: Select allLOGFILES 10Save the file and exit the editor
6. Edit the file $INFORMIXDIR/etc/sqlhosts and add the following line:Code: Select allol_informix1210 onsoctcp localhost 9088Note: 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 alltouch /opt/IBM/ifxdata/rootdbs chmod 660 /opt/IBM/ifxdata/rootdbs
8. Now we are ready to initialize Informix for the first time:Code: Select alloninit -ivThe 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 alltail -f /opt/IBM/informix/tmp/online.logPlease 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. 

– AlexanderAlexKoePosts:25Joined: Tue Jan 20, 2015 3:26 pmContact:Website 

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

• Quote

Sat Jan 24, 2015 9:43 pmJust 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 -

– AlexanderAlexKoePosts:25Joined: Tue Jan 20, 2015 3:26 pmContact:Website 

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

• Quote

Wed Jan 28, 2015 3:35 pmAuto-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.

– AlexanderAlexKoePosts:25Joined: Tue Jan 20, 2015 3:26 pmContact:Website 

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

• Quote

Thu Jan 29, 2015 12:02 pmInstall 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

DougieLawsonPosts:34438Joined: Sun Jun 16, 2013 11:19 pmLocation: Basingstoke, UKContact:Website 

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

• Quote

Thu Jan 29, 2015 12:21 pmCan 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.Note:Having anything remotely humorous in your signature is completely banned on this forum.

Any DMs sent on Twitter will be answered next month.AlexKoePosts:25Joined: Tue Jan 20, 2015 3:26 pmContact:Website 

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

• Quote

Thu Jan 29, 2015 6:05 pmHi 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/

DougieLawsonPosts:34438Joined: Sun Jun 16, 2013 11:19 pmLocation: Basingstoke, UKContact:Website 

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

• Quote

Thu Jan 29, 2015 6:45 pmHi 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.Note:Having anything remotely humorous in your signature is completely banned on this forum.

Any DMs sent on Twitter will be answered next month.edbrunellePosts:13Joined: Mon Feb 03, 2014 8:31 pmLocation: Phoenix, AZ USA

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

• Quote

Sun Aug 16, 2015 8:59 amAlex, the pathname given for the database download is invalid! Got another?AlexKoePosts:25Joined: Tue Jan 20, 2015 3:26 pmContact:Website 

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

• Quote

Sun Aug 16, 2015 9:12 pmHi 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,

AlexanderrotharigerPosts:8Joined: Mon May 20, 2013 1:16 pm

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

• Quote

Tue Aug 18, 2015 6:04 pmHi 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.AlexKoePosts:25Joined: Tue Jan 20, 2015 3:26 pmContact:Website 

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

• Quote

Wed Aug 19, 2015 6:50 pmHallo 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,

AlexanderrotharigerPosts:8Joined: Mon May 20, 2013 1:16 pm

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

• Quote

Wed Aug 19, 2015 7:21 pmThanks Alex.

Regards.msdPosts:6Joined: Sun Feb 21, 2016 8:29 am

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

• Quote

Sun Feb 21, 2016 10:03 amHi,

thanks a lot for this guide Alexander!!

Could help me setting up the REST/JSON listener?

BR
MarcoAlexKoePosts:25Joined: Tue Jan 20, 2015 3:26 pmContact:Website 

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

• Quote

Mon Feb 22, 2016 9:44 ammsd wrote:Hi,

thanks a lot for this guide Alexander!!

Could help me setting up the REST/JSON listener?

BR
MarcoHi 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 allsudo apt-get install oracle-java8-jdk
• Create a new REST listener properties file based on $INFORMIXDIR/etc/jsonListener-example.properties, eg.:Code: Select allcp $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 alljava -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

– AlexanderAlexKoePosts:25Joined: Tue Jan 20, 2015 3:26 pmContact:Website 

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

• Quote

Wed Feb 24, 2016 6:20 pmHi Marco,

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

– AlexandernzDAVEPosts:1Joined: Thu May 26, 2016 3:24 am

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

• Quote

Thu May 26, 2016 3:28 amIs there a version of IDS for the Raspberry Pi 3 ?AlexKoePosts:25Joined: Tue Jan 20, 2015 3:26 pmContact:Website 

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

• Quote

Mon May 30, 2016 11:42 amnzDAVE 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. 

– AlexanderDisplay posts from previous:All posts1 day7 days2 weeks1 month3 months6 months1 yearAll postsSort byAuthorPost timeSubjectPost timeAscendingDescendingAscendingPost Reply18 posts • Page 1 of 1

Return to “Automation, sensing and robotics”Jump to

• Board index
• All times are UTC
• Delete all board cookies

• Like Raspberry Pi on Facebook
• Follow Raspberry Pi on Twitter
• Check out what we’re having for lunch on Instagram
• Connect with us on Google Plus or whatever happens over there
• Subscribe to the Raspberry Pi YouTube channel

ABOUT US

• About us
• Our team
• Governance
• Safeguarding
• Our supporters
• Jobs
• Contact us

SUPPORT

• Help
• Documentation
• Projects
• Training
• Downloads
• Research
• FAQ

Sign up to our newsletterSUBSCRIBE

RASPBERRY PI FOUNDATION
UK REGISTERED CHARITY 1129409

Privacy Cookies Trademark rules and brand guidelines