JDBC reconnect problems with Teradata driver using Spring and Apache DataSource

Question

I'm using the Apache BasicDataSource for both commons DBCP and connection pool:

org.apache.commons.dbcp.BasicDataSource

and managing it through Spring:

org.springframework.jdbc.datasource.DataSourceTransactionManager

While using this combination with the Teradata JDBC driver if my database goes down or there is a network glitch I receive the following error:

08S01 804 : I/O Error, Socket closed. Packet stream write error

Which reflects the situation correctly but the problem is with reconnecting. When the error is ironed out physically or DB comes back up and my program tries to query it, I still end up getting the same error for some time period which varies e.g. 20 minutes, hours, never. The problem goes away when either I restart my JVM or change the connection string e.g. use IP address instead of hostname.

Is there any setting in the DataSource or the Spring Transaction Manager that can rectify this? or maybe a TCP/IP setting?

Answer 1

I'd look into the settings on Apache DBCP:

http://commons.apache.org/dbcp/configuration.html

SELECT 1 can be an efficient validation query. Test on return should be set.

I'd also ask that it remove and log abandoned connections so you can see what's going on.

Answer 2

Do you see this error on both Windows and Linux? If you only see it on Linux, then I recommend checking out the Teradata JDBC Guide. When logging on, they use an algorithm which is slow on the Linux version of the JVM. Try adding the following line to teh start of your service command to see if you get any improvement

-Djava.security.edg=file:/dev/./urandom

Note: this was taken from the troubleshooting portion of the Teradata JDBC Driver User Guide