[et_pb_section bb_built=”1″ fullwidth=”on” specialty=”off” next_background_color=”#000000″][et_pb_fullwidth_header _builder_version=”3.17.2″ background_image=”https://www.krengeltech.com/wp-content/uploads/2017/02/dawid-zawila-407391-unsplash.png” /][/et_pb_section][et_pb_section bb_built=”1″ _builder_version=”3.0.63″ prev_background_color=”#000000″][et_pb_row _builder_version=”3.17.2″][et_pb_column type=”4_4″][et_pb_text admin_label=”Blog Post” _builder_version=”3.17.2″ module_alignment=”left” text_font=”||||” header_font=”||||” use_border_color=”off” max_width_tablet=”50px”]
Originally published: IBM Systems Magazine on 6/1/15
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.
[/et_pb_text][/et_pb_column][/et_pb_row][/et_pb_section]