SQL Server connection pooling (odbc)

[Steve Pringle]

Has anyone implemented connection pooling for sql server connections?

I’m not quite sure how that would work in the engine, as the tcl pooling proc would need to be running 24×7 as a seperate thread, or?

In lieu of a connection pool it seems a persistent odbc connection (per thread) would be a good thing.

We have several tcl procs on threads that currently open and close db connections many times a day, when each thread could be reusing the same connection, one connection per thread.  For that to work you could have your dbopen(hdbc) proc have a db connection handle (SQL_HANDLE_DBC) passed in and check to see if the handle is valid and open.  If so just return w/o opening another connection.

Our dbas would rather we kept a connection open rather than opening a closing a connection many times a day.  More efficient to keep the connection open.

For closing a handle you could do that on the shutdown mode of the upoc using odbc?

Just throwing this out here for thoughts on the issue.

thanks,

Steve

Topic

[Elisha Gould]

I’ve just been looking at the same setup on 5.8

For the present Ive set up a site that is multi-threaded.

I am using multiple upoc threads that each have a connection to the database, and a routing thread that currently routes using the modulus of an incrementing number.

The routing thread may need to be updated to check the status of the upoc threads at a later stage, to skip the thread if it is still in use.

The upoc threads have the database connection in the TPS Outbound section of the thread and the UPOC only does a CONTINUE, because for our case we are performing a select on the database and sending a reply with the data.

The reason for this is that the Write UPOC does not allow modification of reply data.

The reply is done using the Outbound Send OK Proc which creates a reply message swaps the source and destination, and SEND’s the reply and KILLs the original message.

I currently have the connect done when the first query message comes in, but keep the connection open until either the shutdown or the database goes down. In the latter case a reattempt to connect is done on the next query message.

I previously tried putting the connection in the startup code, however the site sometimes didnt come up because the thread occationally timed out while starting up due to the connection taking too long.

If you do not require a reply with data from the database, you should only need the UPOC. If you need a reply you will need the additional code I mentioned.

[Author]

Replies