SQLFabric Configuration

JDBC Connection Properties

The connection properties can be specified either in the connection URL or passed in the Properties parameter to the DriverManager.getConnection method.
In the connection URL, attributes are specified as key=value pairs: [;attributes]*
(preceded by and separated by semicolons)

SQLFabric supports GemFire properties to be passed in as connection properties, prefixed with "gemfire."

Connecting to a Fabric Server using the edge JDBC driver

    try {
      java.util.Properties p = new java.util.Properties();

      // Load the driver class. This is not necessary for JDK 1.6 or newer.
      Class.forName("com.gemstone.sqlfabric.internal.jdbc.ClientDriver");

      // 1527 is the default port that a fabricserver listens for edge client connections
      Connection conn =
         DriverManager.getConnection("jdbc:sqlfabric://myHostName:1527/");
      
      // Now do something with the Connection

    } catch (SQLException ex) {
      // handle any errors
      System.out.println("SQLException: " + ex.getMessage());
      System.out.println("SQLState: " + ex.getSQLState());
      System.out.println("VendorError: " + ex.getErrorCode());
    }

Connecting to a Fabric Server using the GemFire native Peer JDBC driver

    try {
      java.util.Properties p = new java.util.Properties();
      Class.forName("com.gemstone.sqlfabric.internal.jdbc.EmbeddedDriver");

      // Using the GemFire locator for peer member discovery ....
      Connection conn =
         DriverManager.getConnection("jdbc:sqlfabric:;gemfire.locators=localhost\[3340\];gemfire.statistic-sampling-enabled=true;host-data=false");

      // Alternatively use Multicast for discovery
      // All peer members including Fabric servers connect to each other. They discover each other using Multicast
      /* 
      Connection conn =
        DriverManager.getConnection("jdbc:sqlfabric:;gemfire.mcast-port=33666;gemfire.statistic-sampling-enabled=true;host-data=false");
       */
      
      // Now do something with the Connection

    } catch (SQLException ex) {
      // handle any errors
      System.out.println("SQLException: " + ex.getMessage());
      System.out.println("SQLState: " + ex.getSQLState());
      System.out.println("VendorError: " + ex.getErrorCode());
    }

Unlike Derby, SQLFabric does not use a databaseName. Instead of a "database" the connection is to a distributed system. The distributed system is uniquely identified by either the mcast-port or the locators. See GemFire properties. The subprotocol in the URL sqlfabric: ends with a colon (:) and the list of connection attributes starts with a semicolon (;). Setting mcast-port to 0 without specifying locators starts a "loner" distributed system. See GemFire properties. The list of connection attributes is not parsed for correctness. If you pass an incorrect attribute, it is simply ignored Setting the host-data attribute to false specifies that data should not be hosted in this VM. The host-data attribute defaults to true if not specified, which indicates that data can be hosted in this VM.

Fabricserver properties (when starting a server using fabricserver script)

The fabricserver script is provided as a convenience to start a SQL fabric server VM that can act as data store. The GemFire properties can be passed to the script as "<key>=<value>" pairs. The server groups (for more on server groups see the "Server Groups" section on the [DDL page]) of the VM can be specified using the "-serverGroups" option.

Example for passing GemFire properties to the script:

fabricserver start mcast-port=0 statistic-sampling-enabled=true locators=localhost[3340]

Example for specifying server groups for the VM:

fabricserver start mcast-port=0 statistic-sampling-enabled=true locators=localhost[3340] -serverGroups=group1,group2

The full set of options for the fabricserver script are as below:

  fabricserver start [-J<vmarg>]* [<attName>=<attValue>]* [-P<propName>=<propValue>]*
                     [-dir=<workdir>] [-classpath=<classpath>]
                     [-rebalance] [-serverGroups=<groups>]
                     [-maxHeap=<size>] [-initialHeap=<size>]
                     [-configScripts=<sql-files>] [-initScripts=<sql-files>]
                     [-run-netserver=<true|false> (default true)]
                     [-port=<1025-65535> (default 1527)]

  Starts a GemFire SQLFabric Server VM
     <vmarg>     A VM option passed to the spawned SQLFabric Server VM
                 (example -J-Xmx1024m to set the VM heap to 1GB)
     <workdir>   the working directory containing the SQLFabric Server's
                 status file. (defaults to the current directory)
     <classpath> Location of user classes required by the fabric server.
                 This path is appended to the current classpath.
     <size>      Size has the same format as the -Xmx/-Xms JVM options.
                 -maxHeap is equivalent to -J-Xmx and
                 -initialHeap is equivalent to -J-Xms.
     <groups>    A comma separated list of server groups that this VM is
                 a member of. Used for creating tables in particular sets
                 of servers or for firing data-aware procedures in particular
                 server groups. See SQLFabric documentation of SERVER GROUPS
                 extension to CREATE TABLE and of CALL PROCEDURE
                 for more details.
     <sql-files> A comma separated list of files containing initial SQL
                 commands to be executed in this VM prior to executing any
                 other commands. The format of these files is same as that
                 required by the "ij" tool.
                 The scripts in "-configScripts" should include initial DDL
                 statements to be executed before any other operation in the VM
                 including execution of initial DDLs from other VMs or those
                 persisted in the start directory of this VM.
                 The scripts in "-initScripts" should normally include
                 statements required to be executed after initial DDL replay
                 from persisted data in this VM or from other VMs e.g. DMLs
                 to load initial data into existing tables.
     <attName>   Distributed system attribute such as "mcast-port".
     <attValue>  Value of the GemFire property.
     <propName>  SQLFabric specific property such as
                 "sqlfabric.max-lock-wait". This adds the given property
                 to the boot properties of SQLFabric.
     <propValue> Value of the SQLFabric property.
     -rebalance  Indicates that the Cache should immediately be rebalanced.
     -port       The TCP port to use for starting the network server (if
                 -run-netserver is true) that can be used by thin clients.

  fabricserver stop [-dir=<workdir>]

     Stops a GemFire SQLFabric Server VM
     <workdir>   The working directory containing the SQLFabric Server's
                 status file. (defaults to the current directory)
 
  fabricserver status [-dir=<workdir>]

     Reports the status and process id of a GemFire SQLFabric Server VM
     <workdir>   The working directory containing the SQLFabric Server's
                 status file. (defaults to the current directory)

Persisting DataDictionary and startup scripts

By default, the data dictionary is replicated and available on each peer member of the distributed system. This means, all your table, index, trigger, procedure, etc definitions are preserved as long as there is at least a single member of the distributed system running. If all members of the system go down, you have to re-execute the data definition statements (DDL). To persist the data dictionary, first set the connection property sqlfabric.sys-disk-dir to a directory where DataDictionary has to be persisted (the same property is used for persistence of other data like WAN queues, prefix for table persistence for relative disk-dirs specification) and then set the boolean property sqlfabric.persistent-dd to true. Note that two distinct VMs cannot use the same directory for persistence. One thing to note is that persistence is always overridden by initial data from any other VMs in the distributed system. This means that the user has to take care that the VM with persisted data dictionary should be started first while bringing up the distributed system, otherwise the data will be overridden by data from other VMs that are already running in the distributed system. Alternatively it is recommended to set this property in all the peer VMs (typically fabricservers) uniformly to avoid the risk of losing persisted data.

Startup scripts can also be specified that will be executed by a VM during boot procedure. There are two kinds of scripts provided: one set can be executed before doing anything else including initial DDL execution from other VMs or persisted data (termed configuration scripts), and another set can be executed after the initial DDL execution is done during the boot procedure (termed initialization scripts). The former set of scripts is useful for any pre-boot configuration etc. that needs to be done which may effect the DDL execution. The latter set of scripts is useful for data loading or DDL execution that requires initial DDL execution to be complete. The connection property config-sql-scripts can be set to a comma-separated list of script file URLs as configuration scripts, and the property init-sql-scripts can be set for initialization scripts. When using the fabricserver script, the argument "-configScripts" can be used for the former, while the option "-initScripts" can be used for the latter. This mechanism can be used as an alternative to Data dictionary persistence by having the required DDLs configured to execute in the configuration scripts for the first VM that comes up in the distributed system.

ij properties (when running ij)

When running the ij tool, ij properties can be set by specifying a properties file using the -p propertyfile option on the command line.

The list of ij properties can be found in the Derby Tools and Utilities Guide here.

GemFire properties

GemFire properties configure the underlying GemFire DistributedSystem. The supported properties are documented here.

The actual configuration attribute values used to connect come from the following sources:

1. System properties. If a system property named "gemfire.propertyName" is defined and its value is not an empty string then its value will be used for the named configuration attribute.
2. Code properties. For SQLFabric, the properties can be passed in a Properties object as parameter to the DriverManager.getConnection method. The name of the property must be prefixed with "gemfire." in the same way as a system property.
3. File properties. Otherwise if a property is defined in a configuration property file found by this application and its value is not an empty string then its value will be used for that configuration attribute. A configuration property file may not exist. See the following section for how configuration property files are found.

   Unlike when specifying gemfire properties as system properties or code properties passed to DriverManager.getConnection, the names of the properties in this property file should not be prefixed with "gemfire.".

4. Defaults. Otherwise a default value is used.

Here is a complete list of all GemFire properties .
Note that not all of the properties listed are applicable to the SQLFabric Preview release.

A complete list of JDBC connection properties supported by Derby can be found in the Derby reference manual here.
Of those connection attributes supported by Derby, SQLFabric currently only supports the following:

Getting a connection with this attribute will cause the GemFire distributed system to be disconnected (shut down in this VM only).

How GemFire property files are found

The name of the property file can be specified using the "gemfirePropertyFile" system property. If the system property is set to a relative file name then it is searched for in following locations. If the system property is set to an absolute file name then that file is used as the property file. If the system property is not set, then the name of the property file defaults to "gemfire.properties". The configuration file is searched for in the following locations:

1. Current directory (directory in which the VM was launched)
2. User's home directory
3. Class path (loaded as a system resource)