commons-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Durham David Contr 805 CSS/SCBE" <David.Durh...@scott.af.mil>
Subject RE: DBCP vs Oracle's jdbc2.0 optional implementation
Date Tue, 13 May 2003 21:22:48 GMT
Wow, that's really helpful.  Thanks.

Dave

> -----Original Message-----
> From: Michael Holly [mailto:mholly@talisentech.com]
> Sent: Tuesday, May 13, 2003 4:19 PM
> To: Jakarta Commons Users List
> Subject: RE: DBCP vs Oracle's jdbc2.0 optional implementation
> 
> 
> Dave
> 
> I have used DBCP with Oracle and MySQL.  I have had a couple 
> of problems
> that you might want to know about. I found a problem with 
> DBCP when using
> Oracle MTS (Multi Threaded Server). MTS is basically a 
> connection managment
> solution that allows servers with large amount of connections 
> to be able to
> function with limited memory. (Not something I would 
> recommend using unless
> you have to) Sometimes the connection pool would hang for 60+ 
> seconds before
> returning a connection. It never timed out though.
> 
> To get around the MTS problem I had to configure my 
> connection pool to use
> dedicated connections to Oracle.  I sure ended up with a 
> funny looking jdbc
> url. (See the XML below).  Essentially I added the SERVER = 
> DEDICATED to my
> TNS entry and then cut and pasted it on the back of the jdbc 
> url.  I guess
> the Oracle thin driver can handle the url easy enough. (My 
> DBA found this
> technique in Oracle's Metalink)
> 
> My other problem was that the DB I was using had a nightly 
> reboot. (not my
> idea).  When the server would reboot the connections in the 
> pool would be
> come invalid.
> 
> To get around this I had to configure my connection pool to 
> do a validation
> query to test the connection before the actual query went 
> through.  I admit
> the documentation seems to be a little sparse. Most info can 
> be gleaned by
> querying the userlists.  Here are some resources for you
> 
> http://www.mail-archive.com/commons-user@jakarta.apache.org/
> 
> http://www.mail-archive.com/commons-user@jakarta.apache.org/ms
> g02953.html
> 
> http://jakarta.apache.org/commons/dbcp/
> 
> http://cvs.apache.org/viewcvs.cgi/jakarta-commons/dbcp/doc/
> 
> http://jakarta.apache.org/commons/dbcp/apidocs/org/apache/comm
> ons/dbcp/
> 
> 
> Here is a copy of the config for the DBCP pool from my server.xml.
> 
> [code]
>   <Resource name="jdbc/oracle_myco" auth="Container"
>          type="javax.sql.DataSource"/>
> 
>   <ResourceParams name="jdbc/oracle_myco">
>     <parameter>
>       <name>factory</name>
>       <value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
>     </parameter>
>     <parameter>
>       <name>driverClassName</name>
>       <value>oracle.jdbc.driver.OracleDriver</value>
>     </parameter>
>     <parameter>
>       <name>url</name>
>       <!-- old style connect string -->
>       <!--value>jdbc:oracle:thin:@abraxxas:1521:myco</value-->
>       <!-- new style connect string for dedicated connection -->
>       <value>jdbc:oracle:thin:@(DESCRIPTION = (ADDRESS_LIST = 
> (ADDRESS =
> (PROTOCOL = TCP)(HOST = abraxxas.myco.com)(PORT = 1521))) 
> (CONNECT_DATA =
> (SERVER = DEDICATED) (SID = MYCO)))</value>
>     </parameter>
>     <parameter>
>       <name>username</name>
>       <value>myco</value>
>     </parameter>
>     <parameter>
>       <name>password</name>
>       <value>#######</value>
>     </parameter>
>     <parameter>
>       <name>maxActive</name>
>       <value>20</value>
>     </parameter>
>     <parameter>
>       <name>maxIdle</name>
>       <value>10</value>
>     </parameter>
>     <parameter>
>       <name>maxWait</name>
>       <value>-1</value>
>     </parameter>
>     <parameter>
>       <name>validationQuery</name>
>       <value>select 'validationQuery' from dual</value>
>     </parameter>
>     <parameter>
>       <name>testOnBorrow</name>
>       <value>true</value>
>     </parameter>
>   </ResourceParams>
> [/code]
> 
> Notice the odd look for the db url.  A entry on Oracle's 
> metalink said that
> to get a dedicated link you must copy your SID entry from the 
> tnsnames.ora
> file and add the entry (SERVER = DEDICATED) to get the connection.
> Obviously, the oracle jdbc driver can handle this.
> 
> The other thing to look for is the addition of the
> 
> [code]
>     <parameter>
>       <name>validationQuery</name>
>       <value>select 'validationQuery' from dual</value>
>     </parameter>
>     <parameter>
>       <name>testOnBorrow</name>
>       <value>true</value>
>     </parameter>
> [/code]
> 
> parameters for the DBCP pool.
> 
> 
> Hope this helps
> 
> 
> Michael
> 
> 
> 
> 
> 
> 
> 
> 
> 
> -----Original Message-----
> From: Durham David Contr 805 CSS/SCBE
> [mailto:David.Durham1@scott.af.mil]
> Sent: Tuesday, May 13, 2003 3:51 PM
> To: commons-user@jakarta.apache.org
> Subject: DBCP vs Oracle's jdbc2.0 optional implementation
> 
> 
> I'm having a problem with Oracle's connection pooling.  
> Basically it will
> max out db processes and not release connections 
> sporatically.  Has anyone
> switched to DBCP from Oracle's connection pooling to solve 
> this issue or has
> anyone successfully used DBCP with Oracle?
> 
> 
> -Dave
> 
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: commons-user-help@jakarta.apache.org
> 
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: commons-user-help@jakarta.apache.org
> 
> 

Mime
View raw message