Thursday, June 27, 2013

(Updated) NoSQL and JSON with DB2

You may have heard that DB2 10.5, which recently became available, includes a technology preview "DB2 NoSQL JSON". JSON is the JavaScript Object Notation. The important stuff for JSON in DB2 can be found in the sqllib/json directory. Once I added the "lib" directory to the Java CLASSPATH, I could invoke a new command line processor "db2nosql" (found in "bin"):

hloeser@rotach:~/sqllib/json/bin$ sh
JSON Command Shell Setup and Launcher.
This batch script assumes your JRE is 1.5 and higher. 1.6 will mask your password.
Type -help to see options
Enter DB:jsontest

IBM DB2 NoSQL API build 1.0.169
Licensed Materials - Property of IBM
(c) Copyright IBM Corp. 2013 All Rights Reserved.

Debug mode is off.
nosql>Type your JSON query and end it with ;
nosql>Type help() or help for usage information
nosql>Setup Tables and Functions seems to have not been created or have been created incorrectly. Please type enable(true) and enter to setup them up. You must have the correct admin privileges.
If you do not, type enable(false) and enter to see the SQL that will be used.

After entering the database name to use I got connected. Next was to enable the database for JSON processing as suggested above.

Executing SQL...
Database Artifacts created successfully

If you use "enable(false)" the tool will just print the DDL to execute manually. This is the creation of a system table SYSTOOLS.SYSJSON_INDEX and several functions. Next I set a JSON namespace. By default the user schema would be taken as namespace. Because I plan to look into storage details later on, I chose "TEST" (which will use the SQL schemaname "TEST" under covers).

 nosql>use test
Switched to schema TEST

Then I was good to go and insert two documents. Note that it is not necessary to create any table, collection, index or anything. Trying to find one of the documents I stored via predicate also worked.

nosql>db.obj.insert({name: "Henrik", country: "DE"})
nosql>db.obj.insert({name: "Jim", country: "US"})
nosql>db.obj.find({country: "US"})
nosql>Row 1:
nosql> {
nosql> "_id":{"$oid":"51c9acf301c690e828779af2"},
nosql> "name":"Jim",
nosql> "country":"US"
nosql> }
nosql>1 row returned in 134 milliseconds.

Some more commands are described in a recent developerWorks article on JSON in DB2. I also plan to write more about my adventures here in this blog.

Added 27.06.2013:
A couple more developerWorks articles are now available:
Finally, I have a video introducing the technology.