Informix .Net Provider Connection Pool Issue

I was wondering if any of you can help me out here. I'm developing an application in Visual Studio 2005 using the .Net Framework 2.0 connecting to an Informix 9.40C2. I'm using version 3.50.TC1DE of the Client SDK drivers and I'm having an issue with the database connection pool. I'm trying to track down excess connections being created against the database and it seems that we've narrowed it down to a failure in the .Net drivers to clear up connections in the connection pool. To that end, I wrote a quickie test application that uses just the basics: an IfxConnection, IfxDataAdapter and IfxCommand object to fill a datatable. I have the Connection Lifetime argument in the connection string set to 15 (which as far as I understand the documentation, means clean up connections after 15 seconds). Once I open the connection, fill my data adapter and close the connection, the connection doesn't get closed on the server. I'm querying the syssessions table where tty = my machine name to get my database connection count. Has anyone here experienced this? Is there anything I can do to clean up these connections? We have a user base of several hundred people and based on how our applications are executed often have 8-13 connections per user that aren't getting cleaned up. We don't have enough of licensing to support 8-13 connections for 150+ users. I've tried to disable pooling by setting the Pooling = False option in the connect string but based on the quantity of database queries going on, performance is awful. Thanks in advance for any help you can provide. Chris

Hi there, do you have a small reproduction that I could test to see this issue? I have tested a small .NET app using 3.50.TC2 which opens a connection , fills a dataTable from an Informix dataAdapter and then closes the connection, I looped this for several minutes and using "onstat -g ses" only see 1 active session from the .NET application. If you send me a reproduction I can test this also. I am using an 11.50 backend however. thanks Brian Cahill Software Engineer

This should work. Anyways, did you try IBM data server .NET driver which works well with Informix and all the .NET programming expectations ? But before you do your server should be Cheetah2 ideally. Thanks and Regards, Gaurav "CHRISTOPHER VOVERIS" <christopher.j.vo To veris@ssa.gov> ids@iiug.org Sent by: cc ids-bounces@iiug. org Subject Informix .Net Provider Connection Pool Issue [13053] 12/08/2008 01:09 Please respond to ids@iiug.org I was wondering if any of you can help me out here. I'm developing an application in Visual Studio 2005 using the .Net Framework 2.0 connecting to an Informix 9.40C2. I'm using version 3.50.TC1DE of the Client SDK drivers and I'm having an issue with the database connection pool. I'm trying to track down excess connections being created against the database and it seems that we've narrowed it down to a failure in the .Net drivers to clear up connections in the connection pool. To that end, I wrote a quickie test application that uses just the basics: an IfxConnection, IfxDataAdapter and IfxCommand object to fill a datatable. I have the Connection Lifetime argument in the connection string set to 15 (which as far as I understand the documentation, means clean up connections after 15 seconds). Once I open the connection, fill my data adapter and close the connection, the connection doesn't get closed on the server. I'm querying the syssessions table where tty = my machine name to get my database connection count. Has anyone here experienced this? Is there anything I can do to clean up these connections? We have a user base of several hundred people and based on how our applications are executed often have 8-13 connections per user that aren't getting cleaned up. We don't have enough of licensing to support 8-13 connections for 150+ users. I've tried to disable pooling by setting the Pooling = False option in the connect string but based on the quantity of database queries going on, performance is awful. Thanks in advance for any help you can provide. Chris ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Could you post your connection string? You are using fairly latest version of CSDK and there is no known outstanding issue around connection pooling with respect to your problem. On the other hand, do you have by any chance following registry key created? If so, this will cause connections to wait for this long (based on the value its set) before it actually closes the connection at server end. HKEY_LOCAL_MACHINE\\\\SYSTEM\\\\CurrentControlSet\\\\Services\\\\Tcpip\\\\Parameters (There will be TcpTimedWaitDelay value pair) HKEY_LOCAL_MACHINE\\\\SYSTEM\\\\CurrentControlSet\\\\Services\\\\Tcpip6\\\\Parameters (There will be TcpTimedWaitDelay value pair) -Shesh "CHRISTOPHER VOVERIS" <christopher.j.voveris@ssa.gov> Sent by: ids-bounces@iiug.org 12/08/2008 01:09 Please respond to ids@iiug.org To ids@iiug.org cc Subject Informix .Net Provider Connection Pool Issue [13053] I was wondering if any of you can help me out here. I'm developing an application in Visual Studio 2005 using the .Net Framework 2.0 connecting to an Informix 9.40C2. I'm using version 3.50.TC1DE of the Client SDK drivers and I'm having an issue with the database connection pool. I'm trying to track down excess connections being created against the database and it seems that we've narrowed it down to a failure in the .Net drivers to clear up connections in the connection pool. To that end, I wrote a quickie test application that uses just the basics: an IfxConnection, IfxDataAdapter and IfxCommand object to fill a datatable. I have the Connection Lifetime argument in the connection string set to 15 (which as far as I understand the documentation, means clean up connections after 15 seconds). Once I open the connection, fill my data adapter and close the connection, the connection doesn't get closed on the server. I'm querying the syssessions table where tty = my machine name to get my database connection count. Has anyone here experienced this? Is there anything I can do to clean up these connections? We have a user base of several hundred people and based on how our applications are executed often have 8-13 connections per user that aren't getting cleaned up. We don't have enough of licensing to support 8-13 connections for 150+ users. I've tried to disable pooling by setting the Pooling = False option in the connect string but based on the quantity of database queries going on, performance is awful. Thanks in advance for any help you can provide. Chris ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

While I don't know what I am talking about here (nothing unusual about that)... We have also been challenged by this issue. We found that you need to explicitly close the connection from the application. The .net people here were a bit miffed about that saying that the library was supposed to be managed and should implicitly close the connection through the garbage collection.... -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Gaurav Saxena3 Sent: 12 August 2008 06:00 AM To: ids@iiug.org Subject: Re: Informix .Net Provider Connection Pool Issue [13055] This should work. Anyways, did you try IBM data server .NET driver which works well with Informix and all the .NET programming expectations ? But before you do your server should be Cheetah2 ideally. Thanks and Regards, Gaurav "CHRISTOPHER VOVERIS" <christopher.j.vo To veris@ssa.gov> ids@iiug.org Sent by: cc ids-bounces@iiug. org Subject Informix .Net Provider Connection Pool Issue [13053] 12/08/2008 01:09 Please respond to ids@iiug.org I was wondering if any of you can help me out here. I'm developing an application in Visual Studio 2005 using the .Net Framework 2.0 connecting to an Informix 9.40C2. I'm using version 3.50.TC1DE of the Client SDK drivers and I'm having an issue with the database connection pool. I'm trying to track down excess connections being created against the database and it seems that we've narrowed it down to a failure in the .Net drivers to clear up connections in the connection pool. To that end, I wrote a quickie test application that uses just the basics: an IfxConnection, IfxDataAdapter and IfxCommand object to fill a datatable. I have the Connection Lifetime argument in the connection string set to 15 (which as far as I understand the documentation, means clean up connections after 15 seconds). Once I open the connection, fill my data adapter and close the connection, the connection doesn't get closed on the server. I'm querying the syssessions table where tty = my machine name to get my database connection count. Has anyone here experienced this? Is there anything I can do to clean up these connections? We have a user base of several hundred people and based on how our applications are executed often have 8-13 connections per user that aren't getting cleaned up. We don't have enough of licensing to support 8-13 connections for 150+ users. I've tried to disable pooling by setting the Pooling = False option in the connect string but based on the quantity of database queries going on, performance is awful. Thanks in advance for any help you can provide. Chris **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

As far as I understand, unless you explicitly call Close() from your application you are not putting the connection back into the pool. When application calls Close(), provider will take care of putting this into the available pool (if pool is enabled) instead of going ahead and closing the physical connection to the server. Now talking about garbage collection, that means we are destroying the objects itself hence no question of getting this into the pool !! No wonder why you are still seeing number of connections opened, it will wait for garbage collector (which is random!). I think this is the behavior across all the ADO.NET providers. However I can't speak 100% for other providers but IMO this is they way connection pooling works in principle !! -Shesh "Mark Tyrer" <mark.tyrer@rtt.co.za> Sent by: ids-bounces@iiug.org 12/08/2008 12:56 Please respond to ids@iiug.org To ids@iiug.org cc Subject RE: Informix .Net Provider Connection Pool Issue [13060] While I don't know what I am talking about here (nothing unusual about that)... We have also been challenged by this issue. We found that you need to explicitly close the connection from the application. The .net people here were a bit miffed about that saying that the library was supposed to be managed and should implicitly close the connection through the garbage collection.... -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Gaurav Saxena3 Sent: 12 August 2008 06:00 AM To: ids@iiug.org Subject: Re: Informix .Net Provider Connection Pool Issue [13055] This should work. Anyways, did you try IBM data server .NET driver which works well with Informix and all the .NET programming expectations ? But before you do your server should be Cheetah2 ideally. Thanks and Regards, Gaurav "CHRISTOPHER VOVERIS" <christopher.j.vo To veris@ssa.gov> ids@iiug.org Sent by: cc ids-bounces@iig. org Subject Informix .Net Provider Connection Pool Issue [13053] 12/08/2008 01:09 Please respond to ids@iiug.org I was wondering if any of you can help me out here. I'm developing an application in Visual Studio 2005 using the .Net Framework 2.0 connecting to an Informix 9.40C2. I'm using version 3.50.TC1DE of the Client SDK drivers and I'm having an issue with the database connection pool. I'm trying to track down excess connections being created against the database and it seems that we've narrowed it down to a failure in the .Net drivers to clear up connections in the connection pool. To that end, I wrote a quickie test application that uses just the basics: an IfxConnection, IfxDataAdapter and IfxCommand object to fill a datatable. I have the Connection Lifetime argument in the connection string set to 15 (which as far as I understand the documentation, means clean up connections after 15 seconds). Once I open the connection, fill my data adapter and close the connection, the connection doesn't get closed on the server. I'm querying the syssessions table where tty = my machine name to get my database connection count. Has anyone here experienced this? Is there anything I can do to clean up these connections? We have a user base of several hundred people and based on how our applications are executed often have 8-13 connections per user that aren't getting cleaned up. We don't have enough of licensing to support 8-13 connections for 150+ users. I've tried to disable pooling by setting the Pooling = False option in the connect string but based on the quantity of database queries going on, performance is awful. Thanks in advance for any help you can provide. Chris **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

My connection string is: Host=x; Service=onlineDStli; Server=x; User ID=x; password=x; Database=x; Connection Lifetime = 15 Substitute your information for the X's. Thanks! Chris

I'm using the latest .Net provider. I believe we have plans to upgrade to Cheetah - however I'm not sure that this connection pool issue is server related.

Here's the code in my sample project. This code is in a button click event. I'm closing the connection and disposing it. Once I execute the following code, I query the syssessions table where tty = my machine name to get my count. I still get an active connection that doesn't get cleaned up after 15 seconds (connection lifetime argument). Sometimes the connection goes away after 3-4 minutes - sometimes it never goes away. Dim cn As New IfxConnection cn.ConnectionString = "Host=x; Service=onlineDStli; Server=x; User ID=x; password=x; Database=x; Connection Lifetime = 15;" cn.Open() Dim strsql = "SELECT * from workload" Dim da As New IfxDataAdapter da.SelectCommand = New IfxCommand(strsql, cn) Dim dst As New DataSet da.Fill(dst) cn.Close() cn.Dispose()

Defect (idsdb00164302 ) has been logged to investigate this problem further. For more information/update on this defect please contact your IBM Informix Tech Support. -Shesh "CHRISTOPHER VOVERIS" <christopher.j.voveris@ssa.gov> Sent by: ids-bounces@iiug.org 12/08/2008 19:07 Please respond to ids@iiug.org To ids@iiug.org cc Subject Re: RE: Informix .Net Provider Connection Pool.... [13074] Here's the code in my sample project. This code is in a button click event. I'm closing the connection and disposing it. Once I execute the following code, I query the syssessions table where tty = my machine name to get my count. I still get an active connection that doesn't get cleaned up after 15 seconds (connection lifetime argument). Sometimes the connection goes away after 3-4 minutes - sometimes it never goes away. Dim cn As New IfxConnection cn.ConnectionString = "Host=x; Service=onlineDStli; Server=x; User ID=x; password=x; Database=x; Connection Lifetime = 15;" cn.Open() Dim strsql = "SELECT * from workload" Dim da As New IfxDataAdapter da.SelectCommand = New IfxCommand(strsql, cn) Dim dst As New DataSet da.Fill(dst) cn.Close() cn.Dispose() ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.