( this was originally a question "Which database for CONN sa_server_messages()?" . . . now it's a feature request :) As far as I can tell, the sa_server_messages().msg_database column is empty when msg_category = 'CONN'. This means if you have multiple shared memory connections to each of several databases running on one instance of dbsrv17, and you get a "Disconnected" CONN message returned by sa_server_messages(), there is no obvious way to determine which database is the culprit. How do I determine which database is involved? ...after all, each SQL Anywhere connection only exists within the context of a single database. Here is a snippet showing 2 sets of "Disconnected" CONN messages from a total of 1000 (10 connections to each of 100 databases): msg_id, msg_text, msg_time,msg_severity,msg_category,msg_database 4429, Disconnected SharedMemory client's AppInfo: IP=192.168.2.10;HOST=XPS; OSUSER=Breck;OS='Windows 8 Build 9200 '; EXE=C:\\PROJECTS\\BENCHMARK\\benchmark_SA17\\rrloadtest.exe;PID=0x2104; THREAD=0x4084;VERSION=17.0.9.4882;API=ODBC;TIMEZONEADJUSTMENT=-240, '2019-04-19 16:21:31.665','INFO','CONN','' 4428, Disconnecting shared memory client, process id not found, '2019-04-19 16:21:31.665','INFO','CONN','' 4427, Disconnected SharedMemory client's AppInfo: IP=192.168.2.10;HOST=XPS; OSUSER=Breck;OS='Windows 8 Build 9200 '; EXE=C:\\PROJECTS\\BENCHMARK\\benchmark_SA17\\rrloadtest.exe;PID=0x2104; THREAD=0x44c0;VERSION=17.0.9.4882;API=ODBC;TIMEZONEADJUSTMENT=-240, '2019-04-19 16:21:31.665','INFO','CONN','' 4426, Disconnecting shared memory client, process id not found, '2019-04-19 16:21:31.665','INFO','CONN','' |
Hm, I share the experience that for CONN entries, the msg_database value is empty, which seems unexpected. – A wild guess: Apparently those disconnect entries come from the database server dropping orphaned connections so these connections may be in a state no longer attributed to a particular database?
Of course you could add DISCONNECT events to each database to find out which of them the connection belonged to...
> in a state no longer attributed to a particular database
That makes sense... except for the fact the message text itself contains the CONNECTION_PROPERTY ( 'AppInfo' ) string, so presumably CONNECTION_PROPERTY ( 'DBNumber' ) is also available.
DBNumber would make me happy, but a DISCONNECT event is not available... too bad, since it supports EVENT_PARAMETER ( 'ConnectionID' ).