Monday, 31 January 2011

JRuby Oracle RAC 11g R2 SCAN Client Demo

Previously I created a JDBC client against an 11g R2 RAC client using SCAN as shown here. That example simply ensures we are load balancing between the RAC nodes in the cluster because from the JDBC URL we would not even know we are connecting to a RAC cluster.

In the example below we use some JRuby code to do the exact same thing. We are using the Oracle JDBC Driver and an RAC cluster as well.

JRuby Code

require 'java'  
require 'C:/jdev/jdcbdrivers/11.2/11202/ojdbc6.jar'  
java_import java.sql.Statement
java_import java.sql.Connection
java_import java.sql.SQLException
java_import java.sql.DatabaseMetaData

java_import 'oracle.jdbc.OracleDriver'
java_import 'oracle.jdbc.pool.OracleDataSource'

select sys_context('userenv', 'instance_name'),
sys_context('userenv', 'server_host'), 
sys_context('userenv', 'service_name')
from dual

class MyOracleDataSource   
  def initialize(user, passwd, url)  
    @user, @passwd, @url = user, passwd, url
    @ods =
    @ods.set_user user
    @ods.set_password passwd
    @ods.set_url url
  # add getters and setters for all attrributes we wish to expose  
  attr_reader :user, :passwd, :url

  def get_connection()
  def create_statement()
  def to_s  
    "OracleDataSource  [user=#{@user}, passwd=#{@passwd}, " +  

  def self.create(user, passwd, url)
    ods = new(user, passwd, url)
    puts "\n** Error occured **\n"  
    puts "Failed executing SCAN Load Blance test from JRuby ", $!, "\n"

user = "scott"
passwd = "tiger"
url = ""

print "Run at #{} using JRuby #{RUBY_VERSION}\n\n"  

mods = MyOracleDataSource.create(user, passwd, url)

# empty array
conns = []

# obtain 5 connections
for i in 1..5 
  conns[i] = mods.get_connection

# determine instance details
for i in 1..5
  if (i == 1)
    meta = conns[i].get_meta_data
    puts "=============\nDatabase Product Name is ... #{meta.getDatabaseProductName()}"  
    puts "Database Product Version is  #{meta.getDatabaseProductVersion()}"  
    puts "=============\nJDBC Driver Name is ........ #{meta.getDriverName()}" 
    puts "JDBC Driver Version is ..... #{meta.getDriverVersion()}"
    puts "JDBC Driver Major Version is #{meta.getDriverMajorVersion()}"  
    puts "JDBC Driver Minor Version is #{meta.getDriverMinorVersion()}" 
    puts "============="    
  stmt = conns[i].create_statement
  rset = stmt.execute_query INSTANCE_SQL
  puts "Connection #{i} : instance [#{rset.get_string 1}], " + 
       "host[#{rset.get_string 2}], service[#{rset.get_string 3}] "

#close the 5 connections
for i in 1..5

print "\nEnded at #{}"


C:\jdev\scripting\demos\jruby\jdbc\scan-load-balance-test>jruby scan-lbt.rb
Run at Mon Jan 31 14:28:11 +1100 2011 using JRuby 1.8.7

Database Product Name is ... Oracle
Database Product Version is  Oracle Database 11g Enterprise Edition Release - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
JDBC Driver Name is ........ Oracle JDBC driver
JDBC Driver Version is .....
JDBC Driver Major Version is 11
JDBC Driver Minor Version is 2
Connection 1 : instance [A12], host[auw2k4], service[pas_srv]
Connection 2 : instance [A11], host[auw2k3], service[pas_srv]
Connection 3 : instance [A12], host[auw2k4], service[pas_srv]
Connection 4 : instance [A11], host[auw2k3], service[pas_srv]
Connection 5 : instance [A12], host[auw2k4], service[pas_srv]

Ended at Mon Jan 31 14:28:12 +1100 2011

No comments: