aspose file tools*
The moose likes Websphere and the fly likes JDBC Connection Pooling in Websphere Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Products » Websphere
Bookmark "JDBC Connection Pooling in Websphere" Watch "JDBC Connection Pooling in Websphere" New topic
Author

JDBC Connection Pooling in Websphere

Sri Rangan
Ranch Hand

Joined: Dec 08, 2001
Posts: 160
Our project is based EJBs and deployed in Websphere application Server 3.5.
We have the following set up forDatabase pooling:
Min pool Size = 1
Max pool Size = 25.
Ususally when a user logs on the system, he will start with a ong transaction (it spans across more than 50 method calls, around 200 rows will be added)to pull the data from external System.
When the application concurrently is accessed by 1 or 2 users to do this longest transaction, thre is no problem. When more than 2 users start hitting concurrently, we get Transaction Rollback Exception.
Can it hapen due to the low connection pool setting?
(Or)
What could eb the other possible reasons??
Thanks in Advance



Andy Rodriguez
Ranch Hand

Joined: Oct 16, 2001
Posts: 95
I dont think the rollback is due to a min pool setting , if there were a problem with min pool setting i would have assumed that to be a very slow response , but not a rollback . Is it possible to post the logs ?
Just thinking loud - i remember the max pool size best practice formula / thread to be t * (c - 1) + 1 ? so if the pool max is reached and a deadlock occurs - is there a roll back ?
And is your connection auto-commit enabled ? - there could be your other problem
------------------
My ramblings @
http://javarecon.tripod.com


My ramblings @<br /> <a href="http://javarecon.tripod.com" target="_blank" rel="nofollow">http://javarecon.tripod.com</a>
Kyle Brown
author
Ranch Hand

Joined: Aug 10, 2001
Posts: 3892
    
    5
It's not your pooling settings. My guess is your transaction timeout might be too low, or you might have some sort of threading problem (users stepping on each other)...
Kyle
------------------
Kyle Brown,
Author of Enterprise Java (tm) Programming with IBM Websphere
See my homepage at http://members.aol.com/kgb1001001 for other WebSphere information.


Kyle Brown, Author of Persistence in the Enterprise and Enterprise Java Programming with IBM Websphere, 2nd Edition
See my homepage at http://www.kyle-brown.com/ for other WebSphere information.
sandhya menon
Ranch Hand

Joined: May 22, 2001
Posts: 37
We had gone for stress testing with 150 user hits on same time.

But hardly 10-12 hits has been made the Connection at backend reached to the 20 (ie max level specified at WAS Conn.Pool Max.Size). And sites appears to hanged after that.

(WAS Specs for Conn.Pol Parms: --)

Min-1
Max: 20
Connection time Out--180
Idle time out- 100 ;
Orphan time out--100

It made another 7 extra (20+7) after 14 minutes. It went to beyond 30 at a times but never shown the sign of killing the idle connections. And now even after 3 hrs it show 20 conenction holding at Oracle end.

Process we Adopt for DB .Connection

For Conn.Pooling we are using a pure Java Bean which is in application mode of all JSP which retrieves con. objects ,fires Sql and make sure closes connection in all JSPs.
We are making sure all conn. objects gets closed in proper order (result set then statement object finally conn objects.

Please find attached our DbBean.java Program which act as Conn.Supplier Program and a JSP page for ur reference.
We place this Bean in all our JPS's in Application Scope.

Your expert comments/guidance eagerly awaited

Regards

Abhilash

Kyle Brown
author
Ranch Hand

Joined: Aug 10, 2001
Posts: 3892
    
    5
Your code was not posted. Try again.
Kyle
------------------
Kyle Brown,
Author of Enterprise Java (tm) Programming with IBM Websphere
See my homepage at http://members.aol.com/kgb1001001 for other WebSphere information.
sandhya menon
Ranch Hand

Joined: May 22, 2001
Posts: 37
Dear All,
Here comes my code . I have got 2 DB hence 2 DataSoruce used. Our set up is WIN2k 1GB Ram . I tried to apply all Tuning
Settings as follows:
-Xms256m -Xms512m-Djavax.rmi.CORBA.UtilClass=com.ibm.CORBA.iiop.Util -Dcom.ibm.CORBA.iiop.noLcalCopies=true -Xnoclassgc -DinvocationCacheSize=100
my WAS version is 354 with fix pack= jdk_ptf_4.jar
jar files - efixes are added as follows Q48877/53415/48452/46831/52336
My Pool Settings are as follows : Min -1 Max- 30 Idle Time Out- 180 sec Connection Time Out --180 sec and Orphan Time Out--60 secs . This I did after some testing results. What I got from my Testing Research is that As far as possible make sure that Pool Size is not reached to the maximum. Once it reaches the maximum it prolongs for a considerable time. Mine is a strong DB oriented WEB Pages with n number of connections are used.
And this is how we process the DB Connections.
For Conn.Pooling we are using a pure Java Bean which is in application mode of all JSP which retrieves con. objects ,fires Sql and make sure closes connection in all JSPs.
We are making sure all conn. objects gets closed in proper order (result set then statement object finally conn objects.
packagecom.tfl.db;
import javax.sql.DataSource;
import javax.naming.InitialContext;
import javax.naming.Context;
import javax.sql.ConnectionPoolDataSource;
import java.sql.Connection;
import java.sql.SQLException;
import com.ibm.ejs.dbm.jdbcext.*;
import java.util.Hashtable;
public class DbBean {
private String dbname=null;
privatejavax.sql.DataSourcecpds2=null;
privatejavax.sql.DataSourcecpds=null;
java.sql.Connection con=null;
java.sql.Connection con2=null;

Hashtable env=null;
InitialContext ctx=null;
InitialContext ctx2=null;
public DbBean()
{
this.getConnReadyFor("afsweb");
this.getConnReadyFor("twh");
}

public void getConnReadyFor(String toMe)
{
env=new Hashtable();
env.put(Context.INITIAL_CONTEXT_FACTORY,"com.ibm.ejs.ns.jndi.CNInitialContextFactory");
if(toMe!=null){this.dbname=toMe;}
if(toMe.equalsIgnoreCase("afsweb"))
{
try{
ctx=new InitialContext(env);
cpds =(javax.sql.DataSource)ctx.lookup("jdbc/tfl_afs");
ctx.close();
}catch(Exception e){System.out.println("DataSource Context failure ?? --- "+e);}

}
else
if(toMe.equalsIgnoreCase("afsweb"))
{
try{
ctx2=new InitialContext(env);
cpds2=(javax.sql.DataSource)ctx2.lookup("jdbc/tfl_twh");
//System.out.println("JNDI Look up done ");
ctx2.close();
} catch(Exception e){System.out.println("DataSource Look up failure ?? --- "+e);}
}
}

public java.sql.Connection connect_Me_To(String db)
{
this.dbname=db;
Connection con = null;
try{
if(db.equalsIgnoreCase("afsweb"))
{
try{
con= cpds.getConnection("afsweb","afsweb");
//System.out.println("DbBean.java Conn made to dbname 1 ==> "+this.dbname);
con.setAutoCommit(false);
if(con.isClosed()|| con== null)
{
con=null;
this.getConnReadyFor("afsweb");
con= cpds.getConnection("afsweb","afsweb");
//System.out.println("DbBean.java Conn made to dbname 2 ==> "+this.dbname);

}
}catch(Exception e)
{
con=null;
this.getConnReadyFor("afsweb");
con= cpds.getConnection("afsweb","afsweb");
//System.out.println("DbBean.java Conn made to dbname 3 ==> "+this.dbname);
}
}

else

if(db.equalsIgnoreCase("twh"))
{
try{
con=cpds2.getConnection("afsweb","afsweb");
con.setAutoCommit(false);
if(con.isClosed() || con==null)
{
con=null;
//System.out.println("DbBean speaks--con is closed !--TWH-1--make a new ");
this.getConnReadyFor("twh");
con= cpds2.getConnection("afsweb","afsweb");
}

}catch(Exception e)
{
con=null;
this.getConnReadyFor("twh");
con= cpds2.getConnection("afsweb","afsweb");;
}
}
}catch (Exception ex)
{
con=null;
System.out.println(" Err in Conn. making...to==> " +this.dbname+"--err--"+ex);
this.getConnReadyFor(this.dbname);
connectAgain(this.dbname);
}
//System.out.println("DbBean.java Conn made to dbname 4 ==> "+this.dbname);
return con;
}
public void closeAll(Connection con)
{
try{
//System.out.println("DbBean: Closing Connection now..." );
con.close();
}catch (Exception exception){System.out.println("DbBean: ClosAll Error ?..." );}
finally{
try{
if(con!=null){con.close();}
}catch(Exception ex){}
}
}

public void connectAgain(String dbname)
{
connect_Me_To(dbname);
}
}
Kyle Brown
author
Ranch Hand

Joined: Aug 10, 2001
Posts: 3892
    
    5
There are at least two problems here I can find:
(1) Look carefully at public void getConnReadyFor(String toMe) You will notice that your "else" case is EXACTLY the same as your first "IF" case. You will NEVER initialize cpds2.
(2) Your connectAgain() and connect_me_to() will overrwrite the value of your connection, thus losing the old connection. If you did not close() it prior to calling these methods (even with a different database) that previous connection will be lost from the pool and will have to wait until the orphan timeout expires to be reclaimed.
Kyle
Kyle Brown
author
Ranch Hand

Joined: Aug 10, 2001
Posts: 3892
    
    5
One more thing -- are you REALLY using EJB's? From your second discussion it did not appear so, even though you said you were initially.
If so, then PLEASE get rid of the setAutoCommit() -- that will INCREDIBLY screw up your EJB transactions... Always let the EJB container manage transactions.
Kyle
sandhya menon
Ranch Hand

Joined: May 22, 2001
Posts: 37
Oh ! That is marvellous scrutiny u had done.Great Work u were absolutely right ? That else usage did created the problems..
Reg EJB:
No at present i am not using EJBs
Kudos . The Best fast/Quick/Accurate Solution Provider I had met.
Regards
sandhya menon
Ranch Hand

Joined: May 22, 2001
Posts: 37
Just a Doubt . Do u think my Parms for Connection Pooling is Ok ?? This is what my Resource Analyzer Data look like. Well I came to know that %Used will hamper the performance. But my pool size is just 6 out of max 30 and still it shows 60% used ??? whyt is that so. Thsi data has been collected on Test Phase where we had very low level hit. I wonder what would happen if it would be in Live case ...!!
Pls Advice/guide
% Used 60(avg:55.576)Scale:1.0
Avg.Time Held 9857.9576scale:0.01
Avg.WaitTime0.0Scale:1.0
numWaiters0.0Scale:1.0
poolSize6Scale:1.0
%Maxed0Scale:1.0
numCreated83Scale:1.0
numDestroyed73Scale:1.0
numAllocated513Scale:1.0
Regards
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: JDBC Connection Pooling in Websphere