How to schedule a stored procedure created from SQL

[mrwong05]

Hi,

I create my stored procedures from SQL console, rather than using Project Explorer.

Thomas Jung explains that it can be done with just 1 xsjob file.

All the doznes of tutorials for scheduling procedures use this code in xsjob file:

"action": "xsjob-tutorial.jobs:yahoo.xsjs::readStock",

But I dont want to use a repository .xsjs file. I want to use a Stored procedure in my schema i created with SQL

i tried to change the action to my Schema.StoreProcedureName but the Project Explorer won't let me activate it.

Any thoughts how to use a non-project explorer stored procedure in an xsjob? is there an easier way to schedule a stored procedure ? I am using SAP HANA 2.0 SPS02

Thanks,

Matt

[thomas_jung · Accepted solution]

Here is an example of a job for a stored procedure. However you notice its a design time procedure. I don't believe you can reference one via Schema - only repository/design time ones. You could always just create create a procedure wrapper for an existing Schema procedure.

{    "description": "My first SQLScript job",    "action": "sap.hana.democontent.epmNext.procedures::jobsCreateEntry",    "schedules": [{        "description": "Will run every 10 seconds",        "xscron": "* * * * * * 0:59/10"     }]      }

[mrwong05 · Accepted solution]

ok, i'll try a wrapper. im not familiar with designtime vs stored procedures. ^^ Thanks for the info. My alternative, which I am investigating is using a shell script + cron job utilizing hdbsql to run my stored procedure on a schedule. 😄

[mrwong05]

Hi,

I was able to do it via shell script using hdbsql + cron job

here is my cron statement for running the stored procedure every minute, output the log to a log file.

* * * * * /scripts/runstoredprocedure.sh >> /scripts/runstoredprocedure_cron.log 2>&1;

Here is my script (logged in a root).

#!/bin/bash
login="<YOUR HANA DB USER>"
pass="<YOUR HANA DB USER PASSWORD>"
query="'call <YOUR SCHEMA>.COUNT_ROWS_TO_NEW_TABLES();'" 
echo loggin into c01adm
su -l c01adm << EOF echo -e $query | hdbsql -u $login -p $pass 
EOF
echo "Todays date $(date)"

If you want to run as <SID>adm user, you can do that, and you dont need to "su -l c01adm" command or the << EOF to EOF markers. Make sure to edit the cron job of <SID>adm user and not root (login as <SID>adm user, command: crontab -e)

#!/bin/bash
login="<YOUR HANA DB USER>"
pass="<YOUR HANA DB USER PASSWORD>"
query="'call <YOUR SCHEMA>.COUNT_ROWS_TO_NEW_TABLES();'" 
echo -e $query | hdbsql -u $login -p $pass 
echo "Todays date $(date)"

Hope this helps,

Matt