Connect to a SQLFire Server with the Thin Client JDBC Driver

The thin client driver class is packaged in com.vmware.sqlfire.jdbc.ClientDriver. In addition to the basic JDBC Connection URL, you specify the host and port number of a SQLFire server or a locator to which the thin client driver will connect.

For example:
jdbc:sqlfire://myHostName:1527/

Code Example

This code sample shows a more complete example of connecting to a SQLFire server in a Java application:
        try {
        java.util.Properties p = new java.util.Properties();

        // 1527 is the default port that a SQLFire server uses to listen for thin client connections
        Connection conn =
        DriverManager.getConnection("jdbc:sqlfire://myHostName:1527/");
        
        // 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());
        }

Thin Client Failover

When you use the thin client to connect to a SQLFire locator member (rather than directly to a SQLFire server), the thin client driver can provide automatic failover if the initial connection to the distributed system is lost. See sqlf locator. Note, however, that this assumes the initial connection to the specified SQLFire locator succeeds. To improve the chances of establishing an initial connection to a SQLFire system, you can optionally specify the address of a secondary locator in addition to the primary locator, using the secondary-locators connection property. For example:
jdbc:sqlfire://myLocatorAddress:1527/;secondary-locators=mySecondaryLocatorAddress:1527

Enabling Single-Hop Data Access

By default, using the thin-client driver provides either one-hop or two-hop access to the data for executing queries or DML statements. One-hop access is available if the client's SQL statements work against data that happens to reside on the SQLFire member to which the thin client has connected. All other cases result in one-hop or two-hop access to the data: the SQL statement is evaluated first on the SQLFire member to which the client is connected, and if necessary, that server routes the query to other members in the cluster that host the actual data for the statement.

SQLFire provides the option to provide single-hop access to data for certain queries when using a thin client connection. To use this option, set the single-hop-enabled connection property to true when connecting with the thin client driver. For example:
jdbc:sqlfire://myHostName:1527/;single-hop-enabled=true
Or, from within the sqlf utility:
connect client 'myHostName:1527/;single-hop-enabled=true'
Note: Single-hop access for thin clients requires SQLFire data store members in the distributed system to run a network server for direct client access. Configure each data store with network server functionality even if you use a locator for member discovery. See Starting a Network Server.

Single-hop access is provided only for prepared statements. When you enable single-hop access on a connection and then prepare a prepared statement, the local SQLFire server adds data distribution information to other SQLFire server members in the response of the prepare message. When the prepared statement is executed, the client uses the added parameter and information fetched from the connected server to determine the exact SQLFire server on which it can find the data locally; it then directs the execution to that server.

The following types of queries are good candidates for single-hop access:
  • Queries that have a WHERE clause on the partitioning columns of a table.
  • Primary key-based SELECT statements where the primary key is also the partitioning key.
  • IN-based WHERE clauses on partitioning columns.

If the client cannot determine the location of the data based on the WHERE clause, then it defaults to standard two-hop execution. Single-hop execution is performed only when the client can be absolutely certain of the location of data that is touched by the query.

Internally, the client JVM maintains a pool of connections that is shared by all of the prepared statements that might execute statements with single-hop access. For each SQLFire server, the client maintains a queue of connections that can grow to a maximum number of connection specified by the sqlfire.client.single-hop-max-connections system property. After this maximum number of connections has been created for a particular server, further single-hop executions must wait for a connection to become available in the queue. If the number of connections created for a particular SQLFire server has not reached the maximum, then the client creates a new connection on the fly, uses it, and then returns it back to the connection. The default value for sqlfire.client.single-hop-max-connections is 5 connections per server. If you are developing a client that requires more concurrent connections per server for single-hop access, increase the maximum number of connections per server using the sqlfire.client.single-hop-max-connections system property.

Note: To avoid degrading the performance of the network server, use the smallest number of concurrent single-hop threads that satisfy performance requirements.

Configuring TCP Keepalive Settings

By default, SQLFire servers use a TCP keepalive probe to help determine when clients have gone offline. SQLFire thin clients can also use these keepalive settings to accurately determine when a server has gone offline. The relevant configuration properties are:

To use these properties with a SQLFire thin client, include the jna.jar library in your CLASSPATH.

Note: Windows platforms do not support per-socket configuration for keepalive-count. As an alternative, you can configure a system-wide keepalive-count value in some versions of Windows. See http://msdn.microsoft.com/en-us/library/windows/desktop/dd877220%28v=vs.85%29.aspx. Windows Vista and later versions keep this value fixed at 10.
Note: On Solaris platforms prior to r10, system-wide TCP keepalive settings must be changed to larger values (approximately 30 seconds) in order to detect server failures by clients and vice versa. See http://docs.oracle.com/cd/E19082-01/819-2724/fsvdg/index.html. This also applies to other non-Linux, non-Windows platforms. For example, see http://www-01.ibm.com/support/docview.wss?uid=swg21231084.

Thin Client Driver Limitations

When the default batching mode is enabled for transactions, SQLFire detects any conflicts in DML operations lazily. DML conflicts may be thrown by the system at some point later in the transaction (for example, even when executing queries or at commit time). You can configure SQLFire to immediately detect conflicts at operation time by setting the gemfire.tx-disable-batching system property to "true" on all data store members in the distributed system.

Note: Enabling gemfire.tx-disable-batching can degrade performance significantly. Enable this option only after you have thoroughly tested the setting in your system and have determined that the performance tradeoff is necessary to provide immediate conflict detection with thin clients.

If you use the thin client driver to perform an insert to table that has no primary key, an automatic retry of the insert due to a failover (available when connecting via a locator member) can result in duplicate rows being added to the table.

The thin-client driver has the following limitations when the single-hop connection property is enabled:
  • Single-hop access is not provided when using transactions or WAN replication.
    Note: Do not enable single-hop access when using transactions or WAN configurations, as it can lead to unexpected behavior.
  • Single-hop access is only supported for partitioned tables where the partitioning column(s) are of the basic data types: integer, long, double, decimal, char, varchar, and real. If a table is partitioned on a column having any other data type (like date, time, timestamp, blob, clob, and so forth), then queries on that tables are not considered for single-hop execution.
  • Single hop execution is attempted only for prepared statements, and not for simple statements.
  • Single hop execution is attempted only for SELECT, UPDATE and DELETE statements, but not for INSERT statements.
  • SQLFire does not support single-hop access for queries that require data from multiple data stores to be merged together. Typically, queries having aggregates like MAX, MIN, AVG, ORDER BY, and so forth are executed as if single-hop were disabled. If an aggregate query can provide all results from a single data store, then SQLFire provides single-hop access for the query.
  • Tables that have an expression resolver are not considered for single-hop access.