Client Routing API for TimesTen Scaleout

Functionality of the Client Routing API

To increase performance, TimesTen Scaleout enables your client application to route connections to an element based on the key value for a hash distribution key.

You provide a key value for a distribution key and TimesTen Scaleout returns an array of element IDs (or the replica set ID) where the database allocated that value. This enables the client application to connect to the element that stores the row with the specified key value, avoiding unnecessary communication between the element storing the row and the one connected to your application.

Building a Distribution Key

The client application must identify and build a distribution key, which is required to determine the elements (or replica set) that allocates a specific set of key values. The TimesTenDistributionKey and TimesTenDistributionKeyBuilder interfaces specify functionality for building a distribution key.

Note:

The application has to maintain a client connection to the database to build the distribution key, compute the element IDs or replica set ID, and build a connection to an element of the database based on either of the three. Refer to the examples in the next two sections, Getting the Element Location Given a Set of Key Values and Connecting to an Element Based on a Distribution Key.

The TimesTenDistributionKeyBuilder interface specifies the following builder method to support compound keys with different data types.

subkey(Object subkey, java.sql.Types subkeyType)

For a compound distribution key, invoke the subkey method once for every column in the hash distribution key of the table. Invoke each subkey in the same order as the key values and types of the distribution key columns of the table.

Getting the Element Location Given a Set of Key Values

Once you build a distribution key, use the getElementIDs or getReplicaSetID method of the TimesTenDistributionKey interface to get the element IDs or replica set ID that stores the key values specified in the distribution key.

Note:

For TimesTen Scaleout, the TimesTenDataSource class implements factory methods for connection and distribution key.

This example computes and prints the element IDs and replica set ID for a key value in a single column distribution key.

import java.sql.SQLException;
import java.sql.Types;
 
import com.timesten.jdbc.TimesTenDataSource;
import com.timesten.jdbc.TimesTenDistributionKey;
import com.timesten.jdbc.TimesTenDistributionKeyBuilder;

public class ClientRouting {
 
   public static void main(String[] args) {
 
      try {
 
         /* Establish a connection to an element of the database. Maintain this 
          * connection for the duration of the application for computing element 
          * IDs and creating connections. */
         TimesTenDataSource ds = new TimesTenDataSource();
         ds.setUrl("jdbc:timesten:client:database1");
         ds.setUser("terry");
         ds.setPassword("password");
 
         /* Build a distribution key. The distribution key is composed of a single
          * TT_INTEGER column. */
         TimesTenDistributionKey dk = ds.createTimesTenDistributionKeyBuilder()
               .subkey(3, Types.INTEGER)
               .build();
 
         // Get the element IDs for the distribution key.
         short[] elementIDs = dk.getElementIDs();
 
         for (short id : elementIDs) {
            System.out.println("Distribution key(3), element ID: " + id);
         }
 
         // Get the replica set ID for the disitribution key.
         System.out.println("Distribution key(3), replica set ID: " +
 dk.getReplicaSetID());
 
      } catch (SQLException ex) {         ...      }
   }
}

This code snippet computes and prints the element IDs and replica set ID for a set of key values in a distribution key composed of more than one column.

...
/* Build a distribution key. The distribuion key is composed of two columns -- 
 * one TT_INTEGER and one VARCHAR2. */
dk = ds.createTimesTenDistributionKeyBuilder()
      .subkey(1, Types.INTEGER)
      .subkey("john.doe", Types.VARCHAR)
      .build();
 
// Get the element IDs for the distribution key
elementIDs = dk.getElementIDs();
for (short id : elementIDs) {
   System.out.println("Distribution key(1, john.doe), element ID: " + id);
}
// Get the replica set ID for the distribution key.
System.out.println("Distribution key(1, john.doe), replica set ID: " +
 dk.getReplicaSetID());
...

Connecting to an Element Based on a Distribution Key

Your client application may use any custom method to connect to a specific element of a database in TimesTen Scaleout. However, the features specified in the TimesTenConnectionBuilder interface enable your application to connect to an optimal element based on a distribution key, element ID, or replica set ID.

This example builds a distribution key and then builds a connection with it.

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
 
import com.timesten.jdbc.TimesTenDataSource;
import com.timesten.jdbc.TimesTenDistributionKey;
import com.timesten.jdbc.TimesTenDistributionKeyBuilder;
 
public class ClientRouting {
 
   public static void main(String[] args) {
 
      try {
 
         // Create and mantain connection to database.
         TimesTenDataSource ds = new TimesTenDataSource();
         ds.setUrl("jdbc:timesten:client:database1");
         ds.setUser("terry");
         ds.setPassword("password");
 
         // Build a distribution key.
         TimesTenDistributionKey dk = ds.createTimesTenDistributionKeyBuilder()
               .subkey(1, Types.INTEGER)
               .subkey("john.doe", Types.VARCHAR)
               .build();
 
         // Connect to optimal element based on a distribuion key.
         Connection conn;
         conn = ds.createTimesTenConnectionBuilder()
               .user("terry")
               .password("password")
               .distributionKey(dk)
               .build();
         Statement stmt = conn.createStatement();
         stmt.execute("... SQL statement here ...");
         stmt.close();
         conn.close();
 
      } catch (SQLException ex) {
         ...
      }
   }
}

The following code snippet builds a connection based on an element ID.

...
// Connect to optimal element based on an element ID.
short[] elementIDs = dk.getElementIDs();
conn = ds.createTimesTenConnectionBuilder()
      .user("terry")
      .password("password")
      .elementID(elementIDs[0])
      .build();
Statement stmt = conn.createStatement();
stmt.execute("... SQL statement here ...");
stmt.close();
conn.close();
...

This code snippet builds a connection based on a replica set ID.

...
// Connect to optimal element based on a replica set ID.
short repSetID = dk.getReplicaSetID();
conn = ds.createTimesTenConnectionBuilder()
      .user("terry")
      .password("password")
      .replicaSetID(repSetID)
      .build();
Statement stmt = conn.createStatement();
stmt.execute("... SQL statement here ...");
stmt.close();
conn.close();
...

Supported Data Types

For best performance, use the recommended object types to avoid type conversion.

Table 2-5 lists the supported data types and acceptable object types.

Table 2-5 Supported Data Types and Acceptable Object Types

SQL type	java.sql.Types	Recommended Object Class	Acceptable Object Classes
`TT_TINYINT`	`Types.TINYINT`	`Short`	`Byte`, `Short`
`TT_SMALLINT`	`Types.SMALLINT`	`Short`	`Byte`, `Short`
`TT_INTEGER`	`Types.INTEGER`	`Integer`	`Byte`, `Short`, `Integer`
`TT_BIGINT`	`Types.BIGINT`	`Long`	`Byte`, `Short`, `Integer`, `Long`
`CHAR`	`Types.CHAR`	`String`	`String`
`NCHAR`	`Types.NCHAR`	`String`	`String`
`VARCHAR2`	`Types.VARCHAR`	`String`	`String`
`NVARCHAR`	`Types.NCHAR`	`String`	`String`
`NUMBER`	`Types.DECIMAL` `TYPES.NUMERIC`	`BigDecimal`	`BigDecimal` `toString()` method will be invoked for other classes.

Restrictions

There are restrictions for client routing.

The JDBC extensions for client routing in TimesTen Scaleout share the same restrictions as the ones listed in Client Routing API for TimesTen Scaleout in the Oracle TimesTen In-Memory Database C Developer's Guide.