Objectives

In this Exercise you will learn how to:

• Create the Monitor Device type using Monitor API
• Inject and verify 1 row of data
• Inject historical data from CSV file(s)

---

Before you begin:
This Exercise requires that you:

1. Have completed the pre-requisites required for all labs AND Exercise 3
2. Have completed the setup of Node-RED locally
3. Have imported the Node-RED script

The Node-RED script includes 5 steps which are shown with comment nodes in the script:

1. Create generic flow data in the "Set Flow Data" function node
2. Create Asset Table in DB2 using the Monitor API
3. Inject 1 row of data into the table to verify it works
4. Update the "Fomat SQL" function node in step 5 to reflect step 3
5. Upload CSV data to SQL database using the UI uploader

---

Create the Monitor Device type

This section describes how to use the Monitor API to create the demo pump device in Monitor without using the IoT Tool.

1. Open Node-RED in your browser if not done already.
2. Find and open the "Set Flow Data" node in Step 1.
   
   
3. Enter the Monitor API URL monitorAPIURL, the API Key x_api_key and API Token x_api_token
   as defined in the Pre-Requites.
   Replace XX with your initials in line 5 and 7. Press Done.
   The metricTimestampColumn in the "Set Flow Data" function node tells Monitor which column should be used as timestamp, which is important when injecting historical data.
   The metrics are already adjusted to contain the same columns as in Pump CSV data files that you will use later in the lab.
   
   
4. Deploy the script and if you enterred the correct credentials you should see the below message at Node-RED Dashboard (unless the DEMO_PUMP table has already been created).
   
   
5. Step 2 is really easy, as it only requires you to click on the inject node called "Create Table".
   
   
6. Navigate to the Dashboard once the requesting status of the http request node is gone.
   
   

Inject and verify 1 row of data

1. Find and open the "MAS (8.6)" dashDB In node in Step 3.
   
   
2. Click on the pencil to edit the Server data.
   
   
3. Enter the credentials to the DB2 Warehouse server and press Update and then Done.
   
   
4. Find and open the "Format SQL" function node.
   
   
5. Scroll down and you will see two sections where you can enter data.
   The first "line" section is populated with some simulated data which is in the same order as the CSV file data you will be using soon. The second section is where the SQL statement is being created which will be used to inject that data into the DB2 Warehouse.
   You can leave these sections untouched for now, as this excercise will use the provided CSV files that matches that data set - Click on Done or Cancel to close the function node.
   
   
6. Deploy the script and click on the Inject 1 row inject node.
   
   
7. My first try failed due to wrong password as you can see in the debug pane.
   The second attempt was successful as shown in the red box.
   You can also see the actual complete SQL statement that was send to the database. This might help you when you want to experiment with your own data set after you have completed this lab.
   
   
8. And you can also verify that the data was injected into the database using DBeaver.
   
   

Inject historical data from CSV file(s)

Now is the time you have been waiting for ;-)

You will be using the two csv files which was downloaded together with the Node-RED script
(in your Downloads folder). Please remember that location.
The first three steps of the total of five is done and you will now execute the last two steps.

1. As you can see in the script Step 4 is about copying the second section from Format SQL in Step 3 to Format SQL in Step 5.
   
   
2. Open the Format SQL in Step 3, scroll to the end and make a copy of the lines between:
   // BEGIN: THIS SECTION CAN BE COPIED TO STEP 5 "Format SQL" NODE, ONCE IT WORKS. : // END
   Click Cancel.
   
   
3. Open the Format SQL in Step 5, scroll to the end.
   You can see that the content you copied from Step 3 is already there, but now you know the process when experimenting with your own data sets. Just click Cancel.
   
   
4. It is a good idea to delete the test rows that was injected in Step 3 before injecting the CSV data from the files. Scroll down in the workspace and you will see some additional Step 4 nodes to empty the table (but not removing it), click on the Truncate table inject node.
   
   
5. It is now time to inject the first small data set of 10 data lines. The file PumpDataSmall.csv will be used for that purpose.
   Navigate the the Node-RED Dashboard and click on Choose file (or Browse... on Windows).
   
   
6. Select PumpDataSmall.csv and press Open.
   
   
7. Press the Play button.
   
   
8. It took 1 second to inject 10 rows of data.
   
   
9. If that is not the case, then verify that the separator is correct. Open the csv node.
   
   
10. Verify that the Separator is the same as that being used in the CSV file.
    semicolon is used in the pump csv files.
    
    
11. It is a good idea to disable the two debug nodes before injecting the larger csv file. Click on the green (enabled) button so it become grey (disabled).
    
    
12. Now it is time to inject the large CSV file. Click on Choose file and open PumpData.csv.
    
    
13. Click on the Play button and you will see the progress as data is being injected.
    
    
14. You have now injected 132479 rows of data in total from the two CSV files :-)
    
    

---

Congratulations you have successfully adjusted and prepared your script as well as injected the data from the two CSV files.
You will now be able to adjust according to your own data sets and inject those data sets into Maximo Monitor - Enjoy!