Kinda urgent Connection error messages

Websphere application for nurses license renewal application that hits our SQL Server 2000.
This was the first error that we decided to stop and start the application:
Unable to load SQL Server Data Driver driver.
Unable to create database connection.
[Microsoft][SQLServer 2000 Driver for JDBC]Error establishing socket.
com.obs.data.DataObjectException: [Microsoft][SQLServer 2000 Driver for JDBC]Err or establishing socket.
This is the send stream of errors after we had thought we had solved the problem. This is kinda urgent because there are nurses out there waiting to get their licenses renewed and mad as heck:
[Microsoft][SQLServer 2000 Driver for JDBC]Connection reset by peer: Connection reset by peer
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Connection res et by peer: Connection reset by peer
at com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Unknown
Source
)
at com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown
Source)
at com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown
Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sReply(Unknown
Sour
ce)
at com.microsoft.jdbc.sqlserver.SQLServerImplStatemen t.getNextResultType
(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.commonTransi tionToState(Unknown
Source)
at com.microsoft.jdbc.base.BaseStatement.postImplExec ute(Unknown
Source)
at com.microsoft.jdbc.base.BaseStatement.commonExecut e(Unknown
Source)
at com.microsoft.jdbc.base.BaseStatement.executeUpdat eInternal(Unknown S
ource)
at com.microsoft.jdbc.base.BaseStatement.executeUpdat e(Unknown
Source)
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
Hi John. Check the DBMS error log. I believe the DBMS is in trouble. The driver is just
finding out that the socket connection it sues to the DBMS is dieing.
Joe Weinstein at BEA
jdnospam@.hevanet.com wrote:

> Websphere application for nurses license renewal application that hits our SQL Server 2000.
> This was the first error that we decided to stop and start the application:
> Unable to load SQL Server Data Driver driver.
> Unable to create database connection.
> [Microsoft][SQLServer 2000 Driver for JDBC]Error establishing socket.
> com.obs.data.DataObjectException: [Microsoft][SQLServer 2000 Driver for JDBC]Err or establishing socket.
>
> This is the send stream of errors after we had thought we had solved the problem. This is kinda urgent because there are nurses out there waiting to get their licenses renewed and mad as heck:
> [Microsoft][SQLServer 2000 Driver for JDBC]Connection reset by peer: Connection reset by peer
> java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Connection res et by peer: Connection reset by peer
> at com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Unknown
> Source
> )
> at com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown
> Source)
> at com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown
> Source)
> at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sReply(Unknown
> Sour
> ce)
> at com.microsoft.jdbc.sqlserver.SQLServerImplStatemen t.getNextResultType
> (Unknown Source)
> at com.microsoft.jdbc.base.BaseStatement.commonTransi tionToState(Unknown
> Source)
> at com.microsoft.jdbc.base.BaseStatement.postImplExec ute(Unknown
> Source)
> at com.microsoft.jdbc.base.BaseStatement.commonExecut e(Unknown
> Source)
> at com.microsoft.jdbc.base.BaseStatement.executeUpdat eInternal(Unknown S
> ource)
> at com.microsoft.jdbc.base.BaseStatement.executeUpdat e(Unknown
> Source)
>
> ************************************************** ********************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||Hi John. Check the DBMS error log. I believe the DBMS is in trouble. The driver is just
finding out that the socket connection it sues to the DBMS is dieing.
Joe Weinstein at BEA
jdnospam@.hevanet.com wrote:

> Websphere application for nurses license renewal application that hits our SQL Server 2000.
> This was the first error that we decided to stop and start the application:
> Unable to load SQL Server Data Driver driver.
> Unable to create database connection.
> [Microsoft][SQLServer 2000 Driver for JDBC]Error establishing socket.
> com.obs.data.DataObjectException: [Microsoft][SQLServer 2000 Driver for JDBC]Err or establishing socket.
>
> This is the send stream of errors after we had thought we had solved the problem. This is kinda urgent because there are nurses out there waiting to get their licenses renewed and mad as heck:
> [Microsoft][SQLServer 2000 Driver for JDBC]Connection reset by peer: Connection reset by peer
> java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Connection res et by peer: Connection reset by peer
> at com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Unknown
> Source
> )
> at com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown
> Source)
> at com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown
> Source)
> at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sReply(Unknown
> Sour
> ce)
> at com.microsoft.jdbc.sqlserver.SQLServerImplStatemen t.getNextResultType
> (Unknown Source)
> at com.microsoft.jdbc.base.BaseStatement.commonTransi tionToState(Unknown
> Source)
> at com.microsoft.jdbc.base.BaseStatement.postImplExec ute(Unknown
> Source)
> at com.microsoft.jdbc.base.BaseStatement.commonExecut e(Unknown
> Source)
> at com.microsoft.jdbc.base.BaseStatement.executeUpdat eInternal(Unknown S
> ource)
> at com.microsoft.jdbc.base.BaseStatement.executeUpdat e(Unknown
> Source)
>
> ************************************************** ********************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||Joe,
There are a ton of locks in SQL Server under Locks/Process ID that reference the web renewal update table, but we know no one is using it.
Most of the properties of these locks show this:
sp_executesql;1
or this:
BeginTrans
So you are probably right, but I don't know where to look for/which error log. Do I use Enterprise Manager or get on the SQL Server box and look at Win 2000 error log?
What would you do to clean up these locks?
John
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||jdnospam@.hevanet.com wrote:

> Joe,
> There are a ton of locks in SQL Server under Locks/Process ID that
> reference the web renewal update table, but we know no one is using it.
Are you doing XA transactions? If so, in-doubt transactions may be holding
locks. Your DBA should be able to associate the locks with the connection
that holds them. Are you managing transactions yourself or are they managed
by websphere?
You may have to cycle websphere to truly kill all the connections it's
made. If that doesn't clear it I'll be surprised, and you might have to cycle
the DBMS. I can tell you that nothing you've shown implicates the driver at
all. If you were using WebLogic, I'd have lots of tools to help you...

> Most of the properties of these locks show this:
> sp_executesql;1
That implies use of PreparedStatements.
> or this:
> BeginTrans
That sounds like maybe bad application code. How are these transactions started?
You should never be sending "BEGIN TRAN" SQL. You should be only using setAutoCommit()
and commit()/rollback() JDBC calls.

> So you are probably right, but I don't know where to look for/which error log. Do I use Enterprise Manager or get on the SQL Server box and look at Win 2000 error log?
> What would you do to clean up these locks?
As above. First thing is to cycle the client (websphere).
Joe Weinstein at BEA

> John
> ************************************************** ********************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||Hi Joe, As mentioned by John A. Davis, we are getting the following 3
different error message at a random fashion
[Microsoft][SQLServer 2000 Driver for JDBC]Error establishing socket
[Microsoft][SQLServer 2000 Driver for JDBC]Connection reset
[Microsoft][SQLServer 2000 Driver for JDBC]Connection reset by peer: socket
write error
Our prodcut is web based Java application, our application is configured
under Tomcat5 in one machine and the MS-SQL Server 2000 is running on another
machine, both the machines are in the same network in intranet. Right now we
are using MS-JDBC Driver SP2. The above listed errors occurring atlest 2 to 3
times a day, otherwise the application works fine. I am still wondering why
these error occurring in intranet based setup? any help on this appreciated.
"Joe Weinstein" wrote:

> Hi John. Check the DBMS error log. I believe the DBMS is in trouble. The driver is just
> finding out that the socket connection it sues to the DBMS is dieing.
> Joe Weinstein at BEA
> jdnospam@.hevanet.com wrote:
>
>
|||Saran wrote:

> Hi Joe, As mentioned by John A. Davis, we are getting the following 3
> different error message at a random fashion
> [Microsoft][SQLServer 2000 Driver for JDBC]Error establishing socket
> [Microsoft][SQLServer 2000 Driver for JDBC]Connection reset
> [Microsoft][SQLServer 2000 Driver for JDBC]Connection reset by peer: socket
> write error
> Our prodcut is web based Java application, our application is configured
> under Tomcat5 in one machine and the MS-SQL Server 2000 is running on another
> machine, both the machines are in the same network in intranet. Right now we
> are using MS-JDBC Driver SP2. The above listed errors occurring atlest 2 to 3
> times a day, otherwise the application works fine. I am still wondering why
> these error occurring in intranet based setup? any help on this appreciated.
Hi. I wish I could help you more, but I can tell you that from the JDBC
driver perspective, these are all unexpected problems the driver is suffering
when the raw TCP socket it is using to talk to the DBMS either dies or fails
to be created. I guarantee that the driver is making the socket the same way
each time, so every creation should work the same way, and once it's open,
it should just work until it's closed. This is a problem beyond the application
and driver. Do you hold JDBC connections open indefinitely, sometimes without using
them for a long time? Does the problem seem to occur right after a long idle
period? Maybe the DBMS has an idle connection timeout, and is closing your
connections out from under you. Also, if you do have a limit on the number of
DBMS connections you are allowed, how are you controlling the number of connections
you try to make.
Joe Weinstein at BEA
[vbcol=seagreen]
> "Joe Weinstein" wrote:
>
|||We have adapted connection pool concept from the article below, to manage
connection objects.
http://java.sun.com/developer/online...k/conpool.html
We made a log file to see how many connections objects are maintained in
given time, most of the i noticed there will be only one connection object in
the pool to server, very occassionally i could see 2 to 3 connection object
in the pool.
does the above pooling concept may have problem?
"Joe Weinstein" wrote:

>
> Saran wrote:
>
> Hi. I wish I could help you more, but I can tell you that from the JDBC
> driver perspective, these are all unexpected problems the driver is suffering
> when the raw TCP socket it is using to talk to the DBMS either dies or fails
> to be created. I guarantee that the driver is making the socket the same way
> each time, so every creation should work the same way, and once it's open,
> it should just work until it's closed. This is a problem beyond the application
> and driver. Do you hold JDBC connections open indefinitely, sometimes without using
> them for a long time? Does the problem seem to occur right after a long idle
> period? Maybe the DBMS has an idle connection timeout, and is closing your
> connections out from under you. Also, if you do have a limit on the number of
> DBMS connections you are allowed, how are you controlling the number of connections
> you try to make.
> Joe Weinstein at BEA
>
>
|||Saran wrote:

> We have adapted connection pool concept from the article below, to manage
> connection objects.
> http://java.sun.com/developer/online...k/conpool.html
> We made a log file to see how many connections objects are maintained in
> given time, most of the i noticed there will be only one connection object in
> the pool to server, very occassionally i could see 2 to 3 connection object
> in the pool.
> does the above pooling concept may have problem?
Hi. The *concept* has no problem, but the implementation may. If it is not threadsafe,
you may well have one thread closing a connection while another is using it, in which
case you could have any sort of odd failure... but, not, these symptoms are all below
the driver or pool or application. Pooling is good. I would verify if the problems
that come up are when you use a connection that's been idle in the pool for a long
time. You could easily add a checker in the pool, which does a quick "select 1" during
your call to the pool for a connection. If the test passes, the connection is as
good as you can ensure. If the query fails, the pool can replace it and give you a
new one. BEA's WebLogic connection pools do a lot of this pretesting and protection...
Joe Weinstein at BEA
[vbcol=seagreen]
> "Joe Weinstein" wrote:
>
|||All the calls to the pool are syncronized as well pool itself checks if a
connection object is NOT in use before it returns to request.
As you suggested i can run a simple query before the pool returns a
connection to a request. You said "select 1", is it a valid query to validate
a connection object or just a sample query that i need to convert to "SELECT
Column1 FROM Table1".
"Joe Weinstein" wrote:

>
> Saran wrote:
>
> Hi. The *concept* has no problem, but the implementation may. If it is not threadsafe,
> you may well have one thread closing a connection while another is using it, in which
> case you could have any sort of odd failure... but, not, these symptoms are all below
> the driver or pool or application. Pooling is good. I would verify if the problems
> that come up are when you use a connection that's been idle in the pool for a long
> time. You could easily add a checker in the pool, which does a quick "select 1" during
> your call to the pool for a connection. If the test passes, the connection is as
> good as you can ensure. If the query fails, the pool can replace it and give you a
> new one. BEA's WebLogic connection pools do a lot of this pretesting and protection...
> Joe Weinstein at BEA
>
>