Skip to Content

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:

XS Enigne.png

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.

/wp-content/uploads/2015/03/hana_table_667607.png

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

/wp-content/uploads/2015/03/image_2_667608.png

Step 3 :

Create Project in SAP HANA Development Perspective mentioned as below

Goto

File -> New ->  Project

‘New Project’ window will appear as below.

/wp-content/uploads/2015/03/image_2_667608.png

1.Select ‘XS Project’.

2. Click on Next push button.

/wp-content/uploads/2015/03/image_2_667608.png

1. Give the name of the project as ‘XS_Job_Learning

2. Click on Next push button .

/wp-content/uploads/2015/03/image_2_667608.png

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

/wp-content/uploads/2015/03/image_2_667608.png

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

/wp-content/uploads/2015/03/image_2_667608.png

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. )

/wp-content/uploads/2015/03/image_2_667608.png

Please login using appropriate credentials.

Step 7 :

After successful Login, You will find ‘XS_job_Learning’  – > MyJobs.xsjob in Application Objects window

/wp-content/uploads/2015/03/image_2_667608.png

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

/wp-content/uploads/2015/03/image_2_667608.png

Results displays the updated price by 10% .

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

/wp-content/uploads/2015/03/image_2_667608.png

Enjoy…..

Happy Learning

To report this post you need to login first.

20 Comments

You must be Logged on to comment or reply to a post.

  1. Thomas Jung

    Why write an XSJS that all it does is call the Stored Procedure. You can just schedule the Stored Procedure itself from the XSJOB.

    (0) 
      1. Thomas Jung

        I think you missed the point of the comment.  The XSJS wrapper around the Stored Procedure is completely unnecessary. XSJOBs can directly call a Stored Procedure as well as XSJS services.  It simply isn’t needed to have the XSJS call the Stored Procedure as you are doing. Its adding overhead without value.

        (1) 
          1. Thomas Jung

            >Same method is used in SAP HANA developer document at page number 478 / topic 8.7.1.1

            No the same method isn’t used. In the Developer Documentation they are calling an XSJS, yes; but that’s to execute XSJS.  If you want to call a Store Procedure you should simply schedule it directly within the XSJOB. 

            >here, i just tired to make it simple to freshers. So, they can schedule job without any problem.

            But you are telling them the wrong thing.  If they want to schedule a store procedure they don’t need all the steps you are suggesting. You are over complicating things.

            Although I’ve been unable to convince you of this, I hope that others reading this blog will see the comments and realize that what you propose is incorrect and inefficient and ignore the content.

            (1) 
            1. Konstantin Brotzmann

              Hi Thomas,

              what is the right syntax for the calling a stored procedure in an .xsjob file? I didn’t not find anything in the documentation. I tried this:

              {
              “description”: “Update information”,
              “action”: “CALL \”MY_SCHEMA\”.\”MY_PROCEDURE\” ()”,
              “schedules”: [{
              “description”: “Update information every 10 second”,
              “xscron”: “* * * * * * 0:59/10”
              }]
              }

              … this …

              “action”: “MY_SCHEMA.MY_PROCEDURE”

              … and several another combinations.

              But every time I get:  Error -> Parsing job action failed.

               

              regards

              Konstantin

              (0) 
              1. Thomas Jung

                Definitely don’t use the CALL and (). Just the procedure name. However you might need a synonym to remove the schema name if you are using non-repository procedures. For repository procedures its just <namespace>::procedure.  I have an example here: https://www.youtube.com/watch?v=3THGIr5bwx0

                (0) 
            2. Manish Gupta

              Hi Thomas,

              Could you please guide me on using the procedure name in the action property of xsjob.

              As while giving the procedure I am getting the following error:

              Required object does not exist: either specify ‘schema’ in xsjob or provide a public synonym as job

              I tried giving the procedure name with schema, without schema and also tried with synonym but it’s  it’s not.

              I tried ‘schema’ as a property but maybe it’s not there in xsjob.

              Kindly help in this, if you can provide me some documentation or some kind of syntax because I am not able to find this on the web.

              Thanking you,

              Manish

               

              (0) 
  2. Thomas Jung

    Although I’ve been unable to convince you of this, I hope that others reading this blog will see the comments and realize that what you propose is incorrect and inefficient and ignore the content.

    (0) 
    1. Sivakumar Palaniswamy

      Hi Thomas,

      I just wanted to check if there is anyway to set dependency between XS job and maestro job . My requirement is execute my XS job in System B ( Child Company )  after the completion of maestro job in System A ( Parent Company ).

      I have the sap documentation and could not find any valuable information on the same.

      Appreciate the feedback!

      Thanks
      Siva

      (0) 
    2. Chirag Gupta

       

      Hi,

      I have a procedure which I can run using CALL “I7xxxxx”.”UPDATE_MARGIN”;

      I was to schedule it using xsjob. what should we write in the action inside xsjob file?

      I tried following and some variant of it as well, but didn’t work.

      “action”: “I7xxxxx::USP_UPDATE_MARGIN”,

      Please suggest the syntax  for “action” for store procedures.

       

      Thanks,

      Chirag

       

      (0) 
      1. Ashwini Chaturvedi

        Hi Chirag,

        Did you get the syntax for procedure ?

        My procedure statement is – call “SCHEMA1”.”PK1.PK2::getdata”()

        I am not able to drive action tag

        Regards,

        Ashwini

        (0) 
  3. Kevin Geiger

    Hi Thomas, can I schedule a stored procedure that is submitted via a trigger.  My stored procedure inputs are Contents package path and “browser” role.  We are not giving the select to _SYS_BIC but want an automated way to update a “browser” role specific to the Contents package path.  I have the stored procedure working but want a way for developers to submit it on demand. Our process prevents developers from previewing the views until the view is added to the “browser” role.  Our developers are not allowed to update roles, that is a security team function only.

    (0) 
  4. Kevin Geiger

    Hi Thomas, I am trying to schedule the XSJOB running sqlscript and that seems pretty easy.  But when I connect to XS Job Dashboard I update the configuration screen for the job but get a error message the Application Privilege is missing.  I have the …JobAdministrator role assigned to the ID.  What other Application Privilege do I need.

    (0) 
  5. Balaji S

    I am getting the below error when I go to Application Objects window and select my package which is ‘XSJOB’ here. I have got the below roles assisgned as well. Any idea why I could be getting this error.

    1. sap.hana.xs.admin.roles::JobAdministrator
    2. sap.hana.xs.admin.roles::HTTPDestAdministrator

    XS Error.PNG

    (0) 
  6. Nitin Singh

    Hi All,

    I am very new to SAP HANA and I am wondering if I can read csv file from a package and then write back to csv file and then sent out csv file as an email attachment using xsjs and xsjob? is there any library to read/write csv file using xsjs?

    Thanks a lot

    (0) 

Leave a Reply