See Using the Jakarta Commons, Part 3. Lots of cool stuff here, but I'm particularly interested in looking at DBCP, the database connection pool package.
One of my team's apps runs on Tomcat 4.0.x, and we use Tomcat's connection pooling. I'm not sure if it doesn't work how we want it to, or whether we are just using it wrong, but we are having real problems with it. Mainly, the problem is that it seems very keen on throwing connections away. Unless you go to the pool very soon after a connection has been returned, you'll find the pool empty, and have to wait for a new one to be built. Which is, of course, exactly what we are using a pool to avoid. (Note to self - try Evo on Tomcat 4.1.x.)
I also looked into using IBM's AS400JDBCConnectionPool, since it's a '400 that we are trying to get to. But I can't get that going under Tomcat at all.
So, perhaps DBCP is the way to go. Certainly it's worth a look. Unless anyone has any suggestions as to where I'm going wrong with either of the other two approaches?
Posted to Java by Simon Brunning at July 24, 2003 10:28 AMYeah, you're going wrong - you keep trying to compensate for the fact that you're using a reference implementation as a container. Get a real container. If you can't do THAT, even a faux J2EE container like JBoss would do what you needed.
Posted by: Joseph Ottinger on July 24, 2003 12:04 PMI should have been more precise - we develop and test using Tomcat. It seems to me that the reference implementation is *exactly* what you should be using for testing.
We deploy to whatever J2EE server our client wishes to use. Often that *is* Tomcat, and other than this connection pooling deal, we've not had any problems. Other clients use other servers - our last new client used WebSphere, for example.
Is there anything else that Tomcat is bad at? Should be advising our clients against using it for live deployments?
Posted by: Simon Brunning on July 24, 2003 12:22 PM@joseph
what an answer. There is nothing wrong using Tomcat for connection pooling. We also pool connections to IBM AS/400 (know nown as iSeries). What do you mean with get a real container?? TOmcat is not the fastest out there, but pooling can be done in many ways with tomcat.
Back to the original poster:
define a resource in server.xml like this:
<Resource name="jdbc/db2x" auth="Container" type="javax.sql.DataSource"/>
<ResourceParams name="jdbc/db2x">
<parameter> <name>user</name><value>username</value> </parameter>
<parameter> <name>password</name><value>password</value> </parameter>
<parameter> <name>factory</name><value>org.logemann.tomcat.AS400DataSourceFactory</value></parameter>
<parameter> <name>libraries</name><value>jvalib</value></parameter>
<parameter> <name>serverName</name><value>my.as400.com</value></parameter>
<parameter> <name>prompt</name><value>false</value></parameter>
<parameter> <name>trace</name><value>false</value></parameter>
<parameter> <name>naming</name><value>system</value></parameter>
</ResourceParams>
THEN use my own FactoryImplementation for getting a AS400JDBCConnectionPoolDataSource:
// =========================================================
// User: Marc Logemann (created 17.07.2002 - 13:56:53)
// Classname: AS400DataSourceFactory
// =========================================================
package org.logemann.tomcat;
import java.util.Hashtable;
import javax.naming.*;
import javax.naming.spi.ObjectFactory;
import com.ibm.as400.access.AS400JDBCConnectionPoolDataSource;
public class AS400DataSourceFactory implements ObjectFactory {
public Object getObjectInstance(Object obj, Name name, Context nameCtx,
Hashtable environment)
throws Exception {
// Create and configure a AS400JDBCConnectionPoolDataSource instance based on the
// RefAddr values associated with this Reference
AS400JDBCConnectionPoolDataSource dataSource = new AS400JDBCConnectionPoolDataSource();
// AS400JDBCDataSource dataSource = new AS400JDBCDataSource();
RefAddr ra = null;
// We only know how to deal with javax.naming.References
// that specify a class name of "javax.sql.DataSource"
if ((obj == null) || !(obj instanceof Reference)) {
return (null);
}
Reference ref = (Reference) obj;
if (!"javax.sql.DataSource".equals(ref.getClassName())) {
return (null);
}
ra = ref.get("user");
if (ra != null) {
dataSource.setUser(ra.getContent().toString());
}
ra = ref.get("password");
if (ra != null) {
dataSource.setPassword(ra.getContent().toString());
}
ra = ref.get("serverName");
if (ra != null) {
dataSource.setServerName(ra.getContent().toString());
}
ra = ref.get("driver");
if (ra != null) {
dataSource.setDriver(ra.getContent().toString());
}
ra = ref.get("translateBinary");
if (ra != null) {
dataSource.setTranslateBinary
(Boolean.valueOf(ra.getContent().toString()).booleanValue());
}
ra = ref.get("trace");
if (ra != null) {
dataSource.setTrace
(Boolean.valueOf(ra.getContent().toString()).booleanValue());
}
ra = ref.get("prompt");
if (ra != null) {
dataSource.setPrompt
(Boolean.valueOf(ra.getContent().toString()).booleanValue());
}
ra = ref.get("timeFormat");
if (ra != null) {
dataSource.setTimeFormat(ra.getContent().toString());
}
ra = ref.get("dateFormat");
if (ra != null) {
dataSource.setDateFormat(ra.getContent().toString());
}
ra = ref.get("naming");
if (ra != null) {
dataSource.setNaming(ra.getContent().toString());
}
ra = ref.get("libraries");
if (ra != null) {
dataSource.setLibraries(ra.getContent().toString());
}
ra = ref.get("dataSourceName");
if (ra != null) {
dataSource.setDataSourceName(ra.getContent().toString());
}
ra = ref.get("access");
if (ra != null) {
dataSource.setAccess(ra.getContent().toString());
}
// Return the configured data source instance
return (dataSource);
}
}
USE this in your web.xml
<resource-ref>
<description>DataSource DB2/400 (JTopen own)</description>
<res-ref-name>jdbc/db2x</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
Use this code for initial pool setup:
private AS400JDBCConnectionPool pool;
Context context =
(Context) new InitialContext().lookup("java:comp/env");
dataSource =
((AS400JDBCConnectionPoolDataSource) context
.lookup("jdbc/db2x"));
pool = new AS400JDBCConnectionPool(dataSource);
pool.fill(3);
pool.setMaxConnections(10); // allow a maximum of 10 connections within the pool
pool.setCleanupInterval(600000); // set cleanup interval to 10 minutes
pool.setMaxInactivity(28800000); // 8 hours maximum inactivity of a connection
pool.setMaxLifetime(86400000); // 24 hours maximum lifetime of a con
Make sure you can access the pool object from within all servlets, pool will provide anything you need.
Thats it roughly. I also tried DBCP with plain AS400 JDBC driver, but there wasnt much of a difference regarding pooling behavior.
Posted by: Marc Logemann on July 24, 2003 12:27 PMThank you, Marc. That looks like *exactly* what I need. I'll give it a go this afternoon.
(BTW, lest anyone think that Marc's coding style is crap, I'll point out that Movable Type throws away tab based indentation. Which is more of a problem for Python code...)
Posted by: Simon Brunning on July 24, 2003 01:14 PMAlthough it's been a year since I did something similar, we successfully used (in Tomcat for dev) http://www.bitmechanic.com JDBCPool which also handles shrinking and broken connections
Posted by: Philippe on July 25, 2003 07:23 AMI found time to try Marc's solution today. Seems to work a treat. Thanks, Marc!
Posted by: Simon Brunning on July 30, 2003 03:53 PM
I was trying to solve some problems and came accross this site while searching. I have a couple of questions. What are the advantages of using JNDI? and is it required to use? I wrote a dbhandler class for a web app and never used it. But now I am trying to setup connection pooling and using The AS400JDBCConnectionPool class to do so. I can create a pool, pass out connections, and everything.. I just can't execute a query. Whenever I do, I get some error about the table could not be found in *FILE. This has something to do with setting the library to use. I have used setLibraries to set the lib, if I do getLibraries it shows the selected lib. But I still get the same error. How do I select what database/library/schema (whatever as400 people call it) to use for my connection?
Thanks...
Posted by: Bruce Marriner on September 29, 2003 06:33 PMBruce,
you should not have to set the libraries. The SQL on the 400 runs in two naming formats *SYSTEM and *SQL. *SYSTEM is traditional as400 name ie library/file, and *SQL is library.file.
When using *SQL naming format, I believe the database connection job, which will usually be found running subsystem QUSRWRK, will be given a default library list, which usually just includes the basic IBM libraries.
When using *SYSTEM naming format, the db job is given the library list on the supplied users job description.
In either case you either explicitly use library/file, or just use file, and either add the libraries to the job, or change the job description of the user profile used to include the required libraries. If your still having problems, get in tough, because I had the same problems but seem to have it sussed now!
Posted by: colin williams on October 2, 2003 10:11 PM
Well I got the lib problem solved actually. But now I have another problem.. I am trying to find a good way to release a used connection back into the pool once a JSP page is finished with it. What I want to do is have the selectQuery method in the dbHandler class to get a pooled connection, create a statement, and execute the query, return resultset to calling page. Then the page will cycle though the resultset and when finished it will call a function to close the resultset. This function will execute result.getStatement.getConnection.close(); to close the connection. Along with a couple other things but thats the main topic. Now the problem is this does not work right. When I execute teh above line the connection is not returned into the pool, it is left in an "active" status. If I execute result.getStatement.getConnection.isClosed() it will tell me its closed. If I limit my dbHandler class so it will only open one connection from the pool and never try to get another. So after I do this, I run a query, then the getStatement.getconnection.close part and I also made the connection variable public in the class so I tried dbHandler.conn.isClosed() and it tells me it's not closed. So then I try to execute another query off that same connection and I get a sql exception that the connecion does not exist!!!..
Now, I have a way that does work. If I change the query method to require a connection argument, and add a getConnection method. In the JSP file I get a pooled connection from the class, I pass that as an argument to the query method, I use the resultset, then close it and the connection. The connection then gets put correctly back into the pool. But for some reason I do not like this method and it just seems messy to me. If anyone has some other ideas I'd like to hear them!
Posted by: Bruce Marriner on October 7, 2003 04:31 PMBruce,
Hmmm, well, the way that works is the right way to go. You have a pool class, which returns connections. Clients of that class get their connections, use them, and close them, thereby returning them to the pool.
Is that messy? Subjective, I suppose, but I don't find it so, given the problem at hand. (Only the client can close the connection, 'cos only the client knows when it's finished with it.) But either way, that's how it works.
Posted by: Simon Brunning on October 7, 2003 04:37 PMI have tried Marc Logemann's AS400DataSource posted here and It works without loosing connections. However, my question is why is it slow compared to tomcat's dbcp implementation. I agree that tomcat jndi datasource has a serious problem of connetions being lost. But when I execute the same sql it takes 2 seconds with tomcat and 9 seconds with Marc's AS400DataSource.
It would be great if either Marc's solution could be faster or if someone knew how to stabalize the tomcat way.
Any ideas?
Posted by: Kevin Kruse on January 15, 2004 10:21 PMIt does work fast!
I mistakenly had the initial pool setting statements in the jsp and java code I was using.
That should only be used to initialize the pool if it has not already been done.
After removing it this worked fast and did not drop connections.
Thanks you Marc Logemann for this solution!
I am getting a "connection does not exist" on XP-based Tomcat JNDI Datasource accessing AS/400 V5R1 DB2 files through JTOpen's AS400JDBCDriver. It works fine, until I let my PC-Tomcat idle, which will eventually bring the screen saver. After reentering password on screen saver, a URL pointing to a JSP accessing this connection will not work, even though within the DAO I retry the context lookup, get the data source and then get the connection. Second time through, without having to stop/restart Tomcat, it does work! I used Log4j on the Datasource and it seems to be getting it fine. But, it throws an exception on getting the connection. I think that Tomcat is giving back a datasource that points to an invalid address on the AS/400. It is like I need something equivalent to mySql's autoReconnect=true!? Any ideas?
Posted by: Luis on December 8, 2004 12:12 AMDisable the screensaver? ;-)
Posted by: Simon Brunning on December 8, 2004 08:42 AMThis seems like something that should work, but Tomcat appears not to be finding the DataSourceFactory upon startup. Here's the stacktrace from the exception Tomcat throws. How do I make the factory available to Tomcat on startup? Do I have to jar it and stick it in common/lib? I tried sticking the raw class file into common/classes, but that didn't work:
SEVERE: Exception processing Global JNDI Resources
javax.naming.NamingException: Could not create resource factory, ClassNotFoundException:com.yyy.AS400DataSourceFactory
at org.apache.naming.factory.ResourceFactory.getObjectInstance(ResourceFactory.java:80)
at javax.naming.spi.NamingManager.getObjectInstance(NamingManager.java:304)
at org.apache.naming.NamingContext.lookup(NamingContext.java:791)
at org.apache.naming.NamingContext.lookup(NamingContext.java:151)
at org.apache.catalina.mbeans.GlobalResourcesLifecycleListener.createMBeans(GlobalResourcesLifecycleListener.java:155)
at org.apache.catalina.mbeans.GlobalResourcesLifecycleListener.createMBeans(GlobalResourcesLifecycleListener.java:160)
at org.apache.catalina.mbeans.GlobalResourcesLifecycleListener.createMBeans(GlobalResourcesLifecycleListener.java:125)
at org.apache.catalina.mbeans.GlobalResourcesLifecycleListener.lifecycleEvent(GlobalResourcesLifecycleListener.java:97)
at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java:119)
at org.apache.catalina.core.StandardServer.start(StandardServer.java:2306)
at org.apache.catalina.startup.Catalina.start(Catalina.java:556)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:287)
at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:425)
Plus, is the section in the web.xml necessary?
Posted by: Jeremy on December 15, 2004 05:01 PM...the <resource-ref> section that is...
Posted by: Jeremy on December 15, 2004 05:03 PMhi
i have a problem in connection pooling.i m using hsqldb database.how to implement connection pooling in my proj.i am using java code to connect to database.
I've never used HSQLDB in production, not with a connection pool. What it the problem that you are getting?
Posted by: Simon Brunning on April 25, 2005 09:51 AMi have a problem in connection pooling.
After setting the application I get Error :
05/09/10 10:52:29 DBHandlerException ex :DBHandler Exception:Exception in module
DBHandlerConnectionPool :DBHandler Exception:Exception in module DBHandlerConne
ctionPool :jdbc/GGNPooledThinDataSource not found
Please help me.
Wow came across this blog after searching for my name in google and the thread is still alive. Thats awesome. Hope everybody is still being able to connekt to iSeries DB2, times changed and now everything seems even be easier than two years ago. Nowdays i am still using iSeries DB2 for some projects, but relying on a commercial connection pool inside Tomcat and wireing everything up with Spring. Way better than the JNDI/DBCP stuff i used 2 years ago.
Posted by: Marc Logemann on October 28, 2005 11:01 AMi have mssqlserver ..i got "Error: cannot create poolableConnectionFacotry..." as error..plzz help me
Posted by: Vijay on January 7, 2006 09:05 AMHi all, I'm really having serious issues with Tomcat. I hae set up a connection from my box to another remote system by setting
jdbc:microsoft:sqlserver://remotebox:1433;DatabaseName=mydb;SelectMethod=cursor
as one of my entries in the context.xml.
The issue is, after some some of successful database fetches, tomcat freezes, and wont budge until I restart it. I have included the removeAbandoned property also in the xml file but this doesnt work. Please anyone with a fix?
unstable answers i like it
Posted by: weaccedeesymn on February 8, 2010 05:48 AMA Day at Jack and Jill
Posted by: FocreddelaBed on April 14, 2011 08:04 AMhi pipls kak dela 123
Posted by: FocreddelaBed on April 15, 2011 12:03 AMA Day at Jack and Jill
Posted by: FocreddelaBed on April 15, 2011 02:46 AMA Day at Jack and Jill
http://bluetonebigband.com/sitemap.html
http://larasse.com/sitemap.html
generic tadalafil \Dtl¥P\¡w,
]
buy kamagra 4‚Uƒ},
Posted by: FocreddelaBed on June 29, 2011 11:48 AMils définiront panneau solaire photovoltaique fonctionnement durant panneau solaire lyon mail. j'ai glorifié fabrication panneau solaire thermique devis panneau solaire immédiatement de panneau solaire caravane. il eut capté pose panneau solaire quarto avant le panneau solaire. puis vous aperceviez construire un panneau solaire quant à panneau solaire portable.
Posted by: solairesj on July 3, 2011 11:23 PMtu regardas artisans substitue mutuelle grosso modo par projet singularite. j'admirais l'avons â›Airs mutuelle ensemble savamment hors lesquelles faire. surtout nous chargeons Lyon notamment mutuelle jusque confie treize. tu annonceras Lenormand rendre mutuelle annuellement de dus pareillement. http://mutuelle.compareo.net
Posted by: amutuellezqyb on July 7, 2011 05:09 PMm.§¤=(Z¨,
Posted by: Furfaggerse on July 8, 2011 04:02 AM