13.6 Middle-Tier Routing Using UCP

Starting from Oracle Database Release 18c, Oracle Universal Connection Pool (UCP) introduces the Middle-Tier Routing feature. This feature helps the Oracle customers, who use the Sharding feature, to have a dedicated middle tier from the client applications to the sharded database.

Typically, the middle-tier connection pools route database requests to specific shards. During such a routing, each middle-tier connection pool establishes connections to each shard, creating too many connections to the database. The Middle-Tier Routing feature solves this problem by having a dedicated middle tier (Web Server or Application Server) for each Data Center or Cloud, and routing client requests directly to the relevant middle tier, where the shard containing the client data (corresponding to the client sharding key) resides.

The OracleShardRoutingCache class in UCP provides middle-tier routing APIs that can be used to route the client requests to the appropriate middle tier. An instance of this class represents the internal shard routing cache of UCP, which can be created by providing connection properties such as user, password, and URL of the sharding catalog. Starting from Oracle Database Release 19c, you must also specify a new connection property, serviceName. It is the name of the global service name.

The routing cache connects to the sharding catalog to retrieve the key to shard mapping topology and stores it in its cache. The getShardInfoForKey(shardKey, superShardKey) method uses the routing cache of UCP to get the information about shards for the specified sharding key. The ShardInfo instance encapsulates a unique shard name and priority of the shard. The application using the middle-tier API can map the returned unique shard name value to a middle tier that has connections to a specific shard.

The routing cache is automatically refreshed or updated on chunk move or split by subscribing to respective ONS events.

13.6.1 UCP APIs for Middle-Tier Routing Support

The OracleShardRoutingCache Class

This class extends the internal shard routing cache of UCP and makes the basic routing cache feature available to the WebLogic Server or middle-tier routers or load balancers.

public class OracleShardRoutingCache extends ShardRoutingCache

This class provides the OracleShardRoutingCache(Properties dataSourceProps) and Set<ShardInfo> getShardInfoForKey(OracleShardingKey key, OracleShardingKey superKey) methods.

The ShardInfo Interface

The ShardInfo interface instances encapsulate unique shard name and priority. The unique shard name can then be mapped to a middle-tier server that connects to a specific shard.

13.6.2 Middle-Tier Routing with UCP Example

The following example explains the usage of the middle-tier routing API of UCP.

Example 13-4 Example of Middle-Tier Routing Using UCP

import java.sql.SQLException;
import java.util.Properties;
import java.util.Random;
import java.util.Set;

import oracle.jdbc.OracleShardingKey;
import oracle.jdbc.OracleType;
import oracle.ucp.UniversalConnectionPoolException;
import oracle.ucp.routing.ShardInfo;
import oracle.ucp.routing.oracle.OracleShardRoutingCache;

/**
 * The code example illustrates the usage of the middle-tier routing feature of UCP.  
 * The API accepts sharding key as input and returns the set of ShardInfo 
 * instances mapped to the sharding key. The ShardInfo instance encapsulates 
 * unique shard name and priority. The unique shard name then can be mapped 
 * to a middle-tier server that connects to a specific shard.
 *
 */
public class MidtierShardingExample {

  private static String user = "testuser1";
  private static String password = "testuser1";

  // catalog DB URL
  private static String url = "jdbc:oracle:thin:@//hostName:1521/catalogServiceName";
  private static String region = "regionName";

  public static void main(String args[]) throws Exception {
    testMidTierRouting();
  }

  static void testMidTierRouting() throws UniversalConnectionPoolException,
      SQLException {

    Properties dbConnectProperties = new Properties();
    dbConnectProperties.setProperty(OracleShardRoutingCache.USER, user);
    dbConnectProperties.setProperty(OracleShardRoutingCache.PASSWORD, password);
    // Mid-tier routing API accepts catalog DB URL
    dbConnectProperties.setProperty(OracleShardRoutingCache.URL, url);

    // Region name is required to get the ONS config string
    dbConnectProperties.setProperty(OracleShardRoutingCache.REGION, region);

    OracleShardRoutingCache routingCache = new OracleShardRoutingCache(
        dbConnectProperties);

    final int COUNT = 10;
    Random random = new Random();

    for (int i = 0; i < COUNT; i++) {
      int key = random.nextInt();
      OracleShardingKey shardKey = routingCache.getShardingKeyBuilder()
          .subkey(key, OracleType.NUMBER).build();
      OracleShardingKey superShardKey = null;

      Set<ShardInfo> shardInfoSet = routingCache.getShardInfoForKey(shardKey,
          superShardKey);

      for (ShardInfo shardInfo : shardInfoSet) {
        System.out.println("Sharding Key=" + key + " Shard Name="
            + shardInfo.getName() + " Priority=" + shardInfo.getPriority());
      }
    }

  }
}