Tomcat 6.0 / UCP Data Source Setup

To setup tomcat to use UCP as it's data source implementation here are the steps. I used 11.2 JDBC driver and UCP jar files to connect to a 11.2.0.1 RDBMS.

1. Copy ojdbc6.jar and ucp.jar into $TOMCAT_HOME/lib directory. You can download those JAR files from here.

http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/index.html

2. Add your Data Source setup in $TOMCAT_HOME/conf/server.xml as follows.

<!-- Define the default virtual host
Note: XML Schema validation will not work with Xerces 2.2.
-->
<Host name="localhost"  appBase="webapps"
 unpackWARs="true" autoDeploy="true"
 xmlValidation="false" xmlNamespaceAware="false">

<Context docBase="demods" path="/demods" reloadable="true">
<Resource name="jdbc/UCPPool"
      auth="Container"
      factory="oracle.ucp.jdbc.PoolDataSourceImpl"
      type="oracle.ucp.jdbc.PoolDataSource"
      description="Pas testing UCP Pool in Tomcat"
      connectionFactoryClassName="oracle.jdbc.pool.OracleDataSource"
      minPoolSize="2"
      maxPoolSize="5"
      inactiveConnectionTimeout="20"
      user="scott"
      password="tiger"
      url="jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)
           (HOST=beast.au.oracle.com)(PORT=1523))(CONNECT_DATA=
           (SERVICE_NAME=linux11gr2)))"
      connectionPoolName="UCPPool"
      validateConnectionOnBorrow="true"
      sqlForValidateConnection="select 1 from DUAL" />
</Context>
</Host>

3. In your web projects you deploy to tomcat add the following to web.xml

<resource-ref>
  <res-ref-name>jdbc/UCPPool</res-ref-name>
  <res-type>javax.sql.DataSource</res-type>
  <res-auth>Container</res-auth>
</resource-ref>

4. Your code to lookup the data source would be as follows.

private DataSource getDataSource (String dataSourceLocation) throws NamingException
{
  // Get a context for the JNDI look up
  Context ctx = new InitialContext();
  Context envContext = (Context) ctx.lookup("java:/comp/env");

  // Look up a data source
  javax.sql.DataSource ds
   = (javax.sql.DataSource) envContext.lookup (dataSourceLocation);


return ds;
}

private Connection getConnection (DataSource ds) throws SQLException
{
  Connection conn = null;
  // Get a connection object
  conn = ds.getConnection();

  return conn;
}

Note: The dataSouceLocation would be "jdbc/UCPPool"

5. Finally verify using jconsole that indeed your using UCP. In the example below we are verifying that the pool named "UCPPool" is indeed created.

SQL*Plus Direct Connection URL

Note for myself, avoiding the use of a tnsnames.ora file to connect with.

sqlplus {username}/{password}@{hostname}:{port}/{service-name}

 
d:\temp>sqlplus scott/tiger@beast.au.oracle.com:1523/linux11gr2

SQL*Plus: Release 11.2.0.1.0 Production on Mon May 31 08:04:56 2010

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SCOTT@beast.au.oracle.com:1523/linux11gr2>

DBMS_MONITOR for Tracing Current Session from Oracle JDBC

Note for myself.

Turn On

begin DBMS_MONITOR.SESSION_TRACE_ENABLE (session_id => ?, serial_num => ?, waits => TRUE, binds => TRUE); end;

Turn Off

begin DBMS_MONITOR.SESSION_TRACE_DISABLE (session_id => ?, serial_num => ?); end;

In order to get the session_id and serial_num use a query as follows to dynamically determine that. This query can be used from Oracle 9i onwards

SCOTT@linux11gr2> SELECT dbms_debug_jdwp.current_session_id sid,
2  dbms_debug_jdwp.current_session_serial serial#
3  from dual;

    SID    SERIAL#
---------- ----------
    411       5395

SCOTT@linux11gr2>

JDBC Code as follows

  
  // hold session info for connected user
  private int serialNum;
  private int sessionId;

  private void turnOnSessionTrace (Connection conn) throws SQLException
  {
    String sql = 
      "SELECT dbms_debug_jdwp.current_session_id sid, " + 
      "dbms_debug_jdwp.current_session_serial serial#  " +
      "from dual";  
    String turnOnSQL = 
      "begin DBMS_MONITOR.SESSION_TRACE_ENABLE (session_id => ?, " + 
      "serial_num => ?, waits => TRUE, binds => TRUE); end;";

    Statement stmt = conn.createStatement();
    ResultSet rset = stmt.executeQuery(sql);
    rset.next();
    
    if (rset != null)
    {
      sessionId = rset.getInt(1);
      serialNum = rset.getInt(2);
    }
    
    System.out.println(
      String.format("Session Details : session_id=%s, serial_num=%s", 
                    sessionId, serialNum));
    
    OracleCallableStatement csmt = (OracleCallableStatement) conn.prepareCall(turnOnSQL);
    csmt.setInt(1, sessionId);
    csmt.setInt(2, serialNum);
    csmt.execute();
    
    stmt.close();
    rset.close();
    csmt.close();
  }

  private void turnOffSessionTrace (Connection conn) throws SQLException
  {
    String turnOffSQL = 
      "begin DBMS_MONITOR.SESSION_TRACE_DISABLE (session_id => ?, serial_num => ?); end;";
    
    OracleCallableStatement csmt = (OracleCallableStatement) conn.prepareCall(turnOffSQL);
    csmt.setInt(1, sessionId);
    csmt.setInt(2, serialNum);
    csmt.execute();   
  }  

Using Coherence*Web With the Monolithic Session Model

With the release of WLS 10.3.3 we now get the Coherence*Web Library deployed by default in our domains when we add configured them to use Oracle Enterprise Manger as described here. With that here are the steps to ensure your WAR projects use C*Web. In this demo we switch to the Monolithic model which is not the default Session model.

Note: This is a WAR based demo.

1. Create a weblogic.xml file in the WEB-INF directory of your web based project which references the C*web library

<?xml version = '1.0' encoding = 'windows-1252'?>
<weblogic-web-app
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.bea.com/ns/weblogic/weblogic-web-app http://www.bea.com/ns/weblogic/weblogic-web-app/1.0/weblogic-web-app.xsd"
xmlns="http://www.bea.com/ns/weblogic/weblogic-web-app">
 <library-ref>
  <library-name>coherence-web-spi</library-name>
  <specification-version>1.0.0.0</specification-version>
  <implementation-version>1.0.0.0</implementation-version>
  <exact-match>false</exact-match>
 </library-ref>       
</weblogic-web-app>

2. Edit the web.xml of your web based project to switch to Monolithic model.

<context-param>
  <param-name>coherence-sessioncollection-class</param-name>
  <param-value>com.tangosol.coherence.servlet.MonolithicHttpSessionCollection</param-value>
</context-param>

3. Finally ensure you place coherence.jar into your WEB-INF/lib directory

The following show what your project would look like in JDeveloper 11.1.1.3





















4. Once deployed we will see that we have swicthed to the new Session Model which is shown in the managed server log file at startup of the container or at the point when the application is started.


2010-05-13 07:11:37.680/37.754 Oracle Coherence 3.5.3/465 (thread=[ACTIVE] ExecuteThread: '0' for queue: 'weblogic.kernel.Default (self-tuning)', member=n/a): Loaded operational configuration from resource "zip:/home/oracle/product/11gR3/user_projects/domains/cweb_dom/servers/lemon/tmp/_WL_user/cohwebmonolithicmodel/5z88cr/war/WEB-INF/lib/coherence.jar!/tangosol-coherence.xml"
2010-05-13 07:11:37.687/37.761 Oracle Coherence 3.5.3/465 (thread=[ACTIVE] ExecuteThread: '0' for queue: 'weblogic.kernel.Default (self-tuning)', member=n/a): Loaded operational overrides from resource "zip:/home/oracle/product/11gR3/user_projects/domains/cweb_dom/servers/lemon/tmp/_WL_user/cohwebmonolithicmodel/5z88cr/war/WEB-INF/lib/coherence.jar!/tangosol-coherence-override-dev.xml"
2010-05-13 07:11:37.689/37.763 Oracle Coherence 3.5.3/465 (thread=[ACTIVE] ExecuteThread: '0' for queue: 'weblogic.kernel.Default (self-tuning)', member=n/a): Optional configuration override "/tangosol-coherence-override.xml" is not specified
2010-05-13 07:11:37.695/37.769 Oracle Coherence 3.5.3/465 (thread=[ACTIVE] ExecuteThread: '0' for queue: 'weblogic.kernel.Default (self-tuning)', member=n/a): Optional configuration override "/custom-mbeans.xml" is not specified

Oracle Coherence Version 3.5.3/465
Grid Edition: Development mode
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

2010-05-13 07:11:37.853/37.927 Oracle Coherence GE 3.5.3/465 (thread=[ACTIVE] ExecuteThread: '0' for queue: 'weblogic.kernel.Default (self-tuning)', member=n/a): Loaded cache configuration from "file:/home/oracle/product/11gR3/user_projects/domains/cweb_dom/servers/lemon/tmp/_WL_user/coherence-web-spi/bky8eh/WEB-INF/classes/session-cache-config.xml"
2010-05-13 07:11:38.607/38.681 Oracle Coherence GE 3.5.3/465 (thread=[ACTIVE] ExecuteThread: '0' for queue: 'weblogic.kernel.Default (self-tuning)', member=n/a): UnicastUdpSocket failed to set receive buffer size to 1428 packets (2096304 bytes); actual size is 92 packets (135168 bytes). Consult your OS documentation regarding increasing the maximum socket buffer size. Proceeding with the actual value may cause sub-optimal performance.
2010-05-13 07:11:38.925/38.999 Oracle Coherence GE 3.5.3/465 (thread=Cluster, member=n/a): Service Cluster joined the cluster with senior service member n/a
2010-05-13 07:11:42.158/42.232 Oracle Coherence GE 3.5.3/465 (thread=Cluster, member=n/a): Created a new cluster "cluster:0xDDEB" with Member(Id=1, Timestamp=2010-05-13 07:11:38.628, Address=10.187.81.36:8088, MachineId=57380, Location=site:au.oracle.com,machine:wayne-p2,process:16376, Role=WeblogicServer, Edition=Grid Edition, Mode=Development, CpuCount=2, SocketCount=2) UID=0x0ABB5124000001288E5BC584E0241F98
2010-05-13 07:11:42.204/42.278 Oracle Coherence GE 3.5.3/465 (thread=Invocation:Management, member=1): Service Management joined the cluster with senior service member 1
2010-05-13 07:11:43.622/43.696 Oracle Coherence GE 3.5.3/465 (thread=DistributedCache:DistributedSessions, member=1): Service DistributedSessions joined the cluster with senior service member 1
2010-05-13 07:11:43.683/43.757 Oracle Coherence GE 3.5.3/465 (thread=Invocation:SessionOwnership, member=1): Service SessionOwnership joined the cluster with senior service member 1
2010-05-13 07:11:43.709/43.783 Oracle Coherence GE 3.5.3/465 (thread=[ACTIVE] ExecuteThread: '0' for queue: 'weblogic.kernel.Default (self-tuning)', member=1): Configured session model "MonolithicHttpSessionCollection":
Clustered Session Cache Name=session-storage
Local Session Cache Name=local-session-storage
Local Session Attribute Cache Name=local-attribute-storage
Death Certificate Cache Name=session-death-certificates
SessionDistributionController Class Name=
AttributeScopeController Class Name=com.tangosol.coherence.servlet.AbstractHttpSessionCollection$ApplicationScopeController
Maximum Session Inactive Seconds=3600
Session ID Character Length=52
Member Session Locking Enforced=true
Application Session Locking Enforced=true
Thread Session Locking Enforced=false
Assume Session Locality for Reaping=false
Strict "Servlet Specification" Exception Handling=true
Sticky Session Ownership=true
Sticky Session Ownership Service Name=SessionOwnership
2010-05-13 07:11:43.725/43.799 Oracle Coherence GE 3.5.3/465 (thread=[ACTIVE] ExecuteThread: '0' for queue: 'weblogic.kernel.Default (self-tuning)', member=1): Registering HttpSessionManager(ServetContext=ServletContextWrapper (2.5)
SessionHelper!=null
MajorVersion=2
MinorVersion=5
Clustered=false
ServletContext (wrapped)=weblogic.servlet.internal.session.CoherenceWebServletContextWrapper@1f4d4bd
AttributeMap=null
Oracle Coherence 3.5.3/465) using object name "type=HttpSessionManager,nodeId=1,appId=cohwebmonolithicmodel!cohwebmonolithicmodel.war"



Also to start a coherence server which is storage enabled for the session management you would do it as follows. By default the WLS Coherence node is storage disabled. This is a unix script example.

export JAVA_HOME=/home/oracle/product/11gR3/jdk160_18
export COHERENCE_HOME=/home/oracle/product/coherence/353/coherence
export COH_OPTS="-server -classpath $COHERENCE_HOME/lib/coherence.jar:$COHERENCE_HOME/lib/coherence-web-spi.war"
export COH_OPTS="$COH_OPTS -Dtangosol.coherence.cacheconfig=/WEB-INF/classes/session-cache-config.xml
-Dtangosol.coherence.distributed.localstorage=true -Dtangosol.coherence.management.remote=true
-Dtangosol.coherence.session.localstorage=true"

java $COH_OPTS -Xms512m -Xmx512m com.tangosol.net.DefaultCacheServer

JDeveloper 11g - ADF Deployment using a Data Source defined in WLS itself

Typically when deploying ADF applications I will use a data source config defined at the WLS level and targeted to my managed server or cluster. I then deploy my ADF application from JDeveloper to that managed server or cluster however there are a few things to be aware of prior to deployment to ensure your using the correct Data Source. By default you won't be using the WLS defined Data Source if you not aware of a few things.

Note: For those who prefer to use a separate Data Source per application then out of the box you don't need to follow the steps below as it will do that for you by default. Also for our model ADFBC project we are assuming that it's configurtion is set to use "JDBC DataSource".

When your ready to deploy

1. Select "Applications -> Application Properties"
2. Click on "Deployment"

By default the "Auto generate and synchronize weblogic-jdbc.xml Descriptors during deployemnt" will be checked.

3. Uncheck that option we don't want to do that here as we already have a Data Source created in WLS.

4. Verify indeed that the weblogic-jdbc.xml is not part of the archive by selecting "Application -> Deploy -> .. to ear file"

Note: This show we no longer will bundle a weblogic-jdbc.xml file.

 
D:\jdev\jdevprod\11gr3\jdeveloper\jdev\mywork\ADFDemo\deploy>jar -tvf ADFDemo_application1.ear
4305851 Thu May 06 09:45:32 EST 2010 ADFDemo_ViewController_webapp1.war
  221 Thu May 06 09:45:34 EST 2010 META-INF/adfm.xml
  495 Thu May 06 09:45:34 EST 2010 META-INF/application.xml
 3933 Thu May 06 09:04:04 EST 2010 META-INF/cwallet.sso
  949 Thu May 06 09:04:02 EST 2010 META-INF/jps-config.xml
 1559 Thu May 06 09:03:20 EST 2010 META-INF/weblogic-application.xml
  745 Thu May 06 09:04:02 EST 2010 adf/META-INF/adf-config.xml
 1253 Thu May 06 09:04:04 EST 2010 adf/META-INF/connections.xml
  831 Thu May 06 09:05:10 EST 2010 adf/model/common/bc4j.xcfg
  273 Thu May 06 09:45:34 EST 2010 lib/adf-loc.jar

D:\jdev\jdevprod\11gr3\jdeveloper\jdev\mywork\ADFDemo\deploy>

5. Finally it's worth checking that indeed you not using a bundled data source so that you are actually defining the required properties for your data source in WLS console for the correct one.

Installing ADF runtime into FMW 11gR1 patchset 2

If you wish to use ADF with FMW 11gR1 pathset 2 (11.1.1.3) you will need to install it as follows into your stand alone FMW home.

Note: Assuming you already have Weblogic 10.3.3 installed.

1. Navigate to this web page which is where we need to download some files from.

http://www.oracle.com/technology/software/products/middleware/htdocs/fmw_11_download.html

2. Download and install "Application Development Runtime (11.1.1.2.0)" into your stand alone Weblogic 10.3.3

3. Now download and install "Application Development Runtime (11.1.1.3.0)" into your stand alone Weblogic 10.3.3.

4. Now run config.sh to create a new domain for ADF applications. I normally select "Oracle Enterprise Manager" which will then include the JRF required for ADF, as shown below.
















5. Then in EM itself apply the JRF template to the managed servers which will host ADF applications developed in JDeveloper 11.1.1.3.

- http://{server}:{port}/em

- Click the + symbol for "Weblogic Domain".
- Click on the + symbol for your domain.
- Select the managed server you wish to enable ADF applications for.
- Click on the button "Apply JRF Template".

6. Finally deploy an ADF app from JDeveloper 11.1.1.3 to verify the setup.

[09:22:08 AM] ---- Deployment started. ----
[09:22:08 AM] Target platform is (Weblogic 10.3).
[09:22:08 AM] Retrieving existing application information
[09:22:09 AM] Running dependency analysis...
[09:22:09 AM] Building...
[09:22:19 AM] Deploying 2 profiles...
[09:22:23 AM] Wrote Web Application Module to D:\jdev\jdevprod\11gr3\jdeveloper\jdev\mywork\ADFDemo\ViewController\deploy\ADFDemo_ViewController_webapp1.war
[09:22:25 AM] Wrote Enterprise Application Module to D:\jdev\jdevprod\11gr3\jdeveloper\jdev\mywork\ADFDemo\deploy\ADFDemo_application1.ear
[09:22:26 AM] Deploying Application...
[09:22:29 AM] [Deployer:149191]Operation 'deploy' on application 'ADFDemo_application1' is initializing on 'apple'
[09:22:36 AM] [Deployer:149192]Operation 'deploy' on application 'ADFDemo_application1' is in progress on 'apple'
[09:22:47 AM] [Deployer:149194]Operation 'deploy' on application 'ADFDemo_application1' has succeeded on 'apple'
[09:22:47 AM] Application Deployed Successfully.
[09:22:47 AM] The following URL context root(s) were defined and can be used as a starting point to test your application:
[09:22:47 AM] http://10.187.81.36:7003/adfdemo
[09:22:47 AM] Elapsed time for deployment: 39 seconds
[09:22:47 AM] ---- Deployment finished. ----

WITH Clause with Oracle JDBC

Went to a 2 day tuning course with Dan Hotka and he introduced me to the WITH SQL clause.

The WITH query_name clause lets you assign a name to a subquery block. You can then reference the subquery block multiple places in the query by specifying the query name. Oracle optimizes the query by treating the query name as either an inline view or as a temporary table

So I gave this a go and can see this helping out with LONG complicated queries , no need to add the same subquery over and over again, just use WITH AS. In short I tested this with Oracle JDBC , I knew it would work but just wanted to be sure.

SQL

  
SCOTT@linux11gr2> l
  1   with dept_a as (select /*+ materialize */ deptno from dept)
  2      select distinct e.deptno
  3      from dept_a d, emp e
  4      where e.deptno = d.deptno
  5      union
  6*     select deptno from dept_a d2

JDBC Code

  
  public void run() throws SQLException
  {
    Connection conn = null;
    Statement stmt = null;
    ResultSet rset = null;
    
    String SQL_WITH = 
      " with dept_a as (select /*+ materialize */ deptno from dept) " + 
      "    select distinct e.deptno " + 
      "    from dept_a d, emp e " + 
      "    where e.deptno = d.deptno " + 
      "    union " + 
      "    select d2.deptno from dept_a d2";
    
    try
    {
      conn = getConnection();
      stmt = conn.createStatement();
      rset = stmt.executeQuery(SQL_WITH);
      while (rset.next())
      {
        System.out.println(rset.getString(1));
      }
    }
    finally
    {
      if (rset != null)
        rset.close(); 

      if (stmt != null)
        stmt.close(); 
      
      if (conn != null)
        conn.close();   
      
      if (ods != null)
        ods.close();
    }
  }