we have a Chinese version of this blog

   Assume that you will  receive a batch of file every other minutes, and then you need to import these files into SAP HANA. Traditionally ,  you have to write a script program to import these files and then set up a timing task to execute this program. And you may wonder if SAP HANA can support timing task ?


    Before SAP HANA SPS07 , this is not supported. After SAP HANA SPS07, this is simple, and very cool, as you will see, you can tell SAP HANA to execute some timing task autocratically, just SAP HANA, no other components like “crontab” of linux.


   In simple terms, SAP HANA’s timing task will depend on the XS Engine, you can use XS Engine to set up the timing task. There are two types of task you can use, they are SQLScript task and javascript task.

 

   Now I will show you with an example.


1  Set up the environment.

     first ,you need to configure SAP HANA in HANA Studio, like the following figure:

/wp-content/uploads/2014/05/readonly_444643.png


    In default situation, the repository section, the sqlscript_mode  is ‘default’ , here I recommend to change it to ‘UNSECURE’.  If you do not change this ,then  the task may not able to do the ‘insert’ or ‘update’ job.

/wp-content/uploads/2014/05/studioconfig_444668.png


    On the other hand, for the SCHEMA which you will handle, you should assign the related privilege. Or you may see the following error when activate the procedure.

/wp-content/uploads/2014/05/activate_444669.png


when this happens, you should assign the privilege to _SYS_REPO for the schema WEIYY. This is because when you commit to the repository, and then activate, it is the _SYS_REPO’s job to generate the related object. So ,_SYS_REPO, must have the permission.


/wp-content/uploads/2014/05/grant_444670.png


    at last, you also should configure the xsengine, like the following figure, in the xsengine.ini, add a section named scheduler, and add the enabled argument with the value true.

QQ截图20140306133924.png


2 Timing Task


   There are two types of Timing Task, it may be a javascript task or a SQLScript task. The schedule information is described in a xsjob file, as the example will show you ,we will  implement a javascript task and a SQLScript  task.


    First we create a XS Project in SAP HANA Studio, and then create a .xsapp file. And then create a jsjobtest.xsjs file , this is a “xsjs” file, inside it there is a function myjsjob, this function is the job we will do. Here we just insert a record to he table “TESTTBL”, and record the time information.


File: jsjobtest.xsjs


function myjsjob() {
    var sql = "INSERT INTO WEIYY.TESTTBL VALUES (NOW(), 'inserted from javascript job')";
    var conn = $.db.getConnection();
    var pstmt = conn.prepareStatement(sql);
    pstmt.execute();
    conn.commit();
    conn.close();
}

Then we need a xsjob file, to descibe when the job will be scheduled.

File: myjsjobdesc.jsjob

{

    “description”: “my first javascript job”,

    “action”: “weiyy.scheduletest:jsjobtest.xsjs::myjsjob“,

    “schedules”: [

    {

    “description”: “run every 5 seconds”,

    “xscron“: “* * * * * * 0:59/5”

    }

    ]

}


  description: the description of the task.

  action:the task of the file,and the entrance of the function.

   schedules: the schedule information of the task ,the  grammar is similar to the grammar of “crontab”, here we let the task run every 5 seconds.


  And now we add a SQLScript task, it also insert record to the “TESTTBL” ,but the information is a little different.


  File:sqljobtest.procedure


CREATE PROCEDURE sqljobtest ( )  
     LANGUAGE SQLSCRIPT 
     SQL SECURITY INVOKER 
     --DEFAULT SCHEMA <schema> 
AS 
BEGIN 
/*****************************  
     Write your procedure logic  
 *****************************/ 
insert into WEIYY.TESTTBL(T,INFO) VALUES(NOW(),'insertted from SQLScript job');
END;

Here we let it run every 10 seconds.

File:mysqljobdesc.xsjob

{

    “description”: “my first SQLScript job”,

    “action”: “weiyy.scheduletest::sqljobtest“,

    “schedules”: [

    {

    “description”: “run every 10 seconds”,

    “xscron“: “* * * * * * 0:59/10”

    }

    ]

}


[Note: the action part is a little different between js task and SQLScript task ]



3  Launch the timing task

   

  After add the timing task ,the next step is to launch the task. We need to use the “XS Administration Tool”, and you need to assign the role “sap.hana.xs.admin.roles::JobAdministrator” to XS Engine. For different HANA instance ,the web address is like this:


http://<WebServerHost>:80<SAPHANAinstance>/sap/hana/xs/admin/


   For our javascript task, set the “User” and “Locale” arguments and finally check the “Active”, then the task is activated.

/wp-content/uploads/2014/05/myjsjobdesc_444672.png


For the SQLScript task, do the same:

/wp-content/uploads/2014/05/mysqljobdesc_444673.png


then go back to SAP HANA Studio


/wp-content/uploads/2014/05/data_444674.png


you can see that both of the task has been executed. And the javascript task executed more than the SQLScript task.


   In the “_SYS_XS”.”JOBS” table you can see the task information.

    In the “_SYS_XS”.”JOB_LOG” you can see the log information.

/wp-content/uploads/2014/05/jobs_444675.png


/wp-content/uploads/2014/05/log_444676.png


Use the XS Engine javascript API ,you can also add or delete timing task dynamically. for more information ,please refer to

http://help.sap.com/hana/SAP_HANA_Developer_Guide_en.pdf


[SAP HANA version of the test cases used here is SAP HANA SPS7 Revision 70.00. ]







To report this post you need to login first.

13 Comments

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

  1. Marian Canciu

    Hi

    When launching the XS Administration Tool and trying to enter any user/password combination (the correct user with the correct password or any random user with a random password) the tool is not reacting and basically login is not possible.

    What could be the reason?

    Cheers

    Marian

    (0) 
      1. Marian Canciu

        Hi Bala

        Could you please be more specific? Which role should e assigned?

        The login with the SYSTEM user is also not possible and I assume it’s not a role issue.

        And as stated above an incorrect user/password combination will not issue a false password message. After clicking login nothing happens.

        Cheers

        Marian

        (0) 
        1. Whisly Wei Post author

          Hi, Marian, after click,  nothing happens ?  sounds strange, if you use chrome ,i think you can press F12 to trace  the HTTP response . In my environment , if the password is wrong , i will get a error message

          (0) 
          1. Marian Canciu

            That is correct. After clicking login nothing happens.

            The SQL SELECT * FROM _SYS_XS.RUNTIME_CONFIGURATION is throwing the following result:

            PACKAGE_ID;CONFIGURATION;CHANGED_BY;CHANGED_AT

            ;{“authentication”: [{“method”: “Form”},{“method”: “Basic”}]};_SYS_REPO;

            Does this ring a bell:

            1. /sap/hana/xs/formLogin/login.xscfunc
            2. Request Method:POST
            3. Status Code:500 Internal Server Error

            (0) 
            1. Whisly Wei Post author
              1. status code 500 that means something error happened . 
              2. if it is OK ,it should like this:
              3. Request URL:sap/hana/xs/formLogin/login.xscfunc
              4. Request Method:POST
              5. Status Code:200 OK
              (0) 
                1. Whisly Wei Post author

                  and in the repository, path : /sap/hana/xs/formLogin/   you can see the  implimention of ‘login.xscfunc’  and other related files. 

                  (0) 
              1. Whisly Wei Post author

                You may try restart HANA or check the xsengine trace file under the Diagnosis Files pages in HANA Studio, and you can  edit the Trace Configuration the see more information. And  in fact ,XS Administration Tool  is just an application based on XS Engine,  make sure your other XS Engine app can login  correctly .

                I am very interested in this situation,   In my environment , as soon as I installed HANA , I can logon without any setting.

                (0) 
    1. Whisly Wei Post author

      Hi, Marian, it seems that you can not input anything ?  are there any error messages?  or you can change another browser  like chrome to try ?

      Whisly

      (0) 

Leave a Reply