Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Cancel Vertica statement from JDBC

hello,

i would like to have the ability to cancel long running statements upon demand.

i managed to kill statements through vsql cli with the following query:

select INTERRUPT_STATEMENT('session_id', stmt_number);

(according to https://community.dev.hpe.com/t5/Vertica-Forum/How-to-end-an-endlessly-running-query-in-Vertica/td-p/207229)

 

now, i would like to do it through JDBC. it seems that preparedStatement.cancel() does not kill the statement. in addition, connection.close() or connection.abort(executorService) do not work either.

 

my next thought is to run the INTERRUPT_STATEMENT from JDBC. my questions are:

1. is there a built in way to kill statements from JDBC?

2. how can i get session id and statement id from JDBC?

 

thanks,

Asher

Comments

  • Hi ,

     

    1. is there a built in way to kill statements from JDBC? 

    <Eli> Cancel should do the job , however you mention its not working .

     

    2. how can i get session id and statement id from JDBC?

    <Eli> No native API , but you can do something like this for specific running query ->

    select transaction_id,statement_id from query_requests where request like '%Part Of Your query Text that you want to kill %' and is_executing is true 

     

     

    I hope you will find it useful

     

    Thanks 

  • Hi asher_1, 

     

    You are right in finding out that the current JDBC driver does not support canceling queries through .cancel(). 

     

    I think the best alternative is to run the interrupt_statement request through JDBC. To get the required IDs, you can run 

     

    SELECT session_id, transaction_id, statement_id FROM current_session;

    in that same JDBC connction that will execute your long-running query. 

    I found that the next PreparedStatement you create using that same Connection object will have a statement_id of 3 higher than what is returned initially. The session_id will stay the same. 

     

    If you application is unable to keep track of session_ids and statement_ids at execution-time of the queries, you can run a separate command to retrieve the IDs. 

    SELECT session_id, statement_id from sessions where current_statement='...'
  • Thank you all for your help.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.