This chapter provides advanced configuration options that can provide improved data source and driver performance when using Oracle drivers and databases. Options include proxy authentication, setting credentials on a connection, connection harvesting, and labeling connections.
WebLogic Server provides several attributes that provide improved Data Source performance when using Oracle drivers, including:
Oracle Enable JavaNet Fastpath: Enables the Oracle JDBC JavaNet Fastpath to reduce data copies and fragmentation.
Oracle Optimize UTF8 Conversion: Enables the Oracle JDBC optimize UTF-8 conversion option.
If your environment supports these attributes, see Configure Oracle parameters in the Oracle WebLogic Server Administration Console Help.
Proxy authentication allows one JDBC connection to act as a proxy for other JDBC connections to an Oracle database. You can configure a WebLogic data source to allow a client to connect to a database through an application server as a proxy user. The client authenticates with the application server and the application server authenticates with the Oracle database. This allows the client's user name to be maintained on the connection with the database.
Use the following steps to configure proxy authentication to an Oracle database:
If you have not yet done so, create the necessary WebLogic Server and database users.
On the Oracle database, provide CONNECT THROUGH privileges.
For example:
SQL> ALTER USER wlsuser GRANT CONNECT THROUGH dbuser;
where wlsuser is a WebLogic Server user and dbuser is an Oracle database user.
On a WebLogic Server instance:
Create a generic or GridLink data source and set the user to the value of dbuser.
Map the value of wlsuser to the value of dbuser. See "Configure credential mapping for a JDBC data source"" in the Oracle WebLogic Server Administration Console Help.
Redeploy the data source.
Log on to a WebLogic Server instance using the value of wlsuser.
Enable Proxy Authentication, see "Configure Oracle parameters" in Oracle WebLogic Server Administration Console Help.
A proxy session is opened on the connection for the application-authenticated user each time a connection request is made on the pool. The proxy session is closed when the connection is returned to the pool.
Note:
Opening or closing a proxy session:
Closes any existing statements (including result sets) from the original connection.
Clears the WebLogic Server statement cache.
The WebLogic Server test statement for a connection is recreated for every proxy session.
These behaviors may impact applications that share a connection across instances and expect some state to be associated with the connection.
You can configure a WebLogic data source to allow a connection to set database credentials when connecting to an Oracle database. When configured, a proxy session is opened for a connection created using getConnection(username, password) to specify a database user and password. This provides a simple, light-weight mechanism to authenticate many users and avoids the need to credential mapping entries for each database user.
Use the following steps to configure proxy authentication of database credentials set on a connection to an Oracle database:
If you have not yet done so, create the necessary database users.
On the Oracle database, provide CONNECT THROUGH privileges.
For example:
SQL> ALTER USER connectionuser GRANT CONNECT THROUGH dbuser;
where connectionuser is the name of the application user to be authenticated and dbuser is an Oracle database user.
On a WebLogic Server instance, create a generic or GridLink data source and set the user to the value of dbuser.
Enable Use Database Credentials, see "Configure Oracle parameters" in Oracle WebLogic Server Administration Console Help.
A proxy session is opened on the connection for the application-authenticated user each time a connection request is made on the pool. The proxy session is closed when the connection is returned to the pool.
Note:
Opening or closing a proxy session:
Closes any existing statements (including result sets) from the original connection.
Clears the WebLogic Server statement cache.
The WebLogic Server test statement for a connection is recreated for every proxy session.
These behaviors may impact applications that share a connection across instances and expect some state to be associated with the connection.
Open a connection using getConnection(username, password).
Note:
getConnection fails if Use Database Credentials is not enabled and the value of connectionuser is not a WebLogic Server user.
Consider the following when setting database credentials on a connection that participates in transactions:
When using getConnection(username, password) with data sources configured with non-XA LLR or 1PC (JTS driver) with global transactions, the first connection obtained within the transaction is returned on subsequent getConnection() requests regardless of the values of username/password specified. The connection must use the proxy user session information from the first getConnection() request because the connection must be shared among all users of the connection when using LLR or 1PC.
For XA data sources, connections may be stored with the transaction context on a given JVM and subsequent getConnection() requests may return a connection with a proxy user for a prior getConnection() request. Oracle recommends that a single database username be specified when using this feature in global transactions.
You can specify a number of reserved connections to be released when a data source reaches a specified number of available connections. Harvesting helps to ensure that a specified number of connections are always available in the pool and improves performance by minimizing connection initialization.
Connection harvesting is particularly useful if an application caches connection handles. Caching is typically performed for performance reasons because it minimizes the initialization of state necessary for connections to participate in a transaction. For example: A connection is reserved from the data source, initialized with necessary session state, and then held in a context object. Holding connections in this manner may cause the connection pool to run out of available connections. Connection harvesting appropriately reclaims the reserved connections and allows the connections to be reused.
Use the following steps to use connection harvesting in your applications:
The Connection Harvest Trigger Count attribute of a data source configuration is used to enable and specify a threshold to trigger connection harvesting. For example, if Connection Harvest Trigger Count is set to 10, connection harvesting is enabled and the data source begins to harvest reserved connections when the number of available connections drops to 10. A value of -1 indicates that connection harvesting is disabled.
When connection harvesting is triggered, the Connection Harvest Max Count specifies how many reserved connections can be returned to the pool. The number of connections actually harvested ranges from 1 to the value of Connection Harvest Max Count.
See "Configure connection harvesting for a connection pool" in Oracle WebLogic Server Administration Console Help.
When connection harvesting is enabled, all connections are harvestable. If you do not want a connection to be harvestable, you must explicitly mark it as unharvestable by calling the setConnectionHarvestable(boolean) method in the oracle.ucp.jdbc.HarvestableConnection interface with false as the argument value.
For example, use the following statements to prevent harvesting when a transaction is used within a transaction:
. . . Connection conn = datasource.getConnection(); ((HarvestableConnection) conn).setConnectionHarvestable(false); . . .
After the transaction completes, you can mark the connection as harvestable by setting setConnectionHarvestable(true) so the connection can be harvested if required. You can tell the harvestable status of a connection by calling isConnectionHarvestable().
When a connection is harvested, an application callback is executed to cleanup the connection if the callback has been registered. A unique callback must be generated for each connection; generally it needs to be initialized with the connection object. For example:
import java.sql.Connection;
. . .
public myHarvestingCallback implements ConnectionHarvestingCallback {
private Connection conn;
mycallback(Connection conn) {
this.conn = conn;
}
public boolean cleanup() {
try {
conn.close();
} catch (Exception ignore) {
return false;
}
return true;
}
}
. . .
Connection conn = ds.getConnection();
try {
(HarvestableConnection)conn).registerConnectionHarvestingCallback(
new myHarvestingCallback(conn));
(HarvestableConnection)conn).setConnectionHarvestable(true);
} catch (Exception exception) {
// This can't be from registration – setConnectionHarvestable must have failed.
// That most likely means that the connection has already been harvested.
// Do whatever logic is necessary to clean up here and start over.
throw new Exception(”Need to get a new connection”);
}
. . .
Note:
Consider the following:
After a connection is harvested, an application can only call Connection.close.
If the connection is not closed by the application, a warning is logged indicating that the connection was forced closed if LEAK profiling is enabled.
If the callback throws an exception, a message is logged and the exception is ignored. Use JDBCCONN debugging to retrieve a full stack trace.
The return value of the cleanup method is ignored.
Harvesting is done as part of the data source maintenance processing. The data source maintenance interval is fixed by default to the value of SecurityCacheTimeoutSeconds.
Applications often initialize connections retrieved from a connection pool before using the connection. The initialization varies and could include simple state re-initialization that requires method calls within the application code or database operations that require round trips over the network. The cost of such initialization may be significant.
Labeling connections allows an application to attach arbitrary name/value pairs to a connection. The application can request a connection with the desired label from the connection pool. By associating particular labels with particular connection states, an application can retrieve an already initialized connection from the pool and avoid the time and cost of re-initialization. The connection labeling feature does not impose any meaning on user-defined keys or values; the meaning of user-defined keys and values is defined solely by the application.
Some of the examples for connection labeling include, role, NLS language settings, transaction isolation levels, stored procedure calls, or any other state initialization that is expensive and necessary on the connection before work can be executed by the resource.
Connection labeling is application-driven and requires the following:
The oracle.ucp.jdbc.LabelableConnection interface is used to apply and remove connection labels, as well as retrieve labels that have been set on a connection.
The oracle.ucp.ConnectionLabelingCallback interface is used to create a labeling callback that determines whether or not a connection with a requested label already exists. If no connections exist, the interface allows current connections to be configured as required.
Configuring the data source to keep connections after transactions, see "JDBC Data Source: Configuration: Transaction" in Oracle WebLogic Server Administration Console Help.
A labeling callback is used to define how the connection pool selects labeled connections and allows the selected connection to be configured before returning it to an application. Applications that use the connection labeling feature must provide a callback implementation.
A labeling callback is used when a labeled connection is requested but there are no connections in the pool that match the requested labels. The callback determines which connection requires the least amount of work in order to be re-configured to match the requested label and then allows the connection's labels to be updated before returning the connection to the application.
Note:
Connection Labeling is not supported from client applications that use RMI. See "Using the WebLogic RMI Driver (Deprecated)" in Programming JDBC for Oracle WebLogic Server.
To create a labeling callback, an application implements the oracle.ucp.ConnectionLabelingCallback interface. One callback is created per connection pool. The interface provides two methods as shown below:
public int cost(Properties requestedLabels, Properties currentLabels);
public boolean configure(Properties requestedLabels, Connection conn);
The connection pool iterates over each connection available in the pool. For each connection, it calls the cost method. The result of the cost method is an integer which represents an estimate of the cost required to reconfigure the connection to the required state. The larger the value, the costlier it is to reconfigure the connection. The connection pool always returns connections with the lowest cost value. The algorithm is as follows:
If the cost method returns 0 for a connection, the connection is a match (note that this does not guarantee that requestedLabels equals currentLabels). The connection pool does not call configure on the connection found and simply returns the connection.
If the cost method returns a value that is not 0 (a negative or positive integer), then the connection pool iterates until it finds a connection with a cost value of 0 or runs out of available connections.
If the pool has iterated through all available connections and the lowest cost of a connection is Integer.MAX_VALUE (2147483647 by default), then no connection in the pool is able to satisfy the connection request. The pool creates a new connection, calls the configure method on it, and then returns this new connection. If the pool has reached the maximum pool size (it cannot create a new connection), then the pool either throws an SQL exception or waits if the connection wait timeout attribute is specified.
If the pool has iterated through all available connections and the lowest cost of a connection is less than Integer.MAX_VALUE, then the configure method is called on the connection and the connection is returned. If multiple connections are less than Integer.MAX_VALUE, the connection with the lowest cost is returned.
There is also an extended callback interface oracle.ucp.jdbc.ConnectionLabelingCallback that has an additional getRequestLabels() method. getRequestedLabels is invoked at getConnection() time when no requested labels are provided and there is an instance registered. This occurs when the standard java.sql.Datasource getConnection() methods are used that do not provide the label information on the getConnection() call.
The following code example demonstrates a simple labeling callback implementation that implements both the cost and configure methods. The callback is used to find a labeled connection that is initialized with a specific transaction isolation level.
import oracle.ucp.jdbc.ConnectionLabelingCallback;
import oracle.ucp.jdbc.LabelableConnection;
import java.util.Properties;
import java.util.Map;
import java.util.Set;
import weblogic.jdbc.extensions.WLDataSource;
class MyConnectionLabelingCallback implements ConnectionLabelingCallback {
public MyConnectionLabelingCallback() {
}
public int cost(Properties reqLabels, Properties currentLabels) {
// Case 1: exact match
if (reqLabels.equals(currentLabels)) {
System.out.println("## Exact match found!! ##");
return 0;
}
// Case 2: some labels match with no unmatched labels
String iso1 = (String) reqLabels.get("TRANSACTION_ISOLATION");
String iso2 = (String) currentLabels.get("TRANSACTION_ISOLATION");
boolean match =
(iso1 != null && iso2 != null && iso1.equalsIgnoreCase(iso2));
Set rKeys = reqLabels.keySet();
Set cKeys = currentLabels.keySet();
if (match && rKeys.containsAll(cKeys)) {
System.out.println("## Partial match found!! ##");
return 10;
}
// No label matches to application's preference.
// Do not choose this connection.
System.out.println("## No match found!! ##");
return Integer.MAX_VALUE;
}
public boolean configure(Properties reqLabels, Object conn) {
try {
String isoStr = (String) reqLabels.get("TRANSACTION_ISOLATION");
((Connection)conn).setTransactionIsolation(Integer.valueOf(isoStr));
LabelableConnection lconn = (LabelableConnection) conn;
// Find the unmatched labels on this connection
Properties unmatchedLabels =
lconn.getUnmatchedConnectionLabels(reqLabels);
// Apply each label <key,value> in unmatchedLabels to conn
for (Map.Entry<Object, Object> label : unmatchedLabels.entrySet()) {
String key = (String) label.getKey();
String value = (String) label.getValue();
lconn.applyConnectionLabel(key, value);
}
} catch (Exception exc) {
return false;
}
return true;
}
public java.util.Properties getRequestedLabels() {
Properties props = new Properties();
// Set based on some application state that might be on a thread-local, http session info, etc.
String value = ”value”;
props.put("TRANSACTION_ISOLATION”, value);
return props;
}
}
A WLS data source provides the registerConnectionLabelingCallback(ConnectionLabelingCallback callback) method for registering labeling callbacks. Only one callback may be registered on a connection pool. The following code example demonstrates registering a labeling callback that is implemented in the MyConnectionLabelingCallback class:
. . . import weblogic.jdbc.extensions.WLDataSource; . . . MyConnectionLabelingCallback callback = new MyConnectionLabelingCallback(); ((WLDataSource)ds).registerConnectionLabelingCallback( callback ); . . .
You can also register the callback using the Administration Console, see "Configure a connection labeling callback class" in Oracle WebLogic Server Administration Console Help.
You can remove a labeling callback by using one of the following methods:
If you have programatically set a callback, use the removeConnectionLabelingCallback() method as shown in the following example:
. . . import weblogic.jdbc.extensions.WLDataSource; . . . ((WLDataSource)ds).removeConnectionLabelingCallback( callback ); . . .
If you have administratively configured the callback, remove the callback from the data source configuration. See "Configure a connection labeling callback class" in Oracle WebLogic Server Administration Console Help
Note:
An application must use one of the methods to register and remove callbacks but not both. For example, if you register the callback on a connection using registerConnectionLabelingCallback(callback), you must use removeConnectionLabelingCallback() to remove it.
Labels are applied on a reserved connection using the applyConnectionLabel method from the LabelableConnection interface. Any number of connection labels may be cumulatively applied on a reserved connection. Each time a label is applied to a connection, the supplied key/value pair is added to the existing collection of labels. Only the last applied value is retained for any given key.
Note:
A labeling callback must be registered on the connection pool before a label can be applied on a reserved connection; otherwise, labeling is ignored. See Creating a Labeling Callback.
The following example demonstrates initializing a connection with a transaction isolation level and then applying a label to the connection:
. . . String pname = "property1"; String pvalue = "value"; Connection conn = ds.getConnection(); // initialize the connection as required. conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE); ((LabelableConnection) conn).applyConnectionLabel(pname, pvalue); . . .
A WLS data source provides two getConnection methods that are used to reserve a labeled connection from the pool. The methods are shown below:
public Connection getConnection(java.util.Properties labels) throws SQLException;
public Connection getConnection(String user, String password, java.util.Properties labels) throws SQLException;
The methods require that the label be passed to the getConnection method as a Properties object. The following example demonstrates getting a connection with the label property1 value.
. . . import weblogic.jdbc.extensions.WLDataSource; . . . String pname = "property1"; String pvalue = "value"; Properties label = new Properties(); label.setProperty(pname, pvalue); . . . Connection conn = ((WLDataSource)ds).getConnection(label); . . .
It is also possible to use the standard java.sql.Datasource getConnection() methods. In this case, the label information is not provided on the getConnection() call. The interface oracle.ucp.jdbc.ConnectionLabelingCallback is used:
java.util.Properties getRequestedLabels();
getRequestedLabels is invoked at getConnection() time when no requested labels are provided and there is an instance registered.
A connection may have multiple labels that each uniquely identifies the connection based on some desired criteria. The getUnmatchedConnectionLabels method is used to verify which connection labels matched from the requested labels and which did not. The method is used after a connection with multiple labels is reserved from the connection pool and is typically used by a labeling callback. The following code example demonstrates checking for unmatched labels:
. . . String pname = "property1"; String pvalue = "value"; Properties label = new Properties(); label.setProperty(pname, pvalue); . . . Connecion conn = ((WLDataSource)ds).getConnection(label); Properties unmatched = ((LabelableConnection)connection).getUnmatchedConnectionLabels (label); . . .
The removeConnectionLabel method is used to remove a label from a connection. This method is used after a labeled connection is reserved from the connection pool. The following code example demonstrates removing a connection label:
. . . String pname = "property1"; String pvalue = "value"; Properties label = new Properties(); label.setProperty(pname, pvalue); Connection conn = ((WLDataSource)ds).getConnection(label); . . . ((LabelableConnection) conn).removeConnectionLabel(pname); . . .