Monday, August 18, 2014

Accessing DB2 from node.js on IBM Bluemix and locally

Some days ago I started experimenting with node.js. Other than JSON and some click functions on webpages I don't have much experience with JavaScript. The reason for this "adventure" is that node.js is offered as one of several programming languages on IBM Bluemix, IBM's platform-as-a-service (PaaS). I wanted to find out how complex or easy it would be to bring both node.js and DB2 (IBM's relational and in-memory database system) together.

When I start with some new language I typically produce errors. Thus I wanted to avoid pushing my app to Bluemix all the time, but instead wanted to test it locally first. Hence I downloaded and installed a local node.js environment, including the so-called node.js package manager "npm" first. npm allows you to install additional modules/code libraries. They are placed into the directory "node_modules". Within the program (or script), the modules are included and referenced via the "require" statement:

var express = require('express');

The above binds the "ExpressJS Web Application Framework for node". That framework is part of the node.js starter application on IBM Bluemix. With that basic application which is offered for download you can easily test whether the local installation work ok:

node app.js

The command which I executed in a regular shell launches the node.js runtime with the sample application. Based on the configuration it provides a small web application available on my laptop on port 3000. Accessing "" in my web browser shows the demo page. All ok.

To combine node.js and DB2 I require the DB2 database driver:

var ibmdb = require('ibm_db');

Just running the application again would return an error because the module has not been installed. Hence my next step in the command shell is:

npm install ibm_db

This invokes the node package manager and instructs it to download and install the IBM database client driver and related node.js API. Waiting for a minute it returned an error because it couldn't find the file "sqlcli1.h". This is an indicator that my local DB2 was missing the application development environment. Running "db2setup" again (as root), selecting "work with existing" and marking the application development package for installation solved the issue. After db2setup finished, I ran "npm install ibm_db" again and it was able to download, build and install that module.

To test my small app both locally and on Bluemix, I needed to obtain user and DB2 instance information for either the local environment or the Bluemix SQLDB service (DB2). This is done with the following code snippet (not that beauty as I just started...):

1:  // get DB2 SQLDB service information  
2:  function findKey(obj,lookup) {  
3:    for (var i in obj) {  
4:     if (typeof(obj[i])==="object") {  
5:       if (i.toUpperCase().indexOf(lookup) > -1) {  
6:        // Found the key  
7:        return i;  
8:       }  
9:       findKey(obj[i],lookup);  
10:     }  
11:    }  
12:    return -1;  
13:  }  
14:  var env = null;  
15:  var key = -1;  
16:  var db2creds=null;  
17:  if (process.env.VCAP_SERVICES) {  
18:     env = JSON.parse(process.env.VCAP_SERVICES);  
19:     key = findKey(env,'SQLDB');  
20:  }  
21:  if (!env) {  
22:    console.log("We are local");  
23:    var file = __dirname + '/db2cred.json';  
24:    try {  
25:     db2creds = require(file);  
26:    } catch(err) {  
27:     return {};  
28:    }  
29:  //  db2creds = JSON.parse(fileJSON);  
30:    console.log(db2creds);    
31:  } else {  
32:  var db2creds = env[key][0].credentials;  
34:  }  
35:  var connString = "DRIVER={DB2};DATABASE=" + db2creds.db + ";UID=" + db2creds.username + ";PWD=" + db2creds.password + ";HOSTNAME=" + db2creds.hostname + ";port=" + db2creds.port;  
37:  app.get('/db2', routes.db2test(ibmdb,connString));  

In the code I first search for the object with the Bluemix environment information. If it is not found the code assumes it is a local invocation. In that case the DB2 access information is loaded from the file "db2cred.json". It is a file I created in the application directory with a content like here:
Logo for my DB2 node.js app

    "hostname": "",
    "host": "",
    "port": 50000,
    "username": "hloeser",
    "password": "mytopsecretpassword",
    "db": "CLOUDDB"

The code uses the information about the hostname, port, and the user/password combination to create a connection string. That information together with the IBM Database Driver interface can be passed to a request handler in the node.js/Express runtime infrastructure (the "app.get()" call).

My small test application runs successfully both on my laptop as well as on IBM Bluemix. I plan to write more about it over the next days and to upload the code to my Github account. Bluemix-related posts can be accessed by this link.

Update: The follow-up article has been published here, showing geo IP lookup and logging into DB2.