In a recent training course I gave to a UK bank, we were covering web-services and I showed how we can get information from a reliable source and then store it in our database.
They informed me that they still manually input UK Bank Holidays into a table. This was a task that they had to perform manually every year end.
This sounded an excellent job for Node-RED and any project that has holiday in the specification is a great morale booster!
To show some extra details in this piece, I will show how we can create a library, or schema, and create a database table prior to storing the UK Bank Holiday dates.
The first task was to find a web-services that provided UK bank holidays.
Having written software packages that communicates with HMRC (His Majesty’s Revenue and Customs, or the Tax Man). This was the first place to look.
It seems that they provide a webservice of both previous, current and future Bank Holidays in the UK. This service is what our Node-RED flow will use. The main Node-RED component used within these examples is the DB2 for i node
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.
Creating the Schema
The first example shows how to run a SQL statement to create a new schema/library.
Here we can see the msg.payload is set to the string create schema bankpower.
Creating the Table
In this next example we demonstrate how to run a SQL statement to create a table that we will store the Bank Holiday dates in.
Let me explain the steps in the above figure.
The main node in this example is the function node. The properties for this node can be seen in the figure below.
Here we can see the msg object payload property being set to the SQL statement that the DB2 for i adapter needs. This could have been achieved in the injection node, just like we did on the creation of the schema, but this shows an alternative method.
Note: Please ignore the red underlines, which normally show errors, this was expanded for illustrational purposes only.
Running the flow produces a table, as seen below, using the ACS.
The only columns we have to insert are the two highlighted above. All the other columns are automatically populated by the DB2 for i database manager. That will make life easier inserting into this table.
Calling a Webservice
Now we have our schema and table to populate in place, we can focus on calling the webservice and inserting records into the database.
As you can imagine, the majority of the work here is performed in the function after the call to HMRC.
The figure below explains the functionality incorporated in this function.
Running a SQL select statement over the bank holiday table shows the follow results.
The complete flow is shown in the diagram below.
Hopefully, these examples show how Node-RED can be used to improve efficiency and automation on the IBM i platform. It is worth taking a look at Node-RED.
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.