dead connection from jdbc pool for the first time run

Connectivity
N/A

dead connection from jdbc pool for the first time run

Hi,

we have a jdbc data source configured in sap netweaver java server,which connects to Teradata(13.10.07.28). the jdbc driver version is 15.00.00.20.

we have an ejb consuming that data source.

the java server is running on windows.

private Connection getConnection() throws SQLException {
Connection conn = _dataSource.getConnection();
for (int i = 0; i < 3; i++) {

try {
if (!conn.isValid(30)) {
conn = _dataSource.getConnection();
Statement st=conn.createStatement();// this line throws the error
st.close();
st=null;
} else {
_location.infoT("get the connection at "+i+" try.");
break;
}
} catch (Exception e) {
_location.traceThrowableT(Severity.ERROR, "", e);
}

}

return conn;
}

the problem is this methd always return me the dead connection if it is the first time to run the code. it gives valid connection if you run it immediately after the failure and afterwards. if you haven't accessed it for a while, you will get the issue if u run it again and you get valid connection if u run it after the failuer.

in my code  i am checking the connection is dead or live, but it doesn't help.

If you write following code, the loop never ends until server restart(because of out of memory) for the first time run.

While(!conn.isValid(30)){

conn = _dataSource.getConnection();

}

[EXCEPTION]
java.sql.SQLException: [Teradata JDBC Driver] [TeraJDBC 15.00.00.20] [Error 1095] [SQLState HY000] Cannot call a method on closed connection
at com.teradata.jdbc.jdbc_4.util.ErrorFactory.makeDriverJDBCException(ErrorFactory.java:94)
at com.teradata.jdbc.jdbc_4.util.ErrorFactory.makeDriverJDBCException(ErrorFactory.java:64)
at com.teradata.jdbc.jdbc_4.TDSession.createStatement(TDSession.java:1339)
at com.teradata.jdbc.jdbc_4.TDSession.createStatement(TDSession.java:645)
at com.teradata.jdbc.jdbc_4.TDSession.createStatement(TDSession.java:1380)
at com.sap.sql.jdbc.vendor.VendorConnectionHandle.createStatement(VendorConnectionHandle.java:79)
at com.sap.engine.services.dbpool.cci.ConnectionHandle.createStatement(ConnectionHandle.java:97)

any advice will be appreciated.

Thanks

Jun

Tags (2)
5 REPLIES
Teradata Employee

Re: dead connection from jdbc pool for the first time run

Many customers have an automated process that terminates idle connections. Obviously, that causes problems for connection pools, because the connections in a connection pool may be idle much of the time.

If you want to use a connection pool, then you need to work with your Teradata Database administrators to exclude your pooled connections' userid from their process that kills idle connections.

N/A

Re: dead connection from jdbc pool for the first time run

Hi,

thanks for the reply.

i will check that point.

but how that can explain my  problem? it gives me all dead connection in the first run. no matter how many try I made.

Best regards,

Jun

Teradata Employee

Re: dead connection from jdbc pool for the first time run

Even on the "first run" of your application, all the connections in the connection pool may be old and may have been killed.

N/A

Re: dead connection from jdbc pool for the first time run

that is possible but....

from the pool configuration,the maximum pool size is 25, allowed idle connection is 3.

i think in my first run there should be new connection created, how come that is also a dead one?

 

if forget about the auto killing idle connection,  which part is more likely to be culprit for this issue? sap jdbc pool manager or teradata?

 

Teradata Employee

Re: dead connection from jdbc pool for the first time run

It's not productive to speculate about what might be happening.

If you want to troubleshoot the issue, you can specify the Teradata JDBC Driver's LOG=DEBUG connection parameter, which will log an enormous amount of information, including all the message traffic between the JDBC Driver and the Teradata Database, and will indicate when the connection is closed.

You can then correlate that information with the information recorded in DBC.LogOnOff, which indicates when sessions were gracefully or forcefully logged off.