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

How-To: The IBM Informix REST API on the Raspberry Pi

Wed Feb 24, 2016 6:05 pm

Hi,

I am sorry that it took some while before I could continue with my How-To series around IBM Informix and sensor data on the Raspberry Pi, but from time to time I need to work on some real and exciting sensor data projects for my employer. ;)

Just in case you might have missed my other posts on how to use IBM Informix for sensor data management on the Raspberry Pi, here are those articles:

viewtopic.php?f=37&t=97199&p=674959
viewtopic.php?f=37&t=97772&p=678497
viewtopic.php?f=37&t=100029&p=693935

Today I would like to show you how to configure and how to enable the REST API for Informix. The current developer edition of Informix for the RPi (12.10.UC6DE) includes a Java based REST and MongoDB compliant listener. Although Informix's MongoDB compatibility is a very interesting topic in itself, I am focusing today on the REST API listener alone.

The Informix REST API provides an open, very flexible and secure interface for developers who would like to utilize Informix's sensor (time series) data and relational capabilities without the need to dig too deep into Informix's other client development APIs. The API supports GET, POST, PUT and DELETE operations.

Through the REST API Informix can be also very easily integrated into JavaScript based frameworks like Node.js, Node-RED or any kind of client tools and libraries which support REST calls. As a side note: I am currently working on a simple Informix TimeSeries plugin for Grafana 2.6 (http://grafana.org/). That plugin also utilizes the Informix REST API.

Ok, so let's get started.

Since the Informix REST listener is java based, we need to make sure that we do have a Java JRE or JDK installed on the Raspberry Pi:

Code: Select all

sudo apt-get install oracle-java8-jdk
Before we can start the REST listener, we need to define some listener settings in a properties file. The easiest way to do this is to create a copy of the existing listener example file ('$INFORMIXDIR/etc/jsonListener-example.properties') and name it e.g. restListener.properties:

Code: Select all

cp $INFORMIXDIR/etc/jsonListener-example.properties $INFORMIXDIR/etc/restListener.properties
Now you need to open the 'restListener.properties' file in an editor of your choice (e.g. vi or nano) and modify a few of the default properties in that file. I am now listing a few properties with values which I am typically using on my Raspberry Pis, but feel free to experiment with those values:

Code: Select all

url=jdbc:informix-sqli://localhost:9088/sysmaster:INFORMIXSERVER=ol_informix1210;USER=<your_db_user>;PASSWORD=<your_db_user_password>       # You shouldn't use user informix credentials here, if your REST listener is accessible from outside the Raspberry Pi due to potential security issues!
listener.hostName=*
database.share.close.enable=true 
listener.idle.timeout=3000
listener.port=<your desired port number for the REST API eg. 8080>
listener.http.accessControlAllowOrigin="*" 
listener.type=rest
pool.size.maximum=2
pool.connections.maximum=4
pool.type=advanced 
response.documents.count.default=10000     # or any desired number
pool.idle.timeout=60
security.sql.passthrough=true   # only set this property to true if you would like to use the SQL pass through feature!
As soon as you have modified your REST listener properties, you can 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
If you would like to start the REST listener in the background you could e.g. do the following:

Code: Select all

nohup java -cp $INFORMIXDIR/bin/jsonListener.jar com.ibm.nosql.server.ListenerCLI -config $INFORMIXDIR/etc/restListener.properties -logfile $INFORMIXDIR/tmp/restListener.log -loglevel error -start &> /dev/null & 
As soon as the listener is up and running, you can execute some simple REST GET operations against your sensor data through a web browser which is able to connect to the Informix REST listener port on the Raspberry Pi. In the following examples I assume that the 'listener.port' property has been set to '8080'. Also: in my examples I am using 'localhost' as the hostname. Please adjust that value to your specific Raspberry Pi hostname or IP address.

With the following example URL I am fetching all time series data from the VTI table 'sensor_data':

Code: Select all

http://localhost:8080/sensor_db/sensor_data
Now limit the result set to only one primary key (one time series) with the following URL:

Code: Select all

http://localhost:8080/sensor_db/sensor_data?query={"sensor_id":"Sensor01"}
If you only want to retrieve specific columns from your VTI table (or any other relational table in Informix), just use the fields operator:

Code: Select all

http://localhost:8080/sensor_db/sensor_data?query={"sensor_id":"Sensor01"}&fields={"timestamp":1,"value":1}
BTW, time stamps returned are epoch time stamps (milliseconds since 01/01/1970) in UTC.
Ok now let's provide a slightly more complex query through the REST API by providing a time stamp range:

Code: Select all

http://localhost:8080/sensor_db/sensor_data?query={"$and": [{"sensor_id":"Sensor01"},{"timestamp":{"$gte": {"$date":1289347200000}}},{"timestamp":{"$lt": {"$date":1297122300000}}} ]}&fields={"timestamp":1,"value":1}
Alternatively to the numeric representation of the epoch time stamps in the query conditions, you can also use the following JSON time stamp string format:

Code: Select all

http://localhost:8080/sensor_db/sensor_data?query={"$and": [{"sensor_id":"Sensor01"},{"timestamp":{"$gte": {"$date":"2016-02-24T15:00:00.000Z"}}},{"timestamp":{"$lt": {"$date":"2016-02-24T15:30:00.000Z"}}} ]}&fields={"timestamp":1,"value":1}
The SQL pass-through support in the Informix REST listener
If you have set the listener property 'security.sql.passthrough=true' (again, be careful in doing so!), you can send any SQL command to the Informix database. In the following example I am sending a SELECT statement through the REST API to Informix which returns the last entry of a time series:

Code: Select all

http://localhost:8080/sensor_dbsystem.sql?query={"$sql":"select GetLastElem(sensor_values).value from sensor_ts where sensor_id = 'Sensor01'"}
BTW that Informix built-in time series function 'GetLastElem()' can be quite handy if you would like to display/visualize the most recent stored sensor data value. ;)

I think by now you should hopefully have enough information to get you started with the Informix REST API.

Since Informix's REST API is much more powerful than I could have described in such a short introductionary article, I would recommend that you take a look at the following online documentation:
In addition to the online documentation there are also some helpful presentations available on slide share:
Informix TimeSeries Node-RED nodes
Since I briefly mentioned Node-RED in the intro of this post, let me also point you to a set of Informix Node-RED time series nodes which utilize the Informix REST API:
http://flows.nodered.org/node/node-red- ... timeseries

I am using those nodes quite often on my Raspberry Pi to pre-aggregate sensor data before they are sent into the cloud. ;)

And finally: have fun using the Informix REST API in combination with the Informix time series capabilities! :)

- Alexander

Return to “Automation, sensing and robotics”