SCAN 11g R2 JDBC Load Balance Test

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

Output

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

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

Code

  
package au.support.jdbc.scan;

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

import java.sql.Statement;

import java.util.Date;

import oracle.jdbc.pool.OracleDataSource;

public class LoadBalanceTest
{
  private OracleDataSource ods = null;
  public final String userId = "scott";
  public final String password = "tiger";
  
  private static final String url =
    "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)" +
    "(HOST=apctcsol1.au.oracle.com)(PORT=1521))" +
    "(CONNECT_DATA=(SERVICE_NAME=J11G2)))";
  
  public LoadBalanceTest() throws SQLException
  {
    ods = new OracleDataSource();
    ods.setUser(userId);
    ods.setPassword(password);
    ods.setURL(url);  
  }

  public Connection getConnection() throws SQLException
  {
    return ods.getConnection();
  }
  
  public void run () throws SQLException
  {
    Connection[] connArray = new Connection[5];
    
    System.out.println("Obtaining 5 connections");
    for (int i = 0; i < connArray.length; i++)
    {
      connArray[i] = getConnection();
    }
    
    for (int j = 0; j < connArray.length; j++)
    {
      if (j == 0)
      {
        DatabaseMetaData meta = connArray[j].getMetaData ();

        // gets driver info:

        System.out.println("\n=============\nDatabase Product Name is ... " +
        meta.getDatabaseProductName());
        System.out.println("Database Product Version is  " +
        meta.getDatabaseProductVersion());
        System.out.println("=============\nJDBC Driver Name is ........ " +
        meta.getDriverName());
        System.out.println("JDBC Driver Version is ..... " +
        meta.getDriverVersion());
        System.out.println("JDBC Driver Major Version is " +
        meta.getDriverMajorVersion());
        System.out.println("JDBC Driver Minor Version is " +
        meta.getDriverMinorVersion());
        System.out.println("=============");                
      }
      
      getInstanceDetails(connArray[j], j);
    } 
    
    System.out.println("Closing Connections");
    for (int y = 0; y < connArray.length; y++)
    {
      connArray[y].close();
    } 
  }

  public void getInstanceDetails (Connection conn, int i) throws SQLException
  {
    String sql = 
      "select sys_context('userenv', 'instance_name'), " + 
      "sys_context('userenv', 'server_host'), " + 
      "sys_context('userenv', 'service_name') " + 
      "from dual";
    
    Statement stmt = conn.createStatement();
    ResultSet rset = stmt.executeQuery(sql);
    while (rset.next())
    {
      System.out.println
        ("Connection #" + i + " : instance[" + rset.getString(1) + "], host[" + 
         rset.getString(2) + "], service[" + rset.getString(3) + "]");
    }
   
    stmt.close();
    rset.close();
  }
  
  public static void main(String[] args)
  {
    LoadBalanceTest loadBalanceTest;
    try
    {
      System.out.println("Test Started at " + new Date());
      loadBalanceTest = new LoadBalanceTest();
      loadBalanceTest.run();
      System.out.println("Test Ended at " + new Date());
    }
    catch (SQLException e)
    {
      e.printStackTrace();
      System.exit(1);
    }
  }
}