13.2 About Handling Connection Requests for a Sharded Database

This section describes how connection requests are made on a pool for sharded databases.

13.2.1 How to Checkout Connections from a Pool with a Sharding Key

When a connection is borrowed from UCP, then the shard aware application can provide the sharding key and the super sharding key using the new connection builder present in the PoolDataSource class.

If sharding keys do not exist or do not map to the data types specified by the database metadata, then an IllegalArgumentException is thrown. The following code snippet shows how to checkout a connection with sharding keys:

Example 13-1 Checking Out a Connection with Sharding Keys

import java.sql.Connection;
import java.sql.JDBCType;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.ShardingKey; 

import oracle.ucp.jdbc.PoolDataSource;
import oracle.ucp.jdbc.PoolDataSourceFactory; 

public class UCPShardingExample {  

  public static void main(String[] args) throws SQLException {  
      String url = "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(HOST=myhost)(PORT=<gsm_port>)(PROTOCOL=tcp))(CONNECT_DATA=(SERVICE_NAME=myGSMservice)))";
      String user="db_user_name";
      String pwd = "db_password";
    
      PoolDataSource pds = PoolDataSourceFactory.getPoolDataSource();
      pds.setURL(url);
      pds.setUser(user);
      pds.setPassword(pwd);
      pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
      pds.setInitialPoolSize(5);
      pds.setMinPoolSize(5);
      pds.setMaxPoolSize(20);
            
      int empId = 1234;
      // Employee ID is the sharding key column in sharded table

      ShardingKey shardingKey = pds.createShardingKeyBuilder()
                                   .subkey(empId, JDBCType.INTEGER)
                                   .build();
    
      // Borrow a connection to direct shard using sharding key
      try(Connection connection = pds.createConnectionBuilder()
              .shardingKey(shardingKey)
              .build()) {

                          PreparedStatement pst = connection.prepareStatement("select * from employee where emp_id=?");
                          pst.setInt(1, 1234);
                          ResultSet rs = pst.executeQuery();
                          // retrieve the employee details using resultset
                          rs.close();
                          pst.close();
                        }
      }

  }

Note:

You must specify a sharding key during the connection checkout. Otherwise, an error or exception is thrown back to the application.

13.2.2 About Configuring the Number of Connections Per Shard

When UCP is used to pool connections for a sharded database, the pool contains connections to different shards. So, when connections are pulled, to ensure a fair usage of the pool capacity across all shards connected, UCP uses the MaxConnectionsPerShard parameter. This is a global parameter, which applies to every shard in the sharded database, and is used to limit the total number of connections to any shard below the specified limit.

The following table describes the APIs for setting and retrieving this parameter:

Method Description
poolDatasource.setMaxConnectionsPerShard(<max_connections_per_shard_limit>) Sets the maximum number of connections per shard.
poolDatasource.getMaxConnectionsPerShard() Retrieves the value that was set using the setMaxConnectionsPerShard(<max_connections_per_shard_limit>) method.

Note:

You cannot use the MaxConnectionsPerShard parameter in a sharded database with Oracle Golden Gate configuration.

13.2.3 About Connecting to the Shard Catalog or Co-ordinator for Multishard Queries

When connecting to the Shard Catalog or Co-ordinator for running multishard queries, it is recommended that a separate pool be created using a new PoolDataSource instance. You can run multishard queries on connections retrieved from a data source that is created on the coordinator service. The connection request for the coordinator should not have sharding keys in the connection builder API.