Search This Blog

Thursday 25 February 2010

Accessing a Data Source Remotely in FMW 11g (11.1.1.2.0) from JDeveloper 11g (11.1.1.2.0)

I needed to access a data source remotely on FMW 11g server from JDeveloper 11g. I found that to do this I needed to follow these steps.

Note: This was done using 11.1.1.2 which is the latest version of FMW 11g and JDeveloper 11g

1. Edit setDomainEnv script to ensure you set this property to true. By default it's false.

WLS_JDBC_REMOTE_ENABLED="-Dweblogic.jdbc.remoteEnabled=true"

2. Re-start your server to pick up the change done at step #1 above.

3. In your project add the following libraries to allow remote access and the required JDBC driver library.
  • Weblogic 10.3 Remote-Client
  • Oracle JDBC
4. From JDeveloper access your data source remotely with code as follows making sure you use your connection details and the correct JNDI name for the data source.


package pas.au.remote.wls11g;

import java.sql.Connection;

import java.sql.SQLException;

import java.util.Date;
import java.util.Hashtable;
import java.util.logging.Level;
import java.util.logging.Logger;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;

import javax.sql.DataSource;

public class RemoteDataSourceAccess
{
private Logger logger = Logger.getLogger(this.getClass().getSimpleName());
public RemoteDataSourceAccess()
{
}

public void run ()
{
logger.log(Level.INFO, "Started RemoteDataSourceAccess at " + new Date());

InitialContext ctx = null;

try
{
ctx = getInitialContext();
DataSource ds = (DataSource) ctx.lookup("jdbc/scottDS");
Connection conn = (Connection) ds.getConnection();
logger.log(Level.INFO, "Got connection : " + conn);

logger.log(Level.INFO, "Auto Commit = " + conn.getAutoCommit());
}
catch (NamingException e)
{
logger.log(Level.SEVERE, "Error occurred", e);
System.exit(1);
}
catch (SQLException e)
{
logger.log(Level.SEVERE, "SQLException occurred", e);
System.exit(1);
}

logger.log(Level.INFO, "Ended RemoteDataSourceAccess at " + new Date());
}

public static InitialContext getInitialContext() throws NamingException
{
String JNDI_FACTORY="weblogic.jndi.WLInitialContextFactory";
String url = "t3://wayne-p2.au.oracle.com:7003";
String username = "weblogic";
String password = "welcome1";

Hashtable<String,String> env = new Hashtable<String,String>();
env.put(Context.SECURITY_PRINCIPAL, username);
env.put(Context.SECURITY_CREDENTIALS, password);
env.put(Context.INITIAL_CONTEXT_FACTORY, JNDI_FACTORY);
env.put(Context.PROVIDER_URL, url);
return new InitialContext(env);
}

public static void main(String[] args)
{

RemoteDataSourceAccess test = new RemoteDataSourceAccess();
test.run();
}
}

5. Output as follows.

25/02/2010 9:22:56 AM pas.au.remote.wls11g.RemoteDataSourceAccess run
INFO: Started RemoteDataSourceAccess at Thu Feb 25 09:22:56 EST 2010
25/02/2010 9:23:03 AM pas.au.remote.wls11g.RemoteDataSourceAccess run
INFO: Got connection : weblogic.jdbc.rmi.SerialConnection_weblogic_jdbc_rmi_internal_ConnectionImpl_weblogic_jdbc_wrapper_PoolConnection_oracle_jdbc_driver_T4CConnection_1032_WLStub@1
25/02/2010 9:23:03 AM pas.au.remote.wls11g.RemoteDataSourceAccess run
INFO: Auto Commit = true
25/02/2010 9:23:03 AM pas.au.remote.wls11g.RemoteDataSourceAccess run
INFO: Ended RemoteDataSourceAccess at Thu Feb 25 09:23:03 EST 2010

Wednesday 24 February 2010

Getting detailed logging for Universal Connection Pool (UCP) / Fast Connection Failover (FCF) Testing

While testing UCP/FCF I was having some issues with the RAC cluster setup. One useful way to determine exactly what is happening or not happening from the FAN events sent from RAC cluster to the pool was to setup logging as described below.

1. Create a properties file as follows.

# This is the sample logging properties file that configures
# loggers of some classes (FCF-related ones) to produce detailed logging
# (all levels) and just SEVERE and WARNING level messages for the rest
# of loggers. Console output via UCPFormatter (ODL-like messages).

handlers = java.util.logging.ConsoleHandler
java.util.logging.ConsoleHandler.level = ALL
java.util.logging.ConsoleHandler.formatter = oracle.ucp.util.logging.UCPFormatter

.level = WARNING

oracle.ucp.common.FailoverEventHandlerThreadBase.level = ALL

oracle.ucp.jdbc.oracle.ONSDatabaseEventHandlerThread.level = ALL
oracle.ucp.jdbc.oracle.ONSDatabaseFailoverEvent.level = ALL
oracle.ucp.jdbc.oracle.ONSOracleFailoverEventSubscriber.level = ALL
oracle.ucp.jdbc.oracle.OracleDatabaseInstanceInfo.level = ALL
oracle.ucp.jdbc.oracle.OracleDatabaseInstanceInfoList.level = ALL
oracle.ucp.jdbc.oracle.OracleFailoverEventImpl.level = ALL
oracle.ucp.jdbc.oracle.OracleFailoverEventNotification.level = ALL
oracle.ucp.jdbc.oracle.OracleFailoverEventSubscriber.level = ALL
oracle.ucp.jdbc.oracle.OracleFailoverHandler.level = ALL
oracle.ucp.jdbc.oracle.OracleFailoverablePooledConnection.level = ALL
oracle.ucp.jdbc.oracle.OracleConnectionConnectionPool.level = ALL
oracle.ucp.jdbc.oracle.OraclePooledConnectionConnectionPool.level = ALL
oracle.ucp.jdbc.oracle.OracleXAConnectionConnectionPool.level = ALL
oracle.ucp.jdbc.oracle.OracleJDBCConnectionPool.level = ALL
oracle.ucp.jdbc.oracle.OracleUniversalPooledConnection.level = ALL

2. Add the JVM command line option as follows - -Djava.util.logging.config.file=ucp_fcf_log.properties.

Then you should see some detailed output as follows. Very useful information in diagnosing UCP/FCF issues with your pool. Just showing small snippet as this is detailed output.

[java] 2010-02-24T10:44:16.734+1100 UCP FINE seq-103,thread-11 oracle.ucp.jdbc.oracle.ONSDatabaseEventHandlerThread.run event
triggered: Service name: orcl.apemrac.au.oracle.com, Instance name: orcl1, Unique name: orcl, Host name: apemrac1, Status: down,
Cardinality: 0, Reason: user, Event type: database/event/service
[java] 2010-02-24T10:44:16.734+1100 UCP FINEST seq-104,thread-11 oracle.ucp.jdbc.oracle.ONSDatabaseEventHandlerThread.run che
ck for events
[java] 2010-02-24T10:44:16.734+1100 UCP FINEST seq-105,thread-11 oracle.ucp.jdbc.oracle.OracleFailoverEventImpl.setEventType
eventType: database/event/service
[java] 2010-02-24T10:44:16.750+1100 UCP FINEST seq-106,thread-11 oracle.ucp.jdbc.oracle.OracleFailoverEventImpl.validateEvent
Type eventType: database/event/service
[java] 2010-02-24T10:44:16.750+1100 UCP FINEST seq-107,thread-11 oracle.ucp.jdbc.oracle.ONSDatabaseFailoverEvent. event
Type: database/event/service, eventBody: VERSION=1.0 service=HASERVICE.apemrac.au.oracle.com instance=orcl1 database=orcl host=ape
mrac1 status=down reason=failure
[java] 2010-02-24T10:44:16.765+1100 UCP FINEST seq-108,thread-11 oracle.ucp.jdbc.oracle.OracleFailoverEventImpl.setServiceNam
e serviceName: HASERVICE.apemrac.au.oracle.com
[java] 2010-02-24T10:44:16.765+1100 UCP FINEST seq-109,thread-11 oracle.ucp.jdbc.oracle.OracleFailoverEventImpl.setInstanceNa
me instanceName: orcl1
[java] 2010-02-24T10:44:16.765+1100 UCP FINEST seq-110,thread-11 oracle.ucp.jdbc.oracle.OracleFailoverEventImpl.setDbUniqueNa
me dbUniqueName: orcl
[java] 2010-02-24T10:44:16.765+1100 UCP FINEST seq-111,thread-11 oracle.ucp.jdbc.oracle.OracleFailoverEventImpl.setHostName h
ostName: apemrac1
[java] 2010-02-24T10:44:16.765+1100 UCP FINEST seq-112,thread-11 oracle.ucp.jdbc.oracle.OracleFailoverEventImpl.setStatus sta
tus: down
[java] 2010-02-24T10:44:16.781+1100 UCP FINEST seq-113,thread-11 oracle.ucp.jdbc.oracle.OracleFailoverEventImpl.setReason rea
son: failure
[java] 2010-02-24T10:44:16.781+1100 UCP FINEST seq-114,thread-11 oracle.ucp.jdbc.oracle.OracleJDBCConnectionPool.handleFailov
erEvent failover event: Service name: HASERVICE.apemrac.au.oracle.com, Instance name: orcl1, Unique name: orcl, Host name: apemrac
1, Status: down, Cardinality: 0, Reason: failure, Event type: database/event/service
[java] 2010-02-24T10:44:16.796+1100 UCP FINEST seq-115,thread-11 oracle.ucp.jdbc.oracle.OracleJDBCConnectionPool.handleFailov
erEvent service name HASERVICE.apemrac.au.oracle.com in event does not match that in the pool: haservice.apemrac.au.oracle.com. No
FCF attempt.

Tuesday 23 February 2010

Universal Connection Pool (UCP) / Fast Connection Failover (FCF)

I wanted to verify a UCP and how it works with FCF. I had previously used ICC/FCF which UCP/FCF now replaces. Development confirmed it is pretty much the same as explained below.

The basic usage model for FCF in UCP is the same as back in ICC. The user gets a Connection, performs JDBC operations on it, gets an exception from a RAC failure, reconnects and recovers if necessary. The main difference from ICC usage is the isValid() API that UCP provides, so that users do not need to check hard-coded exception error codes when deciding whether to reconnect.

So in short code as follows can now be added avoiding the need to check for error codes to determine if indeed a connection needs to reconnect.


public void run () throws SQLException
{
Connection conn = null;

try
{
counter++;
conn = pool.getConnection();
getInstanceDetails(conn, counter);
pool.displayDetails();
}
catch (SQLException sqle)
{
// The recommended way to check connection usability after a
// RAC-down event triggers UCP FCF actions.
if (conn == null || !((ValidConnection) conn).isValid())
{
logger.log
(Level.INFO,
"** FCFTest : Connection retry necessary : " + sqle.getMessage());

// Use UCP's FCF-specific statistics to verify the pool's
// FCF actions.
OracleJDBCConnectionPoolStatistics stats =
(OracleJDBCConnectionPoolStatistics) pool.getStats();

logger.log
(Level.INFO,
"** FCFTest : " + stats.getFCFProcessingInfo());
}
else
{
logger.log
(Level.SEVERE,
"** FCFTest : Exception occurred ->");
sqle.printStackTrace();
}
}
finally
{
try
{
pool.returnConnection(conn);
}
catch (SQLException se)
{
// not much we can do here
logger.log
(Level.INFO,
"** FCFTest : Exception detected when closing connection ");
se.printStackTrace();
}
}
}

Monday 8 February 2010

SCAN 11g R2 JDBC Load Balance Test

Now that I have a SCAN 11g R2 setup I was able to quickly verify the load balancing of connections using a SCAN URL as shown below. I find it very useful to have such a simple JDBC URL. No matter what nodes are added or removed I never have to alter my client JDBC URL again for this RAC cluster.

Output

Test Started at Mon Feb 08 13:59:21 EST 2010
Obtaining 5 connections

=============
Database Product Name is ... Oracle
Database Product Version is Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
=============
JDBC Driver Name is ........ Oracle JDBC driver
JDBC Driver Version is ..... 11.2.0.1.0
JDBC Driver Major Version is 11
JDBC Driver Minor Version is 2
=============
Connection #0 : instance[J11G22], host[auw2k4], service[J11G2]
Connection #1 : instance[J11G21], host[auw2k3], service[J11G2]
Connection #2 : instance[J11G22], host[auw2k4], service[J11G2]
Connection #3 : instance[J11G21], host[auw2k3], service[J11G2]
Connection #4 : instance[J11G22], host[auw2k4], service[J11G2]
Closing Connections
Test Ended at Mon Feb 08 13:59:22 EST 2010

Code
  
package au.support.jdbc.scan;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;

import java.sql.Statement;

import java.util.Date;

import oracle.jdbc.pool.OracleDataSource;

public class LoadBalanceTest
{
private OracleDataSource ods = null;
public final String userId = "scott";
public final String password = "tiger";

private static final String url =
"jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)" +
"(HOST=apctcsol1.au.oracle.com)(PORT=1521))" +
"(CONNECT_DATA=(SERVICE_NAME=J11G2)))";

public LoadBalanceTest() throws SQLException
{
ods = new OracleDataSource();
ods.setUser(userId);
ods.setPassword(password);
ods.setURL(url);
}

public Connection getConnection() throws SQLException
{
return ods.getConnection();
}

public void run () throws SQLException
{
Connection[] connArray = new Connection[5];

System.out.println("Obtaining 5 connections");
for (int i = 0; i < connArray.length; i++)
{
connArray[i] = getConnection();
}

for (int j = 0; j < connArray.length; j++)
{
if (j == 0)
{
DatabaseMetaData meta = connArray[j].getMetaData ();

// gets driver info:

System.out.println("\n=============\nDatabase Product Name is ... " +
meta.getDatabaseProductName());
System.out.println("Database Product Version is " +
meta.getDatabaseProductVersion());
System.out.println("=============\nJDBC Driver Name is ........ " +
meta.getDriverName());
System.out.println("JDBC Driver Version is ..... " +
meta.getDriverVersion());
System.out.println("JDBC Driver Major Version is " +
meta.getDriverMajorVersion());
System.out.println("JDBC Driver Minor Version is " +
meta.getDriverMinorVersion());
System.out.println("=============");
}

getInstanceDetails(connArray[j], j);
}

System.out.println("Closing Connections");
for (int y = 0; y < connArray.length; y++)
{
connArray[y].close();
}
}

public void getInstanceDetails (Connection conn, int i) throws SQLException
{
String sql =
"select sys_context('userenv', 'instance_name'), " +
"sys_context('userenv', 'server_host'), " +
"sys_context('userenv', 'service_name') " +
"from dual";

Statement stmt = conn.createStatement();
ResultSet rset = stmt.executeQuery(sql);
while (rset.next())
{
System.out.println
("Connection #" + i + " : instance[" + rset.getString(1) + "], host[" +
rset.getString(2) + "], service[" + rset.getString(3) + "]");
}

stmt.close();
rset.close();
}

public static void main(String[] args)
{
LoadBalanceTest loadBalanceTest;
try
{
System.out.println("Test Started at " + new Date());
loadBalanceTest = new LoadBalanceTest();
loadBalanceTest.run();
System.out.println("Test Ended at " + new Date());
}
catch (SQLException e)
{
e.printStackTrace();
System.exit(1);
}
}
}

Using SCAN - Single Client Access Name to Connect to 11g R2 RAC from JDeveloper 11g

The ability to have a single / simple connect string for a RAC cluster seemed like something worth trying which SCAN allows us to have as part of 11g R2. Trying to understand how SCAN works and it's setup was not what I had time for so I took an existing setup and verified I could connect from JDeveloper 11g without any issues.

The tnsnames.ora alias was defined as follows. As you can see there is nothing to suggest we are connecting to RAC here, but we are.

RAC11G2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = apctcsol1.au.oracle.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = J11G2)
)
)

Being able to connect from SQL*PLus was what I first tried and that worked fine.

d:\temp>sqlplus scott/tiger@RAC11G2

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Feb 8 09:15:59 2010

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


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

SCOTT@RAC11G2>
So from JDeveloper here it shows it can connect fine as well as expected.




















So from a JDBC client we would be connecting as follows ensuring we use a URL which indicates the use of service name as follows.

private static final String url =
"jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)" +
"(HOST=apctcsol1.au.oracle.com)(PORT=1521))" +
"(CONNECT_DATA=(SERVICE_NAME=J11G2)))";
We could also use a connect string as follows:

private static final String url = "jdbc:oracle:thin:@apctcsol1.au.oracle.com:1521/J11G2";
Here are some of the commands I ran to verify the SCAN setup and ports on one of the remote RAC instances. RAC Instances register to SCAN listeners as remote listeners.

Check we have SCAN listener configured

[oracle@auw2k3 ~]$ srvctl config scan_listener
SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1521
SCAN Listener LISTENER_SCAN2 exists. Port: TCP:1521
SCAN Listener LISTENER_SCAN3 exists. Port: TCP:1521

Check status of SCAN listeners

[oracle@auw2k3 ~]$ srvctl status scan_listener
SCAN Listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is running on node auw2k4
SCAN Listener LISTENER_SCAN2 is enabled
SCAN listener LISTENER_SCAN2 is running on node auw2k3
SCAN Listener LISTENER_SCAN3 is enabled
SCAN listener LISTENER_SCAN3 is running on node auw2k3

[oracle@auw2k3 ~]$ ps -aef | grep -i SCAN
oragrid 20168 1 0 Feb05 ? 00:00:12 /u01/app/11.2.0/grid/bin/tnslsnr LISTENER_SCAN2 -inherit
oragrid 20179 1 0 Feb05 ? 00:00:11 /u01/app/11.2.0/grid/bin/tnslsnr LISTENER_SCAN3 -inherit

Finally verify that the service I need to connect to existed on the SCAN listener

[oracle@auw2k3 ~]$ lsnrctl services LISTENER_SCAN2

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 08-FEB-2010 10:05:41

Copyright (c) 1991, 2009, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2)))
Services Summary...
Service "J10G" has 2 instance(s).
Instance "J10G1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
REMOTE SERVER
(ADDRESS=(PROTOCOL=TCP)(HOST=auw2k3-vip)(PORT=1521))
Instance "J10G2", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
REMOTE SERVER
(ADDRESS=(PROTOCOL=TCP)(HOST=auw2k4-vip)(PORT=1521))
Service "J10GXDB" has 2 instance(s).
Instance "J10G1", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER
(ADDRESS=(PROTOCOL=tcp)(HOST=auw2k3)(PORT=55880))
Instance "J10G2", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER
(ADDRESS=(PROTOCOL=tcp)(HOST=auw2k4)(PORT=31825))
Service "J10G_TAF" has 2 instance(s).
Instance "J10G1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
REMOTE SERVER
(ADDRESS=(PROTOCOL=TCP)(HOST=auw2k3-vip)(PORT=1521))
Instance "J10G2", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
REMOTE SERVER
(ADDRESS=(PROTOCOL=TCP)(HOST=auw2k4-vip)(PORT=1521))
Service "J10G_XPT" has 2 instance(s).
Instance "J10G1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
REMOTE SERVER
(ADDRESS=(PROTOCOL=TCP)(HOST=auw2k3-vip)(PORT=1521))
Instance "J10G2", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
REMOTE SERVER
(ADDRESS=(PROTOCOL=TCP)(HOST=auw2k4-vip)(PORT=1521))
Service "J11G2" has 2 instance(s).
Instance "J11G21", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
REMOTE SERVER
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=auw2k3-vip)(PORT=1521)))
Instance "J11G22", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
REMOTE SERVER
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=auw2k4-vip)(PORT=1521)))
Service "J11G2XDB" has 2 instance(s).
Instance "J11G21", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER
(ADDRESS=(PROTOCOL=tcp)(HOST=auw2k3)(PORT=63414))
Instance "J11G22", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER
(ADDRESS=(PROTOCOL=tcp)(HOST=auw2k4)(PORT=62891))
Service "sv1" has 2 instance(s).
Instance "J11G21", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
REMOTE SERVER
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=auw2k3-vip)(PORT=1521)))
Instance "J11G22", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
REMOTE SERVER
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=auw2k4-vip)(PORT=1521)))
The command completed successfully
[oracle@auw2k3 ~]$

Tuesday 2 February 2010

Using Database Change Notification (DCN) with a Coherence Cache

I needed to ensure my coherence cache which was storing a table from a database was kept in sync. To do that I used Database Change Notification (DCN) in the 11g JDBC driver with a 11.2 RDBMS. Few things I needed to ensure were as follows.

1. Firstly I made sure my DCN listener on the client which determines what refresh to perform on the cache does this using the Executor interface to call a runnable task in it's own thread. That is done to make sure if the operation takes time it's done in it's own thread and won't hold up the listener itself.


package support.au.coherence.dcn.server.db;

import java.util.concurrent.Executor;

import oracle.jdbc.dcn.DatabaseChangeEvent;
import oracle.jdbc.dcn.DatabaseChangeListener;
import oracle.jdbc.dcn.RowChangeDescription;
import oracle.jdbc.dcn.RowChangeDescription.RowOperation;
import oracle.jdbc.dcn.TableChangeDescription;

public class DCNListener implements DatabaseChangeListener
{
DeptDCNRegister demo;
DCNListener(DeptDCNRegister dem)
{
demo = dem;
}

public void onDatabaseChangeNotification
(DatabaseChangeEvent databaseChangeEvent)
{
System.out.println("DCNListener: got an event (" + this + ")");
System.out.println(databaseChangeEvent.toString());
TableChangeDescription [] tableChanges =
databaseChangeEvent.getTableChangeDescription();

for (TableChangeDescription tableChange : tableChanges)
{
RowChangeDescription[] rcds = tableChange.getRowChangeDescription();
for (RowChangeDescription rcd : rcds)
{
System.out.println("Affected row -> " +
rcd.getRowid().stringValue());
RowOperation ro = rcd.getRowOperation();

Executor executor = new DBExecutor();
String rowid = rcd.getRowid().stringValue();

if (ro.equals(RowOperation.INSERT))
{

System.out.println("INSERT occurred");
executor.execute(new HandleDBRefresh(rowid, "insert"));
}
else if (ro.equals(RowOperation.UPDATE))
{
System.out.println("UPDATE occurred");
executor.execute(new HandleDBRefresh(rowid, "update"));
}
else if (ro.equals(RowOperation.DELETE))
{
System.out.println("DELETE occurred");
executor.execute(new HandleDBRefresh(rowid, "delete"));
}
else
{
System.out.println("Only handling INSERT/DELETE/UPDATE");
}
}
}

synchronized( demo )
{
demo.notify();
}

}
}

2. The "DBExecutor" is defined as follows.


package support.au.coherence.dcn.server.db;

import java.util.concurrent.Executor;

public class DBExecutor implements Executor
{
public void execute(Runnable command)
{
new Thread(command).run();
}
}

3. The runnable class "HandleDBRefresh" is defined as follows.


package support.au.coherence.dcn.server.db;

import com.tangosol.net.CacheFactory;
import com.tangosol.net.CacheFactoryBuilder;
import com.tangosol.net.NamedCache;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import support.au.coherence.dcn.server.CacheHelper;
import support.au.coherence.dcn.server.Dept;

public class HandleDBRefresh implements Runnable
{
private DBConnectionManager connMgr = null;
private String rowId;
private String action;

public HandleDBRefresh()
{
}

public HandleDBRefresh(String rowId, String action)
{
super();
this.rowId = rowId;
this.action = action;
}

public void run()
{
PreparedStatement stmt = null;
ResultSet rset = null;
Connection conn = null;

try
{
connMgr = DBConnectionManager.getInstance();
if (!action.toLowerCase().equals("delete"))
{
conn = connMgr.getConnection();
stmt = conn.prepareStatement
("select rowid, deptno, dname from dept where rowid = ?");
stmt.setString(1, rowId);
rset = stmt.executeQuery();
rset.next();
}

CacheHelper cacheHelper = CacheHelper.getInstance();

// check if action
if (action.toLowerCase().equals("delete"))
{
cacheHelper.removeEntry(rowId);
System.out.println("Cache record delete");
}
else if (action.toLowerCase().equals("insert"))
{
// add to cache
if (rset != null)
{
Dept d = new Dept(rset.getInt(2), rset.getString(3));
cacheHelper.updateEntry(rset.getString(1), d);
System.out.println("Cache updated with new record");
}
}
else if (action.toLowerCase().equals("update"))
{
// refresh record in cache
if (rset != null)
{
Dept d = new Dept(rset.getInt(2), rset.getString(3));
cacheHelper.updateEntry(rset.getString(1), d);
System.out.println("Cache record updated");
}
}
}
catch (Exception e)
{
throw new RuntimeException
("Error updating cache: rowid [" + rowId + "] " + e);
}
finally
{
if (rset != null)
{
try
{
rset.close();
}
catch (SQLException se)
{
}
}

if (stmt != null)
{
try
{
stmt.close();
}
catch (SQLException se)
{
}
}

if (conn != null)
{
try
{
connMgr.returnConnection(conn);
}
catch (SQLException se)
{
}
}

}

}

public void setRowId(String rowId)
{
this.rowId = rowId;
}

public String getRowId()
{
return rowId;
}

public void setAction(String action)
{
this.action = action;
}

public String getAction()
{
return action;
}
}


4. "DeptDCNRegister" is defined as follows.

  
package support.au.coherence.dcn.server;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;

import java.sql.Statement;

import java.util.HashMap;
import java.util.Map;
import java.util.Properties;

import oracle.jdbc.OracleConnection;
import oracle.jdbc.OracleStatement;
import oracle.jdbc.dcn.DatabaseChangeRegistration;

@SuppressWarnings("unchecked")
public class DeptDCNRegister
{
private DBConnectionManager connMgr = null;
//private final String depSQL = "select * from dept";
private DatabaseChangeRegistration dcr = null;
private static DeptDCNRegister instance = null;

static
{
try
{
instance = new DeptDCNRegister();
}
catch (Exception e)
{
throw new RuntimeException("Error creating instance of DeptDCNRegister", e);
}
}

private DeptDCNRegister () throws SQLException
{
connMgr = DBConnectionManager.getInstance();
OracleConnection conn = (OracleConnection) connMgr.getConnection();
if (dcr == null)
{
registerDCN(conn);
}
}

public static DeptDCNRegister getInstance()
{
return instance;
}

private void registerDCN (OracleConnection conn) throws SQLException
{
/*
* register a listener for change notofication to be displayed to standard out
* for testing purposes
*/
Properties props = new Properties();
props.put(OracleConnection.DCN_NOTIFY_ROWIDS, "true");
props.put(OracleConnection.NTF_QOS_RELIABLE, "false");
props.setProperty(OracleConnection.DCN_BEST_EFFORT, "true");

dcr = conn.registerDatabaseChangeNotification(props);

// Add the dummy DCNListener which is DCNListener.java class
DCNListener list = new DCNListener(this);
dcr.addListener(list);

Statement stmt = conn.createStatement();
// Associate the statement with the registration.
((OracleStatement)stmt).setDatabaseChangeRegistration(dcr);
ResultSet rs = stmt.executeQuery("select * from dept where 1 = 2");
while (rs.next())
{
// do nothing no , need to just need query to register the DEPT table
}

String[] tableNames = dcr.getTables();
for(int i=0; i < tableNames.length; i++)
{
System.out.println(tableNames[i]+" successfully registered.");
}

// close resources
stmt.close();
rs.close();

}

public void closeDCN (OracleConnection conn) throws SQLException
{
conn.unregisterDatabaseChangeNotification(dcr);
conn.close();
}
}


5. Finally the key for Dept cache records is the ROWID at the database table level to ensure it's always unique not to mention that's what drives the specific operation so makes it easier this way. The ROWID is what is provided to the listener for the table changes so makes sense to use that as the KEY within the coherence cache.

I did give some thought into making the DCN listener run within the DB but loading the required JARS turned me off that idea. Wasn't sure how I could connect to a coherence cluster itself as storage disabled from within the DB itself to be honest. Otherwise this would of been a good option as the registration would of been active as long as the DB was running.