Monday, 8 February 2010

SCAN 11g R2 JDBC Load Balance Test

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

Output

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

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

Code

  
package au.support.jdbc.scan;

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

import java.sql.Statement;

import java.util.Date;

import oracle.jdbc.pool.OracleDataSource;

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

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

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

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

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

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

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

// gets driver info:

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

getInstanceDetails(connArray[j], j);
}

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

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

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

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

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