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.
Hi sir, In traditional BW using DELTA we can get updated records.I am building the views on Source system because it is an HANA system (ECC System) .How can I get the updated records in to the HANA system ?
Depends on the method of data provisioning you are using in the project. For example, if it is ECC, your project might be using SLT for replication in which case, the tables are updated in real-time whenever records are added/changed in the source table. For more information, please read the Data provisioning tutorial in the SAP HANA section.
The availability of data would be something usually managed by the admin team or the data provisioning tool’s team and the HANA developer usually proceeds to build views. BW’s data models persist data at each layer and hence need data loads but HANA’s reporting data models are purely virtual and hence don’t require the developer to worry about deltas since no data is loaded between HANA layers at all.
If it’s virtual ,It will automatically update the view automatically. why we are going to HANA XS JOB in this scenario? “Copy Data from Employee_Salary table to Salary_Backup Table every 8 hours ” . .
See, in BW we have 2 types of data loads:
1. Data load from Source systems to BW
2. Data load within different models in BW
In HANA, we still need point 1 (data load from source systems to HANA) in most cases. This is where the data provisioning techniques come in.
Point 2, however is much more rare in SAP HANA. Since we don’t load data between layers in HANA like we used to do in BW. But that being said, there are requests where we might need recurring tasks to happen within HANA. For example.
Scenario #1 – Project has a web portal on top of HANA which users use to write data directly into HANA custom table. Now, the client says, we need to clear this data every end of month. In this case, you write a stored procedure to delete table data and create an XS job that repeats this task every 1st day of the new month.
Scenario #2 – Let’s say some external applications use HANA’s data, process it and write it back. This data is overwritten every time. This means that with every such write, we lose some historical data and there may be a case where we need to preserve this for our reporting. So, in this case, we write an XS job to copy the data from this table to another custom table which can be used in our reporting.
There are countless other scenarios that can be possible but as I said, it’s rare. XS jobs automate tasks – it’s not only about writing data.. it can do any DB task like delete, update etc.. in a recurring manner.
Hope this helps.
Thanks for asking. Please support this site by sharing at least 1 article on social media.
Thanks Sir, Can you please make an another page to explain end-to-end data provision techniques Especially SLT ,SAP Data services (BODS) , DXC
Hi! How HANA eecute imultiple schedule job scheduled at at same time? one job at a time or multiple job at a time?
Multiple jobs can run in parallel but if the data is huge, it might go into a memory overflow error.
Hi Shyamuthaman,
Can we call a table function inside a xsjob ?