We are using Sybase 7. We have the connection timeout set to 24 hours. We are also using Powerdynamo which utilizes and ODBC connection pool. The timeout on the DB connections in this pool is set to 5 minutes. We are seeing connections from this pool not being released and not being used. Normally if a connection in this pools sits idle for 5 minutes it is disconnected and it goes away. The problem is that some connections remain open but are never used. We know this by looking at the info in the sa_conn_info procedure. We can see ODBC connections that have not been used for hours. I can only assume this is because the pool manager somehow still thinks they are being used. My question is - how can we tell what the last sql command was for a connection? Almost always the sa_conn_info procedure reports that the last statement was COMMIT on these connections. Is there anyway to correlate the last sql command (i.e. insert / update) with a connection? Could you look at logfiles (using dbtran) and find the last command using the connection ID? I did not see any way to associate a connection ID with the statements in the file.
Apologies if I'm referencing stuff that didn't exist for ASA7...
Either (1) add -zr to your dbsrv7 launch options, or (2) call sa_server_option('RememberLastStatement','YES')
Then you should be able to "select connection_property('LastStatement', ###)" on the connection id you are concerned about.
This should return the last SQL command executed on that connection.
answered 16 Sep '11, 17:40