Skip to Content
Author's profile photo Juan de la Cruz Arellano Royo

How to scheduling XS Job to call stored procedures

Hi,

This tutorial try to explain how to schedule a XS job to call strore procedure.

We need to change the SAP HANA perspective to SAP HANA Development.

/wp-content/uploads/2016/07/2016_07_06_113314_989984.png

We create a XS Project with a new package as follow:

/wp-content/uploads/2016/07/2016_07_06_113535_989985.png

In the next step we must to set the project name

/wp-content/uploads/2016/07/1_989986.png

We select the workspace:

/wp-content/uploads/2016/07/1_989986.png

Here we can create the XS JavaScript file, so we set the name of the file:

/wp-content/uploads/2016/07/1_989986.png

In order to schedule a XS Job we need to create a new file under out package:

/wp-content/uploads/2016/07/1_989986.png

Please, select XS Job Scheduler File:

/wp-content/uploads/2016/07/1_989986.png

Set the parent folder (previously created ZJUANDE_IBO) and set the XS JavaScript file name:

/wp-content/uploads/2016/07/1_989986.png

Now we have the following file structure:

/wp-content/uploads/2016/07/1_989986.png

Here the example of xsjob file:

Important is action. The action keyword enables you to define the function to run as part of the XS job, for example, an XS JavaScript or an SQLScript. The following syntax is required:

“action” : “<package.path>:<XSJS_Service>.xsjs::<functionName>”

The schedule for the specified task (defined in the “action” keyword); the schedule is defined using cron-like syntax. Following some examples:

2016 * * fri 12 0 0

Every Friday of 2016 at 12:00 hours


* * * * 12 0 0

Every day of every year at 12:00 hours

* * * * 12 0 *

Every second of every day of every year

* * * * * */5 30

Every five minutes and, in addition, at the 30th second in the specified minute

My code exeute the job every day at 00:00

/wp-content/uploads/2016/07/1_989986.png

This code enable to call 2 stores procedures per execution. Don’t put the character “;” at the end of the call procedure.

/wp-content/uploads/2016/07/2_990029.png

Now we need to access to XS Engine with the URL http://<XSengine-host><XS-port>/sap/hana/xs/admin/

/wp-content/uploads/2016/07/1_989986.png

/wp-content/uploads/2016/07/1_989986.png

/wp-content/uploads/2016/07/1_989986.png

Here we need to activate it:

/wp-content/uploads/2016/07/1_989986.png

If you want to confirm when the XS Job will run, click on View Logs:

/wp-content/uploads/2016/07/1_989986.png

And check the Planned Time:

/wp-content/uploads/2016/07/1_989986.png

My system is on CEST with DST, for this reason the planned time is 2 hours after scheduled.

Enjoy it!

Juan de la Cruz Arellano Royo

Assigned Tags

      16 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Hello,

      When i tried to follow this procedure, I got the following error.

      "InternalError: dberror(Connection.prepareCall): 257 - sql syntax error: database name not allowed in single-container mode:"

      Could you please help me on what settings if any to change so as to get the desired output?

      Author's profile photo Juan de la Cruz Arellano Royo
      Juan de la Cruz Arellano Royo
      Blog Post Author

      Hi Pavitra,

      Could you attach a screenshot o paste code from xsjs file?

      Regards

      Author's profile photo Former Member
      Former Member

      //xsjs file

      function My_Test()

      {

      var query = "{CALL _SYS_BIC.cs.70_HDB_PROCEDURES::TEST}";

      $.trace.debug(query);

      var conn = $.db.getConnection();

      var pcall = conn.prepareCall(query);

      pcall.execute();

      pcall.close();

      conn.commit();

      conn.close();

      var query1 = "{CALL _SYS_BIC.cs.70_HDB_PROCEDURES::TEST1}";

      $.trace.debug(query1);

      var conn1 = $.db.getConnection();

      var pcall1 = conn.prepareCall(query1);

      pcall1.execute();

      pcall1.close();

      conn1.commit();

      conn1.close();

      }

      //xsjob file

      {

        "description": "Test",

      "action": "cs.xsjob.TDC_JOB:TEST.xsjs::My_Test",

        "schedules":

        [

        {

        "description": "TEST",

        "xscron": "2016 * * thu 09 09 0"

        }

        ]

      }

      Author's profile photo Juan de la Cruz Arellano Royo
      Juan de la Cruz Arellano Royo
      Blog Post Author

      Hi,

      The query is wrong. The correct format is:

      var query = "{CALL <SCHEMA>.<PROCEDURE_NAME>}";



      The xsjob file must to be like this:


      "action": "<PACKAGE>:<XS_JavaScript_Filename>.xsjs::<FUNCTION_NAME>",



      Regards

      Author's profile photo Former Member
      Former Member

      Hello,

      I have changed the query to this.

      var query = "{CALL _SYS_BIC.cs.70_HDB_PROCEDURES.TEST()}";

      And the action remains the same. Which is,

      "action": "cs.xsjob.TDC_JOB:TEST.xsjs::My_Test"

      My XSjob is in TDC_JOB which is in turn in xsjob package which is in cs package. Hence I have written the entire package path.

      It still gives the same error. Please help!

      Author's profile photo Juan de la Cruz Arellano Royo
      Juan de la Cruz Arellano Royo
      Blog Post Author

      Hi Pavitra,

      Still wrong at least the definition of action.

      "action": "<PACKAGE>:<XS_JavaScript_Filename>.xsjs::<FUNCTION_NAME>",


      Change to Developper perpestive and take a view on my example:

      /wp-content/uploads/2016/07/2016_07_22_134448_1000769.png


      On my example I need to change

      • "action": "<PACKAGE>:<XS_JavaScript_Filename>.xsjs::<FUNCTION_NAME>",


      Up to

      • "action": "ZJUANDE_IBO:JOHND_FUNCIONES.xsjs::My_FUNCTION",



      Regards

      Author's profile photo Former Member
      Former Member

      Sorry for the bad image. But I hope you get an idea about why my action has that particular package path.

      sap.PNG

      Inside the TDC_JOB, I have created my TEST.xsjs and xsjob files.

      Thanks.

      Author's profile photo Former Member
      Former Member

      Hi, I have the same problem of Pavitra!

      Have you solve this problem?

      If yes, how?

      Thanks,

      Silvia

       

       

      Author's profile photo Bobby Bal
      Bobby Bal

      Hi Is this issue resolved. We are having the same issue, and followed the solution. However, the issue remains...Can somebody enlighten as how this was resolved..

       

      Author's profile photo Former Member
      Former Member

      Nice blog!! How can we handle dependency here? For example: I have three stored procs : A, B, C. I want to first schedule A and B should start only if A has successfully completed and the same case for C.

       

      Thank you,

      Nishith

      Author's profile photo vara prasad Puram
      vara prasad Puram

      Hi Juan,

      I have followed the same steps what ever u explained in the thread.But scheduled job is failing with the below error.

      InternalError: dberror(Connection.prepareCall): 258 - insufficient privilege: Not authorized (line 5 position 1 in /TEST_REFRESH_TABLE/TEST_RUN.xsjs)

      I have the following roles assigned to me.

      sap.hana.xs.admin.roles::JobAdministrator

      sap.hana.xs.admin.roles::HTTPDestAdministrator

      But still I am getting error.Could you please provide me,what exactly the issue is and how to fix the this issue.

      Regards,

      VaraPrasad.

      Author's profile photo Aurel Havetta
      Aurel Havetta

      To have the job running you needed to add a new section "scheduler" into xsengine.ini with property "enabled" = true.

      And don't forget to restart the xsengine service !

      Author's profile photo Former Member
      Former Member

      Hi

      Is the Xcron job time is only in UTC / GMT time. If I define the a job like this, my system runs it at 10.pm PST. Is there any setting that we can change to define the jobs in PST.

      “xscron”: “2016 * * mon,tues,wed,thu 6 0 0” .

       

      Thanks

      Author's profile photo anil kumar
      anil kumar

      Hi ,

      This blog was very helpful to me, I have scheduled a job in XS engine and I am facing the below issue.

      GIS.xsjs:

      -------------

      function myTable( ) {
      var query = "{call SAPHANADB.system-local.private.demo/ZFL_PROCEDURE()}";
      var conn = $.db.getConnection();
      var pcall = conn.prepareCall(query);
      pcall.execute();
      pcall.close();
      conn.commit();
      conn.close();
      }

       

      what mistake am I doing in .xsjs file. can anyone please help me out.

       

      Thanks In Advance

      Author's profile photo Juan Esteban Mendez
      Juan Esteban Mendez

      Did you solve the Issue?

      If yes, how?

      Thanks

      Author's profile photo Adusumilli Gargi
      Adusumilli Gargi

      Adding the Back slash with the procedure name and double quotes worked.

       

      function My_table()

      {

      var query = "{CALL TEST_SLT.\"XS_Job_Learning::KAL_XSCRON1\"}";

      $.trace.debug(query);

      var conn = $.db.getConnection();

      var pcall = conn.prepareCall(query);

      pcall.execute();

      pcall.close();

      conn.commit();

      conn.close();

      }