In my last article I wrote about the hoops we had to jump through to get the DB2 for i Node-RED adapter installed.

In this article, we will be taking a look at how we use that adapter and also how we can run IBM i commands from within our Node-RED flows.  All very useful.

If you are having problems installing the Node-RED DB2 for i adapter, take at look at my previous article which can be found at this link.

Using the DB2 Adapter

The db2 adapter is provided by IBM and gives us two nodes.  One for the configuration and the other where we run our SQL commands. 

Please note that if you do not have the latest IBM I logo on the node, you do not have the latest version.  Use the Node-RED pallette manager to update the package.

Configuration

Obviously, we have to configure our DB2 for i node before it can be used. We have to detail which IBM i we wish to connect to, and what user ID and password to use.

When we drag the DB2 for i node onto our flow canvas, it presents with a triangle on top of the node.  This indicates that the node is missing some configuration.  Double clicking on the node brings up the properties.

Click on the pencil icon to configure a new configuration.

Please also note that the Single Array Result mode will need to be ticked to return the complete SQL recordset in one go.  Personally, I would like to see this as the default method, maybe in the future IBM?

As stated earlier, input the details of your IBM i.  The database name can be kept at *LOCAL if the database you are querying is on the same server as the one running your Node-RED.

Once all details have been input, the configuration triangle on the node will be removed.

SQL Statements

If we drag an inject node and a debug node either side of the DB2 node, we can give the connection a quick test.

As the documentation for the DB2 node states, we need to provide a msg.payload that contains the SQL query statement to run.  We will do this in the inject node.  Double click on it to bring up the inject nodes properties.

1. In the msg.payload section, click the drop-down menu
2. Select string
3. Enter the SQL statement to execute
4. Press done to complete the inject properties

Our flow should be like the figure below.

Press the inject node to test.

In the figure above, we can see

1. The msg.payload object has returned an array of 12 elements. The 12 records from the QIWS.QCUSTCDT table.
2. Expand the array down arrow
3. Here are the individual records back from the IBM i

All working as expected.

Using the DB2 Adapter to run IBM commands

We can also use the adapter to run IBM i commands too.  By using SQL Stored Procedures we can execute any command.

IBM has provided the QCMDEXC procedure in the QSYS2 library.

Let me show an example.

This time in the inject node, we call the procedure call qcmdexc(‘sndmsg hello andy’)

Clicking the inject node, executes the SNDMSG command.

And the proof of the pudding!

Using the Exec Node

The standard Node-RED installation also provides a method to run and execute IBM i commands.

By using the exec node, with the BASH system built in function, we can execute whatever command we wish, from calling a RPG program to executing a bespoke command.

As we can see from the figure on the left, the exec node has one input and three outputs.  The input takes our command and, as the name states, it executes whatever we supply to it.  This is also knows as standard input (stdin). 

The three outputs relate to standard output (stdout), standard error (stderr) and the last output is for any return code from the command that was executed.  For now, ignore the blue spot, it is reminding us the changes have not been deployed yet.

The figure below details all these IO points (input/output).

If you need to acquire more knowledge on stdin, stdout and stderr, it might be worth while taking a look at a series of BASH videos I produced on YouTube.  The playlist for these videos can be found at the link here.  Mr Editor, please forgive my plug!

In the figure below, we change the inject node to have a msg.payload of system ‘crtlib DEC2022’

When executed, the output from the exec node is in the figure below.

1. The output from stdout shows CPC2102 library created
2. The output from return code is 0 (no errors)

If we run it again, it produces an error, as the library is already there.

1. stderr reports the library already exists
2. the return code message reports the command failed

the complete flow of all the tests we carried out in this article, is shown in the figure below.

Conclusion

Node-RED has a wealth of IOT applications and devices available to us, but with using the DB2 adapter it opens up a wealth of opportunities. Make the most of it!

All the examples I have written for this article, and previous ones, can be found on my open-source repository on GitHub, which can be found at https://github.com/formaserve/f_Learning

If you have any questions, either on this article, or anything else on the IBM i, use the comments below, or send me a message on twitter @AndyYouens

Andy Youens is an IBM i consultant/instructor at Milton Keynes, UK-based FormaServe Systems with over 40 years IBM midrange experience. 

IBM Champion