You are currently on IBM Systems Media’s archival website. Click here to view our new website.


Community-Developed Dashboard With Node and DB2

In article “Riding ExpressJs and Node.js to the Web,” you learned how to create a simple ExpressJs application complete with database access and a view layer. With this newfound knowledge, I thought it would be good to produce a tool for internal use that utilizes IBM’s ever-growing list of DB2 for i Services.

IBM describes DB2 for i Services as “... system services that can be accessed through system-provided SQL views, procedures, and functions. These provide an SQL interface to access, transform, order, and subset the information without needing to code to a system API.”

I describe it as, “Yes! I can finally access system information without investing a full day dusting off my API skills and spending oodles of time sifting through forum postings.”

If you think about it, in many ways this is genius. It makes things simple for application programmers because we’re more familiar with processing SQL result sets compared to processing lists from system API calls. Does anybody else run out of fingers to count offsets? The past few years, the technology space has realized the simple fact of having technology isn’t good enough—instead you need technology that streamlines core business processes. Or, more succinctly put—technology that saves time! That fact is not lost on our IBM friends in the DB2 for i department.

In hoping to squeeze the most reader benefit from this article as possible, I propose the following: Create an open-source Node.js application named “IBM i Dash” to utilize the DB2 for i Services for dashboarding purposes. For example, we can use the USER_STORAGE and USER_INFO DB2 for i Services to create a user dashboard that makes it simpler to view a variety of user information at quick glance.

Figure 1 and Figure 2 are screenshots to give a visual of the app being created. Figure 1 displays a list of users with a few details of information. Figure 2 is further detail for a specific user obtained by clicking on the user’s profile on the first page.

The Details

Now for the details of how this app was created.

Following are initial setup commands you can run. These should be familiar to you from the previous article so we won’t be covering them in detail here.

$ mkdir ibmi_dash && cd ibmi_dash
$ npm install express
$ npm init
$ npm install jade --save
$ mkdir views
$ touch index.js views/users.jade views/user.jade

Next we have the entirety of index.js, which is the main controller program. The new and significant portions are the SQL statements where SQL Views QSYS2.USER_INFO and QSYS2.USER_STORAGE are called upon. Again, these are the views put together by IBM for our viewing pleasure (pun intended). Notice we’re joining the two views based on AUTHORIZATION_NAME, which is the IBM i user profile to bring about a single result set that’s passed to the view layer. On a side note, these long column names make me think IBM is hiring COBOL programmers again. Good, they’ve been waiting since January 1st, 2000 for work!

--- index.js ---
var express = require('express')
var app = express()
var db = require('/QOpenSys/QIBM/ProdData/Node/os400/db2i/lib/db2')


app.set('views', __dirname + '/views')
app.set('view engine', 'jade')

app.get('/', function (req, res) {
 res.render('index', { title: 'Hey', message: 'Hello there!'})

app.get('/users', function (req, res) {
 var sql = 
 db.exec(sql, function(results) {
   res.render('users', { title: 'Users', results: results})

app.get('/user/:id', function (req, res) {
 var sql = 
 db.exec(sql, function(result) {
   res.render('user', { result: result[0]})


Following we have the users.jade view file with a feature we’ve not yet discussed—the include keyword. This operates similar to /COPY in RPG to bring in outside files. In this case, we are including file header.jade, which includes reference to Bootstrap for UI beautification purposes. I will remind you from the previous article that this is Jade syntax and makes the view files significantly smaller and easier to maintain.

--- users.jade ---
include ./includes/header.jade

table(class='table table-striped table-hover table-condensed')
     th Name
     th Status
     th Description
     th Storage Used
   - each row in results
       td: a(href='/user/#{row.AUTHORIZATION_NAME}')=row.AUTHORIZATION_NAME 
include ./includes/footer.jade

Next we have the user.jade page that gives further details about the user. One bit of new Jade syntax are lines starting with a period (i.e., .panel.pane-default). These were formerly HTML <div> tags and since <div> is so common it got its own short-form within Jade. One of the best ways to learn Jade syntax is to type out regular HTML and then paste it into a site like to convert it.

--- user.jade ---
include ./includes/header.jade

     tr: td: b Status 
     tr: td: b Description
     tr: td: b Special Authorities 
     tr: td: b Storage Used
     tr: td: b Home Directory
     tr: td: b Last Used
include ./includes/footer.jade

Your Turn

Ok, now the app is done but the article is not.

In an effort to further community in IBM i, I'd like to invite (challenge?) others to contribute to this application by adding additional menu options that make use of other DB2 for i Services (there are many). Or, come up with your own idea for an additional dashboard page and add it to the project. The code for the entire project resides here on BitBucket.

New to contributing? No worries. Check out this tutorial for how to fork and pull.

If learning Git is a little much, we’ll be covering that in the next article.

Shoot me any comments or questions you may have at

Aaron Bartell is Director of IBM i Innovation for Krengel Technology Inc. and an IBM Champion.

Like what you just read? To receive technical tips and articles directly in your inbox twice per month, sign up for the EXTRA e-newsletter here.



2019 Solutions Edition

A Comprehensive Online Buyer's Guide to Solutions, Services and Education.

Are You Multilingual?

Rational enables development in multiplatform environments

IBM Systems Magazine Subscribe Box Read Now Link Subscribe Now Link iPad App Google Play Store
IBMi News Sign Up Today! Past News Letters