SQL Anywhere 10 does not report database connection failures on the server when there is an underlying TCP/IP error. From looking through the docs I've found an auditing feature for successful and failed connection attempts. This falls short for what we need. Is it possible to enable a simple text based access log for the database similar to a HTTP server e.g. connection request - ip address - time, connection failure - ip address - time? It would also be good to have close connection requests. Thanks in advance, Shane asked 21 Jan '13, 07:35 Shane McEneaney Mark Culp |
Perhaps I'm misunderstanding, but I assume you're referring to a TCP/IP error between the client and the server (e.g. 'host not found', etc.?) If so, the server may not see all of these errors to be logged, since they are potentially happening at the network level. For these types of failures, you would be better to enable client-side logging (i.e. add "LogFile=clientlog.txt" to your connection string). Are you seeing these failures often / unexpectedly?
See the 'dbsrv10 -z -o output.txt' console log output switches. If you would like to create your own logging feature, you can likely do that via the 'Connect / Disconnect' system events and the answered 21 Jan '13, 08:45 Jeff Albion Note that for failing connection attempts due to network errors, the ConnectFailed event won't usually be able to log these ... simply as the server won't notice such an attempt at all (as Jeff and Breck have stated, too)...
(21 Jan '13, 09:17)
Volker Barth
Requiring extra logging or an event is overkill, IMHO. It would be nice if the client posted the OS-level/TCP-level error code plus a human-readable description.
(22 Jan '13, 10:04)
RussC_FromSAP
|
Thanks for taking the time to respond. When I mentioned TCP/IP error it was in the context of an active connection to the database that suffers from a network outage. Our application using connection pooling which involves the re-use of the same connection many times over a prolonged period. If the network goes down between our application host and database host briefly I would like to see an appropriate error in the database logs. Since this is an intermittent problem in our production environment it is not suitable for use to run the database in diagnostic mode using the -z server option. I understand that this has a major affect on performance. Unfortunately the connection events only show connection attempts rathen than the health of existing connections. answered 22 Jan '13, 10:41 Shane McEneaney Just to understand: If a living connection goes down, wouldn't it be easier to log that on the client side? I don't know which API you are using. AFAIK, v11 has introduced a ODBC feature that might be of use in case you are using ODBC - from the "What's new / Programming interfaces" doc page:
(22 Jan '13, 11:05)
Volker Barth
1
I believe you really mean "application logs" here, not database server logs. Again, the failure to contact the server over TCP/IP from the client needs to be logged on the client side - the database server can't log a connection failure that it can't see. Beyond the "LOG=" client log connection string option that I mentioned previously, depending on which API you're using (as Volker indicated), you can usually 'catch' some kind of exception / log an error upon a failed connection attempt from the client side in the API layer, and then "log" it somewhere in your application. e.g. ADO.NET C# code: SAConnection conn = new SAConnection( "Data Source=SQL Anywhere 12 Demo;UID=DBA;PWD=sql" ); e.g JDBC code: try { Connection conn = DriverManager.getConnection( "jdbc:sqlanywhere:uid=DBA;pwd=sql" ); ... } catch (SQLException sqe){ System.err.println("Error: " + sqe.toString() + ", sqlstate = " + sqe.getSQLState()); // do more application log error logging here }
(22 Jan '13, 12:43)
Jeff Albion
|
The CREATE EVENT statement offers some functionality with the Connect, ConnectFailed and Disconnect event types, but it probably falls short of your needs as well. ODBC tracing and the dbsrv10 -z option are probably too extreme.