Using Universal Connection Pool Data Sources

A Universal Connection Pool (UCP) data source is provided as an option for users who wish to use Oracle Universal Connection Pooling to connect to Oracle Databases. UCP provides an alternative connection pooling technology to Oracle WebLogic Server connection pooling.

• What is Universal Connection Pool Data Source
  A Universal Connection Pool data source is provided as an option for users who wish to use UCP for connecting to Oracle Databases. UCP provides an alternative connection pooling technology to Oracle WebLogic Server connection pooling.
• Creating a Universal Connection Pool Data Source
  To create a Universal Connection Pool data source in your WebLogic domain, you can use the WebLogic Server Administration Console, the WebLogic Scripting Tool (WLST), or Fusion Middleware Control.
• Universal Connection Pool Multi Tenant Shared Pool support
  To use this feature, the URI for the Universal Connection Pool (UCP) MT Shared Pool support XML configuration file must be specified using the oracle.ucp.jdbc.xmlConfigFile system property before any UCP data source is loaded in the JVM.
• Monitoring Universal Connection Pool JDBC Resources
  Learn about monitoring Universal Connection Pool JDBC Resources using the WebLogic Sever Administration Console or the JDBCUCPDataSourceRuntimeMBean, JDBCDataSourceRuntimeMBean .
• Oracle Sharding Support
  Sharding is a data tier architecture in which data is horizontally partitioned across independent databases.

What is Universal Connection Pool Data Source

A Universal Connection Pool data source is provided as an option for users who wish to use UCP for connecting to Oracle Databases. UCP provides an alternative connection pooling technology to Oracle WebLogic Server connection pooling.

Note:

Oracle generally recommends the use of Generic data source, Multi Data Source, or Active GridLink data source with Oracle WebLogic Server to establish connectivity with Oracle databases.

WebLogic Server provides the following support when using a UCP data source:

• Configuration as an alternative data source to Generic data source, Multi Data Source, or Active GridLink data source.

• Deploy and undeploy data source.

• Basic monitoring and statistics:

  • ConnectionsTotalCount

  • CurrCapacity

  • FailedReserveRequestCount

  • ActiveConnectionsHighCount

  • ActiveConnectionsCurrentCount

• Certification with Oracle simple driver, XA driver, and JDBC Replay Driver driver.

A UCP data source does not support:

• Additional life cycle operations (suspend, resume, shutdown, forceshutdown, start, and so on).

• Generic support for any connection pool.

• Oracle WebLogic Server Security options.

• JDBC drivers other than those listed above.

• RMI access to a UCP data source.

The implementations of UCP data sources are loosely coupled, allowing the swapping of the ucp.jar to support the use of new UCP features by the applications. UCP data sources are not supported in an application-scoped/packaged or stand-alone module environment.

For details about the Oracle Universal Connection Pool, see Oracle Universal Connection Pool for JDBC Developer's Guide.

Creating a Universal Connection Pool Data Source

To create a Universal Connection Pool data source in your WebLogic domain, you can use the WebLogic Server Administration Console, the WebLogic Scripting Tool (WLST), or Fusion Middleware Control.

Procedures for creating a Universal Connection Pool data source using Fusion Middleware Control are described in Create JDBC Universal Connection Pool data sources in Administering Oracle WebLogic Server with Fusion Middleware Control.

The WebLogic Server Administration Console and WLST methods are described in the following sections:

• Configuring a UCP in the WebLogic Server Administration Console
  
• Configuring a UCP Using WLST
  

Configuring a UCP in the WebLogic Server Administration Console

The procedure for creating a Universal Connection Pool (UCP) data source in the WebLogic Server Administration Console is provided in Create Universal Connection Pool data sources in the Oracle WebLogic Server Administration Console Online Help. This procedure includes instructions for accessing the data source configuration wizard.

The following sections provide an overview of the basics steps used in the data source configuration wizard to create a data source using the WebLogic Server Administration Console:

Set JDBC Data Source Properties

The JDBC Data Source Properties section includes options that determine the identity of the data source and the way the data is handled on a database connection. Guidelines for configuring these properties are described as follows:

• Data Source Names—Enter a name for the UCP data source in the Name field. JDBC data source names are used to identify the data source within the WebLogic domain. For system resource data sources, names must be unique among all other JDBC system resources, including data sources. To avoid naming conflicts, data source names should also be unique among other configuration object names, such as servers, applications, clusters, and JMS queues, topics, and servers.

• Scope—Select the scope for the data source from the list of available scopes. You can set the scope to Global (at the domain level), or to any existing Resource Group or Resource Group Template.

• JNDI Names—Enter a JNDI name for the UCP data source in the JNDI Name field. You can configure a data source so that it binds to the JNDI tree with a single name or multiple names. You can use a multi-JNDI-named data source in place of legacy configurations that included multiple data sources that pointed to a single JDBC connection pool. For more information, see Developing JNDI Applications for Oracle WebLogic Server.

• Database Type and Driver—The UCP data source is certified with three Oracle drivers: thin XA and non-XA, and an JDBC Replay Driver driver. Select the required driver from the menu.

The supported combinations of driver and JDBC connection factory are shown in Table 4-4

Table 4-4 Supported Driver and Connection Factory Combinations for UCP Data Source

Driver	Factory (ConnectionFactoryClassName)
oracle.ucp.jdbc.PoolDataSourceImpl (default)	oracle.ucp.jdbc.PoolDataSourceImpl
oracle.ucp.jdbc.PoolXADataSourceImpl	oracle.jdbc.xa.client.OracleXADataSource
oracle.ucp.jdbc.PoolDataSourceImpl	oracle.jdbc.replay.OracleDataSourceImpl

Note:

The JDBC Replay Driver does not currently support XA transactions.

If a non-XA driver from the list in Table 4-4 is specified with an XA factory from the table, an error is generated. If you specify values that are not in the table they are not validated.

If the driver-name is not specified in the jdbc-driver-params, it defaults to oracle.ucp.jdbc.PoolDataSourceImpl.

If you specify a supported driver name but do not specify the ConnectionFactoryClassName connection property, the corresponding entry from Table 4-4 is used. If you do not specify a supported driver name, an error is generated.

Set Connection Properties

Connection properties are used to configure the connection between the data source and the DBMS. There are two ways that you can enter the connection properties for a UCP data source in the Administration Console.

On the Connection Properties page of the wizard, all of the available connection properties for a UCP driver are displayed so that you can enter the appropriate values. As an alternative, you can configure properties by entering the properties directly into the Properties text box on the Test Database Connection page using the format propertyName=value. Any values that you entered on the Connection Properties page are already listed Properties text box.

Table 4-5 describes the connection properties that you can configure for a UCP data source. For more information about UCP properties, see Class PoolDataSourceImpl. In Oracle Universal Connection Pool for JDBC Java API Reference. Attributes are determined by setters on the PoolDataSourceImpl class. Use the attribute name without the "set" prefix. The names are case insensitive.

Table 4-5 Universal Connection Pool Properties

Property	Type	Description
AbandonedConnectionTimeout	int	Sets the abandoned connection timeout. The range of valid values is 0 to Integer.MAX_VALUE. The default is 0.
ConnectionFactoryClassName	String	Sets the Connection Factory class name.
ConnectionFactoryProperties	name=value	Sets the connection factory properties on the connection factory.
ConnectionFactoryProperty	name=value	Sets a connection factory property on the connection factory.
ConnectionHarvestMaxCount	int	Sets the maximum number of connections that can be harvested when the connection harvesting occurs.
ConnectionHarvestTriggerCount	int	Sets the number of available connections at which the connection pool's connection harvesting will occur.
ConnectionLabelingHighCost	int	Sets the cost value that identifies a connection as "high-cost" for connection labeling.
ConnectionPoolName	String	Sets the connection pool name.
ConnectionProperties	name=value	Sets the connection properties on the connection factory.
ConnectionProperty	name=value	Sets a connection property on the connection factory.
ConnectionWaitTimeout	int	Sets the amount of time to wait (in seconds) for a used connection to be released by a client. The range of valid values is 0 to Integer.MAX_VALUE. The default is 3.
DatabaseName	String	Sets the database name.
DataSourceName	String	Sets the data source name.
Description	String	Sets the data source description.
FastConnectionFailoverEnabled	Boolean	Enables Fast Connection Failover (FCF) for the connection pool accessed using this pool-enabled data source. Valid values are true and false.
HighCostConnectionReuseThreshold	int	Sets the high-cost connection reuse threshold for connection labeling.
InactiveConnectionTimeout	int	Sets the inactive connection timeout. he range of valid values is 0 to Integer.MAX_VALUE. The default is 0.
InitialPoolSize	int	Sets the initial pool size. The range of valid values is 0 to Integer.MAX_VALUE. It is illegal to set this to a value greater than the maximum pool size. The default is 0.
LoginTimeout	int	Sets the login timeout.
MaxConnectionReuseCount	int	Sets the connection reuse count property.
MaxConnectionReuseTime	long	Sets the connection reuse time property.
MaxIdleTime	int	Sets Idle timeout for available connections in the pool.
MaxPoolSize	int	Sets the maximum number of connections. The range of valid values is 1 to Integer.MAX_VALUE. The default is Integer.MAX_VALUE.
MaxStatements	int	Sets the maximum number of statements that may be pooled or cached on a connection.
MinPoolSize	int	Sets the minimum number of connections. The range of valid values is 0 to Integer.MAX_VALUE. It is illegal to set this to a value greater than the maximum pool size. The default is 0.
NetworkProtocol	String	Sets the data source network protocol.
ONSConfiguration	String	Sets the configuration string used for remote ONS subscription.
Password	String	Sets the password with which connections have to be obtained.
PortNumber	int	Sets the database port number.
PropertyCycle	int	Sets the Property cycle in seconds.
RoleName	String	Sets the data source role name.
ServerName	String	Sets the database server name.
SQLForValidateConnection	String	Sets the value (SQL) for SQLForValidateConnection property.
TimeoutCheckInterval	int	Sets the timeoutCheckInterval, in seconds.
TimeToLiveConnectionTimeout	int	Sets the maximum time, in seconds, that a connection may remain in-use.
URL	String	Sets the URL that the data source uses to obtain connections to the database.
User	String	Sets the user name with which connections have to be obtained.
ValidateConnectionOnBorrow	Boolean	Sets whether or not a connection being borrowed should first be validated. Valid values are true and false.

Note:

System properties and encrypted properties are supported in addition to normal string literals. See the following topics in Oracle WebLogic Server Administration Console Online Help:

• Set System Properties

• Encrypt connection properties

If the jdbc-driver-params URL is set, any URL property is ignored. If the encrypted-password is set, any password property is ignored.

The attributes ConnectionFactoryProperty, ConnectionFactoryProperties, ConnectionProperty, and ConnectionFactoryProperties accept values of the form "name1=value1,name2=value2...".

Test Database Connections

The Test Database Connection page allows you to enter free-form values for properties and to test a database connection before the data source configuration is finalized using a table name or SQL statement. If necessary, you can test additional configuration information using the Properties and System Properties attributes.

Select Targets

You can select one or more targets to which to deploy your new UCP data source. If you don't select a target, the data source will be created but not deployed. You will need to deploy the data source at a later time.

Configuring a UCP Using WLST

You can create a UCP data source using WebLogic Scripting Tool (WLST) in the same way that you create other data source types. However, UCP data sources have less attributes.

The configuration elements for a UCP data source are as follows.

• name

• datasource-type=UCP

• jdbc-driver-params url

• jdbc-driver-params property - user

• jdbc-driver-params password-encrypted

• jdbc-data-source-params jndi-name

• jdbc-driver-params other properties

No other elements from the WebLogic Server data source descriptor are recognized. If other elements are specified, they are ignored.

A sample WLST script for creating a UCP data source is provided in Example 4-2

Example 4-2 Sample WLST Script to Create a UCP Data Source

import sys, socket
import os
hostname = socket.gethostname()
connect("username","password","t3://"+hostname+":7001")
edit()
startEdit()
serverName="AdminServer"
serverBean = getMBean('/Servers/'+serverName)
host='%s.us.example.com' %hostname
print 'Creating UCP datasource'
domain = getMBean("/")
startEdit()
resourceName='ucpDS'
print "Creating datasource ds in domain"
systemResource=domain.createJDBCSystemResource(resourceName)
systemResource.setName(resourceName)
jdbcResource=systemResource.getJDBCResource()
jdbcResource.setName(resourceName)
jdbcResource.setDatasourceType('UCP')
driverParams=jdbcResource.getJDBCDriverParams()
driverParams.setDriverName('oracle.ucp.jdbc.PoolDataSourceImpl')
driverParams.setUrl('jdbc:oracle:thin:@dbhost:1521/otrade')
properties = driverParams.getProperties()
properties.createProperty('user', 'dbuser')
properties.createProperty('ConnectionFactoryClassName', 'oracle.jdbc.pool.OracleDataSource')
driverParams.setPassword('PASSWD')
jdbcDataSourceParams=jdbcResource.getJDBCDataSourceParams()
jdbcDataSourceParams.addJNDIName(resourceName)
jdbcDataSourceParams.setGlobalTransactionsProtocol('None')
cd('/SystemResources/' + resourceName )
set('Targets',jarray.array([ObjectName('com.bea:Name=' + serverName + ',Type=Server')], ObjectName))
save()
activate()

Note:

You can also use the sample WLST script for creating a Generic data source that is provided with WebLogic Server as the basis for your UCP data source:

EXAMPLES_HOME\wl_server\examples\src\examples\wlst\online\jdbc_data_source_creation.py

where EXAMPLES_HOME represents the directory in which the WebLogic Server code examples are configured. See WLST Online Sample Scripts in Understanding the WebLogic Scripting Tool.

Universal Connection Pool Multi Tenant Shared Pool support

To use this feature, the URI for the Universal Connection Pool (UCP) MT Shared Pool support XML configuration file must be specified using the oracle.ucp.jdbc.xmlConfigFile system property before any UCP data source is loaded in the JVM.

This can be set on the command line when starting Weblogic Server. Since this is sometimes inconvenient, it is also possible to set the XmlConfigFile connection property. If you use the connection property approach, it must be set on all UCP data sources configured in WebLogic Server, even if they do not use the XML file. The format is generally something like file:///path/file.xml.

See Overview of UCP Shared Pool for Database Sharding in Universal Connection Pool Developer's Guide .

When using the shared pool feature, all attributes for the data source are ignored except for the following:

• Name – the data source name

• Data source Type – UCP

• Driver class name – oracle.ucp.jdbc.PoolDataSourceImpl or oracle.ucp.jdbc.PoolXADataSourceImpl

• Property DataSourceFromConfiguration – data source name in the XML file

• Property XmlConfigFile – optionally set the URI of the XML file if not set as a system property

• JNDI Name – the JNDI name where the data source object is mapped

  Example:

  import sys, socket
  import os
  hostname = socket.gethostname()
  connect("weblogic","server_password","t3://"+hostname+":7001")
  edit()
  startEdit()
  serverName="myserver"
  print 'Creating UCP datasource'
  domain = getMBean("/")
  startEdit()
  resourceName='ds5'
  print "Creating datasource ds in domain"
  systemResource=domain.createJDBCSystemResource(resourceName)
  systemResource.setName(resourceName)
  jdbcResource=systemResource.getJDBCResource()
  jdbcResource.setName(resourceName)
  jdbcResource.setDatasourceType('UCP')
  driverParams=jdbcResource.getJDBCDriverParams()
  driverParams.setDriverName('oracle.ucp.jdbc.PoolDataSourceImpl')
  properties = driverParams.getProperties()
  properties.createProperty('DataSourceFromConfiguration', 'pds1')
  properties.createProperty('XmlConfigFile', 'file:///SharedPoolDemo.xml')
  jdbcDataSourceParams=jdbcResource.getJDBCDataSourceParams()
  jdbcDataSourceParams.addJNDIName(resourceName)
  cd('/SystemResources/' + resourceName )
  set('Targets',jarray.array([ObjectName('com.bea:Name=' + serverName + ',Type=Server')], ObjectName))
  save()
  activate()

  The UCP XML file might look like the following.

  <?xml version="1.0" encoding="UTF-8"?> 
  <ucp-properties> 
  <connection-pool  
  connection-pool-name="pool1"  
  connection-factory-class-name="oracle.jdbc.pool.OracleDataSource" 
  url="jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)
  (HOST=dbhost)(PORT=5521))(CONNECT_DATA=
  (SERVICE_NAME=dbhostservice)))"  
  user="system"  
  password="manager"  
  initial-pool-size="4" 
  min-pool-size="2" 
   max-pool-size="10"  
  shared="true"
    >  
  <data-source data-source-name="pds1" 
   user="system" 
   password="manager" 
   service="pdb1_service" 
   description="pdb1 data source"
    />  
  <data-source data-source-name="pds2"   
  user="system"  
   password="manager"   
  service="pdb2_service"  
   description="pdb2 data source"  
  /> 
  </connection-pool> 
  </ucp-properties>

Monitoring Universal Connection Pool JDBC Resources

Learn about monitoring Universal Connection Pool JDBC Resources using the WebLogic Sever Administration Console or the JDBCUCPDataSourceRuntimeMBean, JDBCDataSourceRuntimeMBean .

The JDBCUCPDataSourceRuntimeMBean provides methods for getting the current state of the data source and for getting statistics about the data source, such as the average number of active connections, the current number of active connections, and the highest number of active connections. This MBean extends the JDBCDataSourceRuntimeMBean so that it can be returned with the list of other JDBC MBeans from the JDBC service. See JDBCUCPDataSourceRuntimeMBean in the MBean Reference for Oracle WebLogic Server.

In addition to runtime statistics, the testPool() operation returns null if the test is success or an error string otherwise (similar to other data source types). Testing the pool is done only if SQLForValidateConnection is set to a SQL string to be executed for validation (for example SELECT 1 from DUAL). The rest of the operations will take no action.

To understand more about JDBC monitoring, see Monitoring WebLogic JDBC Resources.

Oracle Sharding Support

Sharding is a data tier architecture in which data is horizontally partitioned across independent databases.

Oracle sharding is available in 12.2 UCP and surfaced from WebLogic Server via the native UCP data source feature. See Overview of Oracle Sharding in Using Oracle Sharding.

Once the UCP data source is accessed using a JNDI look-up, the sharding API's can be used, as in the following java code:

import javax.naming.Context;
import javax.naming.InitialContext;
import java.sql.Connection;
import oracle.ucp.jdbc.PoolDataSource;

Context cts = new InitialContext();

/// Look up the data source using JNDI
PoolDatasource pds = (PoolDataSource) ctx.lookup("ShardedDB");

 // Create a key corresponding to sharding key columns, to access the correct shard
OracleShardingKey key = pds.createShardingKeyBuilder().subkey(100, JDBCType.NUMERIC).build();

 // Fetch a connection to the shard corresponding to the key
Connection conn = pds.createconnectionBuilder().shardingKey(key).build();

 // Use the above connection for performing shard specific operations

Since Oracle 23.1, UCP supports sharding with global or JTA transactions managed by the WebLogic Server transaction manager. Java EE applications can use UCP data source sharding API to obtain connections to Oracle sharded databases, and participate in JTA or XA transactions managed by WebLogic Server transaction manager.

Due to Oracle sharding server limitation, all the supplied sharding keys should drive the connection requests to the same Oracle sharded database instance, within the same JTA or XA transaction. If application supplies sharding keys that results in a connection going to a different database instance, UCP raises an exception at the connection request.