Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

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%20McEneaney's gravatar image

Shane McEneaney
66339
accept rate: 0%

edited 15 Mar '13, 20:34

Mark%20Culp's gravatar image

Mark Culp
24.9k10141297

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.

(21 Jan '13, 08:21) Breck Carter

SQL Anywhere 10 does not report database connection failures on the server when there is an underlying TCP/IP error.

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?


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.

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 CONNECTION_PROPERTY('AppInfo'), CONNECTION_PROPERTY('CommLink'), and CONNECTION_PROPERTY('NodeAddr') connection properties.

permanent link

answered 21 Jan '13, 08:45

Jeff%20Albion's gravatar image

Jeff Albion
10.8k171175
accept rate: 25%

edited 21 Jan '13, 08:46

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.

permanent link

answered 22 Jan '13, 10:41

Shane%20McEneaney's gravatar image

Shane McEneaney
66339
accept rate: 0%

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:

SQL_ATTR_CONNECTION_DEAD promptly detects dead connection

Using ODBC's SQLGetConnectAttr call to get the SQL_ATTR_CONNECTION_DEAD attribute now gets the value SQL_CD_TRUE if the connection has been dropped even if no request has been made to the server since the connection was dropped. Determining if the connection has been dropped is done without making a request to the server, and the dropped connection is detected within a few seconds. The connection can be dropped for several reasons, for example, on an idle timeout. Before this change, SQL_ATTR_CONNECTION_DEAD only got the value SQL_CD_TRUE if the connection was disconnected or if ODBC driver made a request to the server (by calling SQLExecDirect for example) after the connection was dropped. See Getting connection attributes.

(22 Jan '13, 11:05) Volker Barth
1

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.

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" );

try { conn.Open(); ... } catch (SAException ex){ MessageBox.Show( ex.Errors[0].Message ); // do more application log error logging here }

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
Your answer
toggle preview

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "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:

×159
×119
×40

question asked: 21 Jan '13, 07:35

question was seen: 3,896 times

last updated: 15 Mar '13, 20:34