Thanks Phil.
Phil Steitz <[EMAIL PROTECTED]> wrote:
On 5/8/07, Saeed wrote:
> Dear JAVA-DBCP-UNIX Experts,
> I'm my all-life Cobol guy. I have recently started working on Java side. I
> apologize if I use improper java terminologies/terms. Currently I'm trying to
> bring Oracle data in my Cobol programs through Java objects on SCO UNIX.
>
> Firstly, I have written set of test (cobol/java) programs using jdbc to
> access oracle db, which is working perfectly. I've read accessing oracle db
> through jdbc call, which creates new connection for each user is time
> consuming and costly.
> So finally, I modified & implemented my test bed with DBCP "share pool" of
> open connections. And this approach is also working fine, accessing database
> in my Cobol program successfully.
>
> ******************MY ENVIRONMENT******************
> I'm running Cobol & Java programs (using JDK 1.4) on SCO UNIX, trying to
> access Oracle db located on Windows server. MOST IMPORTANTLY, THERE IS NOT
> ANY KIND OF JAVA APPLICATION SERVER or WEB SERVER on my platform. That means
> I'm using COBOL/JAVA/DBCP (pooling concept) in standalone mode under UNIX.
> ******************MY ENVIRONMENT******************
>
> Questions -
> 1. How can I know this connection pooling concept is working on my Unix
> environment? It looks like the program is creating connections on each user's
> call to db?
Looking at your config below, this is most likely because you have
maxIdle set to 1. That means the pool will only allow one connection
to be idle at a time. This will cause lots of connection churn,
because each time a connection is returned to the pool, if there is
another one idle, the returned connection will be closed. In general,
it is better to set maxIdle to a higher number. The best setting
depends on load patterns, etc. The default is 8. The pool is really
an "idle object pool" when you get down to it, so setting maxIdle to 1
is really setting the pool size to 1.
> 2. I need a strategy (if possible some utility) to confirm connection pool in
> my environment? how can i test this?
Well, you can examine output of getNumActive and getNumIdle while
testing / debugging. These will tell you respectively how many
connections are checked out and how many are idle at a given time.
You can also monitor the database or look at the database logs. Be
careful leaving these diagnostics in for production, because the
getNumXxx methods are synchronized so can be bad for performance.
> 3. Since I'm not using any kind of application & web server, is this thing
> fits under standalone platform like the one i described above? Am I using in
> a right way?
It is fine to use it standalone. Code looks OK.
> 4. When do you think I should call shutdownDataSource method?
I think you mean close, and you should call it when your application shuts down.
Phil
>
> Your great help will be appreciated.
> Best regards, Sayeed
>
> my config file ---
>
> oracle.jdbc.driver.OracleDriver
> usernm
> usrpswd
> jdbc:oracle:thin:@host:1521:dbsid
> 50
> 5
> 1
> 3000
> true
> select * from dual
>
>
> my java file ...
>
> import java.io.IOException;
> import java.sql.Connection;
> import java.sql.ResultSet;
> import java.sql.SQLException;
> import java.sql.Statement;
> import java.util.Iterator;
> import java.util.List;
> import java.util.Properties;
> import javax.sql.DataSource;
> import org.apache.commons.dbcp.BasicDataSource;
> import org.jdom.Attribute;
> import org.jdom.Document;
> import org.jdom.Element;
> import org.jdom.JDOMException;
> import org.jdom.input.SAXBuilder;
> import org.xml.sax.helpers.DefaultHandler;
> public class DataSourceTest {
> // DefaultHandler contain no-op implementations for all SAX events.
> // This class should override methods to capture the events of interest.
> static class MyHandler extends DefaultHandler {
> }
> public static void main(String[] args) throws IOException, Exception {
> // First we set up the BasicDataSource.
> // Normally this would be handled auto-magically by
> // an external configuration, but in this example we'll
> // do it manually.
> //
> System.out.println("Setting up data source.");
> DataSource dataSource = getDataSource();
> System.out.println("Done.");
>
> System.out.println("Print Data Source Statistics.");
> printDataSourceStats(dataSource);
> //
> // Now, we can use JDBC DataSource as we normally would.
> //
> Connection conn = null;
> Statement stmt = null;
> ResultSet rset = null;
> try {
> System.out.println("Creating connection.");
> conn = dataSource.getConnection();
> System.out.println("Creating statement.");
> stmt = conn.createStatement();
> System.out.println("Executing statement.");
> // rset = stmt.executeQuery(args[1]);
> rset = stmt.executeQuery("SELECT desc from tabdesc");
> System.out.println("Results:");
> int numcols = rset.getMetaData().getColumnCount();
> while(rset.next()) {
> for(int i=1;i<=numcols;i++) {
> System.out.print("\t" + rset.getString(i));
> }
> System.out.println("");
> }
> System.out.println("Print Data Source Statistics after open connection.");
> printDataSourceStats(dataSource);
> } catch(SQLException e) {
> e.printStackTrace();
> } finally {
> try { rset.close(); } catch(Exception e) { }
> try { stmt.close(); } catch(Exception e) { }
> try { conn.close(); } catch(Exception e) { }
> }
> System.out.println("Print Data Source Statistics after closing connection.");
> printDataSourceStats(dataSource);
> System.out.println("Print Data Source Statistics after closing data source.");
> shutdownDataSource(dataSource);
> printDataSourceStats(dataSource);
>
> }
> public static DataSource getDataSource()throws IOException, Exception{
> BasicDataSource bds = new BasicDataSource();
> Properties datasourceProperties=new Properties();
> DefaultHandler handler = new MyHandler();
> parseConfigFile(datasourceProperties, "datasource-config.xml", handler,
> false);
> bds.setDriverClassName(datasourceProperties.getProperty("driverClassName"));
> bds.setUsername(datasourceProperties.getProperty("username"));
> bds.setPassword(datasourceProperties.getProperty("password"));
> bds.setUrl(datasourceProperties.getProperty("url"));
> bds.setMaxActive(new
> Integer(datasourceProperties.getProperty("maxActive")).intValue());
> bds.setInitialSize(new
> Integer(datasourceProperties.getProperty("initialSize")).intValue());
> bds.setMaxIdle(new
> Integer(datasourceProperties.getProperty("maxIdle")).intValue());
> bds.setMaxWait(new
> Integer(datasourceProperties.getProperty("maxWait")).intValue());
> bds.setTestOnBorrow(new
> Boolean(datasourceProperties.getProperty("testOnBorrow")).booleanValue());
> bds.setValidationQuery(datasourceProperties.getProperty("validationQuery"));
> System.out.println("MAXActive: " + bds.getMaxActive());
> System.out.println("MAXIdle: " + bds.getMaxIdle());
> System.out.println("NumActive: " + bds.getNumActive());
> System.out.println("NumIdle: " + bds.getNumIdle());
> return bds;
> }
> public static void parseConfigFile(Properties datasourceProperties, String
> filename, DefaultHandler handler, boolean validating) throws JDOMException,
> IOException {
> Document incomDoc =null;
> SAXBuilder builder=null;
> List childList=null;
> Element propItem=null;
> String propItemKey=null;
> String propItemVal=null;
> Attribute attribute = null;
> try {
> builder = new SAXBuilder(false);
> incomDoc = builder.build(filename);
>
> Element rootElement = incomDoc.getRootElement();
> childList = rootElement.getChildren();
> for (Iterator i = childList.iterator();i.hasNext();) {
> propItem = (Element)i.next();
> attribute=propItem.getAttribute("key");
> propItemKey = attribute.getValue();
> propItemVal=propItem.getText();
> datasourceProperties.setProperty(propItemKey, propItemVal);
> }
> } catch(Exception e)
> {
> e.printStackTrace();
> }
> }
> public static void printDataSourceStats(DataSource ds) throws SQLException {
> BasicDataSource bds = (BasicDataSource) ds;
> System.out.println("NumActive: " + bds.getNumActive());
> System.out.println("NumIdle: " + bds.getNumIdle());
> }
> public static void shutdownDataSource(DataSource ds) throws SQLException {
> BasicDataSource bds = (BasicDataSource) ds;
> bds.close();
> }
> }
>
>
>
>
> ---------------------------------
> Ahhh...imagining that irresistible "new car" smell?
> Check outnew cars at Yahoo! Autos.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------
Building a website is a piece of cake.
Yahoo! Small Business gives you all the tools to get online.
