November 30, 2015

Simple JSON / BSON Implementation


Continuing with innovations and enhancements aligning to the Industry trends and patterns, IBM Informix has always positioned itself, as the Next Generation Database. Late in 2013, IBM Informix leaped ahead from being the Object Relational Database to a NoSQL Database ( or Not-Only SQL DBMS ).

Support for JSON & BSON as built-in datatypes helped store NoSQL (Unstructured ) datasets, while support for WIRE Listener enhanced connectivity options to the database, where MongoDB API and REST API can be used to access the NoSQL datasets, along with the Relational datasets, that co-exist, under the single table, making IBM Informix a True Hybrid Database server.

To quickly illustrate on the ease of implementation and use, we have put together a shell script that shall detail the flow of calling JSON/BSON types while designing the table and the access mechanism to read through the NoSQL table.

1.      The shell script is well tested on Linux and might need minor changes on Unix platform. It won’t work on Windows platform. However, you can copy paste SQL statements high lighted in blue in following section and execute on windows manually.
2.      Ensure that Informix engine is online. You can use “onstat –“  command
3.      Download the shell script available in the zip file ( Or Copy - Paste the following link on to the Browser:
https://drive.google.com/open?id=0B4mxiMcgUauaMENkNVdxZXJRQlU )
4.      Unzip the file on linux/Unix machine under some test directory
5.      You might need to execute dos2unix command for each file before execution
6.      Ensure that 'main.sh' and 'func.sh' have Execute Privileges
7.      Execute main.sh and just follow the instructions.

Output of shell script should look like this :

################################################################
Welcome to TimeSeries demo.
This program has sections that demonstrate capabilities of TimeSeries.
Flow in each section is Setup, Data Load & Data Retrieval
please enter your choice of TimeSeries demo
 1      : TimeSeries Demo
 2      : JSON Demo
 3      : TimeSeries with JSON Demo
 4      : TimeSeries with Rolling Window Concept
 5      : TimeSeries with Hertz Frequency Concept
 6      : TimeSeries with Swinging Door Compression Algorithm

2

################################################################
 You have chosen a Demo on Informix NoSQL
################################################################

Please read the options carefully :-
If you are executing the NoSQL Demo for the first time, then
        Press '1'       : To Perform the Setup and create the Demo Environment

If you have already performed the setup for NoSQL Demo, then
        Press ANY NUMBER: To Execute Queries against the prepared Setup

1

Cleaning up old files...
Clean up is over. Please presss Enter to continue

################################################################
Creating dbspace dbspace1

execute function admin('create dbspace','dbspace1',
'/home/amprasan/IDS1210/storage/dbspace1','100 MB','0')
45107280         6        0x60001    6        1        2048     N  BA    informix dbspace1
471ba028         6      6      0          10000      5371                  PO-B-D /home/amprasan/IDS1210/storage/dbspace1

dbspace creation .. passed

Please press Enter to continue

################################################################
Creating database demo_db

drop database if exists demo_db;
create database demo_db in dbspace1 with buffered log

database creation .. passed

Please press Enter to continue

################################################################
Creating table test_ts

create table test_ts
 (
   tagid char(20),
   time_stamp datetime year to fraction(5),
   series bson
) in dbspace1;
alter table test_ts
lock mode(row);

Table creation .. passed

Please press Enter to continue

################################################################
Load data for all tags into the table test_ts

dbaccess demo_db load_bson2.sql

Loading data into NoSQL (JSON/BSON) table .. passed

Please press Enter to continue

Congratulations!!! Your setup is successful. Please press Enter to refresh the screen and be ready to see certain data retrieval queries

################################################################
Simple select on NoSQL (JSON/BSON) table to see range of data

select * from test_ts
where tagid='tag1'
and time_stamp between '2015-07-01 00:02:00.00000' and
'2015-07-01 00:04:00.00000';

Press enter to see output

Database selected.

tagid       tag1
time_stamp  2015-07-01 00:02:00.00000
series      L

tagid       tag1
time_stamp  2015-07-01 00:03:00.00000
series      L

tagid       tag1
time_stamp  2015-07-01 00:04:00.00000
series      L

3 row(s) retrieved.

Database closed.

Press enter to continue

################################################################
Not Happy with the result set returned? Let's Try again
Simple select on NoSQL (JSON/BSON) table to see range of data

select tagid, time_stamp, series::json series from test_ts
where tagid='tag1'
and time_stamp between '2015-07-01 00:02:00.00000' and
'2015-07-01 00:04:00.00000';

Press enter to see output

Database selected.

tagid       tag1
time_stamp  2015-07-01 00:02:00.00000
series      {"lon":50.23487,"lat":4.87345,"temp":38.453,"humidity":20.235,"val":100}

tagid       tag1
time_stamp  2015-07-01 00:03:00.00000
series      {"lon":50.36746,"lat":5.12643,"temp":37.237,"humidity":21.344,"val":101}

tagid       tag1
time_stamp  2015-07-01 00:04:00.00000
series      {"lon":51.46758,"lat":4.87345,"temp":38.947,"humidity":22.137,"val":102}

3 row(s) retrieved.

Database closed.

Press enter to continue

################################################################
Select on NoSQL (JSON/BSON) table to pick individual value from the Document Store

select tagid, time_stamp, bson_get(series,'temp')::json series from test_ts
where tagid='tag2'
and time_stamp = '2015-07-01 00:02:00.00000';

Press enter to see output

Database selected.

tagid       tag2
time_stamp  2015-07-01 00:02:00.00000
series      {"temp":38.453}

1 row(s) retrieved.

Database closed.

Press enter to continue

################################################################
Update a Document on NosQL (JSON/BSON) table

update test_ts set series = '{lon:40.234870,lat:5.873450,temp:18.453000,humidity:25.235000,val:200.000000}'::json::bson where tagid='tag1'
and time_stamp = '2015-07-01 00:02:00.00000';

Press enter to see output

Database selected.

1 row(s) updated.

Database closed.

Press enter to continue

Congratulations!!! Your Demo is completed Successfully.
We hope you had a pleasant learning experience

Click Here to read further on IBM Informix JSON Compatibility

Click Here to read through a IBM Technical Paper - Introducing NoSQL Capabilities

No comments: