cancel
Showing results for 
Search instead for 
Did you mean: 

How do I get the spid of Sybase when using JDBC

Former Member
0 Kudos

Hi All,

I'm developing a Search Engine to search on large data set, I support a WEB GUI for user to define search criteria and then click on Search button to execute. Because there is timed out value for the GUI (default 15 minutes), if the search engine does not response after the timed out, the call from WEB UI will be aborted. But the query still continue in background, that causes the CPU spike to 100%. There is only way to terminate the query by using sp_who procedure and kill command, but I have to do it manually. Is there a way to terminate corresponding spid by programming ? Is there Sybase API  to provide the spid of JDBC connection, so that I can terminate it when timed out hits.  

Thanks

Khoa Tran

Accepted Solutions (1)

Accepted Solutions (1)

dawn_kim
Contributor
0 Kudos

Hi,

In the application why not do a query timeout? You will have to catch the exception and move the application forward. setQueryTimeout(seconds)

Is the stored procedure on the ASE?
Is the stored procedure dyanamic and made from the application?
Why the stored procedure on the ASE is still taking so long would be something to look at.
Maybe the dba on the ASE should do some monitoring on the stored procedure and tune it if the procedure is located on the ASE.

Thanks,
Dawn Kim

former_member89972
Active Contributor
0 Kudos

Dawn y

Your suggestion of query timeout is excellent.

In the original post I do not see any mention of stored procedure on server side !

So it seems to be a case of execution of dynamic SQL getting out of control !

Khoa Tran

There is a global variable @@spid  to get spid for each connection.  You need to get it before you start your big query execution. Use it with caution for killing the spid later if needed. 

Please do not use sp_who or select from master..sysprocesses because these can aggravate the situation if CPU usage is already high.

HTH


Avinash

Former Member
0 Kudos

Hi Dawn Kim,

Your suggestion is about using setQueryTimeout of JDBC is correct, I have set same timed out for JDBC call, when the timed out hit. I checked the spid status via Processes screen of Sybase Central and saw the value of command column has changed from OPEN_CURSOR to AWAITING COMMAND, and CPU did not spike 100% any more.

In my thought, we set the timed out for JDBC call and then it is applied at transaction level, when the transaction is timed out, query will also go away too. We don't need to terminate the spid as my first thought, because it may cause some potential issue. Please correct me If I am wrong

I'm really appreciate for your help.

Thanks

Khoa Tran

Former Member
0 Kudos

Thanks Avinash,

You are correct about the dynamic SQL, basically our product support a WEB UI that allow user to define the search criteria and then use it for querying database. With more than 10 million records, the query seem to run over and over although timed out has already occurred on WEB UI. That is the reason why I need a way to stop the query when timed out is hit.

As Dawn's suggestion has solved my problem by using setQueryTimeOut function of JDBC without killing the spid. And I think that is safe way.

Thanks for so much for your advice about sp_who/master...sysprocessess . I will pay attention on them in the future. 

Thanks

Khoa Tran

former_member89972
Active Contributor
0 Kudos

Khoa Tran

Good that you found a solution based on Dawn' suggestion !

If the spid is not terminated after the query time out :

The only down side I see if a connection is still kept open --- is holding up resources and tempdb objects created if any.  These will not be removed until spid connection is gone.

HTH

Avinash

dawn_kim
Contributor
0 Kudos

Hi,

You are correct. When you hit a query timeout a cancel is sent to the backend. So if you keep an eye on that spid  in the ASE you would see it go away. So you don't have to kill a spid.

I know this is a big database, but a query should not take 15 minutes. There might be some tuning or indexes that need to be added to the ASE. It could be the query that the application is sending maybe send the query differently. Anyways I suggest you all watch this query and run some sysmons against this query and see if you can get it so it doesn't take that long.

Thanks,
Dawn Kim

Answers (0)