How to setup Connection Pooling usinig Apache/Tomcat

Posted at Tuesday, April 08, 2008
Setting up connection pooling for Apache/Tomcat can be quite tricky. Here is an example of how to configure and test connection pooling using Apache/Tomcat 5.5.X, Java SDK 5.0, and JDBC drivers for both MySQL and Oracle data sources.

I first create the Global Naming Resources - references to the databases - in the $CATALINA_HOME/conf/server.xml configuration file. You will want to place these references within the GlobalNamingResources attribute tag:

<Resource name="jdbc/TestMySQL"
              auth="Container"
              factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory"
              type="javax.sql.DataSource"
              driverClassName="org.gjt.mm.mysql.Driver"
              url="jdbc:mysql://localhost:3307/mysql?autoReconnect=true"
              username="root"
              password="password"
              maxActive="100"
              maxIdle="20"
              maxWait="1000"
              removeAbandoned="true"
              logAbandoned="true"
              removeAbandonedTimeout="30"
     />

    <Resource name="jdbc/TestOracle"
              auth="Container"
              factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory"
              type="javax.sql.DataSource"
              driverClassName="oracle.jdbc.OracleDriver"
              url="jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=on)(ADDRESS=(PRO
TOCOL=TCP)(HOST=rac1-vip)(PORT=1525))(ADDRESS=(PROTOCOL=TCP)(HOST=rac2-vip)(PORT
=1525))(CONNECT_DATA=(SERVICE_NAME=jlc.colestock.test)))"
              username="test"
              password="password"
              maxActive="100"
              maxIdle="20"
              maxWait="1000"
              removeAbandoned="true"
              logAbandoned="true"
              removeAbandonedTimeout="30"
     />

As you can see I have added an entry for a MySQL database as well as an Oracle Clustered Database. If following this example, you will want to replace any applicable values with those specific to your environment.

In order to make the aformentioned resources available via the specified JDBC Drivers, the database-specific jars need to be added to $CATALINA_HOME/common/lib. In my case:

For MySQL:

# ls -1 /var/lib/apache-tomcat/common/lib/mysql-connector-java-5.1.5-bin.jar
/var/lib/apache-tomcat/common/lib/mysql-connector-java-5.1.5-bin.jar


For Oracle:

# ls -1 $CATALINA_HOME/common/lib/ojdbc5.jar
/var/lib/apache-tomcat/common/lib/ojdbc5.jar

After performing these steps, you should restart Tomcat per your pre-established process.

Next, I create a Java web application for each data source to test their respective connection pool. Note that these are simply test apps and aren't production-ready example that use proper design patterns and so forth.

Here is the source code for the test web applications, named mysqlcp and oraclecp:

mysqlcp.tar
oraclecp.tar

mysqlcp.tar

mysqlcp/
mysqlcp/docs/
mysqlcp/classes/
mysqlcp/lib/
mysqlcp/src/
mysqlcp/src/com/
mysqlcp/src/com/example/
mysqlcp/src/com/example/TestMySQL.java
mysqlcp/web/
mysqlcp/web/testmysql.jsp
mysqlcp/etc/
mysqlcp/etc/web.xml
mysqlcp/etc/context.xml
mysqlcp/build.xml
mysqlcp/build/
mysqlcp/build/WEB-INF/
mysqlcp/build/WEB-INF/web.xml
mysqlcp/build/WEB-INF/classes/
mysqlcp/build/WEB-INF/classes/com/
mysqlcp/build/WEB-INF/classes/com/example/
mysqlcp/build/WEB-INF/classes/com/example/TestMySQL.class
mysqlcp/build/WEB-INF/lib/
mysqlcp/build/META-INF/
mysqlcp/build/META-INF/context.xml
mysqlcp/build/testmysql.jsp
mysqlcp/dist/
mysqlcp/dist/docs/
mysqlcp/dist/mysqlcp.war
mysqlcp/build.properties

oraclecp.tar 

oraclecp/
oraclecp/docs/
oraclecp/classes/
oraclecp/lib/
oraclecp/src/
oraclecp/src/com/
oraclecp/src/com/example/
oraclecp/src/com/example/TestOracle.java
oraclecp/web/
oraclecp/web/testoracle.jsp
oraclecp/etc/
oraclecp/etc/web.xml
oraclecp/etc/context.xml
oraclecp/build.xml
oraclecp/build/
oraclecp/build/testoracle.jsp
oraclecp/build/WEB-INF/
oraclecp/build/WEB-INF/web.xml
oraclecp/build/WEB-INF/classes/
oraclecp/build/WEB-INF/classes/com/
oraclecp/build/WEB-INF/classes/com/example/
oraclecp/build/WEB-INF/classes/com/example/TestOracle.class
oraclecp/build/WEB-INF/lib/
oraclecp/build/META-INF/
oraclecp/build/META-INF/context.xml
oraclecp/dist/
oraclecp/dist/docs/
oraclecp/dist/oraclecp.war
oraclecp/build.properties


Each test application consists of a test connection class, a test jsp, as well as the requisite configuration files.

The applications' war files contain a context.xml configuration file, referencing the global resources put in server.xml; for example in the oraclecp.war:

<Context path="/oraclecp" docBase="oraclecp" debug="5" reloadable="true">
<ResourceLink global="jdbc/TestOracle" name="jdbc/TestOracle" type="javax.sql.Da
taSource"/>


In order to test these applications, simply unpack them and copy the war files to $CATALINA_HOME/webapps/. Tomcat will automcatically deploy both each application as well as its context, etc.

Once each application is deployed, call its test .jsp page. For MySQL, it should return a page listing the distinct names of users in mysql.user:



For Oracle, it should return a page listing the distinct names of users in sys.dba_users:



Labels: ,

How to Install Apache Tomcat on Linux

Posted at Thursday, April 03, 2008
This article assumes that your O/S already has a distribution of Apache.

Download and install the Java SDK compatible with your Tomcat version, for me: JDK 5.0

# mv jdk-1_5_0_15-nb-6_0_1-linux-ml.sh /var/lib/.
# cd /var/lib
# chmod u+x jdk-1_5_0_15-nb-6_0_1-linux-ml.sh
# export DISPLAY=192.168.1.2:0.0
# ./jdk-1_5_0_15-nb-6_0_1-linux-ml.sh

I install the version that comes with the NetBeans GUI, so I simply export
my DISPLAY, execute the script, and follow the installation wizard's prompts.

Afterwards, I create symbolic links as appropriate:

# ln -sf jdk1.5.0_15 java


# cd /usr/bin
# ln -sf /var/lib/java/bin/java java
# ln -sf /var/lib/java/bin/javac javac
# ln -sf /var/lib/java/bin/javadoc javadoc
# ln -sf /var/lib/java/bin/javah javah

Download and install Apache Tomcat, I will be installing Apache Tomcat 5.5.26

Create a User for tomcat:

# useradd tomcat
# usermod -g tomcat tomcat
# passwd tomcat

Extract binaries, change ownership, create symbolic links:

# tar xvfz apache-tomcat-5.5.26.tar.gz -C /var/lib/
# chown -R tomcat:tomcat apache-tomcat-5.5.26
# ln -sf apache-tomcat-5.5.26 apache-tomcat

Edit the tomcat user's .bash_profile, adding the following lines (Note: Ant will be installed later in this section):

export JAVA_HOME=/var/lib/java
export CATALINA_HOME=/var/lib/apache-tomcat
export ANT_HOME=/var/lib/apache-ant
export PATH=$JAVA_HOME/bin:$ANT_HOME/bin:$PATH

Build jsvc in order to run
Apache Tomcat as a service:

# su - tomcat
# cd $CATALINA_HOME/bin
# tar xvfz jsvc.tar.gz
# autoconf
# chmod u+x configure
# ./configure
# make
# cp jsvc ..

Install the following tomcat startup file to /etc/init.d/tomcat and edit as appropriate:

# chkconfig: 345 64 36
# description: Start Tomcat
#!/bin/sh
JAVA_HOME=/var/lib/java
CATALINA_HOME=/var/lib/apache-tomcat
DAEMON_HOME=/var/lib/apache-tomcat
TOMCAT_USER=tomcat

# for multi instances adapt those lines.
TMP_DIR=/var/tmp
PID_FILE=/var/run/jsvc.pid
CATALINA_BASE=/var/lib/apache-tomcat

CATALINA_OPTS="-Xms48m -Xmx256M"
CLASSPATH=\
$JAVA_HOME/lib/tools.jar:\
$CATALINA_HOME/bin/commons-daemon.jar:\
$CATALINA_HOME/bin/bootstrap.jar

case "$1" in
  start)
    #
    # Start Tomcat
    #
    $DAEMON_HOME/bin/jsvc \
    -user $TOMCAT_USER \
    -home $JAVA_HOME \
    -Dcatalina.home=$CATALINA_HOME \
    -Dcatalina.base=$CATALINA_BASE \
    -Djava.io.tmpdir=$TMP_DIR \
    -wait 10 \
    -pidfile $PID_FILE \
    -outfile $CATALINA_HOME/logs/catalina.out \
    -errfile '&1' \
    $CATALINA_OPTS \
    -cp $CLASSPATH \
    org.apache.catalina.startup.Bootstrap
    #
    # To get a verbose JVM
    #-verbose \
    # To get a debug of jsvc.
    #-debug \
    exit $?
    ;;

  stop)
    #
    # Stop Tomcat
    #
    $DAEMON_HOME/bin/jsvc \
    -stop \
    -pidfile $PID_FILE \
    org.apache.catalina.startup.Bootstrap
    exit $?
    ;;

  *)
    echo "Usage tomcat.sh start/stop"
    exit 1;;
esac

Configure the service via chkconfig and start:

# chkconfig --add tomcat
# chkconfig --list tomcat
tomcat          0:off   1:off   2:off   3:on    4:on    5:on    6:off


# service tomcat start


Configure Tomcat to allow use of the 'manager' application:

# vi $CATALINA_HOME/conf/tomcat-users.xml

Add the 'manager' role and a user of your choosing whom will be assigned this role: 

<role rolename="manager"/>

<user username="admin" password="admin" roles="manager"/>

Test Apache Tomcat by trying any of the various test URLs, including the manager application:







Download and Install Ant from a binary distribution of Ant

# tar xvfz apache-ant-1.7.0-bin.tar.gz -C /var/lib

# cd /var/lib
# ln -sf apache-ant-1.7.0/ apache-ant

Copy the required library to the Ant binaries:

# export CATALINA_HOME=/var/lib/apache-tomcat
# export ANT_HOME=/var/lib/apache-ant
# cp $CATALINA_HOME/server/lib/catalina-ant.jar $ANT_HOME/lib/.

Test the functionality of Ant by downloading and extracting this test application:

# cd /home/tomcat
# tar xvfz test.tar.gz
test/
test/docs/
test/src/
test/src/Test.java
test/web/
test/web/WEB-INF/
test/web/WEB-INF/test.xml
test/web/WEB-INF/web.xml
test/build.xml
test/build.properties

Test Ant by creating a distribution and deploying it to $CATALINA_HOME/webapps:

# cd /home/tomcat/test
# ant dist
Buildfile: build.xml

prepare:
    [mkdir] Created dir: /home/tomcat/test/build
    [mkdir] Created dir: /home/tomcat/test/build/WEB-INF
    [mkdir] Created dir: /home/tomcat/test/build/WEB-INF/classes
     [copy] Copying 2 files to /home/tomcat/test/build
    [mkdir] Created dir: /home/tomcat/test/build/WEB-INF/lib

compile:
    [javac] Compiling 1 source file to /home/tomcat/test/build/WEB-INF/classes

dist:
    [mkdir] Created dir: /home/tomcat/test/dist/docs
      [jar] Building jar: /home/tomcat/test/dist/test.war

BUILD SUCCESSFUL
Total time: 1 second


# cp ./dist/*.war $CATALINA_HOME/webapps/.

Test the deployment via:



Integrate Tomcat with existing Apache installation

Download the mod_jk that corresponds to your Apache version: mod_jk-1.2.26-httpd-2.0.61.so

# cd /usr/sbin/
# ./httpd -version
Server version: Apache/2.0.52
Server built:   Mar 19 2007 12:13:18

Move the .so library to the Apache modules directory:

# mv mod_jk-1.2.26-httpd-2.0.61.so /etc/httpd/modules/mod_jk.so

Create the workers.properties configuration file that will be used by the connector:

# cd $CATALINA_HOME/conf 
# vi workers.properties

workers.properties

workers.tomcat_home=/var/lib/apache-tomcat
workers.java_home=/var/lib/java
ps=/
worker.list=ajp13
worker.ajp13.port=8009
worker.ajp13.host=localhost
worker.ajp13.type=ajp13

Edit the httpd.conf to include this module along with corresponding directives:

# cd /etc/httpd/conf
# vi httpd.conf

Add the following lines:

LoadModule jk_module modules/mod_jk.so

JkWorkersFile /var/lib/apache-tomcat/conf/workers.properties
JkLogFile /etc/httpd/logs/mod_jk.log
JkLogLevel info
JkLogStampFormat "[%a %b %d %H:%M:%S %Y] "
JKMount /*.do ajp13
JKMount /*.jsp ajp13

Reload or stop Apache Service:

# service httpd start
Starting httpd:                                            [  OK  ]

Test the Connector by requesting the test application using Apache instead of Tomcat. If everything is working properly, Apache will hand off the request to Tomcat:



If successful, you have a working Apache Tomcat development environment.


Labels: ,

Connecting to MySQL via MySQL Connector/J

Posted at Saturday, February 23, 2008
MySQL comes with a variety of connectors that allows you to access MySQL databases from the programming environment of your choice.

The following is a sample Java class that utilizes the JDBC driver that comes with MySQL Connector/J. Of course this is just a sample class, which you can edit and compile to verify the efficacy of your installation. This does not represent production-ready code.

Download the Connector/J software

MySQL Connector/J Software Downloads 5.1

Gunzip and untar the distribution, extracting the following jar to the location of your choice:

# ls -1 /opt/mysql/*jar
/opt/mysql/mysql-connector-java-5.1.5-bin.jar

As you can see, after the gunzip and untar, I have opted to place the aforementioned jar file in /opt/mysql

Edit the following class, altering the connection information and the query it executes as appropriate:

MySQLJTest.java

import java.io.*;
import java.sql.*;
public class MySQLJTest {
  public static void main (String args[]){
    Statement stmt = null;
    ResultSet rs = null ;
    Connection conn = null;
    System.out.println("Beginning Test of MySQL Connector J");
    System.out.println("Loading Driver...");
       try {
         Class.forName("com.mysql.jdbc.Driver").newInstance();
       } catch (Exception e) {
         System.out.println("Exception Loading MySQL Connector J Driver");
         e.printStackTrace();
       }
    System.out.println("Obtaining Connection...");
       try {    
         conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3307/mysql?" + "user=oracle&password=password");
       } catch (SQLException se) {
         System.out.println("SQLException: " + se.getMessage());
         System.out.println("VendorError: " + se.getErrorCode());
       }
    System.out.println("Testing Connection with a query...");
        try { stmt = conn.createStatement();
        rs = stmt.executeQuery("SELECT User, Host, Password from user"); 
    System.out.println("Processing the ResultSet...");
        while (rs.next()) {
          System.out.println(rs.getString(1) + "\t" + rs.getString(2) + "\t" + rs.getString(3) + "\n");
        }
        } catch (SQLException se) { 
         System.out.println("SQLException: " + se.getMessage());
         System.out.println("VendorError: " + se.getErrorCode());
        } finally {
           if (rs != null) {
              try { rs.close();
              } catch (SQLException se) { }
              rs = null;
           }
          
           if (stmt != null) {
              try { stmt.close();
              } catch (SQLException se) { }
              stmt = null;
           }
        } //Ends Finally
  } //Ends Main Method
} //Ends Class Definition

After editing, you can use the following script to compile and run the program. Since, I am a heavy Oracle user, I have chosen to use the Java JDK that is in the databases' software home - you will want to modify the following script to account for the location of your JDK:

MySQLJTest.bsh

#!/bin/bash

JAVA_HOME=/u03/app/oracle/product/db/11.1.0.6/jdk
CLASSPATH=/opt/mysql/mysql-connector-java-5.1.5-bin.jar:.:$JAVA_HOME/jre/lib:$CLASSPATH

$JAVA_HOME/bin/javac -classpath $CLASSPATH MySQLJTest.java
$JAVA_HOME/jre/bin/java -version
$JAVA_HOME/jre/bin/java -classpath $CLASSPATH MySQLJTest

Once you have edited the scripts as appropriate, you can run the aforementioned script to compile and run the program:

# ./MySQLJTest.bsh
java version "1.5.0_11"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_11-b03)
Java HotSpot(TM) Client VM (build 1.5.0_11-b03, mixed mode)
Beginning Test of MySQL Connector J
Loading Driver...
Obtaining Connection...
Testing Connection with a query...
Processing the ResultSet...
root    localhost       5d2e19393cc5ef67

root    rac2.colestock.test     5d2e19393cc5ef67

root    127.0.0.1       5d2e19393cc5ef67

oracle  rac2.colestock.com      5d2e19393cc5ef67

oracle  localhost       5d2e19393cc5ef67

oracle  192.168.1.104   5d2e19393cc5ef67

oracle  127.0.0.1       5d2e19393cc5ef67

If successful, you should have output similar to mine.

Here are the source files from this posting: MySQLJTest.tar


Labels: ,

11g New Feature: (DRCP) Database Resident Connection Pooling

Posted at Monday, February 04, 2008
Oracle 11g introduces a Database Server-side Connection pooling feature which holds much promise.

A great overview of this new feature can be found in this excellent article:
Oracle Database 11g:
The Top New Features for DBAs and Developers
by Arup Nanda: Caching and Pooling

I followed Arup's example, and just extended upon it:

Here is the tnsnames.ora entry I created just for connections that are going to use the database resident connection pool (DRCP):

[/u03/app/oracle/product/db/11g/network/admin cubs2@rac2]$ tail -19 tnsnames.ora

CUBS_POOL.COLESTOCK.TEST =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1522))
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1522))
      (LOAD_BALANCE = yes)
    )
    (CONNECT_DATA =
      (SERVER = POOLED)
      (SERVICE_NAME = cubs.colestock.test)
      (FAILOVER_MODE =
        (TYPE = SELECT)
        (METHOD = BASIC)
        (RETRIES = 180)
        (DELAY = 5)
      )
    )
  )

Of course, I did this on both of my instances.

Next, I cut and pasted Arup's Java class and modified it for my purposes:

[/tmp cubs2@rac2]$ more CacheTest.java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class CacheTest {
  private String jdbcURL = "jdbc:oracle:oci8:@CUBS_POOL";
  private Connection conn = null;
  public CacheTest( ) throws ClassNotFoundException  {
    Class.forName("oracle.jdbc.driver.OracleDriver");
  }
  public static void main(String[] args)  throws ClassNotFoundException, SQLException {
    CacheTest check = new CacheTest();
    check.dbconnect();
    check.doSomething();
  }
  public void dbconnect() throws SQLException   {
    System.out.println("Connecting with URL="+jdbcURL+" as scott/tiger");
    try {
      conn = DriverManager.getConnection( jdbcURL, "scott" , "tiger");
      System.out.println("Connected to Database");
    } catch (SQLException sqlEx) {
      System.out.println(" Error connecting to database : " + sqlEx.toString());
    }
  }
  public void doSomething() throws SQLException   {
    Statement stmt = null;
    ResultSet rset = null;
    try {
      stmt = conn.createStatement();
      System.out.println("Created Statement object");
      rset = stmt.executeQuery("select /*+ result_cache */ count(*) from scott.dept");
      System.out.println("Retrieved ResultSet object");
      if(rset.next())
        System.out.println("Result:"+rset.getString(1));
    } catch (SQLException sqlEx)     {
    } finally  {
      try {
        System.out.println("Closing Statment & ResultSet Objects");
        if (rset != null) rset.close();
        if (stmt != null) stmt.close();
        if (conn != null) {
          System.out.println("Disconnecting...");
          conn.close();
          System.out.println("Disconnected from Database");
        }
      } catch (Exception e) { }
    }
  }
}

Then I compiled it:

[/tmp cubs2@rac2]$ export ORACLE_SID=cubs2
[/tmp cubs2@rac2]$ . oraenv
The Oracle base for ORACLE_HOME=/u03/app/oracle/product/db/11g is /u03/app/oracle
[/tmp cubs2@rac2]$ $ORACLE_HOME/jdk/bin/javac -verbose -classpath .:$ORACLE_HOME/jdbc/lib/jodbc5.jar CacheTest.java
[parsing started CacheTest.java]
[parsing completed 52ms]
[search path for source files: [.]]
[search path for class files: [/u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar, /u03/app/oracle/product/db/11g/jdk/jre/lib/jsse.jar, /u03/app/oracle/product/db/11g/jdk/jre/lib/jce.jar, /u03/app/oracle/product/db/11g/jdk/jre/lib/charsets.jar, /u03/app/oracle/product/db/11g/jdk/jre/lib/ext/localedata.jar, /u03/app/oracle/product/db/11g/jdk/jre/lib/ext/sunjce_provider.jar, /u03/app/oracle/product/db/11g/jdk/jre/lib/ext/sunpkcs11.jar, /u03/app/oracle/product/db/11g/jdk/jre/lib/ext/dnsns.jar, .]]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/sql/Connection.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/sql/DriverManager.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/sql/ResultSet.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/sql/SQLException.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/sql/Statement.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/lang/Object.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/lang/String.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/lang/ClassNotFoundException.class)]
[checking CacheTest]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/lang/Exception.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/lang/Throwable.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/lang/Class.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/lang/System.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/io/PrintStream.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/io/FilterOutputStream.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/io/OutputStream.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/util/Properties.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/lang/Error.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/lang/RuntimeException.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/lang/StringBuilder.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/lang/AbstractStringBuilder.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/lang/CharSequence.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/io/Serializable.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/lang/Comparable.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/lang/StringBuffer.class)]
[wrote CacheTest.class]
[total 423ms]

Now, the sample Java Class is ready to go. I created a wrapper script which calls this Class from a bash while loop:

[/tmp cubs2@rac2]$ more run_CacheTest.bsh
#!/bin/bash

export ORACLE_SID=cubs2;
. oraenv ;

while true
do
$ORACLE_HOME/jdk/bin/java -classpath /tmp/:.:$ORACLE_HOME/jdbc/lib/ojdbc5.jar CacheTest
done

Next, we have to start the default connection pool:

SQL> exec dbms_connection_pool.start_pool;

PL/SQL procedure successfully completed.

I placed one on these scripts on each of my database hosts and adjusted the $ORACLE_SID value as appropriate. I will call many instances of this script (using nohup to the background) from each database server to perform a load test; for example:

[/tmp cubs1@rac1]$ nohup /tmp/run_CacheTest.bsh &
[5] 24453
nohup: [/tmp cubs1@rac1]$ appending output to `nohup.out'

The combination of the DRCP as well as the results cache, should allow me to scale well beyond prior limits.

Here are the some of the results:

As you can see, I am simply using the default connection pool:

SQL> select pool_name, inst_id, num_open_servers, num_busy_servers, num_requests, num_waits, historic_max, num_purged from gv$cpool_stats;


POOL_NAME                         INST_ID NUM_OPEN_SERVERS NUM_BUSY_SERVERS NUM_REQUESTS  NUM_WAITS HISTORIC_MAX NUM_PURGED
------------------------------ ---------- ---------------- ---------------- ------------ ---------- ------------ ----------
SYS_DEFAULT_CONNECTION_POOL             1               14                2         2448          0           14          0
SYS_DEFAULT_CONNECTION_POOL             2               10                1          562          0           10          0


Note the high 'Find Count' for each instance:

SQL> select * from gv$result_cache_statistics order by name, inst_id;



   INST_ID         ID NAME                                          VALUE
---------- ---------- ---------------------------------------- ----------
         1          3 Block Count Current                              32
         2          3 Block Count Current                              32
         1          2 Block Count Maximum                            1728
         2          2 Block Count Maximum                            1728
         1          1 Block Size (Bytes)                             1024
         2          1 Block Size (Bytes)                             1024
         1          6 Create Count Failure                              0
         2          6 Create Count Failure                              0
         1          5 Create Count Success                              1
         2          5 Create Count Success                              3
         1          9 Delete Count Invalid                              0
         2          9 Delete Count Invalid                              0
         1         10 Delete Count Valid                                0
         2         10 Delete Count Valid                                0
         1          7 Find Count                                     3426
         2          7 Find Count                                     1292
         1          8 Invalidation Count                                0
         2          8 Invalidation Count                                0
         1          4 Result Size Maximum (Blocks)                     86
         2          4 Result Size Maximum (Blocks)                     86

As you can see, I was able to perform over a thousand executions on the cluster with virtual no I/O over an 11-minute period: 

select * from
  2  (select instance_number,  executions_total - lag(executions_total,1) over (order by instance_number, snap_id) "EXECUTIONS_TOTAL",snap_id
  3   from dba_hist_sqlstat
  4   where sql_id='1gy9qq7yjm054')
  5  where snap_id=96;


INSTANCE_NUMBER EXECUTIONS_TOTAL    SNAP_ID
--------------- ---------------- ----------
              1              662         96
              2              529         96

Here are the AWR reports from the test (both instances) - notice the efficiency:

CUBS1 Instance AWR Report
CUBS2 Instance AWR Report


Labels: , ,

How to Setup Oracle Connection Pooling with Oracle Application Server 10g using JNDI/JDBC

Posted at Tuesday, March 07, 2006
A previous post addresses setting up Oracle Connection Pools and the corresponding JNDI resource for Apache/Tomcat. This post aims to do the same, only this time, for Oracle's Application Server (10g OAS).

Assumptions:

A compatible JDK is already installed and included in your path. Likewise, it is assumed that %JAVA_HOME% is set and resides within your permanent System environment %PATH%. I used the JDK that came with JDeveloper for both the development and as the JDK the Standalone Oracle Application Server uses.

Setting up the Example:

Download Oracle Application Server Containers for J2EE Standalone from Oracle - I used version 10.1.3.0.0, build#060119.

Install Oracle Application Server Containers for J2EE Standalone by
referencing the Standalone User's Guide: Standalone User's Guide. Instead of relying upon the official documentation for the installation, I read the readme.txt file immediate after unzipping the distribution for detailed, distribution-specific installation information.

In my case (adjust accordingly for path differences, etc.):

mkdir c:\oc4j_standalone
cd c:\oc4j_standalone
unzip c:\temp\oc4j_extended_101300.zip
set JAVA_HOME=c:\jdev10g\jdk
set ORACLE_HOME=c:\oc4j_standalone
set PATH=%JAVA_HOME%\bin;%ORACLE_HOME%\bin;%PATH%
oc4j -start


You will receive the following prompt at the command line; enter and confirm password when prompted:

Enter password: ********
Confirm password: ********
Confirm password: The password for OC4J administrator "oc4jadmin" has been set.
06/03/07 15:08:59 The OC4J administrator "oc4jadmin" account is activated.
2006-03-07 15:09:11.959 NOTIFICATION JMS Router is initiating ...
06/03/07 15:09:14 Oracle Containers for J2EE 10g (10.1.3.0.0) initialized

Visit the index page -- in my case, http://localhost:8888/ -- to verify that the Standalone Application Server is functioning.

I created a test application using JDeveloper - first_j2ee - that can be used to validate your set-up (Note: Right-click 'Save Target As'):

Source ¦ first_j2ee.ear

Deploy the Sample Application via (adjust -file and password values as appropriate):

set ORACLE_HOME=c:\oc4j_standalone
set PATH=%ORACLE_HOME%\bin;%PATH%
cd j2ee
cd home
java -jar admin.jar ormi://localhost:23791 oc4jadmin password -deploy -file c:/temp/first_j2ee.ear -deploymentName first_j2ee
[ 2006-03-13 10:03:51.625 PST ] Application Deployer for first_j2ee STARTS.
[ 2006-03-13 10:03:51.672 PST ] Copy the archive to C:\oc4j_standalone\j2ee\home
\applications\first_j2ee.ear
[ 2006-03-13 10:03:51.719 PST ] Initialize C:\oc4j_standalone\j2ee\home\applications\first_j2ee.ear begins...
[ 2006-03-13 10:03:51.719 PST ] Unpacking first_j2ee.ear
[ 2006-03-13 10:03:51.812 PST ] Done unpacking first_j2ee.ear
[ 2006-03-13 10:03:51.812 PST ] Unpacking first_j2ee.war
[ 2006-03-13 10:03:51.922 PST ] Done unpacking first_j2ee.war
[ 2006-03-13 10:03:51.938 PST ] Initialize C:\oc4j_standalone\j2ee\home\applications\first_j2ee.ear ends...
[ 2006-03-13 10:03:51.938 PST ] Starting application : first_j2ee
[ 2006-03-13 10:03:51.938 PST ] Initializing ClassLoader(s)
[ 2006-03-13 10:03:51.938 PST ] Initializing EJB container
[ 2006-03-13 10:03:51.938 PST ] Loading connector(s)
[ 2006-03-13 10:03:52.219 PST ] Starting up resource adapters
[ 2006-03-13 10:03:52.219 PST ] Initializing EJB sessions
[ 2006-03-13 10:03:52.219 PST ] Committing ClassLoader(s)
[ 2006-03-13 10:03:52.219 PST ] Initialize first_j2ee begins...
[ 2006-03-13 10:03:52.234 PST ] Initialize first_j2ee ends...
[ 2006-03-13 10:03:52.234 PST ] Started application : first_j2ee
[ 2006-03-13 10:03:52.250 PST ] Application Deployer for first_j2ee COMPLETES. O peration time: 625 msecs


Next, bind the Sample Application:

java -jar admin.jar ormi://localhost:23791 oc4jadmin password -bindwebapp first_j2ee first_j2ee default-web-site /first_j2ee

Load the Static HTML Page in order to verify successful deployment and subsequent binding. The body of the page should read: 'The first_j2ee application has been deployed successfully!'.

I have packaged along with the application's source, the data-sources.xml file that you need to move and edit in order to accomplish this.

Extract and move the data-sources.xml file to your %ORACLE_HOME%\j2ee\home\application-deployments\first_j2ee directory. This is the *.xml file that defines the connection pool resource, etc. Modify the following elements/attributes for your environment:

  • url - to reflect your database service
  • username - to reflect the username you want to connect with (must have select on v$instance for this example to work)

  • password - to reflect the password for the aforementioned username

Once the aforementioned modificaions have been made, test via: http://localhost:8888/first_j2ee/TestJNDI. If successful, the page will return the value of the instance and host name for the database service you specified in data-sources.xml.



Labels: ,

How to Setup Oracle Connection Pooling with Apache Tomcat using JNDI/JDBC

Posted at Tuesday, February 28, 2006

In order to create truly scalable web applications that leverage a RDBMS, the use of connection-pooling is critical. Setting this up can be a daunting task since every application server/container combination is configured using slightly different methods. Current J2EE standards don't require truly universal server configuration files, deployment, etc. Throw into the mix JNDI (so that you don't have to hard-code authentication information, etc.); JDBC; Oracle, etc. and you can have a real head-scratcher on your hands. Adequate, comprehensive documentation that illustrates how to set this up from end-to-end is virtually non-existent - the vendors really do a poor job. The following example focuses on Apache/Tomcat against Oracle 8i or higher.

Assumptions:

  • You have an existing Oracle database and listener (in my case, a local database named repo.colestock.com)

  • You are using an adequately powered machine with a supported Windows O/S

Download and install a Java 2 SDK (Software Development Kit - not just the runtime!) compatible with your desired Apache/Tomcat release. In my example, I will be installing Apache/Tomcat 5.0.28, therefore I will be using Java SDK 1.4.2.11 - the most recent 1.4.X release at the time of this post:

http://java.sun.com/j2se/1.4.2/download.html

Choose the 'Windows Offline Installation' option, downloading the relevant *.exe.

Launch the installer *.exe and follow the prompts to install the SDK (annotate the install location - this will later become your %JAVA_HOME%)

Set the %JAVA_HOME% system environment variable by navigating to:
Start > Control Panel > System > Advanced (Environment Variables) and creating a new system variable named 'JAVA_HOME' and provide the path of the SDK location as its value (Example: 'C:\j2sdk1.4.2_11'). After words, add %JAVA_HOME%\bin to the front of the system's %PATH% variable. Apache/Tomcat will now be able to use your Java installation.

Download, unzip, and install the desired Apache/Tomcat version from http://tomcat.apache.org/download-55.cgi (I chose the *.zip associated with the 5.0.28 release). When installing, I normally perform a custom install and select the 'Service' option under the 'Tomcat' heading. Annotate the directory location in which you installed the software (i.e. %CATALINA_HOME%) as well as the password you provided for 'Administrator Login'.

After you have installed Tomcat, ensure that it is running by visiting the default page at: http://localhost:8080/. If Tomcat is not running, start the service via the command-line: net start "Apache Tomcat" (or via any other method you prefer).

The next step is to place the required *.jar files that include the Oracle JDBC drivers and other database libraries into the %CATALINA_HOME%\common\lib directory. Doing this makes the libraries globally available to all application as well as the server itself. Copy the following libraries from your %ORACLE_HOME%\jdbc\lib directory to %CATALINA_HOME%\common\lib:

  • ojdbc14.jar

After doing so, cycle Tomcat via your preferred method.

I created a test application using Ant - called first_j2ee - that can be used to validate your set-up (Note: Right-click 'Save Target As'):

Source ¦ first_j2ee.war

Save the *.war file to your local machine (renaming as necessary back to first_j2ee.war). The source is provided, just in case you want to get a closer look and/or if you wish to use it as the basis for your own test application - remember to edit the path information in the build.xml file, should you choose to use this code tree.

Tomcat includes a handy function, that automatically deploys any *.war file dropped in its %CATALINA_HOME%\webapps directory - this is how we will deploy our test application.

Copy first_j2ee.war to your %CATALINA_HOME%\webapps directory (Example: C:\Tomcat5.0\webapps).

Test that the application was deployed via http://localhost:8080/first_j2ee/test.html. A successful deployment will return the following String in the body: "The first_j2ee application has been deployed successfully!". Nothing fancy, granted, but you get the picture.

Now comes the tough part. We need to define in the application's context a connection pool resource and a resource reference to it. This will allow our test Servlet to lookup and obtain an Oracle database connection from an Application Server-managed connection pool. I have packaged along with the application's *.war file, the *.xml file that you need to move and edit in order to accomplish this.

Start by moving the %CATALINA_HOME%\webapps\WEB-INF\first_j2ee.xml file to the %CATALINA_HOME%\conf\Catalina\localhost directory. This is the *.xml file that defines the connection pool resource, etc. Modify the following elements/attributes for your environment:

  • url - to reflect your database service
  • username - to reflect the username you want to connect with (must have select on v$instance for this example to work)

  • password - to reflect the password for the aforementioned username

  • docBase attribute of Context - to reflect the directory path considering your %CATALINA_HOME%

Once the aforementioned modificaions have been made, test via: http://localhost:8080/first_j2ee/TestJNDI. If successful, the page will return the value of the instance and host name for the database service you specified in first_j2ee.xml.

If you have been successful thusfar, then you can work on tweaking/testing other connection pool directives as desired - the example connection pool does not have settings intended for a production environment.

Belatedly, I found an excellent article on the topic.

Labels: ,

How to Manually Deploy a WAR using OC4J (UNIX)

Posted at Friday, April 08, 2005
A how-to guide I drafted on deploying a Java Web Archive (.war) to an OC4J Container on a Unix distribution

Labels: ,


Categories

Previous Posts

Archives

Links


James Colestock's Facebook profile

View James Colestock's profile on LinkedIn

Google