SAP HANA XS JOB – Part 1
Welcome one and all to our brand new adventure on this journey with SAP HANA. This is the first of a three part tutorial where we learn how to create an SAP HANA XS Job.Before we get our hands dirty with the implementation, let’s ask the age old question – What is it and why is it used?
What is an SAP HANA XS JOB?
Once in a blue moon, there comes a requirement where you are needed to create repetitive actions. These actions would be asked to be performed at regular intervals. Manual execution of these tasks are tedious, prone to disruptions due to human laziness and hard to maintain when the number of jobs to be handled is quite large. These repetitive actions required could be like one of the following:
- Copy Data from Employee_Salary table to Salary_Backup Table every 8 hours
- Delete all data older than 2 months from table Employee_attendance
- Check for certain KPIs in a view that exceed a maximum value. In such a case, write this KPI information in a separate Log table. Repeat this check every night at 00:00 hour.
- Take data from HANA and send it to an external web service on the 15th of every month at 22:00 hours.
Imagine the agony of having to keep track of these tasks manually. Since HANA isn’t that cruel, it gives us an option to create an XS JOB. We just have to tell HANA what to do and it will keep on repeating perfectly.The important fact to note here is that the XS JOBs do not carry any of the logic. It just carries the schedule and input parameters to be passed to the logic.
Now the next question would be that if XS JOBs don’t carry the logic, where do we write it then? Well, there are 2 ways.
Method – 1 : SAP HANA Stored procedures
We learnt these in our previous tutorial. Stored procedures are reusable blocks of code that you can call anywhere in HANA. Most of your requirements for such jobs would contain database operations like UPDATE, DELETE, INSERT to add/modify the data in tables. These operations can be easily achieved by stored procedures.
These stored procedures can then be called at XS jobs using the schedule configured in them. If the stored procedure requires input parameters, it can also be passed via the XS job.
In the XS job requirement examples I posted above, the first 3 can be achieved by stored procedures being run by XS jobs
Method -2 : SAP HANA XSJS functions
This tutorial comes under the SQL section of this website but some requirements in HANA are beyond database fiddling. Sometimes, HANA needs to interact with external applications and servers. In these cases, we need to write XSJS codes which is basically JavaScript. Try not to get scared. These requirements are rare and form a smaller portion of the total requirement. And you can pick up the skill easily when you work on some scenarios.
The logic that needs to be run in these cases is written in XSJS codes inside functions.
These XSJS functions can then be called at XS jobs using the schedule configured in them. If the XSJS function requires input parameters, it can also be passed via the XS job.
In the XS job requirement examples I posted above, the fourth requirement can only be achieved by XSJS functions being run by XS jobs
In this tutorial, we will try to run our stored procedure from the last example in a schedule that makes it run every 1 hour. If you remember our previous tutorial, this stored procedure will take a material number that is configured in the XS JOBs as input parameter and pull the sales information for this material. In the next step, it will clear the sales log table’s existing data and then fill the sales log table with the new information pulled on the material.
I’ve seen a lot of online tutorials and articles teaching the implementation of this scenario incorrectly. They wrap the Stored procedure inside an XSJS function and then call the XSJS function in an XS job. It’s a total waste of code and time.
Stored procedures can be run directly from an SAP HANA XS JOB and it isn’t even half as complicated as the previous paragraphs make it sound. Bear with me till the end of this tutorial and even if you feel that this is too complicated, once you read this tutorial once or twice, you will realize that the implementation is really simple.
In the next part of this tutorial,we will learn how to create an XS JOB in SAP HANA.