"Singleton" / Single instance of procedure - SAP HANA

[matma24]

Hello!

In one of my procedures in XSA I am doing some computing using MAP_MERGE operator.

Procedure itself is triggered by job created in job scheduler (every X minutes I'm just checking if there is a new content in the table that should be processed and based on that I'm either finishing or doing some computation). Since this job is quite long - there are times when table will get 10k new rows but I want to do it by 1k per procedure call (thus why I'm simply getting top 1000 records where status = not processed) - simply speaking, I want to ensure that there is only one execution of the procedure at the time.

The job (as of now) is scheduler do run every 5min and this is working fine, nevertheless I would like to change it to 2min (sometimes procedure execution is a bit longer that that) and I'm wondering if there is any way to ensure that this procedure and be ran only "once" at the time = if there is any other statement with state active that have CALL ProcName then I want to skip this schedule (do nothing) otherwise I want to execute that.

I'm thinking of doing that by checking in M_ACTIVE_STATEMENTS or M_SERVICE_THREADS where statement string/thread_detail consist of this call -> if yes then there is something running, if not then I can run it.

Is it a correct way to approach this or is there any better/cleaner solution?

In a simple words, I want to ensure that if the previous run of a procedure did not finish yet then the new one is kind of omitted by simple check inside procedure if logic should be executed or just skip to the end.

Best,

Mark

[matma24 · Accepted solution]

Resolved the issue with checking if the instance of a procedure is in M_ACTIVE_STATEMENTS which is basically the same solution as proposed by sakamoto.ko and the procedure was scheduled in XSA so I have access to it / can check history etc without any additional tables created by me.

Schedule proposed by Michael was also the approach I was thinking of but we are not on SPS06 yet so this Statement is not possible yet.

Nevertheless both of the answers are very helpful thus why i gave them both +1 and will accept this one as an answer simply because it's containing a short summary.

[Cocquerel]

An alternative could be that it is the procedure itself that schedules the next run at the end of the processing.
An advantage of this solution is that you could dynamically increase the run frequency depending on the number of entries in the back log.
With HANA SPS06 or HANA Cloud, you can schedule procedure run via SQL (see https://help.sap.com/viewer/c1d3f60099654ecfb3fe36ac93c121bb/2021_2_QRC/en-US/d7d43d818366460dae1328... )
Note that you have to take care carefully of SQL error handling with this solution to avoid missing scheduling next run in case of issue.

[SakamotoKo]

Hi,

How about to use the syetem view M_ACTIVE_POCEDURES?

M_ACTIVE_PROCEDURES - SAP Help Portal

If you need to check histories of running time of stored procedure, you might want to create a permanetary table folowing the note.
2534862 - HowTo: Capturing SQL statements and run time information triggered by a stored procedure -...

Regards.