Step-by-Step Procedure to Schedule Job In SAP HANA to execute Stored Procedure

[former_member200930]

Hi Guys,

There are many documents available on SAP HANA Job scheduling, but I didn't find any proper document which says how to Schedule the execution of Stored Procedure in SAP HANA.

Most of the time we get requirement from client, where we need to update a table(s) on specified time interval say week or month basis. So, here I come with an easy step-by-step process showing, how to schedule a job in SAP HANA which will auto-execute stored procedure after specified interval .

Prerequisite:

1. Physical table with data should be available.
2. SAP HANA Developer Edition.
3. XS Engine should be in running state.
4. SPS07 or Higher version.

Lets begin..

Note - First verify XS Engine is up and running by using following URL in your Internet Browser where HANA Client is installed. -

http://localhost:8000/ 

(Replace 'localhost'  with IP. Contact Basis / HANA Admin Team to get IP)

Result:

If you get this page that means XS Engine is working fine.

Lets move ahead..

Scenario -  We will Create a stored Procedure which on execution update the records in table in SAP HANA Database.Then we schedule a job in XS Engine which will run stored procedure after fixed interval.

Sample table present in SAP HANA System.

In the above table, we will update price by 10% on selected date for selected state i.e STATE_ID_FK=1.

Step 1:

Create Store procedure to Update Records

Create Procedure USP_UPDATE_PRICE

as

begin

update "<<SCHEMA_NAME>>"."<<TABLE_NAME>>"

set price = price+(Price)*0.1

where STATE_ID_FK =1;

end;

Note : We have not used any date filter in where clause to make example simpler.

Step 2 :

Choose SAP HANA Development Perspective by using following navigation

Step 3 :

Create Project in SAP HANA Development Perspective mentioned as below

Goto

File -> New ->  Project

'New Project' window will appear as below.

1.Select 'XS Project'.

2. Click on Next push button.

1. Give the name of the project as 'XS_Job_Learning'

2. Click on Next push button .

1. Select desired Repository Workspace

2. Click on Finish push button.

Step  4:

Now we will  create following files in project 'XS_Job_Learning'

1. .xsaccess
2. .xsapp
3. MyJob.xsjob
4. MyFirstSourceFile.xsjs

Add following code in respective files

. xsaccess :

{

"exposed" : true,

"authentication" :

[

{ "method" : "Form" }

]

}

.xsapp :

No need to add any code. This file will be blank.

MyFirstSourceFile.xsjs:

function My_table()

{

var query = "{CALL <<YOUR_SCHEMA_NAME>>.<<YOUR_STORE_PROCEDURE_NAME>>}";

$.trace.debug(query);

var conn = $.db.getConnection();

var pcall = conn.prepareCall(query);

pcall.execute();

pcall.close();

conn.commit();

conn.close();

}

MyJobs.xsjob :

{

  "description": "Job to Update MY_TABLE values",

  "action": "XS_job_learning:MyFirstSourceFile.xsjs::My_table",

  "schedules":

  [

  {

  "description": "Table will update after every 59 sec",

  "xscron": "* * * * * * 59"

  }

  ]

}

Note : "action" row in very important line in .xsjob file. It will search 'XS_job_Learning'  then 'MyFirstSourceFile.xsjs' file and try to execute  'My_table' function which will execute the created store procedure.

If you want more information about Cron Job, then click on  Cron - Wikipedia, the free encyclopedia

Step 5 :

Now, you have completed all your coding at desktop level. Move your files at repositories.

Please follow below mentioned step for the same.

Right click on Project Name  - > Team - > Check

Right click on Project Name  - > Team - > Activate All

Step 6 :

Schedule job in XS engine :

Please type http://localhost:8000/<<Your_Path>> in browser. (This is XS_job_Learning Path in repository. For complete path, you can coordinate with your Basis / HANA admin team. )

Please login using appropriate credentials.

Step 7 :

After successful Login, You will find 'XS_job_Learning'  - > MyJobs.xsjob in Application Objects window

On above screen, please select 'Active' checkbox and click on 'Save' push button to activate job scheduling in SAP HANA.

This completes the Job Scheduling process.

After this, Job is auto-scheduled after specified time interval mentioned in MyJobs.xsjob .

To unscheduled job, uncheck the 'Activate' checkbox and click on 'Save' push button.

Observation -

The scheduled job undergoes three step i.e.

1. SCHEDULED
2. RUNNING
3. SUCCESS

which can be observed in Status Column .

Under JOB LOG window

By clicking on Refresh button, job execution is monitored.

Step 8 :

View result in in SQL Panel

Results displays the updated price by 10% .

Note : You need to provide following rights to execute job in xs engine to users.

Enjoy.....

Happy Learning