continually increasing number of open connections

Hi,
I have an application that uses batch updates to insert data into the
database.
The sequence is basically:
Statement.prepareCall()
loopForever
{
GetDataFromSomewhere
CallableStatement.clearBatch()
CallableStatement.addBatch()
CallableStatement.addBatch()
...
CallableStatement.addBatch()
CallableStatement.executeBatch()
}
The application uses the same Statement object which is never closed.
However, the number of connections is constantly growing (netstat & lsof
shows 100,000 connections to the database after several hours and after that
the machine crashed...).
It appears that the driver creates a connection for every statement in the
batch (or at least for each batch). Should it work in this way? is there a
way to use the same connection for the entire batch?
Also, is there way to prevent this connections leak? I'd like to use the
same Statement object to eliminate the need for prepareCall() on each batch
in order to get improved performance.
Any help will be appreciated.
Thanks,
Noam
Noam,
In "selectMode=direct" the MS driver (and all other DataDirect based
drivers) create a "cloned" connection (i.e. a new physical connection)
for every new statement. That may be the source of your problem if you
are creating Statement objects inside a loop.
If you are absolutely sure you are not doing that and that there are no
other places in your code where this might happen, then I can only
recommend you try another (non-DataDirect) driver and see if the same
happens.
Alin.
|||I've tried using selectMode=cursor but it does not help. This is a single
thread that access the database so there are no other places that create new
statenet. Also, this started to happen when I decied to imrpove the code and
use batch mode.
In the mean time, I'm creating a new statement inside the loop (and closing
it at the end if the look) and it works OK.
Thanks,
Noam
|||"Noam Ambar" <NoamAmbar@.discussions.microsoft.com> schrieb im Newsbeitrag
news:37218689-6264-4B3C-B8D9-5EDC05385136@.microsoft.com...
> I've tried using selectMode=cursor but it does not help. This is a
single
> thread that access the database so there are no other places that create
new
> statenet. Also, this started to happen when I decied to imrpove the code
and
> use batch mode.
> In the mean time, I'm creating a new statement inside the loop (and
closing
> it at the end if the look) and it works OK.
Do you ever commit in between? Or is this autocommitted?
robert
|||It is true that the Microsoft SQL Server JDBC driver creates "cloned
connections" and that earlier versions of the DataDirect SQL Server JDBC
driver did as well. The current 3.4 DataDirect SQL Server JDBC driver does
not clone connections.
Sue Purkis
DataDirect Technologies
our current 3.4 SQL Server JDBC driver does not clone connections anymore
"Alin Sinpalean" <alin@.earthling.net> wrote in message
news:1109516553.438410.284260@.o13g2000cwo.googlegr oups.com...
> Noam,
> In "selectMode=direct" the MS driver (and all other DataDirect based
> drivers) create a "cloned" connection (i.e. a new physical connection)
> for every new statement. That may be the source of your problem if you
> are creating Statement objects inside a loop.
> If you are absolutely sure you are not doing that and that there are no
> other places in your code where this might happen, then I can only
> recommend you try another (non-DataDirect) driver and see if the same
> happens.
> Alin.
>
|||Sue Purkis wrote:
> It is true that the Microsoft SQL Server JDBC driver creates "cloned
> connections" and that earlier versions of the DataDirect SQL Server
JDBC
> driver did as well. The current 3.4 DataDirect SQL Server JDBC
driver does
> not clone connections.
Sue,
Thanks for the update; I didn't know about that. So does this mean that
"selectMethod=direct" now supports transactions (autoCommit == false)
too?
Alin,
The jTDS Project.
|||Alin,
Yes, it is true that selectMethod=direct now supports transactions with
the DataDirect 3.4 SQL Server driver.
Sue
DataDirect Technologies
"Alin Sinpalean" <alin@.earthling.net> wrote in message
news:1109930907.961426.268700@.g14g2000cwa.googlegr oups.com...
> Sue Purkis wrote:
> JDBC
> driver does
> Sue,
> Thanks for the update; I didn't know about that. So does this mean that
> "selectMethod=direct" now supports transactions (autoCommit == false)
> too?
> Alin,
> The jTDS Project.
>