Friday, 17 February 2012

Using SQLFire sqlf CLI to connect to Oracle from a MAC OS-X Lion

Being on a MAC OS-X lion there is no client install for oracle to give me SQLPlus for example. BUT I can use sqlf command line client to connect to oracle to give me the ability to execute SQL much like SQLPLus. Handy indeed even if it isn't exactly SQLPlus it's good ienough for what I need when accessing my Oracle database from my MAC OS-X lion given thier is no oracle client for 11g on that platform.

It's done as shown below.

1. Add oracle JDBC driver to your classpath

export CLASSPATH=/Users/papicella/vmware/jdbcdrivers/11.2/ojdbc6.jar

2. Setup PATH to include SQLFire in your path as shown below.

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

3. Run some Oracle SQL as shown below using sql which shows how to load the driver, connect and run some SQL.

[Fri Feb 17 09:04:11 papicella@:~ ] $ sqlf
sqlf version 10.4
sqlf> driver 'oracle.jdbc.OracleDriver';
sqlf> connect 'jdbc:oracle:thin:scott/tiger@172.16.101.70:1521/linux11gr2';
sqlf> select to_char(sysdate, 'dd-MON-yyyy HH24:MI:SS') "Todays Date" from dual;
Todays Date                  
-----------------------------
17-FEB-2012 09:03:34         

1 row selected
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 v$version;
BANNER                                                                          
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production    
PL/SQL Release 11.2.0.2.0 - Production                                          
CORE 11.2.0.2.0 Production                                                      
TNS for Linux: Version 11.2.0.2.0 - Production                                  
NLSRTL Version 11.2.0.2.0 - Production                                          

5 rows selected
sqlf>

Tuesday, 7 February 2012

How to offload data from an existing data store and loading it into SQLFire

A common requirement for using SQLFire is offloading data from an existing data store and loading it into SQLFire. This example show how you can load data into a SQLFire distributed system using Spring Batch from a CSV file and then how to present the data from a simple Spring MVC application. Finally the data is persisted to each SQLFire member to ensure we only need to load the data once and it will always be retained upon restarts of the distributed system.

The full demo can be viewed on the VMware vFabric SQLFire blog using the link below.

http://blogs.vmware.com/sqlfire/2012/02/sqlfire-demo-loading-the-afl-2012-fixture-data.html

Sunday, 5 February 2012

SQLFire locator equals Load Balancing + Failover

In this demo we show how not only does a locator provide load balancing among the SQLFire members from client connections but also high availability should a member crash or perhaps be brought down. This is illustrated below when using a JDBC Connection pool.

1. First we have started a locator and 2 SQLFire members as shown below.

Locator:

sqlf locator start -peer-discovery-address=localhost -peer-discovery-port=41111 -client-bind-address=localhost -client-port=1527

Members:

sqlf server start -server-groups=MYGROUP -locators=localhost[41111] -client-bind-address=localhost -client-port=1528 -dir=server1 &
sqlf server start -server-groups=MYGROUP -locators=localhost[41111] -client-bind-address=localhost -client-port=1529 -dir=server2 &

2. You can verify this by issuing a query as shown below , prior to running a JDBC Connection pool test.

Note: Notice how we connect to the locator client hostname/port rather then the individual SQLFire members.
[Sun Feb 05 21:38:50 papicella@:~/vmware/software/sqlfire/vFabric_SQLFire_101/pasdemos/simpledemo ] $ sqlf
sqlf version 10.4
sqlf> connect client 'localhost:1527';
sqlf> select substr(netservers, 1, 30) as "Netservers", kind from sys.members;
Netservers                    |KIND            
-----------------------------------------------
localhost/127.0.0.1[1529]     |datastore(norma&
localhost/127.0.0.1[1528]     |datastore(norma&
localhost/127.0.0.1[1527]     |locator(normal) 

3 rows selected

3. Here is our JDBC Connection Pool which shows that it's connecting to the locator itself to establish JDBC Conections.
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd">

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

4. At this point we create a test class which simply gets 5 connections and displays which SQLFire member we are connected to. So the process is as follows to test both load balancing and high availability when a member disappears, ensuring that existing connections fail over to the remaining SQLFire member.

- start JDBC Connection pool
- get 5 connections
- display each connection server side SQLFire member (It should load balance between our servers in this case we have 2 of them)
- Sleep for 10 seconds
- get 5 connections (verify that indeed we are only connected to the surviving member)

5. Output as follows.

Feb 5, 2012 9:59:26 PM org.springframework.context.support.AbstractApplicationContext prepareRefresh
INFO: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@657d5d2a: startup date [Sun Feb 05 21:59:26 EST 2012]; root of context hierarchy
Feb 5, 2012 9:59:26 PM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
INFO: Loading XML bean definitions from class path resource [application-config-SQLFIRE.xml]
Feb 5, 2012 9:59:26 PM org.springframework.beans.factory.support.DefaultListableBeanFactory preInstantiateSingletons
INFO: Pre-instantiating singletons in org.springframework.beans.factory.support.DefaultListableBeanFactory@62da3a1e: defining beans [sqlfireUcpDataSource]; root of factory hierarchy
Feb 5, 2012 9:59:27 PM vmware.au.sqlfire.pool.TestSQLFireJDBCPool run
INFO: $Proxy0@1dfd868
Feb 5, 2012 9:59:27 PM vmware.au.sqlfire.pool.TestSQLFireJDBCPool run
INFO: Getting 5 connections from pool
Connection 0 [id=192-168-1-4.tpgi.com.au(1426):55091/50932, netserver=localhost/127.0.0.1[1528]]
Connection 1 [id=192-168-1-4.tpgi.com.au(1426):55091/50932, netserver=localhost/127.0.0.1[1528]]
Connection 2 [id=192-168-1-4.tpgi.com.au(1425):52022/50935, netserver=localhost/127.0.0.1[1529]]
Connection 3 [id=192-168-1-4.tpgi.com.au(1425):52022/50935, netserver=localhost/127.0.0.1[1529]]
Connection 4 [id=192-168-1-4.tpgi.com.au(1426):55091/50932, netserver=localhost/127.0.0.1[1528]]

Feb 5, 2012 9:59:27 PM vmware.au.sqlfire.pool.TestSQLFireJDBCPool run
INFO: Closing all connections from pool
Feb 5, 2012 9:59:27 PM vmware.au.sqlfire.pool.TestSQLFireJDBCPool run
INFO: ** Pool Details **
NumberOfAvailableConnections: 5
BorrowedConnectionsCount: 0


Sleeping for 20 seconds, shutdown a SQLFire server at this point...

6. Shutdown a server as shown below , before the existing program wakes up..

[Sun Feb 05 21:45:34 papicella@:~/vmware/software/sqlfire/vFabric_SQLFire_101/pasdemos/simpledemo ] $ sqlf server stop -dir=server1
The SQLFire Server has stopped.

7. Verify final output as follows

....

Feb 5, 2012 9:59:47 PM vmware.au.sqlfire.pool.TestSQLFireJDBCPool run
INFO: $Proxy0@580754fc
Feb 5, 2012 9:59:47 PM vmware.au.sqlfire.pool.TestSQLFireJDBCPool run
INFO: Getting 5 connections from pool
Connection 0 [id=192-168-1-4.tpgi.com.au(1425):52022/50935, netserver=localhost/127.0.0.1[1529]]
Connection 1 [id=192-168-1-4.tpgi.com.au(1425):52022/50935, netserver=localhost/127.0.0.1[1529]]
Connection 2 [id=192-168-1-4.tpgi.com.au(1425):52022/50935, netserver=localhost/127.0.0.1[1529]]
Connection 3 [id=192-168-1-4.tpgi.com.au(1425):52022/50935, netserver=localhost/127.0.0.1[1529]]
Connection 4 [id=192-168-1-4.tpgi.com.au(1425):52022/50935, netserver=localhost/127.0.0.1[1529]]

Feb 5, 2012 9:59:47 PM vmware.au.sqlfire.pool.TestSQLFireJDBCPool run
INFO: Closing all connections from pool
Feb 5, 2012 9:59:47 PM vmware.au.sqlfire.pool.TestSQLFireJDBCPool run
INFO: ** Pool Details **
NumberOfAvailableConnections: 5
BorrowedConnectionsCount: 0


As you can see from the output above we have switched over all existing connections in our JDBC pool to use the remaining SQLFire member only.

The test class used here is as follows.
package vmware.au.sqlfire.pool;

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

import oracle.ucp.jdbc.PoolDataSource;

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

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

 public void run()
 {
  Connection conn = null;
  List<Connection> connections = new ArrayList<Connection>();
  
  try 
  {
   // ensure pool is started by getting a connection object
   conn = pds.getConnection();
   log.info(conn.toString());
   conn.close();
   
   log.info("Getting 5 connections from pool");
   
   for (int i = 0; i < 5; i++)
   {
    connections.add(pds.getConnection());
   }

   for (int i = 0; i < 5; i++)
   {
    getInstanceDetails(connections.get(i), i);
   }
   
   log.info("Closing all connections from pool");
   
   for (Connection connection: connections)
   {
     connection.close(); 
   }
   
   log.info(displayPoolDetails());
   
 
  } 
  catch (SQLException e) 
  {
   // TODO Auto-generated catch block
   e.printStackTrace();
  } 
  finally
  {
   connections = null;
  }
 }

 public void getInstanceDetails (Connection conn, int i) throws SQLException
 {
  Statement stmt = null;
  PreparedStatement pstmt = null;
  ResultSet rset = null;
  String idSql = "select dsid() from sysibm.sysdummy1";
  String netSeverSql = "select netservers from sys.members where id = ?";
  
  stmt = conn.createStatement();
  rset = stmt.executeQuery(idSql);
  rset.next();
  
  String id = rset.getString(1);
  
  rset = null;
  pstmt = conn.prepareStatement(netSeverSql);
  pstmt.setString(1, id);
  rset = pstmt.executeQuery();
  rset.next();
  
  String netServer = rset.getString(1);
  
  System.out.println
    (String.format("Connection %s [id=%s, netserver=%s]", i, id, netServer));
  
  rset.close();
  stmt.close();
  pstmt.close();
  
 }
   
    public String displayPoolDetails () throws SQLException
    {
      StringBuffer sb = new StringBuffer();
      
      sb.append("** Pool Details **\n");
      sb.append("NumberOfAvailableConnections: " +
                         pds.getAvailableConnectionsCount());
      sb.append("\nBorrowedConnectionsCount: " +
                         pds.getBorrowedConnectionsCount());
      sb.append("\n");
      
      return sb.toString();
    }
    
 /**
  * @param args
  * @throws InterruptedException 
  */
 public static void main(String[] args) throws InterruptedException 
 {
  TestSQLFireJDBCPool test = new TestSQLFireJDBCPool();
  test.run();
        System.out.println("\nSleeping for 20 seconds, shutdown a SQLFire server at this point...\n");
  Thread.sleep(20000);
  test.run();
 }

}