Although Node.js needs no introduction allow me to give you a brief overview : Node.js is a JavaScript runtime powered by Chrome’s V8 JavaScript engine. Its signature event-driven, non-blocking I/O model makes it lightweight and efficient. With over a million npm packages currently available, Node.js is currently one of the most popular frameworks for backend development on the market.
Most of the things that you want to do in your Node.js application are probably covered by one of the million packages already available in the Node.js community. In our case, we want to connect to IBM Db2 with SDK for Node.js all within z/OS, and we have a npm package to do that: node-ibm_db.
Prerequisites
Install IBM SDK for Node.js - z/OS
To install Node.js on z/OS, you need IBM SDK for Node.js – z/OS ®, which is available for download here.
Follow the installation instructions on IBM Knowledge Center to install SDK for Node.js - z/OS.
Db2 and ODBC driver Setup
Before installing node-ibm_db module, you need to have Db2 or ODBC driver setup on your z/OS machine. Contact your system admin for Db2 and ODBC installation and follow ODBC Guide and References to configure your ODBC driver.
Please ensure the following:
- During installation of Db2 on z/OS, apply PTF UI60551 to pick up new ODBC functionality to support Node.js application
- Bind the ODBC package. A sample JCL is provided in the SDSNSAMP dataset in member DSNTIJCL. Customize the JCL with specifics of your system.
- Ensure users that should be authorized have authority to execute the DSNACLI plan. Contact your system admin if you do not have authority to do so.
- Set the IBM_DB_HOME environment variable to the High-Level Qualifier (HLQ) of your Db2 datasets. For example, if your Db2 datasets are located as DSNC10.SDSNC.H and DSNC10.SDSNMACS, you need to set IBM_DB_HOME environment variable to DSNC10 with the following statement (can be saved in ~/.profile):
# Set HLQ of Db2 datasets.
export IBM_DB_HOME="DSNC10"
- Update the STEPLIB environment variable to include the Db2 SDSNEXIT, SDSNLOAD and SDSNLOD2 data sets. You can set the STEPLIB environment variable with the following statement, after defining IBM_DB_HOME to the HLQ of your Db2 datasets as instructed above:
# Assumes IBM_DB_HOME specifies the HLQ of the Db2 datasets.
export STEPLIB=$STEPLIB:$IBM_DB_HOME.SDSNEXIT:$IBM_DB_HOME.SDSNLOAD:$IBM_DB_HOME.SDSNLOD2
-
Configure an appropriate Db2 ODBC initialization file that can be read at application time. You can specify the file by using either a DSNAOINI data definition statement or by defining a DSNAOINI z/OS UNIX environment variable. For compatibility with node-ibm_db and node.js, the following properties MUST BE SET:
In COMMON section:
MULTICONTEXT=2
CURRENTAPPENSCH=ASCII
FLOAT=IEEE
In SUBSYSTEM section:
MVSATTACHTYPE=RRSAF
For example, we saved the following complete initialization file to ~/odbc.ini
; This is a comment line...
; Example COMMON stanza
[COMMON]
MVSDEFAULTSSID=VC1A
CONNECTTYPE=1
MULTICONTEXT=2
CURRENTAPPENSCH=ASCII
FLOAT=IEEE
; Example SUBSYSTEM stanza for VC1A subsystem
[VC1A]
MVSATTACHTYPE=RRSAF
PLANNAME=DSNACLI
; Example DATA SOURCE stanza for STLEC1 data source
[STLEC1]
AUTOCOMMIT=1
CURSORHOLD=1
You can then set DSNAOINI
export DSNAOINI=[YOUR_PATH]/odbc.ini
If you want to see the ODBC trace for debugging purposes, you can do so by specifying the following under COMMON section
APPLTRACE=1
; specify a path you want to save your trace in
APPLTRACEFILENAME="~/trace.log"
Reference Chapter 3 in the ODBC Guide and References for more instructions.
node-ibm_db installation
You can install node-ibm_db in your node application either with npm that shipped with IBM SDK for Node.js - z/OS or from the GitHub repository (requires Git on z/OS):
# Install via npm repository
npm install ibm_db
# --- OR -----
# Install from GitHub repository
npm install git+ssh://git@github.com/ibmdb/node-ibm_db.git
You can import the module in your application by:
const ibmdb = require(‘ibm_db’);
Or you can manually install by using git clone (requires Git on z/OS):
git clone git://github.com/ibmdb/node-ibm_db
cd node-ibm_db
# Install the module dependencies
npm install
However, using this method will require you to include node-ibm_db into your source control as it will not register in your package.json and thus your node modules. When importing node-ibm_db, point to the directory instead:
const ibmdb = require(‘[YOUR_PATH_TO_IBMDB_DIR]’);
Now you have installed node-ibm_db and are ready to connect to your Db2 system in z/OS.
Connecting to Db2 with node-ibm_db
To connect to Db2 with node-ibm_db, you will need to set a connection string, which will include information for connection such as your database name (Datasource name or DSN) and authentication credentials.
An example connection string would look like this:
const connStr = ‘DSN=[YOUR_DATASOURCE_NAME];UID=[YOUR_UID];PWD=xxxxxxx’
Or an alternate way would be setting the environment variable:
export IBM_DB_DBNAME=[YOUR_DATASOURCE_NAME]
export IBM_DB_UID=[YOUR_UID]
export IBM_DB_PWD=”xxxxxxx”
You would also need to set the following environment variable if Db2 is hosted on z/OS:
export IBM_DB_SERVER_TYPE="ZOS"
You can open the connection with the connection string:
ibmdb.open(connStr, function (err, conn) {
//…
});
Or you can open the connection synchronously:
const conn = ibmdb.openSync(connStr);
Querying
You can query Db2 by calling the query method on the Database object, which is returned after successfully opened a connection:
ibmdb.open(connStr, function (err, conn) {
conn.query(‘create table mytab1 (c1 int, c2 varchar(10));’, function (err, data) {
//…
});
});
Note: Currently Db2 on z/OS does not support BOOLEAN type, so you will get an error if you create a table with a BOOLEAN type column
If you want to run multiple queries synchronously, you can either call the following query inside the callback
conn.query(‘create table mytab1 (c1 int, c2 varchar(10));’, function (err, data) {
conn.query(‘insert into mytab1 value (4, 'a');’, function (err, data) {
//…
})
});
Or use querySync
conn.querySync(‘create table mytab1 (c1 int, c2 varchar(10));’);
// This query will run after the query above
conn.query(‘insert into mytab1 value (4, 'a');’, function (err, data) {
//…
});
Asynchronous query
One of the signature features of node.js is that it is asynchronized. You can also easily run multiple query asynchronously:
conn.query(‘select * from mytab1 where c1 = 2;’, function (err, data) {
//…
});
conn.query(‘select * from mytab1 where c1 = 4;’, function (err, data) {
//…
});
but be aware that you need to ensure that the queries you are about to run are safe to run asynchronously.
Prepare statement
It is a good practice to prepare your SQL statement before executing it. You can prepare a statement by:
conn.prepare('insert into mytab (insert into mytab (id, varchar(10)) VALUES (?, ?)', function (err, stmt) {
//…
});
To execute a prepared statement, you can:
conn.prepare('insert into mytab (insert into mytab (id, varchar(10)) VALUES (?, ?)', function (err, stmt) {
stmt.execute([ 42, ‘abc’], function (err, result) {
//…
});
});
It will bind the variable 42 and “abc” to the two “?” declared in the prepared statement respectively and execute the statement.
If your prepared statement is an UPDATE, an INSERT, a DELETE, or a MERGE, you can run:
conn.prepare('insert into mytab (insert into mytab (id, varchar(10)) VALUES (?, ?)', function (err, stmt) {
stmt.executeNonQuery([ 42, ‘abc’], function (err, affectedRowCount) {
//…
});
});
executeNonQuery() returns the number of rows affected in the table by the statement. If no rows are affected, it returns -1 via the callback function.
Metadata
Sometimes you may need to retrieve metadata information in your tables such as column names.
To get the column names:
// result is the ODBCResult object return from execute() and query()
result.getColumnNamesSync();
To get the column metadata:
result.getColumnMetadataSync()
Note: If you are getting SQLCODE = -440 error, please ensure that you have Database MetaData routines installed in your Db2. These routines are provided to perform catalog queries such as SQLColumns and SQLTables. You can run job DSNTIJRV provided in the SDSNSAMP library to validate that the Database MetaData routines are successfully created. To install the routines, run job DSNTIJRT as part of the Db2 installation. If you can confirm that the routines are already installed but still getting -440 failure, try to rebind everything.
Closing connection
You will need to close the connection once you are finished. You can close your connection by:
conn.close(function (err) {
console.log(‘Done’);
});
Synchronously,
conn.closeSync()
You should always remember to close your connection, especially when your program runs into an error:
if(err) {
console.log(err);
return conn.closeSync();
}
Running your application
You can run your application with Node.js by
node myapp.js
provided that your code is saved in myapp.js.
At this point, you should have a basic understanding of how to use the node-ibm_db module to communicate between your node.js application and Db2 on your z/OS system. For the full list of available APIs, see here.
Thank you for reading and happy coding!