Greetings.
I am trying to get a database connection in Websphere 5.0 (running on Redhat Adv. Server) and my connection times are very, very slow. Below is the code to get the connection:
Properties parms = new Properties();
parms.setProperty(Context.INITIAL_CONTEXT_FACTORY,
"com.ibm.websphere.naming.WsnInitialContextFactory");
// Create the Initial Naming Context
Context ctx = new InitialContext(parms);
DataSource ds = (DataSource) ctx.lookup("jdbc/customer");
Connection con = ds.getConnection();
Here is the setup for the database connection within the Websphere Admin Console to an Oracle 8i database:
Implementation: oracle.jdbc.pool.ConnectionPoolDataSource
JNDI Name: jdbc/customer
Statement Cache Size: 10
Data source helper class name: com.ibm.websphere.rsadapter.OracleDataStoreHelper
Connection Timeout: 1800
Maximum Connections: 30
Minimum Connections: 10
Reap Time: 180
Unused Timeout: 1800
Aged Timeout: 0
Purge Policy: Entire Pool
We CAN connect to the database. We CAN run SQL statements and stored procedures just fine. Everything with the database is working correctly. ok, so here is the problem... The statement: Connection con = ds.getConnection(); Takes a long time. 3+ seconds. Every time. No Matter what the load is. We need to get this time down significantly. It's killing our web services.
Using JMeter, We have tested it using a single
thread and up to 50 concurrent threads and it all runs the same. 3+ seconds. We actually changed the code to create a database connection just using
JDBC with no JNDI lookup and it runs at the same speed. It is almost as if the connection pool is not working correctly.
So then we looked to see if we were ever getting the same connection twice, but when we printed out the object references, it looks like the connection is a brand new connection EVERY time because the object references are different. Please see the partial log file I have included below.
Right now, our theory is that the connection pool is just getting us a new connection every time without reusing existing connections. We have played with all the settings on minimum connections, maximum connections, and everything else and nothing seems to help.
Thank you to anyone that might be able to help us.
Stephen
Log File:
----- getDatabaseConnection() ------
ERROR - 1 - Connection is com.ibm.ws.rsadapter.jdbc.WSJdbcConnection@7aac74cd
ERROR -
----- getDatabaseConnection() ------
1066140699427Using existing ds
getting datasource took 0 milliseconds to complete.
getting connection took 3733 milliseconds to complete.
getDatabaseConnection took 3733 milliseconds to complete.
----- getDatabaseConnection() ------
ERROR - 1 - Connection is com.ibm.ws.rsadapter.jdbc.WSJdbcConnection@7580f4cd
ERROR -
----- getDatabaseConnection() ------
1066140699707Using existing ds
getting datasource took 0 milliseconds to complete.
getting connection took 3454 milliseconds to complete.
getDatabaseConnection took 3454 milliseconds to complete.
----- getDatabaseConnection() ------
ERROR - 1 - Connection is com.ibm.ws.rsadapter.jdbc.WSJdbcConnection@5b2b4cd
ERROR -
----- getDatabaseConnection() ------
1066140700199Using existing ds
getting datasource took 0 milliseconds to complete.
getting connection took 2965 milliseconds to complete.
getDatabaseConnection took 2965 milliseconds to complete.
----- getDatabaseConnection() ------
ERROR - 1 - Connection is com.ibm.ws.rsadapter.jdbc.WSJdbcConnection@2301b4ce
ERROR -
----- getDatabaseConnection() ------
1066140699752Using existing ds
getting datasource took 0 milliseconds to complete.
getting connection took 3417 milliseconds to complete.
getDatabaseConnection took 3417 milliseconds to complete.