[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]