C-JDBC User's Guide

C-JDBC is a database cluster middleware that allows any Java™ application (standalone application, servlet or EJB™ container, ...) to transparently access a cluster of databases through JDBC™. You do not have to modify client applications, application servers or database server software. You just have to ensure that all database accesses are performed through C-JDBC.

C-JDBC is a free, open source project of the ObjectWeb Consortium. It is licensed under the GNU Lesser General Public License (LGPL).

In order to use C-JDBC, you will need:

	Note
	If your client application uses ODBC, it is possible to use an ODBC-JDBC bridge such as the unixODBC provided by Easysoft.

You have a Java application or a Java-based application server that accesses one or several databases. The database tier becomes the bottleneck of your application or it is a single point of failure or both. C-JDBC can help you resolve these problems by providing:

C-JDBC provides a flexible architecture that allows you to achieve scalability, high availability and failover with your database tier. C-JDBC implements the concept of RAIDb: Redundant Array of Inexpensive Databases (see Section 10, “RAIDb Basics”). The database is distributed and replicated among several nodes and C-JDBC load balances the queries between these nodes.

C-JDBC provides a generic JDBC driver to be used by the clients (see Section 4, “C-JDBC Driver”). This driver forwards the SQL requests to the C-JDBC controller (see Section 6, “C-JDBC controller”) that balances them on a cluster of databases (reads are load balanced and writes are broadcasted). C-JDBC can be used with any RDBMS (Relational DataBase Management System) providing a JDBC driver, that is to say almost all existing open source and commercial databases. Figure 1, “C-JDBC principle” gives an overview of the C-JDBC principle.

Figure 1. C-JDBC principle

C-JDBC allows to build any cluster configuration including mixing database engines from different vendors. The main features provided by C-JDBC are performance scalability, fault tolerance and high availability. Additional features such as monitoring, logging, SQL requests caching are provided as well.

The architecture is widely open to allow anyone to plug custom requests schedulers, load balancers, connection managers, caching policies, ...

From a software point of view, C-JDBC is an open-source software licensed under LGPL which means that it is free of charge for any usage (personal or commercial). If you are using commercial RDBMS (such as Oracle, DB2, ...), you will have to buy extra licenses for the nodes where you install replicas of the database. But you can possibly use open-source databases to host replicas of your main database.

You need to buy extra machines if you want more performance and more fault tolerance. C-JDBC has been designed to work with standard off-the-shelf workstations because it primarily targets low cost open-source solutions but it can work as well with large SMP machines. A standard Ethernet network is sufficient to achieve good performance.

You do not have to change anything to your application or your database.

You only have to update the JDBC driver configuration used by your application (usually it is just a configuration file update) and to setup a C-JDBC configuration file (see Section 11, “Virtual database configuration”).

Once you have installed the C-JDBC controller, you will find the driver JAR file in the drivers/ directory of the controller installation location.

To install the C-JDBC driver, you just have to add the c-jdbc-driver.jar file to the client application classpath. This driver replaces the database native driver in the client application. The database native driver will be used by the C-JDBC controller to access your database. Therefore, the C-JDBC driver and controller can be seen as a proxy between your application and your database native driver.

Since version 1.0b13, you have access to a RAIDb-1 configuration of HyperSonic SQL databases, just by launching the demo-raidb1.sh or demo-raidb1.bat file from the demo directory in your C-JDBC installation.

This is especially useful if you are new to clustering, or new to C-JDBC. The setup used is as follows:

Squirrel shows the C-JDBC driver and HSQL database driver, and links to databases. You can click on them to view the different data. The login to use for C-JDBC is user with an empty password. The login for both HSQL databases is test with an empty password.

The C-JDBC driver is a generic JDBC driver that is designed to replace any database specific JDBC driver that could be used by a client. The client only has to know on which node the C-JDBC controller is running and the name of the database to access. The C-JDBC driver implements most of the JDBC 2.0 interface and some functionalities from JDBC 3.0 such as the support for autogenerated keys.

Users reported successful usage of C-JDBC with the following RDBMS: Oracle®, PostgreSQL, MySQL, Apache Derby, IBM DB2®, Sybase®, SAP DB (MySQL MaxDB), HyperSonic SQL, Firebird, MS SQL Server and InstantDB.

The C-JDBC driver can be loaded as any standard JDBC driver from the client program using:

 
        Class.forName("org.objectweb.cjdbc.driver.Driver"); 
      

	Note
	The c-jdbc-driver.jar file must be in the client classpath else the driver will fail to load.

The JDBC URL expected for the use with C-JDBC is the following: jdbc:cjdbc://host1:port1,host2:port2/database.

host is the machine name (or IP address) where the C-JDBC controller is running, port is the port the controller is listening for client connections.

At least one host must be specified but a list of comma separated hosts can be specified. If several hosts are given, one is picked up randomly from the list. If the currently selected controller fails, another one is automatically picked up from the list.

The port is optional is the URL and the default port number is 25322 (C-JDBC on the phone!) if it is omitted. Those two examples are equivalent:

 
        DriverManager.getConnection("jdbc:cjdbc://localhost/tpcw"); 
        DriverManager.getConnection("jdbc:cjdbc://localhost:25322/tpcw"); 
        

Examples using two controllers for fault tolerance:

 
        DriverManager.getConnection("jdbc:cjdbc://c1.objectweb.org,c2.objectweb.org/tpcw"); 
        DriverManager.getConnection("jdbc:cjdbc://localhost,remote.objectweb.org:2048/tpcw"); 
        DriverManager.getConnection("jdbc:cjdbc://smpnode.com:25322,smpnode.com:1098/tpcw"); 
        

 
        DriverManager.getConnection("jdbc:cjdbc://host/db?user=me&password=secret")

 
        DriverManager.getConnection("jdbc:cjdbc://host/db;user=me;password=secret")

By default, the C-JDBC driver interprets the PreparedStatement locally and forwards a pre-processed statement. The C-JDBC controller executes directly these statements on the backends as statements without recreating a whole PreparedStatement and re-calling all setXXX() methods on the PreparedStatement. The default setting is the one that consumes the less resources and carries the minimum information over the network.

However, it might happen that this behavior is not desired or that the C-JDBC driver interpretation is unsuitable for some data types or database specific syntax. Therefore, it is possible to make the driver act as a real proxy that will forward all setXXX() calls to the database native driver. This will usually result in slightly lower performance but better portability.

The proxying mode can be enabled for a connection by setting a specific variable named driverProcessed to false (default value if omitted is true). To enable PreparedStatement proxying in C-JDBC use a connection URL like this:

 
				DriverManager.getConnection("jdbc:cjdbc://host/db?driverProcessed=false")
		  

Note: since any optional blob encoding is performed by the driver, blob encoding is disabled by driverProcessed=false. If you ever encoded blobs, you CANNOT switch to driverProcessed=false anymore.

Another way to use the C-JDBC driver is to use its DataSource implementation. Data sources have been introduced in JDBC 2.0 Standard Extension API and are also a part of JDBC 3.0. They use the Java Naming and Directory Interface (JNDI) to break the application dependence on the JDBC driver configuration (i.e., driver class name, machine name, port number, etc.). With a data source, the only thing an application has to know is the name assigned to the DataSource object in the jdbc naming subcontext of the JNDI namespace.

The example below registers a data source object with a JNDI naming service. It is typically used by an application server.

      import org.objectweb.cjdbc.driver.DataSource;
      import javax.naming.Context;
      import javax.naming.InitialContext;
      import javax.naming.NamingException;
      ...
      private final static String NAME = "jdbc/c-jdbc";
      private final static String URL = "jdbc:cjdbc://localhost:25322/mysql";
      
      // Initializing data source
      DataSource ds = new DataSource();
      ds.setUrl(URL);

      // Get initial context
      Context ctx;
      try {
        ctx = new InitialContext();
      } catch (javax.naming.NamingException _e) {
        ... // Naming exception
      }
		
      // Bind data source to a JNDI name
      try {
        ctx.bind(NAME, ds);
      } catch (javax.naming.NamingException _e) {
        ... // Naming exception
      }
      

The org.objectweb.cjdbc.driver.DataSource class implements the javax.sql.DataSource JDBC 3.0 interface. The setUrl line initializes the data source properties (the URL in this case). The data source object is bound to a logical JNDI name by calling ctx.bind(). In the example above, the JNDI name specifies a "jdbc" subcontext and a "c-jdbc" logical name within this subcontext.

Once a data source object is registered to JNDI, it can be used by an application. The example below gets the data source using the JNDI naming service. Such a piece of code is typically a part of an application that uses JDBC.

      import javax.naming.Context;
      import javax.naming.InitialContext;
      import javax.naming.NamingException;
      import java.sql.Connection;
      import javax.sql.DataSource;
      ...
      private final static String NAME = "jdbc/c-jdbc";

      // Lookup for the data source object
      try {
        Context ctx = new InitialContext();
        Object obj = ctx.lookup(NAME);
        if (null == obj) {
          ... // Something wrong: NAME not found
        }
        ctx.close( );
      } catch (javax.naming.NamingException _e) {
        ... // Naming exception
      }
      
      // Get a new JDBC connection
      try {
        DataSource ds = (DataSource) obj;
        Connection conn = ds.getConnection("user", "c-jdbc");
        ... // Use of the connection retrieved
        ...
      } catch (SQLException _e) {
        ... // SQL exception
      }
      

The ctx.lookup() line in the example uses the retrieved initial JNDI naming context to do a lookup using the data source logical name. The method returns a reference to a Java object which is then narrowed to a javax.sql.DataSource object. Such an object can be then used to open a new JDBC connection by invoking one of its getConnection() methods. The application code is completely independent of the driver details, such as the Driver class name, URL, etc. (the user name and password used by the connection can be also set by the application server - look at the C-JDBC javadoc documentation for more details). The only information a JDBC application has to know is the logical name of the data source object to use.

	Note
	The URL used for the C-JDBC data source is the same as for the Driver decribed in the previous section.

Stored procedures are supported by C-JDBC since version 1.0b6. Note that C-JDBC only support calls in the form {call <procedure-name>[<arg1>,<arg2>, ...]} but does not support {? = call <procedure-name>[<arg1>,<arg2>, ...]}.

A call to a stored procedure is systematically broadcasted to all backends since there is no way to know if the stored procedure will update the database or not. Therefore, the query cache (see Section 11.6.3, “Request Cache”), is completely flushed on every stored procedure call. To prevent cache flushing, the user can force the connection to read-only before calling the stored procedure. But never set a connection to read-only when calling a stored procedure that updates the database. If C-JDBC detects a read-only connection, it will not flush the cache. However, the call will still be broadcasted to all nodes resulting in duplicated jobs on each backend. Here is an example on how to prevent cache flushing when calling a stored procedure that does only read-only:

 
        ... 
        CallableStatement cs = connection.prepareCall("{call myproc(?)}"); 
        cs.setString(1,"parameter1");
        // Force no cache flush  
        connection.setReadOny(true); 
        // Call the stored procedure without flushing the cache ... 
        ResultSet rs = cs.executeQuery(); 
      

In the case of horizontal scalability, only read-only stored procedures are not broadbasted. All other stored procedures returning an int or a ResultSet are executed by all backends at all controllers.

	Note
	It is not allowed to set a connection to read-only in the middle of a transaction. If you need to set a connection to read-only, you must do so before starting the transaction.

Binary large objects can now be stored using the C-JDBC driver since 1.0b10. Data is encoded into hexadecimal to be portable accross database engines.

FIXME: this whole section is outdated and should be re-written. Blob encoding is now configured in controller's DTD and set to "none" by default. It requires driverProcessed=true, which is the default.

	Note
	The column type used to store large objects with MySQL is text. The column type used to store large objects with PostgreSQL is bytea.

You should not have to change your code for storing blobs into your database, but previous blobs have to be converted to their hexadecimal form. You can use Octopus to perform this transformation.

Please refer to the following lines of code for storing and retrieving of large objects:

        // In the code below:
        // The signature of the readBinary method is:
        // byte[] readBinary(File file) throws IOException
        // it just read a file, and convert its content into an array of bytes
        
        // Store file in database 
        File fis = new File(storeFile); 
        query = "insert into ... values(...,?)"; 
        ps1 = con.prepareStatement(query); 
        if (callBlobMethods) 
        {
          org.objectweb.cjdbc.driver.Blob bob = 
            new org.objectweb.cjdbc.driver.Blob(readBinary(fis)); 
          ps1.setBlob(1, bob); 
        } 
        else 
        { 
          ps1.setBytes(1, readBinary(fis)); 
        } 
        ps1.executeUpdate();
        // Read File from database 
        query = "select * from ... where id=..."; 
        ps1 = con.prepareStatement(query); 
        ResultSet rs = ps1.executeQuery(); 
        rs.first(); 
        byte[] lisette; 
        if (callBlobMethods) 
        { 
          Blob blisette = rs.getBlob("blobcolumnname"); 
          lisette = blisette.getBytes((long) 1, (int) blisette.length());
        }
        else 
        {
          lisette = rs.getBytes("blobcolumnname"); 
        } 
      

CLOB is a built-in type that stores a Character Large Object as a column value in a row of a database table. By default drivers implement Clob using an SQL locator (CLOB), which means that a Clob object contains a logical pointer to the SQL CLOB data rather than the data itself. A Clob object is valid for the duration of the transaction in which it was created.

Clobs in C-JDBC are handled like strings. You can refer to the section of code below to make good usage of clobs. This code is part of the C-JDBC test suite.

 
        String clob = "I am a clob"; 
        ps = con.prepareStatement("insert into ... values(...,?)"); 
        ps.setString(1, clob); 
        ps.executeUpdate(); 
        
        // Test retrieval 
        String ret; 
        ps = con.prepareStatement("Select * from ... where id=..."); 
        rs = ps.executeQuery(); 
        rs.first(); 
        clob = rs.getClob("name"); 
        ret = clob.getSubString((long) 0, (int) clob.length()); 
      

In its default mode, when a query is executed on a backend, C-JDBC makes a copy of the backend's native ResultSet into a C-JDBC serializable ResultSet. If the result contains many rows or very large objects, the controller might run out of memory when trying to copy the whole ResultSet.

Since C-JDBC 1.0rc6, it is possible to fetch ResultSets by blocks using the Statement.setFetchSize(int rows) method. In this case, the ResultSet will be copied by block of rows and returned when needed by the client. Note that the current implemtation only allows to fetch forward streamable ResultSet, which basically means that you are only allowed to call ResultSet.next() on a streamable ResultSet.

C-JDBC will try to call setFetchSize() on the backend's driver to let the backend driver also perform the necessary optimizations. However, some driver requires a prior call to setCursorName() in which case you will also have to call setCursorName() on C-JDBC to pass it to the backend's driver.

A typical usage of the ResultSet streaming feature is as follows:

...        
Connection con = getCJDBCConnection();
con.setAutocommit(false);
Statement s = con.createStatement();
s.setCursorName("cursor name");
s.setFetchSize(10);
rs = s.executeQuery(sql);
while (rs.next())
{ // Every 10 calls, C-JDBC will transfer a new block of rows
  XXX o = rs.getXXX("some column name");
}
...
con.commit();
      

Note

Streamable ResultSets are not cacheable. The result cache automatically detects this kind of ResultSet and does not keep them in the cache. However, as database specific ResultSets are copied into C-JDBC ResultSets, the memory footprint of the fetched blocks will be twice the one obtained without C-JDBC. If you have memory restrictions, you can reduce your fetch size by half to reduce the memory footprint of streamed ResultSets. Streamable ResultSets do not work properly in autocommit mode as the connection used for retrieving the ResultSet is handed back to the pool. The workaround is to always encapsulate the query in a transaction. Note that databases such as PostgreSQL do not support streamable ResultSets in autocommit mode as well.

The C-JDBC driver currently does not support the following features:

If the application you are using C-JDBC with requires a mapper, the best thing to do is to configure the mapping to be that of C-JDBC's underlying databases. For example, if you were using JBoss with PostgreSQL , then using C-JDBC on top of the PostgreSQL backends with JBoss would imply to still use the mapping for PostgreSQL while plugging the application server to C-JDBC (using C-JDBC's driver and C-JDBC's url).

Copy the c-jdbc-driver.jar file to the lib directory of your web application (for example: $TOMCAT_HOME/webapps/mywebapp/WEB-INF/lib).

There are many ways to obtain connections from a Tomcat application. Just ensure that you are using org.objectweb.cjdbc.driver.Driver as the driver class name and that the JDBC URL is a C-JDBC URL (see Section 4.3, “C-JDBC JDBC URL”).

The c-jdbc-driver.jar file must be found in the JOnAS CLASSPATH.

Here is an example of a cjdbc.properties file to store in JONAS 3.x conf directory (use the config directory for JOnAS 2.x):

 
        ###################### C-JDBC DataSource configuration example # 
        datasource.name      jdbc_1 
        datasource.url       jdbc:cjdbc://someMachine/someDatabase 
        datasource.classname org.objectweb.cjdbc.driver.Driver 
        datasource.username  your-username 
        datasource.password  your-password 
      

Copy the c-jdbc-driver.jar file to $JBOSS_DIST/server/default/lib for JBoss 3.x or to $JBOSS_DIST/jboss/lib/ext for JBoss 2.x.

Here is an example of a datasource configuration file to be used with JBoss:

 
        <?xml version="1.0" encoding="UTF-8"?> 
        <!-- ===================================================================== --> 
        <!--                                                                       --> 
        <!-- JBoss Server Configuration                                            -->
        <!--                                                                       --> 
        <!-- ===================================================================== --> 

        <!-- ===================================================================== --> 
        <!-- Datasource config for C-JDBC                                          -->
        <!-- ===================================================================== --> 
        <datasources> 
          <local-tx-datasource> 
            <jndi-name>cjdbc-DS</jndi-name> 
            <connection-url>jdbc:cjdbc://localhost:25322/lscluster</connection-url> 
            <driver-class>org.objectweb.cjdbc.driver.Driver</driver-class> 
            <user-name>user</user-name> 
            <password>tagada</password> 
          </local-tx-datasource> 
        </datasources> 
      

Place the c-jdbc-driver.jar file in the classpath of the Weblogic Server.

Here is an example of a connection pool configuration for use with Weblogic:

    <JDBCConnectionPool 
        DriverName="org.objectweb.cjdbc.driver.Driver"
        InitialCapacity="1"  MaxCapacity="15" 
        Name="cjdbcPool" Properties="user=username;password=password" 
        ShrinkingEnabled="true" SupportsLocalTransaction="true" 
        Targets="wlservername" URL="jdbc:cjdbc://192.168.0.1/vdb" 
        XAPreparedStatementCacheSize="0"/>
      

Next, create the required TXDataSources:

        <JDBCTxDataSource EnableTwoPhaseCommit="true" 
          JNDIName="cjdbc-DS" Name="C-JDBC TX Data Source" 
          PoolName="cjdbcPool" RowPrefetchEnabled="true" Targets="wlservername"/>
      

C-JDBC just has to be defined as any JDBC driver in Hibernate, leaving the syntax set to the proper database. Here is a configuration example to use Hibernate with a C-JDBC cluster made of Sybase backends:

## C-JDBC
hibernate.dialect                 net.sf.hibernate.dialect.SybaseDialect
hibernate.connection.driver_class org.objectweb.cjdbc.driver.Driver
hibernate.connection.username     user
hibernate.connection.password     pass
hibernate.connection.url          jdbc:cjdbc://localhost:25322/test        
      

Our Hibernate dialect is as follows:

 
        import net.sf.hibernate.dialect.PostgreSQLDialect; 
        public class CJDBCPostgreSQLDialect extends PostgreSQLDialect 
        { 
          public String getSequenceNextValString(String sequenceName) 
          { 
            return "{call nextval('"+sequenceName+"')}"; 
          } 
        } 
      

We simply extend the default PostgreSQL Dialect and override the getSequenceNextValString() method and tell it to use "{call ..." so that all the sequences in the cluster get incremented.

We then changed our Hibernate conf file to user to our custom dialect instead of net.sf.hibernate.dialect.PostgreSQLDialect.

The C-JDBC controller is made of several components as shown in Figure 2, “C-JDBC controller design overview”. The controller hosts virtual databases. A virtual database gives the illusion of a single database to the user. It exports the same database name and login/password as those used in the client application. Therefore the client application can run unmodified with C-JDBC.

When the client application connects to the database using an URL like jdbc:cjdbc://host:25322/myDB, the C-JDBC driver tries to connect to a C-JDBC controller running on port 25322 on node host. Once the connection is established the login and password are sent with the myDB database name to be checked by the controller.

A virtual database contains the following components:

Figure 2. C-JDBC controller design overview

Each virtual database and its components are configured using an XML configuration file that is sent from the administration console to the C-JDBC controller.

	Note
	A research report details RAIDb and C-JDBC implementation. Other documents and presentations about C-JDBC can be found in the documentation section of the web site.

The bin directory of the C-JDBC distribution contains the scripts to start the controller. Unix users must start the controller with controller.sh whereas Windows users will use controller.bat.

Since C-JDBC Controller version 1.0b11, the controller start is tuned via a configuration file, called controller.xml, included under the config/controller directory of your C-JDBC installation. A simple configuration file looks like this:

A standard C-JDBC Controller configuration file looks like this:

<?xml version="1.0" encoding="ISO-8859-1" ?>
<!DOCTYPE C-JDBC-CONTROLLER PUBLIC "-//ObjectWeb//DTD C-JDBC-CONTROLLER 2.0.2//EN"  "http://c-jdbc.objectweb.org/dtds/c-jdbc-controller-2.0.2.dtd">
<C-JDBC-CONTROLLER>
	<Controller port="25322">
    <Report hideSensitiveData="true" generateOnFatal="true"/>
    <JmxSettings>
      <RmiJmxAdaptor/>
    </JmxSettings>
  </Controller>
</C-JDBC-CONTROLLER>
      

You can specify at startup a different file than config/controller/controller.xml. This is useful if you have to startup many identical controllers from the network. You can then use the command controller.sh -f filename on Unix machines or controller.bat -f filename on windows.

For more information you can refer to the controller-configuration.xml example in the example directory of c-jdbc.

Next section describes how to write a controller configuration file.

The controller is entirely configurable via an xml file, by default it is controller.xml located in the config/controller of the C-JDBC installation. This section details how to write such a file.

<!ELEMENT Controller (Internationalization?, Report?, JmxSettings?, 
                                    VirtualDatabase*, SecuritySettings?)>
<!ATTLIST Controller
  port             CDATA "25322"
  ipAddress        CDATA "127.0.0.1"
  backlogSize      CDATA "10"
>
    

<?xml version="1.0" encoding="ISO-8859-1" ?>
<!DOCTYPE C-JDBC-CONTROLLER PUBLIC "-//ObjectWeb//DTD C-JDBC-CONTROLLER 2.0.2//EN"  "http://c-jdbc.objectweb.org/dtds/c-jdbc-controller-2.0.2.dtd">
<C-JDBC-CONTROLLER>
	<Controller port="25322" ipAddress="192.168.0.1">
		<JmxSettings enabled="false"/>
	</Controller>
</C-JDBC-CONTROLLER>
    

    <!ELEMENT Internationalization EMPTY>
    <!ATTLIST Internationalization language (en|fr|it|jp) "en">
    

<!ELEMENT Report EMPTY>
<!ATTLIST Report
     hideSensitiveData  (true|false) "true"
     generateOnShutdown (true|false) "true"
     generateOnFatal    (true|false) "true"
     enableFileLogging  (true|false) "true"
     reportLocation     CDATA        #IMPLIED
>
    

    <!ELEMENT JmxSettings (HttpJmxAdaptor?, RmiJmxAdaptor?)>
    <!ELEMENT HttpJmxAdaptor EMPTY>
    <!ATTLIST HttpJmxAdaptor
      port CDATA "8090"
    >

     <!ELEMENT RmiJmxAdaptor (SSL?)>
     <!ATTLIST RmiJmxAdaptor
       port         CDATA        "1090"
       username     CDATA        #IMPLIED
       password     CDATA        #IMPLIED
     >


     <!ELEMENT SSL EMPTY>
     <!ATTLIST SSL
       keyStore			CDATA        #REQUIRED
       keyStorePassword		CDATA        #REQUIRED
       keyStoreKeyPassword	CDATA        #IMPLIED
       isClientAuthNeeded	(true|false) "false"
       trustStore		CDATA        #IMPLIED
       trustStorePassword	CDATA        #IMPLIED
     >
     

     <JmxSettings>
     	 <RmiJmxAdaptor/>
		 </JmxSettings>
     

<!ELEMENT VirtualDatabase EMPTY>
<!ATTLIST VirtualDatabase 
    configFile          CDATA #REQUIRED
    virtualDatabaseName CDATA #REQUIRED
    autoEnableBackends  (true | false | force) "true"
    checkpointName      CDATA ""
>
      

<VirtualDatabase configFile="/databases/MySQLDb.xml" virtualDatabaseName="rubis" autoEnableBackends="true"/>
      

      <!ELEMENT SecuritySettings (Jar?, Shutdown?, Accept?, Block?)>
      <!ATTLIST SecuritySettings
        defaultConnect (true|false) "true"
      >
      

<!ELEMENT Jar EMPTY>
<!ATTLIST Jar
	allowAdditionalDriver (true|false) "true"
>
      

<Shutdown>
  <Client allow="true" onlyLocalhost="true"/>
  <Console allow="true" onlyLocalhost="true"/>
</Shutdown>
      

<!ELEMENT Shutdown (Client?,Console?)>

<!ELEMENT Client EMPTY>
<!ATTLIST Client
    allow         (true|false) "true"
    onlyLocalhost (true|false) "true"
>
<!ELEMENT Console EMPTY>
<!ATTLIST Console
    allow (true|false) "true"
    onlyLocalhost (true|false) "true"
>
      

<!ELEMENT Accept (Hostname|IpAddress|IpRange)*>
<!ELEMENT Block (Hostname|IpAddress|IpRange)*>

<!ELEMENT Hostname EMPTY>
<!ATTLIST Hostname 
     value CDATA #REQUIRED
>
      

<!ELEMENT IpAddress EMPTY>
<!ATTLIST IpAddress  
     value CDATA #REQUIRED
>
      

<!ELEMENT IpRange EMPTY>
<!ATTLIST IpRange  
     value CDATA #REQUIRED
>
      

<SecuritySettings defaultConnect="false">
  <Jar allowAdditionalDriver="true"/>
  <Shutdown>
    <Client allow="true" onlyLocalhost="true"/>
    <Console allow="false"/>
  </Shutdown>
  <Accept>
    <IpRange value="192.168.*.*"/>
  </Accept>
</SecuritySettings>
      

C-JDBC uses the Log4j logging framework. The log4j.properties configuration file is located in the /c-jdbc/config directory of your installation. Here is a brief description of the loggers available in the configuration file:

When you want to add a database to your cluster, you do not want to stop the system, replicate the current database state to the new database (that may take a long while) and then restart the system. The Recovery Log helps you in the process of dynamically adding a new backend (or recovering a previously failed backend) without stopping the system.

The Recovery Log records the write operations and transactions that are performed by the C-JDBC controller between checkpoints. A checkpoint is just a logical index in the log that reflect the recovery log state at a given time. As of C-JDBC 2.0, checkpoints are automatically managed by the controller and are generated when needed on behalf of the administrator when a backend is disabled or enter a backup phase. When re-enabling the backend, the Recovery Log replays all write queries and transactions that the backend missed during the time it was offline and it comes back to the enabled state once it is synchronized with the other nodes.

Note

Since version 2.0, the backup infrastructure has completely changed and is based on Backupers. We provide a generic Backuper based on Enhydra Octopus to copy, backup and restore content of backends through JDBC. Even if Octopus is supposed to handle most common databases, it might fail for some specific databases or data types. In that case, we strongly recommend to use or implement a database specific Backuper.

6.5.1. A practical example

Your Web site is running with a single database and you want to use C-JDBC with three nodes using full replication (RAIDb-1). You have two new backends ready to be installed. You can start the C-JDBC console and connect to the controller. Start the administration module by connecting to the virtual database. Type: backup <backend name> <dump name> <backuper name> <path to backup directory>. If you want to use Octopus you will use a command line like backup node1 dump1 Octopus /var/backups. During the backup, the update requests are logged in the recovery log, so no update is lost. If the backend was in the enabled state when backup was initiated, it will automatically replay the recovery log to resynchronize itself and return to the enabled state.

To restore the dump on another backend, just type restore <newbackend> <dumpname> and the appropriate backuper (Octopus in our previous example) will be used to restore the dump. After restoring the dump, you can enable the backend at any time so that the recovery log replays all the missing requests since the dump was taken.

Here is the set of commands to use in the C-JDBC console if node1 is your existing backend and you want to dynamically add node2 and node3:

backup node1 initial_dump Octopus /var/backups
restore node2 initial_dump
restore node3 initial_dump
enable node2
enable node3
        

	Note
	Note that these steps can be automated by scriptin the console.

If a node crashes, use the administration console to restore the dump on the node using the restore command. Once the dump is restored, re-enable the backend from the stored checkpoint and the Recovery Log will automatically replay all the write queries to rebuild a consistent database state on the node.

To prevent the recovery log from being too large, you can periodically perform backup operations. This will also lower the recovery time since the part of the log to replay will be smaller. You can delete older dumps and logs if you do not need them anymore.

To prevent the C-JDBC controller from being a single point of failure, C-JDBC provides controller replication also called horizontal scalability. A virtual database can be replicated in several controllers that can be added dynamically at runtime. Controllers use the JGroups group communication middleware to synchronize updates in a distributed way. The JGroups stack configuration is found in config/jgroups.xml and should not be altered unless you specifically know what you are doing. Keep in mind that total order reliable multicast is needed to ensure proper synchonization of the controllers. More information about JGroups can be found on the JGroups web site. Note that JGroups requires proper network settings, here are a few guidelines:

In order for a virtual database to be replicated, you must define a Distribution element in the virtual database configuration file (see Section 11.2.1, “Distribution”). There are several constraints for different controllers to replicate a virtual database:

	Note
	As backends cannot be shared between controllers, it is not possible to use a SingleDB load balancer with controller replication. If each controller only has a single database backend attached to it, then you must use a RAIDb-1 configuration since in fact you have 2 replicated backends in the cluster.

Several configuration file examples are available in the doc/examples/HorizontalScalability directory of your C-JDBC distribution.

	Note
	You can find more information in the document titled "C-JDBC Horizontal Scalability - A controller replication user guide" available from the C-JDBC web site.

The C-JDBC controller in its 2.0.2 release has the following limitations:

Here is a list of the JMX remote notifications generated by C-JDBC.

The bin directory of the C-JDBC distribution contains the scripts to start the console. Unix users must start the console with console.sh -t whereas Windows users have to start console.bat -t.

The console script accepts several options:

For example, console.sh -t -i 192.168.0.1 -p 1234 will connect the console to the controller using the RMI JMX adaptor listening on port 1234 on 192.168.0.1.

The console has an online help that is accessible by typing help at any time.

Here is a quick description of the steps needed to make a controller ready to serve requests:

Here is an example of a controller configuration and startup:

 
[emmanuel@gre-home bin]$ console.sh -t
Launching the C-JDBC controller console
Initializing Controller module...
Initializing VirtualDatabase Administration module...
Initializing Monitoring module...
Initializing SQL Console module...
C-JDBC driver (v. 2.0) successfully loaded.
  
gre-home:1090 >help
Commands available for the Controller module are:
admin <virtualdatabase name>
   Administrate a virtual database
connect controller <controller hostname> <jmx port>
   Connect to a C-JDBC controller
drop virtualdatabase <virtualdatabase name>
   Drop a virtual database from the controller
help
   Print this help message
history [<commandIndex>]
   Display history of commands for this module
load virtualdatabase config <virtualdatabase xml file>
   Send a virtual database XML configuration file to the controller and load it
monitor <virtualdatabase name>
   Monitor a virtual database
quit
   Quit this console
reload logging configuration
   Refresh the trace system by reloading the logging configuration file
save configuration
   Save the current configuration of the virtual databases as an XML file
show controller config
   Show Controller configuration
show logging config
   Show logging configuration and the most recent traces
show virtualdatabases
   Show the names of the virtual databases for this controller
shutdown [mode]
   Shutdown the controller and all its virtual databases. Mode parameter must be:
        1 -- wait for all client connections to be closed, does not work with a connection pool
        2 -- mode safe, default value, waits for all current transactions to complete
        3 -- mode force, immediate shutdown without consistency: recovery will be needed on restart
sql client <c-jdbc url>
   Open a SQL client console for the virtual database specified by the C-JDBC URL
upload driver <driver file>
   Upload a driver to the controller

gre-home:1090 > <userinput>show virtualdatabases</userinput>
myDB
gre-home:1090 > <userinput>admin myDB</userinput>
Virtual database Administrator Login > <userinput>admin</userinput>
Virtual database Administrator Password > <userinput>*****</userinput>
Ready to administrate virtual database myDB
myDB(admin) > help
Commands available for the VirtualDatabase Administration module are:
backup <backend name> <dump name> <backuper name> <path> [<tables>]
   Backup a backend into a dump file and associate a checkpoint with this dump
delete dump <dump name>
   Delete a dump
disable <backend name | *>
   Disable the specified backend and automatically set a checkpoint
        * means that all backends of this virtual database must be disabled
enable <backend name | *>
   Enable the specified backend
        * means that all backends of this virtual database must be enabled
expert <on|off>
   Switch to expert mode (commands for advanced users are available)
help
   Print this help message
history [<commandIndex>]
   Display history of commands for this module
quit
   Quit this console
restore <backend name> <dump name> [<tables>]
   Starts the recovery process of the given backend for a given dump name
show backend <backend name | *>
   Show information on backend of this virtual database
        * means to show information for all the backends of this virtual database
show backends
   Show the names of the backends of this virtual database on the current controller
show backupers
   Show the backupers available for backup
show controllers
   Show the names of the controllers hosting this virtual database
show dumps
   Show all dumps available for database recovery
show virtualdatabase config
   Show the XML configuration of the virtual database
transfer dump <dump name> <controller name> [nocopy]
   Make a dump available for restore on another controller.
Optional 'nocopy' (default: false) flag specifies not to copy the dump.

myDB(admin) > <userinput>show backend *</userinput>
+--------------------------+-----------------------------------+
| Backend Name             | localhost                         |
| Driver                   | org.hsqldb.jdbcDriver             |
| URL                      | jdbc:hsqldb:hsql://localhost:9001 |
| Active transactions      | 0                                 |
| Pending Requests         | 0                                 |
| Read Enabled             | true                              |
| Write Enabled            | true                              |
| Is Initialized           | true                              |
| Static Schema            | false                             |
| Connection Managers      | 1                                 |
| Total Active Connections | 5                                 |
| Total Requests           | 0                                 |
| Total Transactions       | 0                                 |
| Last known checkpoint    | <unknown>                         |
+--------------------------+-----------------------------------+
| Backend Name             | localhost2                        |
| Driver                   | org.hsqldb.jdbcDriver             |
| URL                      | jdbc:hsqldb:hsql://localhost:9002 |
| Active transactions      | 0                                 |
| Pending Requests         | 0                                 |
| Read Enabled             | true                              |
| Write Enabled            | true                              |
| Is Initialized           | true                              |
| Static Schema            | false                             |
| Connection Managers      | 1                                 |
| Total Active Connections | 5                                 |
| Total Requests           | 0                                 |
| Total Transactions       | 0                                 |
| Last known checkpoint    | <unknown>                         |
+--------------------------+-----------------------------------+

      

The graphical version of the console provides a shell-like history (more precisely a tcsh-like behavior). You can recall a previous command by using the arrow keys (up and down) to browse the history. If you prefix a command by !, the console will browse the history and complete the command with the latest command in the history starting with the command prefix (completion occurs when you press the tab key). In the graphical version, you can also access all the commands of the different module using the right button of the mouse.

	Note
	All the commands issued can also be recalled using the history menu in the contextual menu that appears on a right-button click.

Commands available from the console main menu are:

Once the configuration file has been loaded on the controller, all backends are in the disabled state. You must enable them all or one by one to allow them to execute requests. C-JDBC does not check that database contents are synchronized and you must ensure that all backends are in a coherent state prior to starting the controller. To ensure that backends remain synchronized on startup, you must use checkpoints (see Section 6.5.2, “Understanding checkpoints”).

If you properly shutdown the controller using the wait or safe mode, database backend states are properly recorded and their state is automatically restored when they are enabled.