SA12 : drop connection problem

[former_SQLA_member1694890]

Used SA 12 (12.0.1 EBF 3311 on Linux; EBF 3324 on Windows). We have seen lock tables (using sa_locks() stored procedure). Than we try to drop some connections using DROP CONNECTION 'CONNID' command or using 'Disconnect' option in Sybase Central Connections folder. In both case it seems that connection is dropped, but when we check again connections exist and 'Last Request Type' is Fetch. Moreover we have restart application (java server based on JBOSS), connection still appears in connection list and in sa_locks() result. In Sybase Central 'Overview' folder message 'The number of requests waiting to be fullfilled: 6' appears. We will not stop/kill database server, want understand what is caused to this problem. Size of database is around 25G, running on Linux machine. Query/reports is running on tables having 173M and 63M records. Please help, explain drop connection problem

[ian_mchardy]

What information does sa_conn_info give you about the connection that has the locks?

From the sa_conn_info information, can you determine where the connection is coming from? Is is from your application? What API is it using (ODBC/DBLIB/SQL Anywhere JDBC/JConnect/etc)?

After you drop the connection does the same connection number still appear in sa_conn_info? What about a minute or two after the drop? Does the same connection still appear in the sa_conn_info list a couple minutes after the application that creates the connection is restarted?

[former_SQLA_member1694890]

We are using JDBC connection (Our server application based on JBOSS java server). After drop connection in sa_conn_info() result appears this dropped connection and related children internal connection. May be these internal connections make problem to close parental connection. It is something new in SA12 that connection with request type 'Fetch' make many internal connections?!

[ian_mchardy]

Are you saying after the drop connection, the connection no longer appears in sa_conn_info() or that the connection does still appear in sa_conn_info()? In SA 10 and up, the server may use a parallel plan to execute a query, in which case there will be multiple internal connections with connection_property( 'Name' ) of 'INT:Exchange'.

FYI, you have only answered my question about the API and the application. You haven't answered my other questions in my original comment.

[former_SQLA_member1694890]

After drop connection and waitind 1-2 minutes we can see this dropped connection in sa_conn_info() result,and as mention in my previous comment this connection have children connections, appears in result of sa_conn_info() too

[jeff_albion]

There is a known issue (CR #702733 - 11.0.1.2786, 12.0.1.3713) which allows connections to be 'undroppable' in rare circumstances (usually involving a parallelized query that makes use of a HashFilter). Setting the connection option 'MAX_QUERY_TASKS' to '1' or restarting the database server is a workaround for the issue.

For more information about this issue, see my response to this question here: http://sqlanywhere-forum.sap.com/questions/6164/connections-that-cannot-be-dropped/6197

[Former Member]

I would just like to add we are running SQL Anywhere 12.0.1.3759 and are still experiencing the same problem with connections that cannot be dropped. We've had a Sybase trouble ticket opened since April 2012 on the matter. If I get a solution I will post and let people know.