How to kill programmatically a running stored procedure from XS code?

[Former Member]

Hello,

I'm calling a stored procedure from XS Java script code.

I want to kill programmatically the execution of this SP from the XS code while the SP still running.

(IMHO - This could be done by ALTER SYSTEM CANCEL SESSION '<session_id>')

1.    How could we know programmatically what is this SP session_id ?

2.    Is there an option getting this information when executing the call, store it and using it later on for the canceling?

Thanks, Elad

[lbreddemann · Accepted solution]

Hi Elad

To get the session/connection id for your *own* session you can always use the current_connection() function.

However, as you want to kill an other session (not your current one), you would need to figure out which session is the one you're after.

From the top of my head I see two options:

1. you set a session context variable e.g. a job name or something similar and look for the session with this specific session context variable set.

For this use SET 'variable_name' = 'variable_value' and the m_session_context view.

2. you encode identifying comments into your calling SQL code, e.g.

call  /* this is my job ID */ schema_name.proc_name();

Then you can check for sessions that execute exactly this piece of code in M_PREPARED_STATEMENTS.

All in all I'd rather say that this could and maybe should be a feature request to SAP HANA development: that you can specify the maximum wanted run time for a procedure call.

BTW: if you're after ending the CALL only and not so much after killing a session, you should look into the CANCEL REQUEST command instead.

- Lars

[Former Member · Accepted solution]

Hello Lars and Thanks,

Could you please elaborate regarding #1, where and what should I search in m_session_context view?

Thanks, I'll update with the progress.

Elad

[lbreddemann · Accepted solution]

Ok, what you could do in your SQL code is you can set session variables.

The variables and their values can be inspected in the m_session_context view.

So by creating a session variable in your code, this would be the variable you'd be looking for.

Anyhow: is there any specific reason why your procedure should run too long?

For lock waits e.g. there is a server wide timeout parameter available.

- Lars    

[Former Member · Accepted solution]

Hello Lars and Thanks,

Our product should enable a massive operations (such as huge load) to our users.
We are developing a process manager which will enable configure steps and compose them into processes.

Among our requirements, we have to enable a time-out for each such step; If the step will stuck it should be terminated after this time-out and its inner operations should be rolled back.

Our code is based on XS code which calls asynchronous SP's (Which represents the steps above).

I want to isolate the connection to the specific stack SP and cancel only this connection.

As per to the discussed syntax :

#1 : session context could be set from HANA studio (in design time) but I couldn’t find a way to combine it into stored procedure so be exposed in run time.
Is there such an option?

#2 If our XS code will "call /* this is my job ID */ " it will let me found the overall XS code but I want to cancel only the connection to the stacked SP

Your advices are very beneficial. I believe that we are very close to solution!
Thanks and Best Regards, Elad