The Blas from Pas

Automatic Client Failover within vFabric SQLFire

2012-01-28T22:26:00.000+11:00

If your connected to a SQLFire member who unexpectedly disapears or is shutdown SQLFire will automatically ensure that client swicthes to another server within the distributed system without the need to reconnect as demonstrated using SQLFire command line client "sqlf".

Lets assume we have started 2 SQLFire members as follows

sqlf server start -server-groups=MYGROUP -dir=server1 -client-port=1527 -mcast-port=12333 &
sqlf server start -server-groups=MYGROUP -dir=server2 -client-port=1528 -mcast-port=12333 &

1. Lets connect to the first member as shown below

[Sat Jan 28 22:14:59 papicella@:~/vmware/software/sqlfire/vFabric_SQLFire_101/pasdemos/afl2011 ] $ sqlf
sqlf version 10.4
sqlf> connect client 'localhost:1527';
sqlf>

2. Now lets ensure we are indeed connected to the first member as shown below.

sqlf> show connections;
CONNECTION0* -  jdbc:sqlfire://localhost:1527/
* = current connection
sqlf>

3. Lets run a query against a table in the distributed system

sqlf> show connections;
sqlf> select team_id, name, long_name from teams;
TEAM_ID    |NAME           |LONG_NAME                     
----------------------------------------------------------
18         |Giants         |GWS GIANTS                    
1          |Tigers         |Richmond                      
16         |Bulldogs       |Western Bulldogs              
12         |Power          |Port Adelaide                 
5          |Pies           |Collingwood                   
17         |Suns           |Gold Coast Suns               
4          |Blues          |Carlton                       
2          |Crows          |Adelaide Crows                
10         |Roos           |North Melbourne               
13         |Saints         |St. Kilda                     
8          |Cats           |Geelong Cats                  
14         |Swans          |Sydney Swans                  
15         |Eagles         |West Coast Eagles             
11         |Demons         |Melbourne                     
7          |Dockers        |Fremantle                     
9          |Hawks          |Hawthorn                      
3          |Lions          |Brisbane Lions                
6          |Bombers        |Essendon                      

18 rows selected
sqlf>

4. At this point we will shutdown the first SQLFire member who accepted the connection above using it's client port of 1527.

[Sat Jan 28 22:20:24 papicella@:~/vmware/software/sqlfire/vFabric_SQLFire_101/pasdemos/afl2011 ] $ sqlf server stop -dir=server1
The SQLFire Server has stopped.

5. Now lets re-run the query and verify we are still connected to the distributed system and can still display table data

sqlf> select team_id, name, long_name from teams;
TEAM_ID    |NAME           |LONG_NAME                     
----------------------------------------------------------
18         |Giants         |GWS GIANTS                    
1          |Tigers         |Richmond                      
16         |Bulldogs       |Western Bulldogs              
12         |Power          |Port Adelaide                 
5          |Pies           |Collingwood                   
17         |Suns           |Gold Coast Suns               
4          |Blues          |Carlton                       
2          |Crows          |Adelaide Crows                
10         |Roos           |North Melbourne               
13         |Saints         |St. Kilda                     
8          |Cats           |Geelong Cats                  
14         |Swans          |Sydney Swans                  
15         |Eagles         |West Coast Eagles             
11         |Demons         |Melbourne                     
7          |Dockers        |Fremantle                     
9          |Hawks          |Hawthorn                      
3          |Lions          |Brisbane Lions                
6          |Bombers        |Essendon                      

18 rows selected
sqlf>

6. Finally lets ensure we indeed did switch to the remaining member which accepts client connections on 1528

sqlf> show connections;
CONNECTION0* -  jdbc:sqlfire://localhost:1528/
* = current connection

For more information on SQLFire see the link below.

http://www.vmware.com/products/application-platform/vfabric-sqlfire/overview.html

Using a Connection Pool with SQLFire

2012-01-12T22:39:00.000+11:00

A Java application can use the JDBC thin driver to access a single member of a SQLFire cluster and execute SQL statements. This makes it a perfect candidate to use a connection pool to maintain connections for clients rather then connect to it individually. Two that spring to mind are Apache DBCP or Oracle's UCP. In this example we show a configuration using Spring and Oracle UCP which creates a pool of SQLFire connections which clients can then use as required.

1. Create a new spring bean application config file with a UCP defined as follows.

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd">

  <bean id="sqlfireUcpDataSource" class="oracle.ucp.jdbc.PoolDataSourceFactory" factory-method="getPoolDataSource">
    <property name="URL" value="jdbc:sqlfire://localhost:1527/" />
    <property name="connectionFactoryClassName" value="com.vmware.sqlfire.jdbc.ClientDriver" />
    <property name="connectionPoolName" value="SQLFIRE_UCP_POOL" />
    <property name="minPoolSize" value="5" />
    <property name="maxPoolSize" value="20" />
    <property name="initialPoolSize" value="5" />
  </bean>
  
</beans>

2. Create a test class as follows to verify the pool as shown below.

package vmware.au.sqlfire.pool;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Logger;

import oracle.ucp.jdbc.PoolDataSource;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class TestSQLFireJDBCPool 
{
 private Logger log = Logger.getLogger("com.vmware.sqlfire");
 private ApplicationContext context;
 private static final String BEAN_NAME = "sqlfireUcpDataSource";
 private PoolDataSource pds;
 
 public TestSQLFireJDBCPool() 
 {
  context = new ClassPathXmlApplicationContext("application-config-SQLFIRE.xml");
  pds = (PoolDataSource) context.getBean(BEAN_NAME);  
 }

 public void run()
 {
  Connection conn = null;
  List<Connection> connections = new ArrayList<Connection>();
  
  try 
  {
   // ensure pool is started by getting a connection object
   conn = pds.getConnection();
   log.info(conn.toString());
   conn.close();
   
   log.info("Getting 5 connections from pool");
   
   for (int i = 0; i < 5; i++)
   {
    connections.add(pds.getConnection());
   }
   
   log.info(displayPoolDetails());
   log.info("Closing all connections from pool");
   
   for (Connection connection: connections)
   {
     connection.close(); 
   }
   
   log.info(displayPoolDetails());
   
 
  } 
  catch (SQLException e) 
  {
   // TODO Auto-generated catch block
   e.printStackTrace();
  } 
  finally
  {
   connections = null;
  }
 }

    public String displayPoolDetails () throws SQLException
    {
      StringBuffer sb = new StringBuffer();
      
      sb.append("** Pool Details **\n");
      sb.append("NumberOfAvailableConnections: " +
                         pds.getAvailableConnectionsCount());
      sb.append("\nBorrowedConnectionsCount: " +
                         pds.getBorrowedConnectionsCount());
      sb.append("\n");
      
      return sb.toString();
    }
    
 /**
  * @param args
  */
 public static void main(String[] args) 
 {
  TestSQLFireJDBCPool test = new TestSQLFireJDBCPool();
  test.run();

 }

}

3. You will need to use the following JAR files such as Spring, Oracle UCP etc..

Output as follows:

Jan 12, 2012 10:11:54 PM org.springframework.context.support.AbstractApplicationContext prepareRefresh
INFO: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@657d5d2a: startup date [Thu Jan 12 22:11:54 EST 2012]; root of context hierarchy
Jan 12, 2012 10:11:54 PM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
INFO: Loading XML bean definitions from class path resource [application-config-SQLFIRE.xml]
Jan 12, 2012 10:11:55 PM org.springframework.beans.factory.support.DefaultListableBeanFactory preInstantiateSingletons
INFO: Pre-instantiating singletons in org.springframework.beans.factory.support.DefaultListableBeanFactory@62da3a1e: defining beans [sqlfireUcpDataSource]; root of factory hierarchy
Jan 12, 2012 10:11:55 PM vmware.au.sqlfire.pool.TestSQLFireJDBCPool run
INFO: $Proxy0@1dc18a4c
Jan 12, 2012 10:11:55 PM vmware.au.sqlfire.pool.TestSQLFireJDBCPool run
INFO: Getting 5 connections from pool
Jan 12, 2012 10:11:55 PM vmware.au.sqlfire.pool.TestSQLFireJDBCPool run
INFO: ** Pool Details **
NumberOfAvailableConnections: 0
BorrowedConnectionsCount: 5

Jan 12, 2012 10:11:55 PM vmware.au.sqlfire.pool.TestSQLFireJDBCPool run
INFO: Closing all connections from pool
Jan 12, 2012 10:11:55 PM vmware.au.sqlfire.pool.TestSQLFireJDBCPool run
INFO: ** Pool Details **
NumberOfAvailableConnections: 5
BorrowedConnectionsCount: 0

Using DBVisualizer to connect to a SQLFire cluster

2012-01-12T22:08:00.000+11:00

There are many database development visual tools out there which more often then not allow you to connect to the many different RDBMS databases including Oracle, MySQL, etc.. In this post here we use DBVisualizer to connect to SQLFire and show how easy you can view/manipulate your distributed SQLFire database from a visual tool.

Note: It's assumed you already have DbVisualizer installed and running.

First thing we need to do is add SQLFire driver JAR file to the list of drivers DBVisualizer can use.

1. Select Tools -> Driver Manager
2. Click the + symbol to define a new driver and enter details as shown below.

Select the com.vmware.sqlfire.jdbc.Driver class

Note: sqlfireclient.jar can be found in $SQLFIRE_HOME/lib directory

In order to use the client driver, you must specify a JDBC connection URL for your SQLFire distributed system. The basic URL format for the client driver is: jdbc:sqlfire://hostname:port/
where hostname and port correspond to the -client-bind-address and -client-port value of a SQLFire server or locator in your distributed system.

3. Close the window when done
4. Right click on "Connections" icon and select "Create Database Connection".
5. If promoted elect "Use Wizard"
6. Enter a connection name in this demo I enter -> pas-sqlfire
7. Select the database driver we created at step #2 which should be "SQLFire 1.0"
8. Click next
9. Enter in connection details as shown below.

If authentication is disabled, then you can specify any temporary username and password value into these fields.

Note: SQLFire uses the username specified in the JDBC connection as the schema name when you do not provide the schema name for a database object. SQLFire uses "APP" as the default schema. If your system does not enable authentication, you can specify "APP" for both the username and password to maintain consistency with the default schema behavior.

10. Click finish

The following shows a query we run to view the distributed database SQLFire members.

For more information on SQLFire see the link below.

http://www.vmware.com/products/application-platform/vfabric-sqlfire/overview.html

vFabric SQLFire - Modelling the classic DEPT/EMP tables

2011-12-26T22:41:00.001+11:00

VMware vFabric SQLFire is a memory-optimized, distributed database management system designed for applications that have demanding scalability and availability requirements. Here we will show a quick demo on how to get up and running using the classic DEPT/EMP RDBMS tables only this time in memory usig SQLFire.

Note: I's assumed SQLFire is installed in a directory as follows:

/Users/papicella/vmware/software/sqlfire/vFabric_SQLFire_10

1. Setup your environment as shown below. MAC example.

export PATH=/Users/papicella/vmware/software/sqlfire/vFabric_SQLFire_10/bin:$PATH

2. Ensure you have a valid JDK in your path as shown below

[Mon Dec 26 22:01:26 papicella@:~/vmware/software/sqlfire/vFabric_SQLFire_10/pasdemos/deptemp ] $ java -version
java version "1.6.0_26"
Java(TM) SE Runtime Environment (build 1.6.0_26-b03-383-11A511)
Java HotSpot(TM) 64-Bit Server VM (build 20.1-b02-383, mixed mode)

3. In your current directory created 2 folders called "server1" and "server2"

mkdir server1

mkdir server2

4. Start 2 servers as shown below they will discover each other using multicast port 12333

sqlf server start -dir=server1 -client-port=1527 -mcast-port=12333 &
sqlf server start -dir=server2 -client-port=1528 -mcast-port=12333 &

Output:

[Mon Dec 26 22:11:49 papicella@:~/vmware/software/sqlfire/vFabric_SQLFire_10/pasdemos/deptemp ] $ ./start-servers.sh
[Mon Dec 26 22:11:51 papicella@:~/vmware/software/sqlfire/vFabric_SQLFire_10/pasdemos/deptemp ] $ Starting SQLFire Server using multicast for peer discovery: 239.192.81.1[12333]
Starting network server for SQLFire Server at address localhost/127.0.0.1[1527]
Starting SQLFire Server using multicast for peer discovery: 239.192.81.1[12333]
Starting network server for SQLFire Server at address localhost/127.0.0.1[1528]
SQLFire Server pid: 1032 status: running
Distributed system now has 2 members.
Other members: 192-168-1-3.tpgi.com.au(1031:datastore):14506/50489
Logs generated in /Users/papicella/vmware/software/sqlfire/vFabric_SQLFire_10/pasdemos/deptemp/server2/sqlfserver.log
SQLFire Server pid: 1031 status: running
Distributed system now has 2 members.
Other members: 192-168-1-3.tpgi.com.au(1032:datastore):23687/50492
Logs generated in /Users/papicella/vmware/software/sqlfire/vFabric_SQLFire_10/pasdemos/deptemp/server1/sqlfserver.log

5. Connect as shown below

[Mon Dec 26 22:13:06 papicella@:~/vmware/software/sqlfire/vFabric_SQLFire_10/pasdemos/deptemp ] $ sqlf
sqlf version 10.4
sqlf> connect client 'localhost:1527';
sqlf>

6. In a separate terminal window create a file called "deptempPERSIST.sql" with contents as follows.

AUTOCOMMIT OFF;

create diskstore STORE1;

CREATE TABLE DEPT (
 DEPTNO INTEGER NOT NULL CONSTRAINT DEPTNO_PK PRIMARY KEY,
 DNAME VARCHAR(14),
 LOC VARCHAR(13))
 partition by column (deptno)
 persistent 'STORE1'
 REDUNDANCY 1;

INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH',   'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES',      'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');

CREATE TABLE EMP (
 EMPNO INTEGER NOT NULL CONSTRAINT EMP_PK Primary Key,
 ENAME VARCHAR(10),
 JOB VARCHAR(9),
 MGR INTEGER,
 HIREDATE DATE,
 SAL INTEGER,
 COMM INTEGER,
 DEPTNO INTEGER)
 partition by column (deptno)
 COLOCATE WITH (DEPT)
 persistent 'STORE1'
 REDUNDANCY 1;

INSERT INTO EMP VALUES
        (7369, 'SMITH',  'CLERK',     7902, '1980-12-17',  800, NULL, 20);
INSERT INTO EMP VALUES
        (7499, 'ALLEN',  'SALESMAN',  7698, '1981-02-21', 1600,  300, 30);
INSERT INTO EMP VALUES
        (7521, 'WARD',   'SALESMAN',  7698, '1981-02-22', 1250,  500, 30);
INSERT INTO EMP VALUES
        (7566, 'JONES',  'MANAGER',   7839, '1981-04-02',  2975, NULL, 20);
INSERT INTO EMP VALUES
        (7654, 'MARTIN', 'SALESMAN',  7698, '1981-09-28', 1250, 1400, 30);
INSERT INTO EMP VALUES
        (7698, 'BLAKE',  'MANAGER',   7839, '1981-05-01',  2850, NULL, 30);
INSERT INTO EMP VALUES
        (7782, 'CLARK',  'MANAGER',   7839, '1981-06-09',  2450, NULL, 10);
INSERT INTO EMP VALUES
        (7788, 'SCOTT',  'ANALYST',   7566, '1982-12-09', 3000, NULL, 20);
INSERT INTO EMP VALUES
        (7839, 'KING',   'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10);
INSERT INTO EMP VALUES
        (7844, 'TURNER', 'SALESMAN',  7698, '1981-09-08',  1500,    0, 30);
INSERT INTO EMP VALUES
        (7876, 'ADAMS',  'CLERK',     7788, '1983-01-12', 1100, NULL, 20);
INSERT INTO EMP VALUES
        (7900, 'JAMES',  'CLERK',     7698, '1981-12-03',   950, NULL, 30);
INSERT INTO EMP VALUES
        (7902, 'FORD',   'ANALYST',   7566, '1981-12-03',  3000, NULL, 20);
INSERT INTO EMP VALUES
        (7934, 'MILLER', 'CLERK',     7782, '1982-01-23', 1300, NULL, 10);

COMMIT;

ALTER TABLE EMP
   ADD CONSTRAINT EMP_FK Foreign Key (DEPTNO)
   REFERENCES DEPT (DEPTNO);

COMMIT;

7. Run the file "deptempPERSIST.sql" as shown below by returning to the terminal window at step #5

sqlf> run 'deptempPERSIST.sql';

8. Query and verify you now have DEPT / EMP tables with data as shown below.

sqlf> select * from dept;
DEPTNO     |DNAME         |LOC          
----------------------------------------
10         |ACCOUNTING    |NEW YORK     
20         |RESEARCH      |DALLAS       
30         |SALES         |CHICAGO      
40         |OPERATIONS    |BOSTON       

4 rows selected
sqlf> select * from emp;
EMPNO      |ENAME     |JOB      |MGR        |HIREDATE  |SAL        |COMM       |DEPTNO     
-------------------------------------------------------------------------------------------
7934       |MILLER    |CLERK    |7782       |1982-01-23|1300       |NULL       |10         
7782       |CLARK     |MANAGER  |7839       |1981-06-09|2450       |NULL       |10         
7839       |KING      |PRESIDENT|NULL       |1981-11-17|5000       |NULL       |10         
7902       |FORD      |ANALYST  |7566       |1981-12-03|3000       |NULL       |20         
7876       |ADAMS     |CLERK    |7788       |1983-01-12|1100       |NULL       |20         
7788       |SCOTT     |ANALYST  |7566       |1982-12-09|3000       |NULL       |20         
7369       |SMITH     |CLERK    |7902       |1980-12-17|800        |NULL       |20         
7566       |JONES     |MANAGER  |7839       |1981-04-02|2975       |NULL       |20         
7521       |WARD      |SALESMAN |7698       |1981-02-22|1250       |500        |30         
7499       |ALLEN     |SALESMAN |7698       |1981-02-21|1600       |300        |30         
7900       |JAMES     |CLERK    |7698       |1981-12-03|950        |NULL       |30         
7844       |TURNER    |SALESMAN |7698       |1981-09-08|1500       |0          |30         
7654       |MARTIN    |SALESMAN |7698       |1981-09-28|1250       |1400       |30         
7698       |BLAKE     |MANAGER  |7839       |1981-05-01|2850       |NULL       |30         

14 rows selected

9. Check how our DEPT/EMP tables stored within the distributed system.

sqlf> select substr(tablename, 1, 10) as tablename, datapolicy from sys.systables where tableschemaname='APP';
TABLENAME |DATAPOLICY              
-----------------------------------
EMP       |PERSISTENT_PARTITION    
DEPT      |PERSISTENT_PARTITION    

2 rows selected

Here the data is not only partitioned by also persisted to local disk to ensure it can be rehydrated when the members are brought down and re-started.

10. Verify where our data is stored and ensure that it's partioned by DEPTNO and that the EMP records are co located with the DEPT data.

sqlf> select substr(dsid(), 1, 48) as dsid, deptno, dname from dept;
DSID                                            |DEPTNO     |DNAME         
---------------------------------------------------------------------------
192-168-1-3.tpgi.com.au(1031)<v0>:14506/50489   |30         |SALES         
192-168-1-3.tpgi.com.au(1031)<v0>:14506/50489   |40         |OPERATIONS    
192-168-1-3.tpgi.com.au(1032)<v1>:23687/50492   |10         |ACCOUNTING    
192-168-1-3.tpgi.com.au(1032)<v1>:23687/50492   |20         |RESEARCH      

4 rows selected
sqlf> select substr(dsid(), 1, 48) as dsid, deptno, ename from emp;
DSID                                            |DEPTNO     |ENAME     
-----------------------------------------------------------------------
192-168-1-3.tpgi.com.au(1031)<v0>:14506/50489   |30         |WARD      
192-168-1-3.tpgi.com.au(1031)<v0>:14506/50489   |30         |ALLEN     
192-168-1-3.tpgi.com.au(1031)<v0>:14506/50489   |30         |JAMES     
192-168-1-3.tpgi.com.au(1031)<v0>:14506/50489   |30         |TURNER    
192-168-1-3.tpgi.com.au(1031)<v0>:14506/50489   |30         |MARTIN    
192-168-1-3.tpgi.com.au(1031)<v0>:14506/50489   |30         |BLAKE     
192-168-1-3.tpgi.com.au(1032)<v1>:23687/50492   |10         |MILLER    
192-168-1-3.tpgi.com.au(1032)<v1>:23687/50492   |10         |CLARK     
192-168-1-3.tpgi.com.au(1032)<v1>:23687/50492   |10         |KING      
192-168-1-3.tpgi.com.au(1032)<v1>:23687/50492   |20         |FORD      
192-168-1-3.tpgi.com.au(1032)<v1>:23687/50492   |20         |ADAMS     
192-168-1-3.tpgi.com.au(1032)<v1>:23687/50492   |20         |SCOTT     
192-168-1-3.tpgi.com.au(1032)<v1>:23687/50492   |20         |SMITH     
192-168-1-3.tpgi.com.au(1032)<v1>:23687/50492   |20         |JONES     

14 rows selected

For more information on vFabric SQLFire visit the link below.

http://www.vmware.com/products/application-platform/vfabric-sqlfire/overview.html

GemFire Write-Behind with an Oracle RDBMS

2011-12-12T14:13:00.000+11:00

Many in memory data fabric's use an Oracle RDBMS to persist data and make it available as required. In this demo we explore an effective way to use GemFire write-behind functionality to an Oracle RDBMS and strategies around how to increase throughput. GemFire's Write-Behind technology simply translates what in-memory data management does uniquely well (low-latency/high throughput of small data updates) to what disk-based data management does best (higher latency/high throughput of large data updates).

Here is an example config of such a setup.

cache XML file

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE cache PUBLIC
    "-//GemStone Systems, Inc.//GemFire Declarative Caching 6.6//EN" 
"http://www.gemstone.com/dtd/cache6_6.dtd">
<cache>
  <gateway-hub id="DBWriterHub" port="-1" startup-policy="none">
     <gateway id="DBWriter">
        <gateway-listener>
           <class-name>pas.au.gemfire.demo.cachewriter.DBGatewayListener</class-name>
        </gateway-listener>
        <!-- 10 seconds limit that can elapse between sending batches of up to 1000 -->
        <gateway-queue batch-size="5000" batch-time-interval="10000"/>
     </gateway>
  </gateway-hub>
  <region name="firstRegion" refid="PARTITION_REDUNDANT"> 
    <region-attributes enable-gateway="true" hub-id="DBWriterHub">
      <eviction-attributes>
        <lru-heap-percentage action="overflow-to-disk" />
      </eviction-attributes>
    </region-attributes>
  </region>
  <function-service>
 <function>
   <class-name>pas.au.gemfire.demo.cachewriter.SizeFunction</class-name>
 </function>
  </function-service> 
  <resource-manager critical-heap-percentage="75" eviction-heap-percentage="65"/>
</cache>

Gateway Listener Code

package pas.au.gemfire.demo.cachewriter;

import com.gemstone.gemfire.cache.Declarable;
import com.gemstone.gemfire.cache.Operation;
import com.gemstone.gemfire.cache.util.GatewayEvent;
import com.gemstone.gemfire.cache.util.GatewayEventListener;

import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import pas.au.gemfire.demo.cachewriter.dao.jdbcbatch.JdbcBatch;
import pas.au.gemfire.demo.cachewriter.dao.jdbcbatch.JdbcBatchDAO;
import pas.au.gemfire.demo.cachewriter.dao.jdbcbatch.JdbcBatchDAOImpl;

public class DBGatewayListener implements GatewayEventListener, Declarable 
{
  private Logger logger = Logger.getLogger(this.getClass().getSimpleName());
  private ApplicationContext context;
  private static final String BEAN_NAME = "jdbcBatchDAO";
  private JdbcBatchDAO jdbcBatchDAO;

  public DBGatewayListener()
  {
    context = new ClassPathXmlApplicationContext("application-config.xml");
    jdbcBatchDAO = (JdbcBatchDAOImpl) context.getBean(BEAN_NAME);
  }

  @Override
  public boolean processEvents(List<GatewayEvent> list)
  {
    logger.log (Level.INFO, String.format("Size of List<GatewayEvent> = %s", list.size()));
    List<JdbcBatch> newEntries = new ArrayList<JdbcBatch>();
    
    List<JdbcBatch> updatedEntries = new ArrayList<JdbcBatch>();
    List<String> destroyedEntries = new ArrayList<String>();
    @SuppressWarnings("unused")
 int possibleDulicates = 0;
    
    for (GatewayEvent ge: list)
    {
      
      if (ge.getPossibleDuplicate())
       possibleDulicates++;
       
      if ( ge.getOperation().equals(Operation.UPDATE)) 
      {
     updatedEntries.add((JdbcBatch) ge.getDeserializedValue());
      }
      else if ( ge.getOperation().equals(Operation.CREATE))
      {
        newEntries.add((JdbcBatch) ge.getDeserializedValue());
      }
      else if ( ge.getOperation().equals(Operation.DESTROY))
      {
     destroyedEntries.add(ge.getKey().toString());
      }
     
    }
    
    if (newEntries.size() > 0)
    {
     jdbcBatchDAO.storeInsertBatch(newEntries); 
    }
    
    if (updatedEntries.size() > 0)
    {
     jdbcBatchDAO.storeUpdateBatch(updatedEntries);
    }
    
    if (destroyedEntries.size() > 0)
    {
     jdbcBatchDAO.storeDeleteBatch(destroyedEntries);
    }
    
    logger.log (Level.INFO, 
          String.format("New Entries = [%s], Updated Entries = [%s], Destroyed Entries = [%s], Possible Duplicates = [%s]", 
                  newEntries.size(), 
                  updatedEntries.size(), 
                  destroyedEntries.size(), 
                  possibleDulicates));
    
    return true;
  }

  @Override
  public void close()
  {
  }

  @Override
  public void init(Properties properties)
  {
  }
}

For more information on vFabric GemFire use the link below.

http://www.vmware.com/products/application-platform/vfabric-gemfire/overview.html

Running GFMon on a 64bit MAC OSX

2011-11-23T12:05:00.000+11:00

Here are the quick setup steps to run GFMon 26 on a 64 bit MAC OSX operating system.

1. Change WS (osgi.ws [windowing system]) in the gfmon command from gtk to carbon

2. Add the -d32 switch

3. Add the -XstartOnFirstThread switch

The command in the gfmon script should look like with the changes in BOLD:

${GF_JAVA:-java} -d32 -XstartOnFirstThread -Xms256m -Xmx512m ${JAVA_ARGS} -Xbootclasspath/a:"$GEMFIRE/lib/gemfire.jar" -jar "$gfmon/plugins/org.eclipse.equinox.launcher_1.0.101.R34x_v20080819.jar" -WS carbon $@

4. Copy the following jar (from Eclipse ganymede) to the gfmon plugins directory:

$GFMON_HOME/plugins/org.eclipse.swt.carbon.macosx_3.4.1.v3449c.jar

Accessing GemFire Regions from Jython

2011-11-14T08:35:00.001+11:00

My previous blog entry showed how to access GemFire Regions from JRuby. This demo below accesses the same regions , this time using jython. The code has identical java calls with the obvious jython syntax rather then JRuby. Here I just show the jython script and output.

Before we run the script we need to set the classpath correctly to pick up the GemFire JAR's as well as the JAR file required to access the Region.

export CUR_DIR=`pwd`
export CLASSPATH=$CUR_DIR/lib/antlr.jar:$CUR_DIR/lib/gemfire.jar:$CUR_DIR/lib/gemfire-quickstart.jar

jython script - gemfire-caching-proxy-client.jy

from java.util import Date
from java.util import Collection
from java.util import Iterator

from vmware.au.se.demo.domain import AllDBObject 
  
from com.gemstone.gemfire.cache import Region 
from com.gemstone.gemfire.cache.client import ClientCache
from com.gemstone.gemfire.cache.client import ClientCacheFactory

from com.gemstone.gemfire.cache.query import Query  
from com.gemstone.gemfire.cache.query import QueryService
from com.gemstone.gemfire.cache.query import SelectResults

print "*********************************"  
print "GemFire 6.6 Example from JYTHON"  
print "*********************************"  
  
print "Started at " + Date().toString()  
  
try:  
  
 ccf = ClientCacheFactory()
 ccf.set("cache-xml-file", "client.xml")  
 cache = ccf.create() 

 allObjectRegion = cache.getRegion("AllObjectRegion")  
 queryService = cache.getQueryService()  
 
 query = queryService.newQuery("SELECT * FROM /AllObjectRegion where owner = 'SCOTT'")  
 print "\n** All OBJECTS with owner = 'SCOTT'\n"  

 result = query.execute() 
 collection = result.asList()
 iter = collection.iterator()
 i = 0  
 
 while iter.hasNext():
  i = i + 1  
  entry = iter.next()
  print "Entry " , i , " : " , entry
  
except:  
    print "Unexpected error:", sys.exc_info()[0]  
    raise  
      
finally:  
    cache.close()
     
print "Ended at " + Date().toString()

Output as follows

Pas-Apicellas-MacBook-Pro:quickstart-client papicella$ jython gemfire-caching-proxy-client.jy 
*********************************
GemFire 6.6 Example from JYTHON
*********************************
Started at Mon Nov 14 08:02:06 EST 2011

[info 2011/11/14 08:02:07.033 EST <main> tid=0x1] 

...

  udp-recv-buffer-size="1048576"
  udp-send-buffer-size="65535"
  writable-working-dir=""
  

[info 2011/11/14 08:02:07.045 EST <main> tid=0x1] Running in local mode since mcast-port was 0 and locators was empty.

[info 2011/11/14 08:02:07.113 EST <Thread-3 StatSampler> tid=0x15] Disabling statistic archival.

[info 2011/11/14 08:02:07.380 EST <poolTimer-client-2> tid=0x1a] AutoConnectionSource discovered new locators [/10.117.85.62:41111]

[config 2011/11/14 08:02:07.381 EST <main> tid=0x1] Updating membership port.  Port changed from 0 to 49,335.

[config 2011/11/14 08:02:07.443 EST <main> tid=0x1] Pool client started with multiuser-authentication=false

[info 2011/11/14 08:02:07.444 EST <main> tid=0x1] Overridding MemoryPoolMXBean heap threshold bytes 0 on pool CMS Old Gen with 352,321,536

[info 2011/11/14 08:02:07.447 EST <main> tid=0x1] Overridding MemoryPoolMXBean heap threshold bytes 352,321,536 on pool CMS Old Gen with 352,321,536

[info 2011/11/14 08:02:07.447 EST <Cache Client Updater Thread  on Pas-Apicellas-MacBook-Pro(434)<v2>:38304/49301> tid=0x1c] Cache Client Updater Thread  on Pas-Apicellas-MacBook-Pro(434)<v2>:38304/49301 (10.117.85.62:49311) : ready to process messages.

[config 2011/11/14 08:02:07.527 EST <main> tid=0x1] Cache initialized using "jar:file:/Users/papicella/vmware/scripting/demos/jython/gemfire/quickstart-client/lib/gemfire-quickstart.jar!/client.xml".

** All OBJECTS with owner = 'SCOTT'

Entry  1  :  AllDBObject [owner=SCOTT, objectName=BONUS, objectId=74213, objectType=TABLE, status=VALID]
Entry  2  :  AllDBObject [owner=SCOTT, objectName=SALGRADE, objectId=74214, objectType=TABLE, status=VALID]
Entry  3  :  AllDBObject [owner=SCOTT, objectName=PK_EMP, objectId=74212, objectType=INDEX, status=VALID]
Entry  4  :  AllDBObject [owner=SCOTT, objectName=PK_DEPT, objectId=74210, objectType=INDEX, status=VALID]
Entry  5  :  AllDBObject [owner=SCOTT, objectName=DEPT, objectId=74209, objectType=TABLE, status=VALID]
Entry  6  :  AllDBObject [owner=SCOTT, objectName=EMP, objectId=74211, objectType=TABLE, status=VALID]
Entry  7  :  AllDBObject [owner=SCOTT, objectName=JDBC_BATCH_TABLE, objectId=75753, objectType=TABLE, status=VALID]

[info 2011/11/14 08:02:07.742 EST <main> tid=0x1] GemFireCache[id = 1069736291; isClosing = true; created = Mon Nov 14 08:02:07 EST 2011; server = false; copyOnRead = false; lockLease = 120; lockTimeout = 60]: Now closing.

[info 2011/11/14 08:02:07.771 EST <main> tid=0x1] Resetting original MemoryPoolMXBean heap threshold bytes 0 on pool CMS Old Gen

[config 2011/11/14 08:02:07.803 EST <main> tid=0x1] Destroying connection pool client
Ended at Mon Nov 14 08:02:07 EST 2011

Accessing GemFire Regions from JRuby

2011-11-09T12:19:00.000+11:00

Here is a quick demo showing how easy it is to access GemFire regions from JRuby clients. It's assumed you have cache servers started and in this example the cache servers and JRuby client use a locator for connection. It's worth noting the JRuby client is setup as a PROXY client with no client side cache.

Our cache server nodes use a config file as follows - server.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE cache PUBLIC
    "-//GemStone Systems, Inc.//GemFire Declarative Caching 6.6//EN" 
"http://www.gemstone.com/dtd/cache6_6.dtd">
<cache>
  <disk-store name="ds1" auto-compact="true" max-oplog-size="1024" queue-size="10000" time-interval="15">
 <disk-dirs>
  <disk-dir dir-size="4096">persistData</disk-dir>
 </disk-dirs>
  </disk-store>
  <region name="AllObjectRegion"> 
    <region-attributes refid="PARTITION_PERSISTENT" disk-store-name="ds1">
      <partition-attributes redundant-copies="1" />
      <eviction-attributes>
        <lru-heap-percentage action="overflow-to-disk" />
      </eviction-attributes>
    </region-attributes>
    <index name="ownerIdx">
      <functional from-clause="/AllObjectRegion" expression="owner"/>
    </index>
  </region>
  <function-service>
 <function>
   <class-name>vmware.au.se.demo.SizeFunction</class-name>
 </function>
  </function-service> 
  <resource-manager critical-heap-percentage="75" eviction-heap-percentage="65"/>
</cache>

Our JRuby client uses a client config as follows - client.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE client-cache PUBLIC
    "-//GemStone Systems, Inc.//GemFire Declarative Caching 6.6//EN" 
"http://www.gemstone.com/dtd/cache6_6.dtd"> 
<client-cache>
 <pool name="client" subscription-enabled="true">
     <locator host="localhost" port="41111"/>
 </pool>
 <!-- No cache storage in the client region because of the PROXY client region shortcut setting. -->
    <region name="AllObjectRegion">
  <region-attributes refid="PROXY" />
    </region>
    <resource-manager critical-heap-percentage="75" eviction-heap-percentage="65"/>
</client-cache>

Our JRuby code is as follows - gemfire-caching-proxy-client.rb

require 'java'
require File.dirname(__FILE__) + '/lib/gemfire.jar'
require File.dirname(__FILE__) + '/lib/antlr.jar'
require File.dirname(__FILE__) + '/lib/gemfire-quickstart.jar'

import java.util.Collection;
import java.util.Iterator;

import "vmware.au.se.demo.domain.AllDBObject";

import com.gemstone.gemfire.cache.Region;
import com.gemstone.gemfire.cache.client.ClientCache;
import com.gemstone.gemfire.cache.client.ClientCacheFactory;
import com.gemstone.gemfire.cache.query.Query;
import com.gemstone.gemfire.cache.query.QueryService;
import com.gemstone.gemfire.cache.query.SelectResults;

puts "*********************************************************"
puts "GemFire 6.6 Proxy Client Example from JRUBY"
puts "*********************************************************"

print "Started at ", Time.now, "\n"

begin

 ccf = ClientCacheFactory.new
 ccf.set("cache-xml-file", "client.xml")
 cache = ccf.create
 
 allObjectRegion = cache.getRegion("AllObjectRegion")
 queryService = cache.getQueryService
 
 query = queryService.newQuery("SELECT * FROM /AllObjectRegion where owner = 'SCOTT'")
 print "\n** All OBJECTS with owner = 'SCOTT'\n"
 
 result = query.execute
 collection = result.asList
 iter = collection.iterator
 i = 0
 
 while iter.hasNext
   i = i + 1
   entry = iter.next
   print "Entry ", i , " : " , entry , " \n"
   
 end
 
    cache.close
    
rescue 
  print "\n** Error occured **\n"
  print "Failed to obtian data from gemfire region ", $!, "\n\n"
  
end

print "\nEnded at ", Time.now, "\n"

Output as follows

Note: Some of the gemfire output is omitted to make it a little more readable.

Pas-Apicellas-MacBook-Pro:quickstart-client papicella$ jruby gemfire-caching-proxy-client.rb 
*********************************************************
GemFire 6.6 Proxy Client Example from JRUBY
*********************************************************
Started at Wed Nov 09 12:10:29 +1100 2011

[info 2011/11/09 12:10:29.319 EST <main> tid=0x1] 
  ---------------------------------------------------------------------------
  
....

  udp-recv-buffer-size="1048576"
  udp-send-buffer-size="65535"
  writable-working-dir=""
  

[info 2011/11/09 12:10:29.335 EST <main> tid=0x1] Running in local mode since mcast-port was 0 and locators was empty.

[info 2011/11/09 12:10:29.392 EST <Thread-1 StatSampler> tid=0xf] Disabling statistic archival.

[info 2011/11/09 12:10:29.645 EST <poolTimer-client-2> tid=0x14] AutoConnectionSource discovered new locators [/10.117.85.62:41111]

[config 2011/11/09 12:10:29.646 EST <poolTimer-client-3> tid=0x15] Updating membership port.  Port changed from 0 to 50,522.

[config 2011/11/09 12:10:29.669 EST <main> tid=0x1] Pool client started with multiuser-authentication=false

[info 2011/11/09 12:10:29.671 EST <main> tid=0x1] Overridding MemoryPoolMXBean heap threshold bytes 0 on pool CMS Old Gen with 344,064,000

[info 2011/11/09 12:10:29.673 EST <main> tid=0x1] Overridding MemoryPoolMXBean heap threshold bytes 344,064,000 on pool CMS Old Gen with 344,064,000

[info 2011/11/09 12:10:29.673 EST <Cache Client Updater Thread  on Pas-Apicellas-MacBook-Pro(2183)<v2>:37232/49977> tid=0x16] Cache Client Updater Thread  on Pas-Apicellas-MacBook-Pro(2183)<v2>:37232/49977 (10.117.85.62:49987) : ready to process messages.

[config 2011/11/09 12:10:29.753 EST <main> tid=0x1] Cache initialized using "jar:file:/Users/papicella/vmware/scripting/demos/jruby/gemfire/quickstart-client/./lib/gemfire-quickstart.jar!/client.xml".

** All OBJECTS with owner = 'SCOTT'
Entry 1 : AllDBObject [owner=SCOTT, objectName=EMP, objectId=74211, objectType=TABLE, status=VALID] 
Entry 2 : AllDBObject [owner=SCOTT, objectName=PK_EMP, objectId=74212, objectType=INDEX, status=VALID] 
Entry 3 : AllDBObject [owner=SCOTT, objectName=JDBC_BATCH_TABLE, objectId=75753, objectType=TABLE, status=VALID] 
Entry 4 : AllDBObject [owner=SCOTT, objectName=PK_DEPT, objectId=74210, objectType=INDEX, status=VALID] 
Entry 5 : AllDBObject [owner=SCOTT, objectName=BONUS, objectId=74213, objectType=TABLE, status=VALID] 
Entry 6 : AllDBObject [owner=SCOTT, objectName=SALGRADE, objectId=74214, objectType=TABLE, status=VALID] 
Entry 7 : AllDBObject [owner=SCOTT, objectName=DEPT, objectId=74209, objectType=TABLE, status=VALID] 

[info 2011/11/09 12:10:29.962 EST <main> tid=0x1] GemFireCache[id = 1691463635; isClosing = true; created = Wed Nov 09 12:10:29 EST 2011; server = false; copyOnRead = false; lockLease = 120; lockTimeout = 60]: Now closing.

[info 2011/11/09 12:10:29.986 EST <main> tid=0x1] Resetting original MemoryPoolMXBean heap threshold bytes 0 on pool CMS Old Gen

[config 2011/11/09 12:10:30.015 EST <main> tid=0x1] Destroying connection pool client

Ended at Wed Nov 09 12:10:30 +1100 2011

Viewing All MBeans for a GemFire Cluster

2011-11-07T15:12:00.000+11:00

In order to view all the MBeans available in a GemFire cluster you could write a simple console client application as follows. This will extract all the MBeans by name and hence work out which one you want to take a look at.

1. First we need to start a JMX agent. The JMX Agent can be run as a separate, “invisible” distributed system member, or it can be collocated in an application. The JMX Agent manages only a single distributed system.

Here we use a located to join the distributed system

> agent start -J-Xmx550m -dir=agent mcast-port=0 locators=localhost[41111]

2. By default the JMX service URL is as follows

service:jmx:rmi://{agent-host}/jndi/rmi://:{agent-port}/jmxconnector

3. Write a Java Client as follows

package pas.au.gemfire.jmx;

import java.io.IOException;
import java.net.MalformedURLException;
import java.util.Set;

import javax.management.MBeanServerConnection;
import javax.management.ObjectName;
import javax.management.remote.JMXConnector;
import javax.management.remote.JMXConnectorFactory;
import javax.management.remote.JMXServiceURL;

public class ShowAllMBeans 
{
    /** The JMX client connector */
    private JMXConnector jmxConnector;

    /** The JMX MBean server connection */
    private MBeanServerConnection mbs;
    
    public void run()
    {
     String urlString = "service:jmx:rmi://localhost/jndi/rmi://:1099/jmxconnector";
     JMXServiceURL url = null;
     
     try 
     {
   url = new JMXServiceURL(urlString);
   jmxConnector = JMXConnectorFactory.connect(url);
   mbs = jmxConnector.getMBeanServerConnection();
   
   // show all MBeans here...
   Set<ObjectName> mbeans = mbs.queryNames(null, null);
   System.out.println(mbeans.size() + " MBeans found\n");
      for (ObjectName mbeanName : mbeans) 
      {
          System.out.println("-> " + mbeanName);
      }   
   
  } 
     catch (MalformedURLException e) 
     {
   // TODO Auto-generated catch block
   e.printStackTrace();
  } 
     catch (IOException e) 
     {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
     finally
     {
      disconnectFromAgent();
     }
     
     System.out.println("all done...");
    }

 private void disconnectFromAgent()
 {
     if(this.jmxConnector != null) 
     {
       try 
       {
   this.jmxConnector.close();
       } 
       catch (IOException e) 
       {
   // TODO Auto-generated catch block
   e.printStackTrace();
       }
     }
 }
 
 /**
  * @param args
  */
 public static void main(String[] args) 
 {
  // TODO Auto-generated method stub
  ShowAllMBeans test = new ShowAllMBeans();
  test.run();
 }

}

4. Output as follows

292 MBeans found

-> GemFire.Statistic:source=10.117.85.62(23883)-32249/50418,type=IndexStats,name=ownerIdx,uid=130
-> GemFire.Statistic:source=10.117.85.62(23883)-32249/50418,type=IndexStats,name=ownerIdx,uid=131
-> GemFire.Statistic:source=10.117.85.62(23883)-32249/50418,type=IndexStats,name=ownerIdx,uid=132
-> GemFire.Statistic:source=10.117.85.62(23883)-32249/50418,type=IndexStats,name=ownerIdx,uid=133
-> GemFire.Statistic:source=10.117.85.62(23883)-32249/50418,type=IndexStats,name=ownerIdx,uid=134
-> GemFire.Statistic:source=10.117.85.62(23883)-32249/50418,type=IndexStats,name=ownerIdx,uid=135
-> GemFire.Statistic:source=10.117.85.62(23876)-41281/50410,type=IndexStats,name=ownerIdx,uid=115
-> GemFire.Statistic:source=10.117.85.62(23876)-41281/50410,type=IndexStats,name=ownerIdx,uid=116
-> GemFire.Statistic:source=10.117.85.62(23876)-41281/50410,type=IndexStats,name=ownerIdx,uid=113
-> GemFire.Statistic:source=10.117.85.62(23876)-41281/50410,type=IndexStats,name=ownerIdx,uid=114
-> GemFire.Statistic:source=10.117.85.62(23876)-41281/50410,type=IndexStats,name=ownerIdx,uid=111
-> GemFire.Statistic:source=10.117.85.62(23876)-41281/50410,type=IndexStats,name=ownerIdx,uid=112
-> GemFire.Statistic:source=10.117.85.62(23883)-32249/50418,type=DLockStats,name=dlockStats,uid=18
-> connectors:protocol=rmi
-> GemFire.Statistic:source=10.117.85.62(23876)-41281/50410,type=IndexStats,name=ownerIdx,uid=110
-> GemFire.Statistic:source=10.117.85.62(23876)-41281/50410,type=IndexStats,name=ownerIdx,uid=119
-> GemFire.Statistic:source=10.117.85.62(23876)-41281/50410,type=IndexStats,name=ownerIdx,uid=117
-> GemFire.Statistic:source=10.117.85.62(23876)-41281/50410,type=IndexStats,name=ownerIdx,uid=118
-> GemFire.Statistic:source=10.117.85.62(23876)-41281/50410,type=VMMemoryPoolStats,name=Par Eden Space-Heap memory,uid=7
-> GemFire.Statistic:source=10.117.85.62(23876)-41281/50410,type=DiskRegionStatistics,name=/AllObjectRegion,uid=20
-> GemFire:type=MemberInfoWithStatsMBean
-> GemFire.Cache:name=default,id=1558631662,owner=10.117.85.62(23876)-41281/50410,type=Cache
-> GemFire.Statistic:source=10.117.85.62(23876)-41281/50410,type=DLockStats,name=dlockStats,uid=18
-> GemFire.Statistic:source=10.117.85.62(23876)-41281/50410,type=VMStats,name=vmStats,uid=3
-> GemFire.Statistic:source=10.117.85.62(23876)-41281/50410,type=IndexStats,name=ownerIdx,uid=102

For more information on using JMX to administer GemFire see the link below.

http://www.gemstone.com/docs/html/gemfire/6.0.0/SystemAdministratorsGuide/JMX.9.1.html

VMware vFabric - my new world

2011-10-04T10:50:00.000+11:00

Just recently I joined VMware vFabric System Engineering Team. So my new life will center around the following products.

vFabric Product Family

GemFire - memory-oriented data management
SQLFire - fast, scalable SQL data in the cloud
tc Server - lightweight Spring Java application server
RabbitMQ - messaging that just works
Hyperic - comprehensive server monitoring
Web Server - fast & secure Apache HTTP server
ERS - fast & secure Apache HTTP server

So in short this blog will continue but will focus on VMware - vFabric Cloud Application Platform

Batch Inserting using Spring JDBC (JdbcTemplate)

2011-09-07T22:34:00.002+10:00

In this small example we use the classic JdbcTemplate to complete batch processing with the Oracle JDBC driver. Although we could simply use SimpleJdbcTemplate and write less code this one just seems more natural to me. The code is assuming insertion of new records here.

Table definition

drop table jdbc_batch_table;

create table jdbc_batch_table
(message_id number primary key,
 message_type varchar2(1),
 message varchar2(100))
/

purge recyclebin;

exit;

Spring DAO - JdbcBatchDAOImpl

package pas.au.gemfire.demo.cachewriter.dao.jdbcbatch;

import java.math.BigDecimal;

import java.sql.PreparedStatement;

import java.sql.SQLException;

import java.util.List;

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

import javax.sql.DataSource;

import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;

public class JdbcBatchDAOImpl implements JdbcBatchDAO
{
  private Logger logger = Logger.getLogger(this.getClass().getSimpleName());
  
  private JdbcTemplate jdbcTemplate;

  public void setDataSource(DataSource dataSource)
  {
    this.jdbcTemplate = new JdbcTemplate(dataSource);
  }
  
  @Override
  public void storeBatch(final List<JdbcBatch> jdbcBatchEntries)
  {

    int[] updateCounts = 
     jdbcTemplate.batchUpdate
      (Constants.INSERT_SQL,
       new BatchPreparedStatementSetter() 
       {
          public void setValues(PreparedStatement ps, int i) throws SQLException 
          {
              ps.setBigDecimal(1, new BigDecimal(jdbcBatchEntries.get(i).getMessageId().toString()));
              ps.setString(2, jdbcBatchEntries.get(i).getMessageType());
              ps.setString(3, jdbcBatchEntries.get(i).getMessage());
          }
          
          public int getBatchSize() 
          {
            return jdbcBatchEntries.size();                  
          }
        }
      );
    
    logger.log (Level.INFO, 
                String.format("Inserted %s records using spring jdbc batching", updateCounts.length));
  }
}

Constants Interface

package pas.au.gemfire.demo.cachewriter.dao.jdbcbatch;

public interface Constants
{
  public final String INSERT_SQL = "insert into jdbc_batch_table values (:1, :2, :3)";
}

Output showing the insertion of 100 records at a time

Sep 7, 2011 10:03:43 PM pas.au.gemfire.demo.cachewriter.dao.jdbcbatch.JdbcBatchDAOImpl storeBatch
INFO: Inserted 100 records using spring jdbc batching
Sep 7, 2011 10:03:43 PM pas.au.gemfire.demo.cachewriter.DBGatewayListener processEvents
INFO: Size of DBGatewayListener = 100
Sep 7, 2011 10:03:43 PM pas.au.gemfire.demo.cachewriter.dao.jdbcbatch.JdbcBatchDAOImpl storeBatch
INFO: Inserted 100 records using spring jdbc batching
Sep 7, 2011 10:03:43 PM pas.au.gemfire.demo.cachewriter.DBGatewayListener processEvents
INFO: Size of DBGatewayListener = 100
Sep 7, 2011 10:03:43 PM pas.au.gemfire.demo.cachewriter.dao.jdbcbatch.JdbcBatchDAOImpl storeBatch
INFO: Inserted 100 records using spring jdbc batching
Sep 7, 2011 10:03:43 PM pas.au.gemfire.demo.cachewriter.DBGatewayListener processEvents
INFO: Size of DBGatewayListener = 100
Sep 7, 2011 10:03:44 PM pas.au.gemfire.demo.cachewriter.dao.jdbcbatch.JdbcBatchDAOImpl storeBatch
INFO: Inserted 100 records using spring jdbc batching
Sep 7, 2011 10:03:44 PM pas.au.gemfire.demo.cachewriter.DBGatewayListener processEvents
INFO: Size of DBGatewayListener = 100
Sep 7, 2011 10:03:44 PM pas.au.gemfire.demo.cachewriter.dao.jdbcbatch.JdbcBatchDAOImpl storeBatch
INFO: Inserted 100 records using spring jdbc batching

More info on Spring JDBC can be found here.

http://static.springsource.org/spring/docs/3.0.x/spring-framework-reference/html/jdbc.html

Using Oracle Universal Connection Pool with Spring from JDeveloper 11.1.2

2011-08-15T08:55:00.001+10:00

In this example we simply setup Oracle UCP to be used by Spring from JDeveloper 11g 11.1.2. When using read write backing maps with Oracle Coherence that are using a back end database such as RAC it's UCP RAC integration which makes this UCP the ideal choice. In this example we are just using a single instance Oracle database but can easily add the FCF properties required for UCP.

1. Create a spring framework beans.xml file as follows

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans" 
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xsi:schemaLocation="http://www.springframework.org/schema/beans 
                           http://www.springframework.org/schema/beans/spring-beans.xsd">
    <bean id="ucpDataSource" class="oracle.ucp.jdbc.PoolDataSourceFactory" factory-method="getPoolDataSource">
      <property name="URL" value="jdbc:oracle:thin:@beast.au.oracle.com:1524/linux11gr2" />
      <property name="user" value="scott" />
      <property name="password" value="tiger" />
      <property name="connectionFactoryClassName" value="oracle.jdbc.pool.OracleDataSource" />
      <property name="connectionPoolName" value="TEST_POOL" />
      <property name="minPoolSize" value="1" />
      <property name="maxPoolSize" value="10" />
      <property name="initialPoolSize" value="1" />
    </bean>
    
  <bean id="ucpJDBCTest" class="pas.au.spring.demo.ucp.test.TestJDBC">
    <property name="dataSource" ref="ucpDataSource"/>
  </bean>
  
</beans>

2. Add the required libraries to the project

3. Create the TestJDBC class as follows

package pas.au.spring.demo.ucp.test;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;

public class TestJDBC
{
  private JdbcTemplate jdbcTemplate;

  public void setDataSource(DataSource dataSource)
  {
    this.jdbcTemplate = new JdbcTemplate(dataSource);
  }
  
  public void insertDept()
  {
    /**
     * Specify the insert values 
     */
    jdbcTemplate.update("insert into dept values (:1, :2, :3)", 
                        new Object[] 
                        { 
                          Integer.valueOf(89),
                          "Pas", 
                          "Spring"
                        });
  }
}

4. Create Main test class as follows.

package pas.au.spring.demo.ucp.test;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class Main
{
  public static void main(String[] args)
  {
    ApplicationContext context = new ClassPathXmlApplicationContext("beans.xml");
    TestJDBC testJDBC = (TestJDBC) context.getBean("ucpJDBCTest");
    testJDBC.insertDept();
    System.out.println("all done..");
  }
}

5. Run and verify output as follows

Aug 15, 2011 8:52:42 AM org.springframework.context.support.AbstractApplicationContext prepareRefresh

INFO: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@b753f8: startup date [Mon Aug 15 08:52:42 EST 2011]; root of context hierarchy

Aug 15, 2011 8:52:42 AM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions

INFO: Loading XML bean definitions from class path resource [beans.xml]

Aug 15, 2011 8:52:43 AM org.springframework.beans.factory.support.DefaultListableBeanFactory preInstantiateSingletons

INFO: Pre-instantiating singletons in org.springframework.beans.factory.support.DefaultListableBeanFactory@3411a: defining beans [ucpDataSource,ucpJDBCTest]; root of factory hierarchy

all done..

6. Verify insertion from SQL*Plus

c:\temp>sqlplus scott/tiger@linux11gr2

SQL*Plus: Release 11.2.0.1.0 Production on Mon Aug 15 08:53:31 2011

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


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

SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        50 MARKETING      MELBOURNE
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        89 Pas            Spring

6 rows selected.

Setting up Exalogic using IB with SDP enabled Data Sources to Exadata

2011-08-03T15:35:00.026+10:00

Got the opportunity to setup a 1/2 RACK EL (Exalogic) with a 1/2 RACK ED (Exadata). My role was to setup the EL RACK and configure WebLogic and Coherence and also TCP/SDP GridLink Data Sources. Here I will document the process of how we setup our GridLink Data Sources to use SDP enabled listener to take advantage of the IB and the 40G link between the 2 RACKS.

A Grid Link Data Source is the JDBC connectivity between Oracle WebLogic Server and a service targeted to an Oracle RAC cluster. It uses the Oracle Notification Service (ONS) to adaptively respond to state changes in an Oracle RAC instance.

Enable SDP on the DB Nodes

1. Edit the /etc/infiniband/openib.conf and set the following.

SDP_LOAD=yes

2. Edit the /etc/ofed/libsdp.conf and change the following two lines at the bottom.

    use tcp server * *:*
    use tcp client * *:*

3. Edit the /etc/modprobe.conf file and add these settings.

options ib_sdp sdp_zcopy_thresh=0 recv_poll=0

4. Reboot all database nodes for the changes to take effect.

Create a Listener on the IB Network

1. Edit /etc/hosts on each node in the cluster to add the virtual ip addresses you will use for the infiniband network. Make sure that these IP addresses are not used.

172.19.2.21     xx-ibvip.int.corp.sun     xx-ibvip
172.19.2.22     yy-ibvip.int.corp.sun     yy-ibvip

Note: These entries must be replicated on each WLS compute node which will be using SDP enabled data sources.

2. On one of the database nodes, as the root user create a network resource for the IB network.

$ /u01/app/grid/product/11.2.0.2/bin/srvctl add network -k 2 -S 172.19.0.0/255.255.252.0/bondib0

3. Validate that the network was added correctly, by running one of the following commands:

$ /u01/app/grid/product/11.2.0.2/bin/crsctl stat res -t | grep net
ora.net1.network
ora.net2.network

$ /u01/app/grid/product/11.2.0.2/bin/srvctl config network -k 2

Network exists: 2/172.19.0.0/255.255.252.0/bondib0, type static

4. Add the Virtual IP addresses on the network created as 2 for each node in the cluster.

$ /u01/app/grid/product/11.2.0.2/bin/srvctl add vip -n edd1db03 -A xx-ibvip/255.255.252.0/bondib0 -k 2

$ /u01/app/grid/product/11.2.0.2/bin/srvctl add vip -n edd1db04 -A yy-ibvip/255.255.252.0/bondib0 -k 2

5. As the "oracle" user (who owns the Grid Infrastructure Home), add a listener which will listen on the VIP addresses created in step #3. Login as 'grid' user.

$ srvctl add listener -l LISTENER_IB -k 2 -p TCP:1522,/SDP:1522

6. Add the following entries in $ORACLE_HOME/network/admin/tnsnames.ora (RDBMS)

DB Node1

[smlora@edd1db03 admin]$ cat tnsnames.ora

DBM =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = edracd1)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = db_srv)))

DBM_IB = (DESCRIPTION = (LOAD_BALANCE=on) (ADDRESS = (PROTOCOL = TCP)(HOST = xx-ibvip)(PORT = 1522)) (ADDRESS = (PROTOCOL = TCP)(HOST = yy-ibvip)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = db_srv)))

LISTENER_IBREMOTE = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = yy-ibvip)(PORT = 1522))))

LISTENER_IBLOCAL = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = xx-ibvip)(PORT = 1522)) (ADDRESS = (PROTOCOL = SDP)(HOST = yy-ibvip)(PORT = 1522))))

LISTENER_IPLOCAL = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = xx-vip)(PORT = 1521))))

LISTENER_IPREMOTE = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = edracd1)(PORT = 1521))))

DB Node2

[smlora@edd1db04 admin]$ cat tnsnames.ora

DBM = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = edracd1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = db_srv)))

DBM_IB = (DESCRIPTION = (LOAD_BALANCE=on) (ADDRESS = (PROTOCOL = TCP)(HOST = xx-ibvip)(PORT = 1522)) (ADDRESS = (PROTOCOL = TCP)(HOST = yy-ibvip)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = db_srv)))

LISTENER_IBREMOTE = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = xx-ibvip)(PORT = 1522))))

LISTENER_IBLOCAL = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = yy-ibvip)(PORT = 1522)) (ADDRESS = (PROTOCOL = SDP)(HOST = yy-ibvip)(PORT = 1522))))

LISTENER_IPLOCAL = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = yy-vip)(PORT = 1521))))

LISTENER_IPREMOTE = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = edracd1.int.corp.sun)(PORT = 1521))))

7. Modify the listener_networks init parameter. Connect to the database instance as sysdba and issue an SQL as follows. You only have to do this on one of the instances.

$ SQL> alter system set listener_networks='((NAME=network2)
(LOCAL_LISTENER=LISTENER_IBLOCAL)(REMOTE_LISTENER=LISTENER_IBREMOTE))',
'((NAME=network1)(LOCAL_LISTENER=LISTENER_IPLOCAL)(REMOTE_LISTENER=LISTENER_IPREMOTE))' scope=both;

8. Stop and start LISTENER_IB for the modification in 7.

$ srvctl stop listener -l LISTENER_IB

$ srvctl start listener -l LISTENER_IB

9. Verify output from GRID home of the status of the LISTENER_IB. This will confirm we have SDP setup correctly.

[smlgrid@rac1 ~]$ lsnrctl status listener_ib

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 03-AUG-2011 10:08:09

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_IB)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_IB
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                02-AUG-2011 16:43:17
Uptime                    0 days 17 hr. 24 min. 52 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/grid/product/11.2.0.2/network/admin/listener.ora
Listener Log File         /u01/app/grid/product/11.2.0.2/log/diag/tnslsnr/edd1db03/listener_ib/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_IB)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=sdp)(HOST=xx-ibvip)(PORT=1522)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xx-ibvip)(PORT=1522)))
Services Summary...
Service "dbm" has 2 instance(s).
Instance "dbm1", status READY, has 1 handler(s) for this service...
Instance "dbm2", status READY, has 2 handler(s) for this service...
Service "db_srv" has 2 instance(s).
Instance "dbm1", status READY, has 1 handler(s) for this service...
Instance "dbm2", status READY, has 2 handler(s) for this service...
The command completed successfully

GridLink Data Source fropm WebLogic Compute Nodes on Exalogic

1. Edit the script "startWebLogic.sh" to include the following to enable SDP from the WebLogic JVM's. It's important to note that these SDP enabled JVM parameters are specifically for the managed servers as that is where the Data Sources will be targeted to run from. How you start you managed servers will determine where the place to put these parameters is. These are needed to switch to the IPv4Stack as IPv6 is not support by SDP currently as well as enabling SDP in general.

JAVA_OPTIONS="${JAVA_OPTIONS} -Djava.net.preferIPv4Stack=true -Doracle.net.SDP=true"

. ${DOMAIN_HOME}/bin/setDomainEnv.sh $*

2. When creating a GridLink Data Source ensure you use a connect string as follows. Make sure you use SDP protocol as well as the right listener port. You can't use a SCAN URL here as SCAN is for client connectivity using TCP, hence why it's a multi node connect string.

jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=YES)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=SDP)(HOST=xx-ibvip)(PORT=1522))(ADDRESS=
(PROTOCOL=SDP)(HOST=yy-ibvip)(PORT=1522)))(CONNECT_DATA=(SERVICE_NAME=db_srv)))

Below is a screen shot showing it can successfully connect to the IB listener.

Starting a Coherence Server From WLS 10.3.4 Console

2011-07-07T16:51:00.000+10:00

Decided it might make sense to use Node Manager to stop/start my stand alone coherence servers which will be accessed by the WebLogic managed server instances as storage disabled members. The process is pretty straight forward and is shown in this blog entry below. However there is one thing to be aware of.

http://blogs.oracle.com/jamesbayer/entry/weblogic_server_1034_screencas

In my example I wanted to use Coherence 3.7 and so to do that I needed to add the following to the "Server Start" Classpath field

/u01/Middleware/coherence/370/coherence/lib/coherence.jar

Once I did that and tried to start the Coherence Server from the admin console , using node manager it failed with an error as follows.

Jul 8, 2011 4:36:25 AM> <INFO> <NodeManager> <Server output log file 
 is 
 '/u01/Middleware/domains/1034/acs_dom/servers_coherence/CoherenceServer1/logs/CoherenceServer1.out'>
 Exception in thread "Main Thread" java.lang.NoClassDefFoundError: 
 weblogic/nodemanager/server/provider/WeblogicCacheServer
 Caused by: java.lang.ClassNotFoundException: 
 weblogic.nodemanager.server.provider.WeblogicCacheServer
         at java.net.URLClassLoader$1.run(URLClassLoader.java:202)
         at java.net.URLClassLoader.findClass(URLClassLoader.java:190)
         at java.lang.ClassLoader.loadClass(ClassLoader.java:307)
         at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:308)
         at java.lang.ClassLoader.loadClass(ClassLoader.java:248)

The poblem here is if you leave the classpath blank in the startup properties in the console configuration, then the two minimal jars (weblogic.server.modules.coherence.server_10.3.4.0.jar and coherence.jar) that are required are added for you. Once you specify a non-empty classpath (which is what I did), then you have to explicitly include those jars plus any others you want.

So what I needed was the following.

/u01/Middleware/coherence/370/coherence/lib/coherence.jar:/u01/Middleware/1034/modules/features/weblogic.server.modules.coherence.server_10.3.4.0.jar

Once started the Log file for the coherence server exists on the file system as follows:

$DOMAIN_HOME/servers_coherence/{COH_SERVER_NAME}/logs/{COH_SERVER_NAME}.out

The PID for the server is found here, I normally add that to the log file but it's not possible when starting Coherence this way.

$DOMAIN_HOME/servers_coherence/{COH_SERVER_NAME}/data/nodemanager/{COH_SERVER_NAME}.pid

Installing WLS 10.3.4 domain across 2 Compute Nodes on Exalogic

2011-07-04T21:39:00.002+10:00

Got a rare opportunity to access 2 compute nodes on Exalogic and I thought it would be a good opportunity to test creating a WLS domain across the 2 compute nodes. Here is some brief steps showing how this was done.

1. Create a share to be used by both compute nodes. In this demo we created one as follows.

/u01/Middleware

2. Install WLS software onto the share as shown below.

[oracle@auxlcn07 1034]$ pwd
/u01/Middleware/1034
[oracle@auxlcn07 1034]$ d
total 148
drwxr-xr-x+ 7 oracle oinstall   464 Jun 30 21:25 modules/
drwxr-xr-x+ 8 oracle oinstall     9 Jun 30 21:25 utils/
drwxr-x---+ 7 oracle oinstall     9 Jun 30 21:25 jrockit_160_22_D1.1.1-3/
-rw-r--r--+ 1 oracle oinstall   623 Jun 30 21:25 ocm.rsp
-rw-r--r--+ 1 oracle oinstall 73475 Jun 30 21:25 registry.dat
-rw-r--r--+ 1 oracle oinstall  2306 Jun 30 21:25 registry.xml
-rw-r--r--+ 1 oracle oinstall    26 Jun 30 21:25 .home
drwxr-xr-x+ 8 oracle oinstall    13 Jun 30 21:25 ./
drwxr-xr-x+ 9 oracle oinstall    11 Jun 30 21:25 wlserver_10.3/
drwxr-xr-x+ 6 oracle oinstall     9 Jun 30 21:25 coherence_3.6/
-rw-rw----+ 1 oracle oinstall   192 Jul  1 08:25 domain-registry.xml
drwxr-xr-x+ 2 oracle oinstall    12 Jul  1 17:16 logs/
drwx------+ 6 oracle oinstall     6 Jul  4 15:56 ../

3. Now we can create a PRODUCTION domain into a share in a directory as follows "/u01/Middleware/domains/1034/acs_dom". Ensure you create a machine for each compute node and ensure you place the managed servers you wish on each compute node in the domain config screens.

[oracle@auxlcn07 acs_dom]$ d
total 23
drwxr-xr-x+  4 oracle oinstall   4 Jul  1 08:25 ../
drwxr-x---+  2 oracle oinstall   3 Jul  1 08:25 autodeploy/
drwxr-x---+  2 oracle oinstall   3 Jul  1 08:25 console-ext/
drwxr-x---+  2 oracle oinstall   3 Jul  1 08:25 lib/
drwxr-x---+  2 oracle oinstall   6 Jul  1 08:25 security/
-rw-r-----+  1 oracle oinstall 650 Jul  1 08:25 startManagedWebLogic_readme.txt
drwxr-x---+  2 oracle oinstall   7 Jul  1 08:25 init-info/
-rw-r-----+  1 oracle oinstall 462 Jul  1 08:25 fileRealm.properties
-rwxr-x---+  1 oracle oinstall 252 Jul  1 08:25 startWebLogic.sh*
drwxr-x---+ 10 oracle oinstall  12 Jul  1 08:26 config/
drwxr-x---+ 12 oracle oinstall  16 Jul  1 08:29 ./
drwxr-----+  2 oracle oinstall   2 Jul  1 10:50 pending/
-rw-r-----+  1 oracle oinstall  32 Jul  1 10:50 edit.lok
drwxr-----+  2 oracle oinstall   2 Jul  1 10:50 tmp/
drwxr-----+  8 oracle oinstall   8 Jul  1 14:10 servers/
drwxr-x---+  5 oracle oinstall  11 Jul  1 15:38 bin/

Note: No need for pack/unpack given we are using a share.

4. In this demo we want to create a node manager process on each compute node sharing the same domain directory. To do this we simply create seperate directories for each on our compute nodes and copy a set of node manager files available in the default $WLS_HOME/wlserver_10.3/common/nodemanager directory.

[oracle@auxlcn07 nodemanager]$ pwd
/u01/Middleware/domains/1034/nodemanager
[oracle@auxlcn07 nodemanager]$ d
total 6
drwxr-xr-x+ 4 oracle oinstall 4 Jun 30 21:54 ./
drwxr-xr-x+ 4 oracle oinstall 4 Jul  1 08:25 ../
drwxr-xr-x+ 2 oracle oinstall 8 Jul  1 14:27 node08/
drwxr-xr-x+ 2 oracle oinstall 9 Jul  1 15:29 node07/

Example of node07 files

[oracle@auxlcn07 node07]$ pwd
/u01/Middleware/domains/1034/nodemanager/node07
[oracle@auxlcn07 node07]$ d
total 30
drwxr-xr-x+ 4 oracle oinstall     4 Jun 30 21:54 ../
-rw-r-----+ 1 oracle oinstall   130 Jul  1 08:44 nm_data.properties
-rw-r-----+ 1 oracle oinstall    77 Jul  1 11:20 nodemanager.domains
-rw-r--r--+ 1 oracle oinstall   164 Jul  1 11:28 nodemanager.properties
-rwxr-x---+ 1 oracle oinstall  4378 Jul  1 13:23 startNodeManager.sh*
-rw-r-----+ 1 oracle oinstall  1379 Jul  1 14:02 nodemanager.log.1
-rw-r-----+ 1 oracle oinstall     0 Jul  1 14:28 nodemanager.log.lck
-rw-r-----+ 1 oracle oinstall 15618 Jul  1 14:29 nodemanager.log
drwxr-xr-x+ 2 oracle oinstall     9 Jul  1 15:29 ./

nodemanager.properties example, the HOME will differ in the node08 file.

NodeManagerHome=/u01/Middleware/domains/1034/nodemanager/node07
StartScriptEnabled=true
StopScriptEnabled=true

5. Edit startNodeManager.sh to include node manager home location now it's moved
away from WLS_HOME. Example for node07.

NODEMGR_HOME="/u01/Middleware/domains/1034/nodemanager/node07"

6. Start both node managers from there directories on each compute node, example for node07.

/u01/Middleware/domains/1034/nodemanager/node07/startNodeManager.sh

7. For the ADMIN SERVER node ensure we set the following in "startWebLogic.sh".

# START WEBLOGIC

JAVA_OPTIONS="${JAVA_OPTIONS} -Dweblogic.security.SSL.ignoreHostnameVerification=true"

echo "starting weblogic with Java version:"

8. Start admin server, here we use node07.

/u01/Middleware/domains/1034/acs_dom/bin/startWebLogic.sh

9. Log into the admin console

10. Ensure the node manager processes are available from each machine and reachable.

11. Start all managed servers, once you ensure the node manager is reachable. I prefer to start managed servers from the command line but given we have 2 node managers on the compute nodes we can use that through the admin console.

Example of such a setup.

Taking JSF 2.0 for a test drive with JDeveloper 11.1.2

2011-06-21T22:03:00.001+10:00

I have been waiting for JSF 2.0 in JDeveloper for some time and after using Netbeans for all my JSF 2.0 work I thought I would quickly test how well it worked in the recently released JDeveloper 11.1.2 release.

Firstly my aim was to use a basic JPA/EJB 3.0 entity model project and JSF 2.0 View project. I am going to skip over the model setup as that support has been in JDeveloper for some time, but in short we use the SCOTT schema and create a Emp JPA Entity and an EJB Session Facade in the model project accepting all the defaults.

Note: I created a Java EE Web Application which seems to be the default one I needed for this setup.

The plan here was to use DI (Depency Injection) for the EJB session bean within my managed beans and for my view pages to use Facelet *.JSF pages. I could of used a *.XHTML pages but I feel more comfortable in the JSP world versus HTML world.

1. In the ViewController project create a new JSF page using "File -> New -> Web Tier-> JSF/Facelets -> Page". In this example I created a file called "pages/Emps.jsf". Once created leave it as is we will return to it soon.

Note: I could create the managed bean to serve this page at the same time on the wizard page but I choose not to at this stage, rather do it by hand.

2. Create a new java class called "EmpManagedBean" in a package as follows "view".
3. Replace the code in the empty class with code as follows. Be sure you edit the reference to the EJB session bean to the correct session bean name from the model project.

package view;

import java.util.List;

import java.util.logging.Logger;

import javax.annotation.PostConstruct;

import javax.faces.bean.ManagedBean;
import javax.ejb.EJB;

import model.Emp;
import model.EmpSessionEJBLocal;

@ManagedBean (name = "empBean")
public class EmpManagedBean
{
  
  @EJB 
  EmpSessionEJBLocal empSessionEJBLocal;
  private Logger logger = Logger.getLogger(this.getClass().getSimpleName());
  
  @PostConstruct
  public void init()
  {
    emps = empSessionEJBLocal.getEmpFindAll();    
  }
  
  private List<Emp> emps;
  
  public EmpManagedBean()
  {  
  }

  public void setEmps(List<Emp> emps)
  {
    this.emps = emps;
  }

  public List<Emp> getEmps()
  {
    return emps;
  }
}

Note: Auto import as required.

4.Now at this point return to Emps.jsf and add code as follows.

<?xml version='1.0' encoding='windows-1252'?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<f:view xmlns:f="http://java.sun.com/jsf/core" xmlns:h="http://java.sun.com/jsf/html">
<html xmlns="http://www.w3.org/1999/xhtml">
<h:head>
  <title>Employees</title>
</h:head>
<h:body>
  <h3 style="font-family: arial; font-variant: small-caps; color: #336699">
    Employees
  </h3>
  <h:form>
    <h:dataTable var="row"
                 value="#{empBean.emps}"
                 border="1">
        <h:column>
          <f:facet name="header">Empno#</f:facet>
          #{row.empno}
        </h:column>
        <h:column>
          <f:facet name="header">Ename</f:facet>
          #{row.ename}
        </h:column>
    </h:dataTable>
  </h:form>
</h:body>
</html>
</f:view>

Note: The CE insight is available for the managed bean within the JSF page ensuring you get the correct property name you need to use.

5. Add the library "Java EE 1.5 API" so the DI code will compile without issues. There may be a smaller library then this but it wasn't obvious from the list.

The rest , such as web.xml setup etc should be setup for us , so no need to do anything there.

6. Run Emps.jsf and verify runtime output as follows.

The JDeveloper log window should show some output as follows once the page is launched.

Run startup time: 12439 ms.
[Application JavaEETestApp deployed to Server Instance IntegratedWebLogicServer]

Target URL -- http://127.0.0.1:7101/javaee-jsf/faces/pages/Emps.jsf

<2011-06-21 21:55:56.114--ServerSession(14286368)--EclipseLink, version: Eclipse Persistence Services - 2.1.3.v20110304-r9073>

<2011-06-21 21:55:56.115--ServerSession(14286368)--Server: 10.3.5.0>

<2011-06-21 21:55:56.378--ServerSession(14286368)--file:/C:/jdev/jdevprod/11.1.2/jdeveloper/jdev/system11.1.2.0.38.60.17/o.j2ee/drs/JavaEETestApp/ModelEJB.jar/_Model login successful>

WebLogic - Obtain Groups of an Autenticated User

2011-06-17T08:17:00.000+10:00

Note for myself:

 for(Principal p: subject.getPrincipals()) {
            if(p instanceof WLSGroup) {
                if(first) {
                    first=false;
                } else {
                    groups.append(", ");
                }
                groups.append(p.getName());
            } else if (p instanceof WLSUser) {
                user = p.getName();
            }
        }

Struts 2 Starter Project for JDeveloper 11g (11.1.1.5)

2011-05-31T23:13:00.000+10:00

I thought I would create a Struts 2 starter project (not before time) for JDeveloper 11g which included some must haves as follows to make this as close to being integrated into the IDE as possible.

Be able to run actions without having to alter the URL at runtime
Provide XML insight for struts.xml
View the struts.xml file from the navigator
Include the struts 2 tags in the component palette

Here is how I did this.

1.Create an empty project called "Web"
2. Add a HTML file called index.html, ignoring it's content for now.
3. This will give us a "public_html" directory and of course a web.xml , so at this point navigate to the file system and add the Struts 2 JAR files as follows, all these are required to "WEB-INF\lib".

commons-fileupload-1.2.2.jar
commons-logging-1.1.1.jar
freemarker-2.3.16.jar
javassist-3.11.0.GA.jar
ognl-3.0.1.jar
struts2-core-2.2.3.jar
xwork-core-2.2.3.jar

4. Next we have to add a struts.xml to the IDE which for Struts 2 belongs in the "WEB-INF\classes" folder. Create the folder on the file system and add a empty struts.xml file.
5. Now to view struts.xml in the navigator we have to edit the project properties and add the "WEB-INF\classes" to the project source path so it shows in the navigator as shown below.

6. From the JAR file "struts2-core-2.2.3.jar" extract the DTD "struts-2.1.7.dtd" and place it into the "WEB-INF\classes". It's the DTD which will enable XML code insight to help us build our struts 2 XML config file.
7. Refresh the project to ensure your project is starting to take shape
8. Edit struts2.xml and add content as follows, you could use code insight if you wanted to now we have that setup. You will see we reference the DTD as a SYSTEM file itself from the same directory.

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE struts SYSTEM "struts-2.1.7.dtd">

<struts>

    <package name="struts-demo" namespace="/" extends="struts-default">
      
      <action name="entername">
        <result>nameinput.jsp</result>
      </action>
      
      <action name="sayHello" class="pas.acs.struts.demo.actions.Hello">
        <result name="input">nameinput.jsp</result>
        <result>response.jsp</result>
      </action>
    </package>

</struts>

9. Create our action class as follows.

package pas.acs.struts.demo.actions;

import com.opensymphony.xwork2.ActionSupport;

public class Hello extends ActionSupport
{
  private String name;
  private String message;
  
  public Hello()
  {
  }

  public void setName(String name)
  {
    this.name = name;
  }

  public String getName()
  {
    return name;
  }

  public void setMessage(String message)
  {
    this.message = message;
  }

  public String getMessage()
  {
    return String.format("Hello %s", getName());
  }
}

10. Create a Hello-validation.xml to use for validation in the same directory as our class above.

<!DOCTYPE validators PUBLIC
"-//OpenSymphony Group//XWork Validator 1.0.2//EN"
"http://www.opensymphony.com/xwork/xwork-validator-1.0.2.dtd">

<validators>
  <field name="name">
    <field-validator type="requiredstring">
      <param name="trim">true</param>
      <message>Please enter name</message>
    </field-validator>
  </field>
</validators>

11. Create the 2 required JSP as follows

nameinput.jsp

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<%@ page contentType="text/html;charset=windows-1252"%>
<%@ taglib uri="/struts-tags" prefix="s"%>
<html>
<head>
  <style type="text/css">
    .errorMessage
    {
      color:red;
    }
  </style>
  <meta http-equiv="Content-Type" content="text/html; charset=windows-1252"/>
  <title>Struts 2 Demo In JDeveloper 11g PS3</title>
</head>
<body>
  <h2>Struts 2 Demo In Jdeveloper 11g PS3</h2>
  <s:form action="sayHello" method="POST">
    <s:textfield label="Your Name" name="name" />
    <s:submit value="Go" />
  </s:form>
</body>
</html>

response.jsp

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<%@ page contentType="text/html;charset=windows-1252"%>
<%@ taglib uri="/struts-tags" prefix="s"%>
<html>
<head>
  <meta http-equiv="Content-Type" content="text/html; charset=windows-1252"/>
  <title>Struts 2 Demo In JDeveloper 11g PS3</title>
</head>
<body>
  <h2>Struts 2 Demo In JDeveloper 11g PS3</h2>
  <s:property value="message" />
</body>
</html>

12. Edit index.html with content as follows to start our struts 2 application with the correct URL syntax to invoke the action.

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
    <META HTTP-EQUIV="Refresh" CONTENT="0;URL=entername.action">
</head>

<body>
<p>Loading ...</p>
</body>
</html>

13. Edit then project properties and select "JSP tag Libraries' and then select "Add" and navigate to "struts2-core-2.2.3.jar" to find the struts tags and add them to the component palette.

14. Edit web.xml to look as follows.

<?xml version = '1.0' encoding = 'windows-1252'?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"
         version="2.5" xmlns="http://java.sun.com/xml/ns/javaee">
    <filter>
        <filter-name>struts2</filter-name>
        <filter-class>org.apache.struts2.dispatcher.FilterDispatcher</filter-class>
    </filter>

    <filter-mapping>
        <filter-name>struts2</filter-name>
        <url-pattern>/*</url-pattern>
    </filter-mapping>         
</web-app>

15. Run index.html to invoke the Integrated WebLogic server and verify a page as follows.

16. Enter in your name and press the "go" button

So now you have a Struts 2 starter project for JDeveloper 11g (11.1.1.5)

Your project would look as follows within JDeveloper 11g IDE

Intermittent Oracle JDBC Connection Failures to RAC

2011-05-25T09:54:00.000+10:00

More often then not I get the following come my way regarding Oracle JDBC connections to a RAC Cluster.

"My JDBC connections to a RAC cluster intermittently fail every now and then, but SQL*Plus works fine?"

Here is a little demo that will enable you to continually get JDBC connections to the RAC cluster , waiting for a connection failure. Nice way to verify if indeed there are intermittent problems from ORACLE JDBC when connecting to the cluster. We use the ScheduledExecutorService to get connections periodically and never terminate until we get a connection request failure. This demo is using an 11g R2 cluster with SCAN.

PeriodicJDBCRacTester.java (Main class to run the test)

package pas.au.jdbc.rac;

import java.io.FileInputStream;

import java.text.DateFormat;

import java.util.Date;
import java.util.Properties;
import java.util.concurrent.Executors;
import java.util.concurrent.ScheduledExecutorService;
import java.util.concurrent.ScheduledFuture;
import java.util.concurrent.TimeUnit;

public class PeriodicJDBCRacTester
{
  private static final DateFormat DATEFORMAT = DateFormat.getInstance();
  private String periodString = "30";
  private String url = null;
  private String user = null;
  private String passwd = null;
  private static Properties myProperties = new Properties();
  
  public PeriodicJDBCRacTester()
  {
    try
    {
      myProperties.load(new FileInputStream("jdbcractest.properties"));
      processProps(myProperties);
    }
    catch (Exception e)
    {
      e.printStackTrace();
    }
  }
  
  public void run()
  {
    ScheduledExecutorService scheduler = Executors.newScheduledThreadPool(1);
    try 
    {

      LoadBalanceTest loadBalanceTest = 
         new LoadBalanceTest(url, user, passwd);

      String periodString = "30";
      int prd = Integer.parseInt(periodString);
      long period = (long) prd;
      
      System.out.println("\nPeriodic JDBC Load Balance Test ");
      System.out.println("Period to report results in seconds is : " + period + "\n");
     
      System.out.printf("** Started [%s] with %s(sec) interval between runs **\n", 
                        DATEFORMAT.format(new Date()),
                        periodString);
      
      final ScheduledFuture jdbcRacMonitor = 
          scheduler.scheduleAtFixedRate(loadBalanceTest, 0, 30, 
                                        TimeUnit.SECONDS);
                                       
              
    } 
    catch (Exception ex) 
    {
      ex.printStackTrace();
    } 
  }

  private void processProps(Properties inProps)
  {
    periodString = inProps.getProperty("period");
    url = inProps.getProperty("url");
    user = inProps.getProperty("user");
    passwd = inProps.getProperty("passwd");
  }
  
  public static void main(String[] args)
  {
    PeriodicJDBCRacTester test = new PeriodicJDBCRacTester();
    test.run();
  }
}

LoadBalanceTest.java (Test class which is called periodically which obtains connections)

package pas.au.jdbc.rac;

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

import java.text.DateFormat;

import java.util.Date;

import oracle.jdbc.pool.OracleDataSource;

public class LoadBalanceTest implements Runnable
{
  private static final DateFormat DATEFORMAT = DateFormat.getInstance();
  
  private OracleDataSource ods = null;
  public final String userId = "scott";
  public final String password = "tiger";

  private String url = null;
  
  public LoadBalanceTest(String url, String user, String password) throws SQLException
  {
    ods = new OracleDataSource();
    ods.setUser(user);
    ods.setPassword(password);
    ods.setURL(url);  
    
    this.url = url;
    
  }

  public Connection getConnection() throws SQLException
  {
    return ods.getConnection();
  }
  
  public void run ()
  {
    Connection[] connArray = new Connection[5];
    System.out.println("\nCurrent run started at " + new Date());
    System.out.println("Obtaining 5 connections");
    try
    {
      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();
          System.out.println("using URL : " + url + "\n");
          // gets driver info:

          System.out.println("=============\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\n");
      for (int y = 0; y < connArray.length; y++)
      {
        connArray[y].close();
      }
    }
    catch (SQLException sqle)
    {
      // TODO: Add catch code
      sqle.printStackTrace();
    }
  }

  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();
  }
}

jdbcractest.properties (Properties file used to connect to the cluster and define the period in seconds for when the test will run)

period=30

url=jdbc:oracle:thin:@apctcsol1.au.oracle.com:1521/pas_srv

user=scott

passwd=tiger

If you want to run this with ANT you can use a build.xml as follows.

<?xml version="1.0" encoding="windows-1252" ?>

<project default="run" name="JDBCRACTester" basedir=".">

  <property file="build.properties"/>
   
  <path id="j2ee.classpath">
    <pathelement path="./lib/ojdbc6.jar"/>
    <pathelement path="./lib/JDBCRACTester.jar"/>
  </path>

  <property name="src.dir" value="src"/>
  <property name="classes.dir" value="classes"/>
  <property name="lib.dir" value="lib"/>
  <property name="class.name" value="pas.au.jdbc.rac.PeriodicJDBCRacTester"/>
  
  <target name="init">
    <tstamp/>
    <mkdir dir="${lib.dir}"/>
    <mkdir dir="${classes.dir}"/>
  </target>
  
  <target name="clean" description="Clean lib, classes directories">
    <delete dir="${classes.dir}"/>
  </target>
  
  <target name="compile" description="Compiles classes into ${classes.dir}" depends="init">
    <javac includeantruntime="false" srcdir="${src.dir}" debug="true" destdir="${classes.dir}" 
           includes="**/*.java" >
      <classpath refid="j2ee.classpath"/>
    </javac>
  </target>

  <target name="package" depends="compile" description="Package application into ${ant.project.name}.jar">
    <jar basedir="${classes.dir}" destfile="${lib.dir}/${ant.project.name}.jar">
      <include name="**/*.class"/>
    </jar>
  </target>
  
  <target name="run" depends="package" description="Run the JDBC RAC Tester application">
    <echo message="Running the JDBC RAC Tester application"/>
    <java classname="${class.name}" fork="true">
      <classpath>
        <path refid="j2ee.classpath"/>
        <path path="${lib.dir}/${ant.project.name}.jar"/>
      </classpath>
    </java>
  </target>

</project>

Output (Run using ANT)

run:
     [echo] Running the JDBC RAC Tester application
     [java]
     [java] Periodic JDBC Load Balance Test
     [java] Period to report results in seconds is : 30
     [java]
     [java] ** Started [5/25/11 9:38 AM] with 30(sec) interval between runs **
     [java]
     [java] Current run started at Wed May 25 09:38:59 EST 2011
     [java] Obtaining 5 connections
     [java] using URL : jdbc:oracle:thin:@apctcsol1.au.oracle.com:1521/pas_srv
     [java]
     [java] =============
     [java] Database Product Name is ... Oracle
     [java] Database Product Version is Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
     [java] With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
     [java] Data Mining and Real Application Testing options
     [java] =============
     [java] JDBC Driver Name is ........ Oracle JDBC driver
     [java] JDBC Driver Version is ..... 11.2.0.2.0
     [java] JDBC Driver Major Version is 11
     [java] JDBC Driver Minor Version is 2
     [java] =============
     [java] Connection #0 : instance[A12], host[auw2k4], service[pas_srv]
     [java] Connection #1 : instance[A11], host[auw2k3], service[pas_srv]
     [java] Connection #2 : instance[A12], host[auw2k4], service[pas_srv]
     [java] Connection #3 : instance[A11], host[auw2k3], service[pas_srv]
     [java] Connection #4 : instance[A12], host[auw2k4], service[pas_srv]
     [java] Closing Connections
     [java]
     [java]
     [java] Current run started at Wed May 25 09:39:29 EST 2011
     [java] Obtaining 5 connections
     [java] using URL : jdbc:oracle:thin:@apctcsol1.au.oracle.com:1521/pas_srv
     [java]
     [java] =============
     [java] Database Product Name is ... Oracle
     [java] Database Product Version is Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
     [java] With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
     [java] Data Mining and Real Application Testing options
     [java] =============
     [java] JDBC Driver Name is ........ Oracle JDBC driver
     [java] JDBC Driver Version is ..... 11.2.0.2.0
     [java] JDBC Driver Major Version is 11
     [java] JDBC Driver Minor Version is 2
     [java] =============
     [java] Connection #0 : instance[A12], host[auw2k4], service[pas_srv]
     [java] Connection #1 : instance[A11], host[auw2k3], service[pas_srv]
     [java] Connection #2 : instance[A12], host[auw2k4], service[pas_srv]
     [java] Connection #3 : instance[A11], host[auw2k3], service[pas_srv]
     [java] Connection #4 : instance[A11], host[auw2k3], service[pas_srv]
     [java] Closing Connections
     [java]

How to Access Oracle Coherence Caches from a JRuby App

2011-05-05T09:53:00.000+10:00

The following how to shows how to access the Coherence Grid using JRuby as an extend client. Easy to setup and run using ANT.

http://www.oracle.com/technetwork/articles/dsl/jruby-coherence-393993.html

RESTful SQL Query Service using XML SQL Utility (XSU) API

2011-04-29T13:41:00.000+10:00

A little demo showing how easily we can create a SQL query utility using Oracle's XML SQL Utility (XSU) within a JAX-RS Web Service. Although basic you can easily create more query parameters to take full advantage of the XML SQL Utility (XSU) API.

1. Create a JAX-RS Web Service as follows.

package pas.au.xmlsql.services;

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

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

import javax.sql.DataSource;

import javax.ws.rs.GET;
import javax.ws.rs.Path;
import javax.ws.rs.Produces;
import javax.ws.rs.QueryParam;
import javax.ws.rs.core.MediaType;

import oracle.xml.parser.v2.XMLDocument;
import oracle.xml.sql.query.OracleXMLQuery;

@Path ("/queryservice") 
public class QueryService
{
  private DataSource ds = null;
  private final String dataSourceLocation = "jdbc/scottDS";
  
  public QueryService()
  {
    Context context;  
    try  
    {  
      context = getInitialContext();  
      // lookup data source at this point
      ds = (DataSource) context.lookup (dataSourceLocation);
    }  
    catch (NamingException e)  
    {  
      throw new RuntimeException (e.getMessage());  
    } 
  }

  private Connection getConnection () throws SQLException
  {
    return ds.getConnection();  
  }
  
  private static Context getInitialContext() throws NamingException  
  {  
    return new InitialContext();  
  }

  @GET
  @Path("/query")
  @Produces(MediaType.APPLICATION_XML)
  public XMLDocument runQuery (@QueryParam("sql")String querySql)
  {
    Connection conn = null;
    OracleXMLQuery qry = null;
    XMLDocument domDoc = null;
    
    try
    {
      conn = getConnection();
      System.out.println("Query is -> " + querySql);
      qry = new OracleXMLQuery(conn, querySql);
      domDoc = (XMLDocument)qry.getXMLDOM();
      return domDoc;
    }
    catch (Exception e)
    {
      throw new RuntimeException
        (String.format("Error running query [%s]", querySql), e);
    }
    finally
    {
      /* 
       * if exception occurs here no real need to log it just catch it and continue
       */

      if (qry != null)
      {
        qry.close();
      }
      
      if (conn != null)
      {
        try
        {
          conn.close();
        }
        catch (SQLException s)
        {}
      }
    }
    
    
  }
}

Note: From the code above we lookup a data source using the JNDI name "jdbc/scottDS" which exists for the Integrated Server. You will need to create that for this demo to work.

2. Ensure you add the required libraries within JDeveloper. Apart from the Jersey libraries for JAX-RS you will need the 3 extra libraries as shown below.

3. Run Query Service and eventually it should show it's ready as follows.

[01:28:26 PM] ---- Deployment finished. ----
Run startup time: 4461 ms.
[Application XMLSQLUtility deployed to Server Instance IntegratedWebLogicServer]

Target Application WADL -- http://localhost:7101/xmlsql-service/jersey/application.wadl

Target URL -- http://localhost:7101/xmlsql-service/jersey/queryservice/query?sql=string

4. Now using "curl" run some queries as shown below. Of course it only accepts SQL queries but still useful enough.

Query: select user from dual

[oracle@beast pas]$ curl http://paslap-au.au.oracle.com:7101/xmlsql-service/jersey/queryservice/query?sql="select user from dual"

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<ROWSET>
  <ROW num="1"><USER>SCOTT</USER></ROW>
</ROWSET>

Query: select * from dept

[oracle@beast pas]$ curl http://paslap-au.au.oracle.com:7101/xmlsql-service/jersey/queryservice/query?sql="select * from dept"

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<ROWSET>
<ROW num="1"><DEPTNO>10</DEPTNO><DNAME>ACCOUNTING</DNAME><LOC>NEW YORK</LOC</ROW>
<ROW num="2"><DEPTNO>20</DEPTNO><DNAME>RESEARCH</DNAME><LOC>DALLAS</LOC></ROW>
<ROW num="3"><DEPTNO>30</DEPTNO><DNAME>SALES</DNAME><LOC>CHICAGO</LOC></ROW>
<ROW num="4"><DEPTNO>40</DEPTNO><DNAME>OPERATIONS</DNAME><LOC>BOSTON</LOC></ROW></ROWSET>

Display DATE columns from Oracle JDBC

2011-04-26T11:16:00.000+10:00

If we display a DATE column from a Oracle JDBC program we may not get the desired output. This basic demo shows how to format the display to meet your own needs using the toText method from a oracle.sql.DATE object.

Code

package pas.au.jdbc.dates;

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

import java.util.Date;

import oracle.jdbc.OracleResultSet;
import oracle.jdbc.OracleStatement;
import oracle.jdbc.pool.OracleDataSource;

import oracle.sql.DATE;


public class FormatDateJDBC
{
  private OracleDataSource ods = null;
  private final String userId = "scott";
  private final String password = "tiger";
  private final String url = 
    "jdbc:oracle:thin:@beast.au.oracle.com:1524/linux11gr2";
  
  public FormatDateJDBC() 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()
  {
    Connection conn = null;
    OracleStatement stmt = null;
    OracleResultSet rset = null;
    
    try
    {
      conn = getConnection();
      System.out.println(conn);
      stmt = (OracleStatement)conn.createStatement();
      rset = (OracleResultSet) stmt.executeQuery("select SYSDATE from dual");
      rset.next();
      if (rset != null)
      { 
        DATE todaysDate = rset.getDATE(1);
        System.out.println
          (String.format("Todays date is %s", 
                         todaysDate.toText("DD/MM/YY HH:MM:SS", null)));
      }
    }
    catch (SQLException se)
    {
      se.printStackTrace();
    }
    finally
    {
      /* 
       * if exception occurs here no real need to log it just catch it and continue
       */
      if (rset != null)
      {
        try
        {
          rset.close();
        }
        catch (SQLException s)
        {}
      }
      
      if (stmt != null)
      {
        try
        {
          stmt.close();
        }
        catch (SQLException s)
        {}
      }     

      if (conn != null)
      {
        try
        {
          conn.close();
        }
        catch (SQLException s)
        {}
      }
    }
  }
  
  public static void main(String[] args)
  {
    FormatDateJDBC demo = null;
    try
    {
      System.out.println("Started JDBC display date at " + new Date());
      demo = new FormatDateJDBC();
      demo.run();
      System.out.println("Completed JDBC display date at " + new Date());
    }
    catch (SQLException e)
    {
      e.printStackTrace();
      System.exit(-1);
    }
  }
}

Output

Started JDBC display date at Tue Apr 26 11:07:59 EST 2011
oracle.jdbc.driver.T4CConnection@c24c0
Todays date is 26/04/11 10:04:38
Completed JDBC display date at Tue Apr 26 11:08:00 EST 2011

JRuby OTN How to's With FMW and Oracle RDBMS

2011-04-11T10:53:00.003+10:00

A couple of OTN how to's which show how to use JRuby with some of the Oracle Fusion Middleware products as well as single instance and RAC oracle databases.

1. Using JRuby with Oracle Database
http://www.oracle.com/technetwork/articles/dsl/jruby-oracle11g-330825.html

2. Use JRuby with JMX for Oracle WebLogic Server 11g
http://www.oracle.com/technetwork/articles/oem/jruby-wls-jmx-356114.html

3. Use a JRuby Script to Verify an Oracle RAC Setup using SCAN
http://www.oracle.com/technetwork/articles/oem/jruby-ucp-rac-355460.html

RESTful Web Service using JPA/EJB 3.0 Entities

2011-03-29T13:28:00.001+11:00

In this example I created a more common use case of using JAX-RS Web Service based on a JPA/EJB 3.0 entity. I will only show the code here and what the project looks like in JDeveloper but in short you would have a setup as follows based on a DEPT table in the classic SCOTT schema.

JAX-RS Web Service -> SLSB -> JPA/EJB 3.0 entity

1. Create the DEPT JPA/EJB 3.0 Entity using the"Entities from tables" EJB wizard.
2. Create a Statelesss Session bean which generates facade methods for the JPA Entity we created at step #1

Note: In this example we will just implement the default Dept.findAll named query created from the JPA Entity

package pas.au.jaxrs.jpa.dept;

import java.util.List;

import javax.ejb.Stateless;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

@Stateless(name = "DeptSessionEJB", mappedName = "JAX-RS-DeptJPADemo-Demo-DeptSessionEJB")
public class DeptSessionEJBBean
  implements DeptSessionEJB, DeptSessionEJBLocal
{
  @PersistenceContext(unitName="Demo")
  private EntityManager em;

  public DeptSessionEJBBean()
  {
  }

  public Object queryByRange(String jpqlStmt, int firstResult,
                             int maxResults)
  {
    Query query = em.createQuery(jpqlStmt);
    if (firstResult > 0)
    {
      query = query.setFirstResult(firstResult);
    }
    if (maxResults > 0)
    {
      query = query.setMaxResults(maxResults);
    }
    return query.getResultList();
  }

  /** <code>select o from Dept o</code> */
  public List<Dept> getDeptFindAll()
  {
    return em.createNamedQuery("Dept.findAll").getResultList();
  }
}

3. Create the JAX-RS Web Service as follows.

package pas.au.jaxrs.jpa.dept;

import java.util.Hashtable;
import java.util.List;

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

import javax.ws.rs.GET;
import javax.ws.rs.Path;
import javax.ws.rs.Produces;
import javax.ws.rs.core.MediaType;


@Path ("/deptjpaservice")
public class DeptService
{
  private DeptSessionEJB deptBean;
  
  public DeptService()
  {
    Context context;
    try
    {
      context = getInitialContext();
      deptBean = (DeptSessionEJB)
        context.lookup
         ("JAX-RS-DeptJPADemo-Demo-DeptSessionEJB#pas.au.jaxrs.jpa.dept.DeptSessionEJB");
    }
    catch (NamingException e)
    {
      throw new RuntimeException (e.getMessage());
    }
  }

  @GET
  @Path("/departments")
  @Produces(MediaType.APPLICATION_XML)
  public List<Dept> listDepartments()
  {
    return deptBean.getDeptFindAll();
  }

  private static Context getInitialContext() throws NamingException
  {
    return new InitialContext();
  }
}

You could deploy this to Weblogic 10.3.4as per a previous post. In the example client above we just rujn it in the Integrated Server from JDeveloper to test it out.

4. Run the JAX-RS Web Service when finished you should see the following in the log file.

[01:10:31 PM] ---- Deployment finished. ----
Run startup time: 11380 ms.
[Application JAX-RS-DeptJPADemo deployed to Server Instance IntegratedWebLogicServer]

Target Application WADL -- http://localhost:7101/jaxrs-deptjpa/jersey/application.wadl

Target URL -- http://localhost:7101/jaxrs-deptjpa/jersey/deptjpaservice/departments

5. Using curl access it from a putty client as follows.

> curl http://paslap-au.au.oracle.com:7101/jaxrs-deptjpa/jersey/deptjpaservice/departments

Of course the example above just shows how easy it is to call a JAX-RS service from a simple UNIX command line , but to be honest the easiest way is from JDeveloper itself using the HTTP analzyer by clicking on the link in the log window as shown below. At least this way the output is nicely formatted for you.

Output

<?xml version="1.0" encoding="UTF-8" standalone="yes"?><depts><department><deptno>10</deptno><dname>ACCOUNTING</dname><loc>NEW YORK</loc></department><department><deptno>20</deptno><dname>RESEARCH</dname><loc>DALLAS</loc></department><department><deptno>30</deptno><dname>SALES</dname><loc>CHICAGO</loc></department><department><deptno>40</deptno><dname>OPERATIONS</dname><loc>BOSTON</loc></department></depts>

The JDeveloper project would look as follows"

How-To: Use Oracle WebLogic Server with a JDBC GridLink Data Source

2011-03-09T10:10:00.000+11:00

This recently created how-to I created show's how to configure and test your new Oracle WebLogic 10.3.4 JDBC GridLink Data Source. The demo here is using an 11g R2 RAC cluster with SCAN. The link is as follows which includes a web test client.

http://www.oracle.com/technetwork/middleware/weblogic/wls-jdbc-gridlink-howto-333331.html