DB2 JSON capabilities, Part 4: Using the IBM NoSQL Wire Listener for DB2

Rapidly changing application environments require a flexible mechanism to store and communicate data between different application tiers. JSON (JavaScript Object Notation) has proven to be a key technology for mobile, interactive applications by reducing overhead for schema designs and eliminating the need for data transformations.

DB2® DB2 JSON enables developers to write applications using a popular JSON-oriented query language created by MongoDB to interact with data stored in IBM DB2® for Linux®, UNIX®, and Windows® or IBM DB2® for z/OS®. This driver-based solution embraces the flexibility of the JSON data representation within the context of a RDBMS with well-known enterprise features and quality of service. This DB2 NoSQL capability supports a command-line processor, a Java™ API, and a wire listener to work with JSON documents.

In this article, the IBM NoSQL Wire Listener for DB2 is introduced. It parses messages based on the MongoDB wire protocol. It thus enables using MongoDB community drivers, and the skills acquired when working with these drivers, to store, update and query JSON documents with DB2 as JSON store.

Ioannis Papapanagiotou, Ph.D., ETI Solutions Developer, IBM

Ioannis Papapanagiotou photoIoannis Papapanagiotou is part of the Emerging Technology Institute (ETI) in IBM, an in-house incubator team reporting to the WebSphere CTO and working on middleware systems for front-end caches and data store systems. He holds dual Ph.D. degrees in Computer Engineering and Operations Research from North Carolina State University. He has authored several papers and patents in the areas of data redundancy elimination and wireless networks.



Marion Behnen, DB2 JSON and Spatial Development, IBM

Marion Behnen is a Senior Software Engineer within the IBM Software Group and has been working on several NoSQL JSON features. Before delving into the world of NoSQL, she has been the technical lead for several DB2 and warehousing application components. Before joining IBM, she was involved with many aspects of business process and data integration, in particular related to database application development in the manufacturing industry.


developerWorks Contributing author
        level

John Paul Parkin, WebSphere eXtreme Scale Developer , IBM

JP Parkin photo John Paul Parkin is currently a member of the WebSphere eXtreme Scale development team in Toronto working on serialization optimization and NoSQL interfaces. JP has spent 15 years developing solutions for a variety of products at IBM including DB2, InfoSphere Warehouse and WebSphere eXtreme Scale.



Jyh-Chen Fang, DB2 JSON Development , IBM

Jyhchen Fang is a staff software engineer within the IBM Software Group. Before working on the IBM DB2 JSON solution, he worked on IBM Data Server Driver for JDBC and SQLJ.



27 June 2013

Also available in Chinese Japanese

Before you start

IBM NoSQL Wire Listener for DB2, hereafter referred to as "wire listener," provides the following features:

  • Enables developers to use DB2 with skills they have acquired working with JSON document stores.
  • Supports both applications written with MongoDB driver and MongoDB Command shell commands
  • Leverages features provided by the DB2 JSON API for handling BSON documents.

Background information

The prevalent method for modern web user interface development is JavaScript for Web 2.0. JavaScript is key for mobile, engaging applications and its data interchange format JSON is thus considered by many as the language of the web. The advantages of the JSON format are that it provides schema flexibility and development agility by reducing the schema-design overhead.

In this context, HTTP REST/JSON is typically used to communicate with the mid-tier application layer. This has led to the development and rising acceptance of document stores, such as MongoDB, that natively support JSON. NoSQL stores like MongoDB work very well with server-side JavaScript (e.g. NodeJS) since both speak the same language, and understand JSON documents. The combination of these two powerful concepts enables the rapid development of real-time applications with high requirements for throughput and concurrency.

MongoDB

MongoDB (from “humongous”) is a scalable, high performance, open source database. MongoDB is a document store; in other words each document is written as a unit with its own individual structure. MongoDB's API is a native mixture of JSON objects and JavaScript functions. Developers interact with MongoDB either via a language driver (Java, C/C++, Ruby, NodeJS, and so on) that access data store instances, or via the shell program. Such applications interact with the database through the MongoDB wire protocol. The MongoDB wire protocol is a simple socket-based, request-response style protocol. Clients communicate with the database server through TCP/IP.

BSON format

MongoDB supports schema-less documents stored in BSON format. BSON (Binary JSON) is a binary-encoded serialization of JSON documents and contains extensions that enable the representation of data types of the JSON specification. It includes support for JSON structures like arrays or nested objects with documents within other documents or arrays.

BSON documents in MongoDB are schema-less, which makes it possible to build collections of documents with different structures. This flexibility of document structures is useful in domains where data, for example, has varying, but similar attributes, or sparse attributes, or quickly evolving document structures. A classic example is a book collection in a web application. Given a stack of book entries, you will see that they present different data: some include the author and the title, others reviews by users, others a summary, or information about the publishing company. The data varies, but the model or function is the same.

IBM NoSQL Wire Listener

This article describes the wire listener, which enables DB2 users to leverage the MongoDB community drivers to natively store and query JSON data in DB2 and thus exploit DB2 as a JSON document store.

The wire listener is a server application that accepts and processes the MongoDB Wire Protocol. It leverages a DB2 JSON API to process, store and retrieve BSON documents from DB2. The following figure showcases the communication between an application developed with a MongoDB API and DB2.

Figure 1. IBM NoSQL Wire Listener architecture
Applications use the MongoAPI to submit requests to the wire listener which uses the DB2 JSON Java API to process operations through the DB2 client on the DB2 server.

Enabling JSON support for DB2

DB2 JSON is a driver-based solution available with DB2 for Linux, UNIX, and Windows 10.5 and with the IBM DB2 Accessories Suite V3.1 to work with DB2 on z/OS V10. It facilitates using DB2 as a JSON document store. The following steps show how to prepare the environment for a wire listener application.

DB2 on Linux, Unix and Windows

See the DB2 Information Center for details about DB2 installation, database planning, and command execution environments.

Step 1: Configure the server

Create a UTF8 database to store JSON data. It is recommended to use a 32K page size.

Listing 1. Server configuration
db2 create database jsondb automatic storage yes using codeset
     utf-8 territory us collate using system pagesize 32 K

If the database server has not yet been configured for TCP/IP, open the TCP channel on the database server to listen for incoming requests from the JDBC driver. The following example uses the default port number 50000.

Listing 2. Opening the DB2 port
db2set DB2COMM=TCPIP
db2 update dbm cfg using svcename 50000
db2stop
db2start

Step 2: Enable the DB2 database

To enable the database for JSON support, run the enable command with the db2nosql JSON command-line processor. You can submit the command on the database server or perform it on the client. The scripts are located in <DB2 Installation Directory>/json/bin.

The following examples show how to execute an enable command through the db2nosql command-line processor on the server (localhost) or on a client (with hostname or TCP/IP address).

Listing 3. Windows (local and remote example)
db2nosql.bat -user bob -hostName localhost -port 50000 
             -db jsondb -setup enable -password mypassword

db2nosql.bat -user bob -hostName bob.bobhome.com -port 23023 
             -db jsondb -setup enable -password mypassword
Listing 4. Linux or UNIX (local and remote example)
./db2nosql.sh -user bob -hostName localhost -port 50000
              -db jsondb -setup enable -password mypassword

./db2nosql.sh -user bob -hostName bob.bobhome.com -port 23023 
              -db jsondb -setup enable -password mypassword

Notes:

  • Ensure that "java" is in the PATH.
  • Make sure that the JDBC driver (db2jcc.jar version 3.66 or above, or db2jcc4.jar) is included in the classpath. If necessary, add the jarfile into the CLASSPATH or the -cp in the db2nosql script.
  • The command line processor requires information needed to connect to a database. Ensure that the user has the proper administration privileges to create tables and functions, which are required to enable DB2 JSON functionality.

See also DB2 JSON Capabilities, Part 2: Using the Command Line Processor for more details about setting up the JSON CLP environment.

DB2 on z/OS

Applications can use the DB2 JSON API or the DB2 JSON WireListener on a distributed system platform and use a DB2 Connect JDBC driver to use DB2 for z/OS as a JSON datastore.

The following components are required for this deployment scenario:

  • A DB2 10.5 FP3 (or later) for Linux, Unix, and Windows server or client installation with the DB2 JSON and DB2 Driver components. After installation these components are located in SQLLIB/json.
  • The DB2 Accessories Suite V3.1.
  • DB2 for z/OS Version 10 with required APARs: See APAR II14727 for details about prerequisites and installation of DB2 JSON with DB2 for z/OS.

With DB2 for z/OS, enabling a database will be performed as a one-time installation task by a DB2 for z/OS Database or System Administrator. The enablement via the db2nosql command line tool is not supported for z/OS. See APAR PM98357 for details about enablement of DB2 JSON with DB2 on z/OS.

Catalog the database on the distributed system and note the connection information or the URL of the DB2 database on z/OS for use with the DB2 JSON client.


IBM NoSQL JSON Wire Listener

Step 3: Set up and start the wire listener

The wire listener can be started and stopped with the wplistener script, located in <DB2 Installation Directory>/json/bin. The wire listener must be started with Java runtime environment (JRE) version 6 or higher and requires information to connect to a database. The -debug flag is optional, but if it is used, the logpath must point to an existing folder.

  • Ensure that "java" is in the PATH.
  • Make sure that the JDBC driver (db2jcc.jar version 3.66 or above, or db2jcc4.jar) is included in the classpath.

See an example to start the wire listener in Listings 5 and 6.

Listing 5. Windows
wplistener.bat -start -mongoPort 27017 -userid <db2admin> -password <mypasswd> 
                      -dbName <dbName> 

wplistener.bat -start -mongoPort 27017 -userid <db2admin> -password <mypasswd> 
                      -dbName <dbName> -debug -logPath c:/temp/logs
Listing 6. Linux or UNIX
./wplistener.sh -start -mongoPort 27017 -userid <db2admin> -password <mypasswd> 
                       -dbName <dbName>

./wplistener.sh -start -mongoPort 27017 -userid <db2admin> -password <mypasswd> 
                       -dbName <dbName> -debug -logPath /usr/temp/logs

The -dbName must match the database name that was enabled for JSON support in Step 2.

The wire listener is now ready to process the incoming application messages. See the MongoDB command line sample and the JSON programming samples later in this article for example applications that use the wire listener to create a collection, and insert and query JSON documents.

Note: To stop the wire listener, use the wplistener script with the connection information to send a shutdown request as shown in the following examples.

Listing 7. Windows
wplistener.bat  -shutdown -noSQLHost localhost -mongoPort 27017 
                            -dbName <dbName> -userid <db2admin>
Listing 8. Linux or UNIX
./wplistener.sh  -shutdown -noSQLHost localhost -mongoPort 27017 
                               -dbName <dbName> -userid <db2admin>

Note that -noSQLHost refers to the address of the wire listener for the shutdown.

General options

The Wire Listener supports a set of input arguments to specify the connection information, and determine runtime parameters as shown in the following listing.

Listing 9. Wire listener parameters
-dbName <DB2 database>     DB2 database name used as the JSON document store
-debug                     Enable debug for the wire listener
-help                      Display usage help for the wire listener
-host <DB2 host:port>      hostname and port when connecting
                           to a remote DB2 server(host:port)
-logPath <path>            Path for storing execution log files
-maxTCPThreads <threads>   Maximum number of TCP Threads
-minTCPThreads <threads>   Minimum number of TCP Threads
-mongoPort <port>          Port that is listening for MongoDB client requests
-noSQLHost <hostname>      Name of the Host running the wire listener
-password <password>       Password for the backend server
-shutdown                  Stop the wire listener
-start                     Start the wire listener
-userid <userid>           Userid for the backend server

Notes: Not all options are required. The valid start and shutdown options are listed below:

  • <start options> : -mongoPort <port> -userid <userid> [ -logPath <path> ] [-password <password>] [-debug] [-minTCPThreads <threads>] [-maxTCPThreads <threads>]
  • <shutdown options> : -mongoPort <port> -noSQLHost <hostname> -userid <userid> [ -logPath <path> ] [-password <password>] [-debug]

-minTCPThreads and -maxTCPThreads are performance arguments that can be used for throttling the thread processing of the incoming work, based on the size of the server and load. These are optional arguments, and most users do not have to use them.


DB2 JSON operations

The wire listener supports most common operations to work with JSON namespaces, collections, and documents. These include for example,

  • create, remove, drop collections
  • create (ensure), remove, drop indexes
  • insert, update, remove documents
  • import, export documents
  • find, findOne, count, aggregate, distinct, group documents
  • ... and many more ...

Note that some operations return database-specific information, so some message or argument options may not be applicable.

Also consider that collections and indexes are created with default settings if no options are specified. See DB2 JSON Capabilities, Part 3: Using the JAVA API and the reference documentation for details about supported functions and other details.


MongoDB command line sample

Defining a database

For a collection to be created in MongoDB a database must be defined. In MongoDB, if the database name is not provided, the database "test" is used. The main difference with DB2 is that the concept of a MongoDB database is matched to a JSON namespace, which is represented by a DB2 schema. Therefore, like with MongoDB, databases can be defined during the runtime of the application. In the example above, the wire listener was started with -dbName jsondb . This is the DB2 database that will host the specified JSON namespaces. In other words, a MongoDB database is represented by a DB2 schema. This provides similar flexibility as using databases with MongoDB, and allows you to define any MongoDB database name that conforms to DB2 schema naming conventions at any point of time during the development phase.

The following example shows using the MongoDB shell with the wire listener to select a database:

Listing 10. Defining a database
> use mydb
Switched to db mydb

This will create a JSON namespace, or SQL schema, named MYDB in the DB2 database.

Creating a collection

The MongoDB equivalent of a collection is the DB2 table. MongoDB will create a collection implicitly upon its first use. Similarly, in DB2, a table is implicitly created upon its first use.

Listing 11. Creating a collection
> db.mycollection.insert({"_id":1 , "name":"Ioannis"})

> show collections
mycollection
system.indexes

Storing and querying data

The following listing shows some sample operations to insert, update, find, and remove documents. The collection will be automatically created.

Listing 12. Sample operations
> db.mycollection.insert({"_id":1 , "name":"Ioannis"})
> db.mycollection.findOne()
{ "_id" : 1, "name" : "Ioannis" }
> db.mycollection.update({"_id":1},{$set:{"name":"Marion"}})
> db.mycollection.findOne()
{ "_id" : 1, "name" : "Marion" }
> db.mycollection.insert({"_id":2, "name":"JP", "last":"Parkin"})
> db.mycollection.find()
{ "_id" : 1, "name" : "Marion" }
{ "_id" : 2, "name" : "JP", "last" : "Parkin" }
> db.mycollection.remove({"name":"JP"})
> db.mycollection.count()
1

JSON programming samples

The examples require the MongoDB Node.js driver which can be installed with the following command:

Listing 13. Install MongoDB for NodeJS
npm install mongodb

JSON NodeJS programming sample: insert documents

The sample application shown in Listing 14 inserts some documents into a collection.

Listing 14. A simple NodeJS sample
var Db = require('mongodb').Db;
var Connection = require('mongodb').Connection
var Server = require('mongodb').Server
var format = require('util').format;

var mongo = {
        "host": "localhost",
        "port": "27017",
        "db": "mydb"
};

var db = mongo.db;
console.log("Connecting mongo at: " + mongo.host + ":" + mongo.port + ":" + mongo.db);

Db.connect(format("mongodb://%s:%s/%s?w=1", mongo.host, mongo.port, mongo.db), 
   function(err,  db) {
        db.collection('sample', function(err, collection) {
            // Start with a clean collection
            collection.remove({}, function(err, result) {
    
            // Insert a JSON document
            collection.insert({name:'Joe'},{w:1}, function(err,res){    
             collection.findOne({name:'Joe'}, function(err, item) {
              console.log("created at " + new Date(item._id.generationTime) + "\n")   

               // Insert multiple JSON documents with different schema
               for(var i = 0; i < 5; i++) {
                 collection.insert({'a':i}, {w:0});
               }
    
            });
        });
    });
    });
});

JSON Python programming sample

The sample Python application shown in Listing 15 inserts some documents and searches the documents in the collection.

Listing 15. Python programming sample
import pymongo
from pymongo import MongoClient

client = MongoClient('localhost', 27017)

db = client.items

db.mycol.drop()

# inserting into mycol collection.
db.mycol.insert({'model': 'AB1234', 'type': 'bottom freezer', 'price': 2000})
db.mycol.insert({'model': 'XY9876', 'type': 'french doors', 'price': 1200})
db.mycol.insert({'model': 'OP4567', 'type': 'side-by-side', 'price': 800})
db.mycol.insert({'model': 'OP4599', 'type': 'bottom freezer', 'price': 1800})

q = {'price' : {'$gt': 1200, '$lte': 2000}}

# looking for the items with the price greater than 1200 and less than or equal to 2000.
aaa = db.mycol.find(q).sort("price")

# printing the document
for doc in aaa:
    print (doc)

Note that by default, insert operations are asynchronous in separate transactions. Depending on resources and timing, you might therefore see a smaller list than the number of inserted documents. See the code example in Listing 16 for a node.js example to wait with accessing documents until all inserts are complete.

JSON NodeJS programming sample: insert and read documents

Listing 16. NodeJS example with insert and find
var Db = require('mongodb').Db;
var Connection = require('mongodb').Connection
var Server = require('mongodb').Server
var format = require('util').format;

var mongo = {
        "host": "localhost",
        "port": "27017",
        "db": "mydb"
};

var db = mongo.db;
console.log("Connecting mongo at: " + mongo.host + ":" + mongo.port + ":" + mongo.db);

Db.connect(format("mongodb://%s:%s/%s?w=1", mongo.host, mongo.port, mongo.db), function(err,  db)
{
    db.collection('sample', function(err, collection)
    {
        // Start with a clean collection
        collection.remove({}, function(err, result)
        {
            // Insert a JSON document
            collection.insert({name:'Joe'},{w:1}, function(err,res)
            {    
                collection.findOne({name:'Joe'}, function(err, item)
                {
                    console.log("created at " + new Date(item._id.generationTime) + "\n")   
    
                    // Insert multiple JSON documents with different schema
                    function insert(i)
                    {
                        if (i < 5)
                        {   
                            collection.insert({'a':i}, {w:1}, function(err,res)
                            {
                                insert(i + 1);
                            });
                        }
                        else
                        {
                            // Count documents
                            collection.count(function(err, count)
                            {
                                console.log("There are " + count + " records in the sample collection.");
                                collection.find().each(function(err, item)
                                {
                                    if (item != null)
                                    {
                                        console.dir(item);
                                    }
                            
                                    // Null signifies end of iterator
                                    if (item == null)
                                    {
                                        // Destory the collection
                                        collection.drop(function(err, collection)
                                        {
                                            db.close();
                                        });
                                    }
                                });
                            });
                        }
                    };
                    insert(0);
    
                }); // end findOne
                
            }); //end insert
            
        }); //end remove
        
    }); //end collection
    
}); // end connect

See other articles in the series for a discussion of alternatives that allow reducing transactional overhead.


Conclusion

This article showed how to enable JSON support and run the IBM NoSQL Wire Listener for DB2 with some simple steps. The wire listener parses messages based on the MongoDB wire protocol, and developers can use the skills that they acquired working with MongoDB community drivers to write applications that leverage DB2 JSON interfaces. When planning your application, make sure to review the DB2 JSON documentation to account for DB2 specific extensions and restrictions.

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management, Open source, Java technology
ArticleID=935320
ArticleTitle=DB2 JSON capabilities, Part 4: Using the IBM NoSQL Wire Listener for DB2
publish-date=06272013