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.
We create a XS Project with a new package as follow:
In the next step we must to set the project name
We select the workspace:
Here we can create the XS JavaScript file, so we set the name of the file:
In order to schedule a XS Job we need to create a new file under out package:
Please, select XS Job Scheduler File:
Set the parent folder (previously created ZJUANDE_IBO) and set the XS JavaScript file name:
Now we have the following file structure:
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
This code enable to call 2 stores procedures per execution. Don’t put the character “;” at the end of the call procedure.
Now we need to access to XS Engine with the URL http://<XSengine-host><XS-port>/sap/hana/xs/admin/
Here we need to activate it:
If you want to confirm when the XS Job will run, click on View Logs:
And check the Planned Time:
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
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?
Hi Pavitra,
Could you attach a screenshot o paste code from xsjs file?
Regards
//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"
}
]
}
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
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!
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:
On my example I need to change
Up to
Regards
Sorry for the bad image. But I hope you get an idea about why my action has that particular package path.
Inside the TDC_JOB, I have created my TEST.xsjs and xsjob files.
Thanks.
Hi, I have the same problem of Pavitra!
Have you solve this problem?
If yes, how?
Thanks,
Silvia
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..
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
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.
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 !
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
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
Did you solve the Issue?
If yes, how?
Thanks
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();
}