8 Using Universal Connection Pool Data Sources

A Universal Connection Pooling (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 a Universal Connection Pool Data Source?

A Universal Connection Pooling (UCP) 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 Active GridLink data source, multi data source, or JDBC 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 application continuity 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.

Creating a Universal Connection Pool Data Source

To create a UCP data source 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 sections:

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

Configuring a UCP Data Source in the WebLogic Server Administration Console

The procedure for creating a UCP data source in the WebLogic Server Administration Console is provided in Create UCP 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 application continuity (replay) driver. Select the required driver from the menu.

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

Table 8-1 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 replay driver does not currently support XA transactions.

If a non-XA driver from the list in Table 8-1Table 8-1 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 8-1 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 8-2 describes the connection properties that you can configure for a UCP data source. For more information about UCP connection 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 8-2 UCP 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.

Configuring a UCP Data Source Using WLST

You can create a UCP data source using 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 8-1

Example 8-1 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.

UCP MT Shared Pool support

To use this feature, the URI for the UCP 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>

Monitoring Universal Connection Pool JDBC Resources

Learn about monitoring UCP 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.

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.

See JDBCUCPDataSourceRuntimeMBean in the MBean Reference for Oracle WebLogic Server.

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.

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

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