Search This Blog

Monday 26 December 2011

vFabric SQLFire - Modelling the classic DEPT/EMP tables

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

Monday 12 December 2011

GemFire Write-Behind with an Oracle RDBMS

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

Wednesday 23 November 2011

Running GFMon on a 64bit MAC OSX

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

 

Monday 14 November 2011

Accessing GemFire Regions from Jython

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  

Wednesday 9 November 2011

Accessing GemFire Regions from JRuby

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  

Monday 7 November 2011

Viewing All MBeans for a GemFire Cluster

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

Tuesday 4 October 2011

VMware vFabric - my new world

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

Wednesday 7 September 2011

Batch Inserting using Spring JDBC (JdbcTemplate)

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 

Monday 15 August 2011

Using Oracle Universal Connection Pool with Spring from JDeveloper 11.1.2

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.

Thursday 7 July 2011

Starting a Coherence Server From WLS 10.3.4 Console

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

Monday 4 July 2011

Installing WLS 10.3.4 domain across 2 Compute Nodes on Exalogic

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.

Tuesday 21 June 2011

Taking JSF 2.0 for a test drive with JDeveloper 11.1.2

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>

Friday 17 June 2011

WebLogic - Obtain Groups of an Autenticated User

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

Tuesday 31 May 2011

Struts 2 Starter Project for JDeveloper 11g (11.1.1.5)

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

Wednesday 25 May 2011

Intermittent Oracle JDBC Connection Failures to RAC

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]

Friday 29 April 2011

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

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>

Tuesday 26 April 2011

Display DATE columns from Oracle JDBC

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

Monday 11 April 2011

JRuby OTN How to's With FMW and Oracle RDBMS

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

Tuesday 29 March 2011

RESTful Web Service using JPA/EJB 3.0 Entities

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"