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

IBM i > DEVELOPER > GENERAL

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')

db.debug(true)
db.init()
db.conn("*LOCAL")

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 = 
   "SELECT * FROM QSYS2.USER_STORAGE AS US" +
   " LEFT JOIN QSYS2.USER_INFO AS UI on UI.AUTHORIZATION_NAME=US.AUTHORIZATION_NAME" 
 db.exec(sql, function(results) {
   res.render('users', { title: 'Users', results: results})
 })
})

app.get('/user/:id', function (req, res) {
 var sql = 
   "SELECT * FROM QSYS2.USER_STORAGE AS US" +
   " LEFT JOIN QSYS2.USER_INFO AS UI on UI.AUTHORIZATION_NAME=US.AUTHORIZATION_NAME" +
   " WHERE US.AUTHORIZATION_NAME='" + req.params.id + "'"
 db.exec(sql, function(result) {
   res.render('user', { result: result[0]})
 })
})

app.listen(80)

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

h1=title
table(class='table table-striped table-hover table-condensed')
 thead
   tr
     th Name
     th Status
     th Description
     th Storage Used
 tbody
   - each row in results
     tr
       td: a(href='/user/#{row.AUTHORIZATION_NAME}')=row.AUTHORIZATION_NAME 
       td=row.STATUS
       td=row.TEXT_DESCRIPTION 
       td=row.STORAGE_USED
         
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 html2jade.org to convert it.

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

.panel.panel-default
 .panel-heading
   =result.AUTHORIZATION_NAME
 .panel-body
   
   table(class='table')
     tr: td: b Status 
       td=result.STATUS
     tr: td: b Description
       td=result.TEXT_DESCRIPTION 
     tr: td: b Special Authorities 
       td=result.SPECIAL_AUTHORITIES
     tr: td: b Storage Used
       td=result.STORAGE_USED        
     tr: td: b Home Directory
       td=result.HOME_DIRECTORY  
     tr: td: b Last Used
       td=result.LAST_USED_TIMESTAMP   
  
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 albartell@krengeltech.com.

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.



Advertisement

Advertisement

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