Here is the scenario: A SQL Anywhere event makes a connection to a remote server. An EXECUTE IMMEDIATE FORWARD TO statement establishes the actual remote server connection.

Another event detects when the first event has not received control back from the FORWARD TO statement, and this second event runs an EXECUTE IMMEDIATE DROP CONNECTION on the first event's local connection number to stop the waiting.

The DROP CONNECTION statement runs quickly, but it seems the other event's local connection is not actually dropped until after the FORWARD TO returns control. Repeated DROP CONNECTION statements for the same connection number also run quickly... they don't fail, but they don't have any better luck than the first.

In some tests, multiple "the check is in the mail" messages appear in the database console ...

I. 08/24 11:51:32. User "DBA" dropped event connection 1000000033 ("attempt_connection")
I. 08/24 11:51:33. User "DBA" dropped event connection 1000000033 ("attempt_connection")
I. 08/24 11:51:35. User "DBA" dropped event connection 1000000033 ("attempt_connection")

... for those of you who haven't had your morning coffee yet, it is logically impossible to drop the same connection number more than once :)

Is that expected behavior? ...I suspect it is, just wanted to be sure.

What else (if anything) blocks DROP CONNECTION?

Some insight into the inner workings of DROP CONNECTION would be appreciated... there's more to it than meets the eye :)

asked 24 Aug '14, 13:42

Breck%20Carter's gravatar image

Breck Carter
accept rate: 20%

edited 24 Aug '14, 13:44

Another event detects when the first event has not received control back from the FORWARD TO statement, ...

Can you tell how the second event does know about the state of the first one - does it interpret some particular values from sa_connInfo() or the like?

(25 Aug '14, 03:44) Volker Barth

Does it behave differently when you drop the remote connection first (via v16's new DROP REMOTE CONNECTION TO ... CLOSE <connection_id> statement)?

(Or via ALTER SERVER ... CONNECTION CLOSE <connection_id> for older versions?)

(25 Aug '14, 05:41) Volker Barth

As you surmise correctly, the dropping of a connection will require all remote connections to be dropped as well. And there could be multiple. Heavy use of multiple remote servers and external envirnements could cause some overhead in the clean up.

That is in addition to any local context information related to that connection. (same also goes for each of the remote connections local to their respective db and servers). So global temp table entries, non-transactional objects, local temp tables, temporary procedures, ... etcetc ... might add up to some overhead independant of the remote disconnects.

Is there any obvious contributing factors that remote disconnects might take longer in your enviroment?

(26 Aug '14, 10:11) Nick Elson S...

Have same symptom from time to time with a remote connection to as Oracle DB. I have tried to drop the event connection, drop remote connection no success. Did somebody find a solution without restarting the server ?

(24 Sep '15, 08:01) Thomas Dueme...
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 24 Aug '14, 13:42

question was seen: 2,169 times

last updated: 24 Sep '15, 08:01