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

To report this post you need to login first.

8 Comments

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

  1. Pavitra Vobilisetty

    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?

    (0) 
      1. Pavitra Vobilisetty

        //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”

          }

          ]

        }

        (0) 
        1. Juan de la Cruz Arellano Royo 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

          (0) 
          1. Pavitra Vobilisetty

            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!

            (0) 
            1. Juan de la Cruz Arellano Royo 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

              (0) 
              1. Pavitra Vobilisetty

                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.

                (0) 
  2. Kapadia Nishith

    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

    (0) 

Leave a Reply