Skip to Content

How to schedule a stored procedure created from SQL

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Apr 10 at 05:16 PM

    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"     }]      }
    

    Add comment
    10|10000 characters needed characters exceeded

    • 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. :D

  • Apr 11 at 05:59 AM

    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

    Add comment
    10|10000 characters needed characters exceeded