How to log a connection from the server side

[former_member329524]

Hello, all

One of our clients wrote his own application, which connects to SQL Anywhere 16 db.

We have strong suspicion that his multi-threaded application at some points opens a large number of connections to the DB causing some of them to fail.

The connections are short-lived and no connection pool is used (do not ask me why, this is a separate issue).

Since we do not control the client application, I would like to log the connections from the server side.

Basically, I need to know how I could trigger an insert statement on any db connection, so I could know how many connections are being made at the same time.

Thank you

Arcady

[Former Member · Accepted solution]

Hi Arcady,

If you want to log connections from server side, made by client application, you could consider using Request Level Logging. Request Logging is useful to find out which connectionID is doing specific activity on database.

Here is a dcx link on Request Logging :

http://dcx.sap.com/index.html#sa160/en/dbusage/performance-s-5753386.html

Or have you considered to enable auditing? If you enable auditing from your database server, it will record

details about events in the database in transaction log.

Here is a link about auditing:

http://dcx.sap.com/index.html#sa160/en/dbadmin/auditing-option.html

and following is about sa_enable_auditing_type store procedure

http://dcx.sap.com/index.html#sa160/en/dbreference/sa-enable-auditing-type-system-procedure.html

[former_member329524 · Accepted solution]

Thank you, Hanbin

I was looking for something more specific.

Like making sure a certain procedure would launch on every connection.

I cannot really start request logging for a long period of time on a busy DB.

[Former Member · Accepted solution]

Hi Arcady,

So what you really want is to find out about activities on particular stored procedures?

Then, I think enabling procedure profiling would be another option and here is a link for it :

http://dcx.sap.com/index.html#sa160/en/dbusage/enabling-profiling-perform.html

However, Request Level Loggigng, will log everything including stored proceudre that has been fired off to the database.

Thank youm

[former_member329524 · Accepted solution]

No, Hanbin, you misunderstood

I am obviously not being clear enough.

There is a stored procedures called sa_post_login_procedure, which is supposed to be launched on every connection.

When I edit it to call an additional stored procedure (which is inserting a row into a specific table) nothing seems to happen.

I just want to launch a user-defined stored procedure on every connection.

[Former Member · Accepted solution]

All that sa_post_login_procedure does is to check whether userID is about to expire.

From what you described in your first reply,
if this is what you really want :

"Basically, I need to know how I could trigger an insert statement on any db connection, so I could know how many connections are being made at the same time"

Then you can use procedure profiling by first calling and enabling the procedure and

selecting on particular stored procedure:

i.e. select  * from sa_procedure_profile where object_type = 'p' and object_name = '<user-defined store procedure>';

Here is the link for all the properties that can be used for procedure profiling :

http://dcx.sap.com/index.html#sa160/en/dbreference/sa-procedure-profile-system-procedure.html

I hope I didnt misunderstand you this time.

[former_member329524 · Accepted solution]

Thanks, Hanbin

Turns out I used the wrong stored procedure.

When I used sp_login_environment everything started working.

Thanks for your help.

[chris_keating · Accepted solution]

Just to make sure that you understand that sp_login_environment should not be modified. Rather, you need to set the LOGIN_PROCEDURE option to a user defined procedure that implements your login logic and calls the sp_login_environment if the login attempt is not rejected. Since you are not planning to reject connections but simply log that information, you will simply need to call the sp_login_environment() before exiting your procedure.

[VolkerBarth]

A further approach would be to use an event of system type CONNECT for logging purposes. Note that events run on their own connection so they cannot "impersonate" the newly connected user but might be helpful to log the total number of current connections or some characteristics of the connection like the User or AppInfo information - available via the EVENT_PARAMETER system function.

You could use the corresponding "DISCONNECT" event to un-list the accoding user. (Or you could use the latter in conjunction with the suggested login procedure to update the row in your "current connections" table to mark the connection as closed, even if a connection is closed irregular.)