commons-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Michael Holly" <mho...@talisentech.com>
Subject RE: DBCP vs Oracle's jdbc2.0 optional implementation
Date Tue, 13 May 2003 21:18:42 GMT
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/msg02953.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/commons/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


Mime
View raw message