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.
This chapter provides information on how to configure and monitor UCP data sources. This chapter includes the following topics:
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.
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.
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 |
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.
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.
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("weblogic","welcome1","t3://"+hostname+":7001")
edit()
startEdit()
serverName="AdminServer"
serverBean = getMBean('/Servers/'+serverName)
host='%s.us.company.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('MYPASSWD')
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.
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.
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","welcome1","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()
<?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>
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.
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 operationsConnection retry, early failure, and critical data sources are available from WebLogic Server 12.2.1.3, to enhance the initial capacity connections in the connection pool.
Creating the Initial Capacity Connections in the Connection Pool
Whenever a server starts, the data source tries to create the initial capacity connections in the connection pool. Prior to 12.2.1.3, the data source attempted to create initial capacity connections even if some of the connection attempts failed. This can take a long time if one or more of the connection failures take a long time due to unavailability of network or database.
Connection Retry
There are two connection properties that control retrying the initial connection creation failure:
weblogic.jdbc.startupRetryCount — If this property is set and the value is greater than 0, if failure occurs connection creation will be retried based on the value. The default value is 0 (no retry).
weblogic.jdbc.startupRetryDelaySeconds— If this property is set and the value is greater than 0 and retry count is set, the connection creation will delay for the specified number of seconds between retries. The default value is 0 (no delay).
Early Failure
The following connection property controls whether or not to continue after connection creation fails:
weblogic.jdbc.continueMakeResourceAttemptsAfterFailure=true — If startup retry is enabled, the driver property weblogic.jdbc.continueMakeResourceAttemptsAfterFailure=true is ignored and the data source will not continue to create connections after a failure when the server is starting. It will continue create attempts if the data source is deployed or redeployed on a running server.
Critical Data Sources
If a failure occurs while populating the initial capacity connections in the connection pool, the data source is not deployed (it won't be in JNDI so the application will fail to find it) but the server continues to startup and is not marked as unhealthy. In some applications, a data source may be a critical resource such that no useful processing can be done if the data source is not deployed. This can be controlled using a connection property:
weblogic.jdbc.critical — If this value is set to true, the managed server fails to boot; this does not apply to the administration server, which is available to process configuration changes. The default value is false, where the server continues to boot without deploying the data source.
Example 8-2 WLST Sample Code
edit()
startEdit()
datasource="dsname"
cd("/JDBCSystemResources/" + datasource + "/JDBCResource/" + datasource + "/JDBCDriverParams/"
+ datasource + "/Properties/" + datasource)
cmo.createProperty("weblogic.jdbc.startupRetryCount", "5")
cmo.createProperty("weblogic.jdbc.startupRetryDelaySeconds", "10")
save()
activate()