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.
Configuring Universal Connection Pool Data Source
To configure Universal Connection Pool data sources 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.

Parent topic: JDBC Data Sources Types

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:

WebLogic Server Transaction Manager (one-phase, LLR, JTS, JDBC TLog, determiner resource, and so on).
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.
Oracle WebLogic Server data operations such as JMS, Leasing, EJB, and so on.
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.

Parent topic: Using Universal Connection Pool Data Sources

Configuring Universal Connection Pool Data Source

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

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

Parent topic: Using Universal Connection Pool Data Sources

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:

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.

Parent topic: Configuring Universal Connection Pool Data Source

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.

Parent topic: Configuring Universal Connection Pool Data Source

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.

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>

Parent topic: Using Universal Connection Pool Data Sources

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.

Parent topic: Using Universal Connection Pool Data Sources

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

Parent topic: Using Universal Connection Pool Data Sources