JDBC Connection Pooling in Websphere

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

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

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.

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;

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

posted Jan 14, 2002 05:44:00

0

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

posted Jan 14, 2002 05:51:00

0

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

posted Jan 14, 2002 06:03:00

0

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