|
 |
| e-docs > WebLogic Server > Administration Guide > Managing JDBC Connectivity |
|
Administration Guide
|
The following sections provide guidelines for configuring and managing database connectivity through the JDBC components—Data Sources, connection pools and MultiPools—for both local and distributed transactions:
Overview of JDBC Administration
The Administration Console provides an interface to the tools that allow you to configure and manage WebLogic Server features, including JDBC (Java database connectivity). For most JDBC administrative functions, which include creating, managing and monitoring connectivity, systems administrators use the Administration Console or the command-line interface. Application developers may want to use the JDBC API or the WebLogic Management API.
Frequently performed tasks to set and manage connectivity include:
About the Administration Console
Your primary way to set and manage JDBC connectivity is through the Administration Console. Using the Administration Console, you set up persistent connectivity—connection pools, Data Sources, Tx Data Sources, and Multipools that are available even after you stop and restart the server. These JDBC objects are known as static objects. You can create dynamic objects—objects that you expect to use and then remove—with the administration command line or in application code.
In addition to setting connectivity, the Administration Console allows you to manage and monitor established connectivity.
About the Command-Line Interface
The command-line interface provides a way to dynamically create and manage connection pools and data sources. For information on how to use the command-line interface, see WebLogic Server Command-Line Interface Reference.
For information on setting and managing connectivity programmatically, see Programming WebLogic JDBC.
The JDBC drivers, used locally and in distributed transactions, interface with many WebLogic Server components and information appears in several documents. For example, information about JDBC drivers is included in the documentation sets for JDBC, JTA, and WebLogic jDrivers.
Here is a list of additional resources for JDBC, JTA and Administration:
The following documentation is written primarily for application developers. Systems Administrators may want to read the introductory material as a supplement to the material in this document.
The following documentation is written primarily for application developers. Systems Administrators may want to read the following as supplements to the material in this section.
JDBC Components—Connection Pools, Data Sources, and MultiPools
The following sections provide a brief overview of the JDBC connectivity components—connection pools, MultiPools, and Data Sources.
Figure 8-1 JDBC Components in WebLogic Server
A connection pool contains a group of JDBC connections that are created when the connection pool is registered—when starting up WebLogic Server or when assigning the connection pool to a target server or cluster. Connection pools use a type 2 or type 4 JDBC driver to create physical database connections. Your application borrows a connection from the pool, uses it, then returns it to the pool by closing it. Read more about Connection Pools in Programming WebLogic JDBC.
All of the settings you make with the Administration Console are static; that is, all settings persist even after you stop and restart WebLogic Server. You can create dynamic connection pools—those that you expect to use and delete while the server is running—using the command line (see WebLogic Server Command-Line Interface Reference) or programmatically using the API (see Creating a Connection Pool Dynamically in Programming WebLogic JDBC).
Connection pool settings are persisted in the config.xml file, including settings for dynamically created connection pools (until you programmatically delete the connection pool). For information about entries in the config.xml file, see the JDBCConnectionPool section of the Configuration Reference Guide.
Application-Scoped JDBC Connection Pools
When you package your enterprise applications, you can include the weblogic-application.xml supplemental deployment descriptor, which you use to configure application scoping. Within the weblogic-application.xml file, you can configure JDBC connection pools that are created when you deploy the enterprise application.
An instance of the connection pool is created with each instance of your application. This means an instance of the pool is created with the application on each node that the application is targeted to. It is important to keep this in mind when considering pool sizing.
Connection pools created in this manner are known as application-scoped connection pools, app scoped pools, application local pools, app local pools, or local pools, and are scoped for the enterprise application only. That is, they are isolated for use by the enterprise application.
For more information about application scoping and application scoped resources, see:
A MultiPool is a pool of connection pools. Used in local (non-distributed) transactions on single or multiple WebLogic Server configurations, MultiPools aid in either:
A Data Source object enables JDBC applications to obtain a DBMS connection from a connection pool. Each Data Source object binds to the JNDI tree and points to a connection pool or MultiPool. Applications look up the Data Source to get a connection. Data Source objects can be defined with JTA (Tx Data Sources in the Administration Console) or without JTA (Data Sources in the Administration Console). You use Tx Data Source for distributed transactions. See JDBC Configuration Guidelines for Connection Pools, MultiPools, and DataSources for more information about using Data Sources and Tx Data Sources.
In WebLogic Server, you can bind a JDBC DataSource resource into the WebLogic Server JNDI tree as a resource factory. You can then map a resource factory reference in the EJB deployment descriptor to an available resource factory in a running WebLogic Server to get a connection from a connection pool.
For details about creating and using a JDBC Data Source factory, see Resource Factories in Programming WebLogic Enterprise JavaBeans.
Security for JDBC Connection Pools
You can optionally restrict access to JDBC connection pools. In previous releases of WebLogic Server, ACLs were used to protect WebLogic resources. In WebLogic Server version 7.0, security policies answer the question "who has access" to a WebLogic resource. A security policy is created when you define an association between a WebLogic resource and a user, group, or role. A WebLogic resource has no protection until you assign it a security policy. For instructions on how to set up security for all WebLogic Server resources, see "Setting Protections for WebLogic Resources" in the Administration Console Online Help.
Security for JDBC Connection Pools in Compatibility Mode
WebLogic Server 7.0 continues to support the security model from version 6.1 for backward compatibility. To use version 6.1 security, you must run in compatibility mode. For details about running in compatibility mode, see the following documents:
The default security realm for WebLogic Server 6.1 was the File realm, which uses ACLs in the fileRealm.properties file for authorization and authentication. Connection pools are unprotected unless you define ACLs for connection pools (as a resource type) or for individual connection pools. If you define an ACL for connection pools, access is restricted to exactly what is defined in the ACL. For example, before you have any ACLs for connection pools in your fileRealm.properties file, everyone has unrestricted access to all connection pools in your domain. However, if you add the following line to the file, access becomes very restricted:
acl.reset.weblogic.jdbc.connectionPool=Administrators
This line grants reset privileges to Administrators on all connection pools and it prohibits all other actions by all other users. By adding an ACL, file realm protection for connection pools is activated. WebLogic Server enforces the ACLs defined in fileRealm.properties and only allows access specifically granted in the file. If your intent in adding the ACL was to restrict resets only on connection pools, you must specifically grant privileges for other actions to everyone or to specific roles or users. For example:
acl.reserve.weblogic.jdbc.connectionPool=everyone
acl.shrink.weblogic.jdbc.connectionPool=everyone
acl.admin.weblogic.jdbc.connectionPool=everyone
Table 8-1 lists the ACLs that you can use in fileRealm.properties to secure connection pools.
Configuring and Managing JDBC Connection Pools, MultiPools, and DataSources Using the Administration Console
The following sections discuss how to set database connectivity by configuring JDBC components—connection pools, Data Sources, and MultiPools. Once connectivity is established, you use either the Administration Console or command-line interface to manage and monitor connectivity. See Table 8-3 for descriptions of the configuration tasks and links to the Administration Console Online Help.
JDBC settings you make in the Administration Console, including configuration settings for connection pools, MultiPools, DataSources, and TxDataSources, are persisted in the config.xml file for the domain. For information about entries in this file, see the following sections of the Configuration Reference Guide:
In this section, we define configuration as including these processes:
Using the Administration Console, you create the JDBC components—connection pools, Data Sources, and MultiPools—by specifying attributes and database properties. See Configuring JDBC Connectivity Using the Administration Console.
First you create the connection pools and optionally a MultiPool, then you create the Data Source. When you create a Data Source object, you specify a connection pool or MultiPool as one of the Data Source attributes. This associates that Data Source with one specific connection pool or MultiPool ("pool").
Once you configure the Data Source and associated connection pool (or MultiPool), you then target each object to the same servers or clusters. Some common scenarios are as follows:
See "JDBC Connections" in Using WebLogic Clusters for more information about connection pools, data sources, and MultiPools in a cluster. See Configuring JDBC Connectivity Using the Administration Console for a description of the tasks you perform.
Refer to the following table for more information about association and assignment in the configuration process.
You can assign these Data Source/connection pool combinations to more than one server or cluster, but they must be assigned in combination. For example, you cannot assign a Data Source to Managed Server A if its associated connection pool is assigned only to Server B.
You can configure dynamic connection pools (after the server starts) using the WebLogic API (see Creating a Dynamic Connection Pool in Programming WebLogic JDBC) or the command-line interface (see JDBC Configuration Tasks Using the Command-Line Interface). WebLogic Server also includes example code for creating and configuring dynamic Data Sources and connection pools in the server samples, if you opted to install samples during installation. See SAMPLES_HOME\server\src\examples\jdbc, where SAMPLES_HOME is the location of the top-level directory for all samples and examples for the WebLogic Platform (c:\bea\weblogic700\samples, by default).
Configuring JDBC Connectivity Using the Administration Console
The Administration Console allows you to configure, manage, and monitor JDBC connectivity. To display the tabs that you use to perform these tasks, follow these steps:
The following table shows the connectivity tasks, listed in typical order in which you perform them. You may perform these tasks in a different order; but you must configure an object before associating or assigning it.
|
On the Configuration tabs in the right pane, you set the attributes for the connection pool, such as Name, URL, and database Properties. |
|
|
This task copies a connection pool. On the Configuration tabs, you change Name of pool to a unique name; and accept or change the remaining attributes. This a useful feature when you want to have identical pool configurations with different names. For example, you may want to have each database administrator use a certain pool to track individual changes to a database. |
|
|
Using the Target tab, you assign the connection pool to one or more Servers or Clusters. See Table 8-2 Association and Assignment Scenarios. Also, to assign several connection pools to a server, see Assigning JDBC Connection Pools to a Server in the Online Help. |
|
|
On the MultiPool tabs, you set the attributes for the name and algorithm type, either High Availability or Load Balancing. On the Pool tab, you assign the connection pools to this MultiPool. |
|
|
Using the Target tab, you assign the configured MultiPool to Servers or Clusters. |
|
|
Configure a Data Source (and Associate it with a Connection Pool) |
Using the Data Source tab, set the attributes for the Data Source, including the Name, JNDI Name, and Pool Name (this associates, or assigns, the Data Source with a specific pool—connection pool or MultiPool.) |
|
Using the Target tab, you assign the configured Data Source to servers or clusters. |
|
|
Configure a Tx Data Source (and Associate it with a Connection Pool) |
Using the Tx Data Source tab, set the attributes for the Tx Data Source, including the Name, JNDI Name, and Connection Pool Name (this associates, or assigns, the Data Source with a specific pool). Note: Do not associate a Tx Data Source with a MultiPool; MultiPools are not supported in distributed transactions. |
|
Using the Target tab, you assign the configured Tx Data Source to servers. |
Database Passwords in Connection Pool Configuration
When you create a connection pool, you typically include at least one password to connect to the database. If you use an open string to enable XA, you may use two passwords. You can enter the passwords as a name-value pair in the Properties field or you can enter them in their respective fields:
openString=Oracle_XA+Acc=P/userName/+SesTm=177+DB=dbHost+Threads=true=Sqlnet=dvi0+logDir=.
If you specify a password in the Properties field when you first configure the connection pool, WebLogic Server removes the password from the Properties string and sets the value as the Password value in an encrypted form the next time you start WebLogic Server. If there is already a value for the Password attribute for the connection pool, WebLogic Server does not change any values. However, the value for the Password attribute overrides the password value in the Properties string. The same behavior applies to any password that you define as part of an open string. For example, if you include the following properties when you first configure a connection pool:
user=scott;
password=tiger;
openString=Oracle_XA+Acc=p/scott/tiger+SesTm=177+db=dbHost+Threads=true+Sqlnet=lcs817+logDir=.+dbgFl=0x15;server=dbHost
The next time you start WebLogic Server, it moves the database password and the password included in the open string to the Password and Open String Password attributes, respectively, and the following value remains for the Properties field:
user=scott;
openString=Oracle_XA+Acc=p/scott/+SesTm=177+db=dbHost+Threads=true+Sqlnet=lcs817+logDir=.+dbgFl=0x15;server=dbHost
After a value is established for the Password or Open String Password attributes, the values in these attributes override the respective values in the Properties attribute. That is, continuing with the previous example, if you specify tiger2 as the database password in the Properties attribute, WebLogic Server ignores the value and continues to use tiger as the database password, which is the current encrypted value of the Password attribute. To change the database password, you must change the Password attribute.
Note: The value for Password and Open String Password do not need to be the same.
JDBC Configuration Tasks Using the Command-Line Interface
The following table shows what methods you use to create a dynamic connection pool.
|
|
For more information, see WebLogic Server Command-Line Interface Reference, and "Creating a Connection Pool Dynamically" in Programming WebLogic JDBC.
Managing and Monitoring Connectivity
Managing connectivity includes enabling, disabling, and deleting the JDBC components once they have been established.
JDBC Management Using the Administration Console
To manage and monitor JDBC connectivity, refer to the following table:
|
Using the instructions in Assign a Connection Pool to the Servers/Clusters, on the Target tab deselect the target (move target from Chosen to Available) and assign a new target. To assign several connection pools to a server, see Assigning JDBC Connection Pools to a Server in the Online Help. |
|
|
Using the instructions in Assign the MultiPool to Servers or Clusters, on the Target tab deselect the target (move target from Chosen to Available) and assign a new target. |
|
|
See Deleting a Connection Pool in the Online Help. |
|
|
See Deleting a JDBC MultiPool in the Online Help. |
|
|
See Deleting a Connection Pool in the Online Help. |
|
|
To monitor the connections for a single connection pool, see Monitoring Connections in a JDBC Connection Pool in the Online Help. To monitor all active connection pools for a server, see Monitoring All Active JDBC Connection Pools in the Online Help. |
|
|
Modify an Attribute for a Connection Pool, MultiPool, or DataSource |
JDBC Management Using the Command-Line Interface
The following table describes the connection pool management using the command-line interface. Select the command for more information.
For information on using the connection pool commands, see WebLogic Server Command-Line Interface Reference
JDBC Configuration Guidelines for Connection Pools, MultiPools, and DataSources
This section describes JDBC configuration guidelines for connection pools, MultiPools, and Data Sources used in local and distributed transactions.
Overview of JDBC Configuration
To set up JDBC connectivity, you configure connection pools, Data Source objects (always recommended, but optional in some cases), and MultiPools (optional) by defining attributes in the Administration Console or, for dynamic connection pools, in application code or at the command line.
There are three types of transaction scenarios:
You configure Data Source objects (DataSources and TxDataSources), connection pools, and MultiPools according to the way transactions are handled in your system. The following table summarizes how to configure these objects for use in the three transaction scenarios:
|
Data Source object |
Select Emulate Two-Phase Commit for non-XA Driver (set enable two-phase commit=true) if more than one resource is involved. See Configuring Non-XA JDBC Drivers for Distributed Transactions. |
||
|
Requires Data Source object when configuring in the Administration Console. |
|||
Note: For distributed transactions, use an XA-compliant driver, such as the WebLogic jDriver for Oracle/XA, which is the XA compliant version of the WebLogic jDriver for Oracle.
If your applications or environment meet any of the following criteria, you should use a Tx Data Source instead of a Data Source:
With an EJB architecture, it is common for multiple EJBs that are doing database work to be invoked as part of a single transaction. Without XA, the only way for this to work is if all transaction participants use the exact same database connection. WebLogic Server uses the JTS driver and a TxDataSource (with Emulate Two-Phase Commit for non-XA Driver selected) to do this behind the scenes without requiring you to explicitly pass the JDBC connection from EJB to EJB. With XA (requires an XA driver), you can use a Tx Data Source in WebLogic Server for distributed transactions with two-phase commit so that EJBs can use a different database connections for each part of the transaction. In either case (with or without XA), you should use a Tx Data Source.
Read more about Data Sources in Programming WebLogic JDBC.
Note: Do not create two Tx Data Sources that point to the same connection pool. If a transaction uses two different Tx Data Sources which are both pointed to the same connection pool, you will get an XA_PROTO error when you try to access the second connection.
Drivers Supported for Local Transactions
JDBC 2.0 drivers that support the JDBC Core 2.0 API (java.sql), including the WebLogic jDrivers for Oracle and Microsoft SQL Server. The API allows you to create the class objects necessary to establish a connection with a data source, send queries and update statements to the data source, and process the results.
Drivers Supported for Distributed Transactions Using XA
Any JDBC driver that supports JDBC 2.0 distributed transactions standard extension interfaces (javax.sql.XADataSource, javax.sql.XAConnection, javax.transaction.xa.XAResource), such as the WebLogic jDriver for Oracle/XA.
Drivers Supported for Distributed Transactions without XA
Any JDBC driver that supports JDBC 2.0 Core API but does not support JDBC 2.0 distributed transactions standard extension interfaces (non-XA). Only one non-XA JDBC driver can participate in a distributed transaction. See Configuring Non-XA JDBC Drivers for Distributed Transactions.
Avoiding Server Lockup with the Correct Number of Connections
When your applications attempt to get a connection from a connection pool in which there are no available connections, the connection pool throws an exception stating that a connection is not available in the connection pool. Connection pools do not queue requests for a connection. To avoid this error, make sure your connection pool can expand to the size required to accommodate your peak load of connection requests.
To set the maximum number of connections for a connection pool in the Administration Console, expand the navigation tree in the left pane to show the Services
Configuring JDBC Drivers for Local Transactions
To configure JDBC drivers for local transactions, set up the JDBC connection pool as follows:
For more information on WebLogic two-tier JDBC drivers, refer to the BEA documentation for the specific driver you are using: Using WebLogic jDriver for Oracle and Using WebLogic jDriver for Microsoft SQL Server. If you are using a third-party driver, refer to Using Third-Party JDBC XA Drivers with WebLogic Server in Programming WebLogic JTA and the vendor-specific documentation. The following tables show sample JDBC connection pool and Data Source configurations using the WebLogic jDrivers.
The following table shows a sample connection pool configuration using the WebLogic jDriver for Oracle.
Note: The following configuration examples use a Password attribute. The Password attribute value overrides any password defined in Properties (as a name/value pair). This attribute is passed to the 2-tier JDBC driver when creating physical database connections. The value is stored in an encrypted form in the config.xml file and can be used to avoid storing passwords in clear text in that file.
|
GeneralTab |
|
|
tiger (Displayed as ***** when typed, hidden thereafter; this value overrides any password defined in Properties as a name value pair) |
|
The following table shows a sample Data Source configuration using the WebLogic jDriver for Oracle or Microsoft SQL Server.
The following table shows a sample connection pool configuration using the WebLogic jDriver for Microsoft SQL Server.
|
GeneralTab |
|
|
user=sa |
|
|
secret (Displayed as ****** when typed, hidden thereafter; this value overrides any password defined in Properties as a name value pair) |
|
The following table shows a sample connection pool configuration using the IBM Informix JDBC Driver.
|
GeneralTab |
|
|
informix (Displayed as ****** when typed, hidden thereafter; this value overrides any password defined in Properties as a name value pair) |
|
Configuring XA JDBC Drivers for Distributed Transactions
To allow XA JDBC drivers to participate in distributed transactions, configure the JDBC connection pool as follows:
The following table shows an example of a JDBC connection pool configuration using the WebLogic jDriver for Oracle in XA mode.
|
GeneralTab |
|
|
tiger (Displayed as ***** when typed, hidden thereafter; this value overrides any password defined in Properties as a name value pair) |
|
The following table shows an example of a Tx Data Source configuration using the WebLogic jDriver for Oracle in XA mode.
You can also configure the JDBC connection pool to use a third-party vendor's driver in XA mode. In such cases, the data source properties are set via reflection on the XADataSource instance using the JavaBeans design pattern. In other words, for property abc, the XADataSource instance must support get and set methods with the names getAbc and setAbc, respectively.
The following attributes are an example of a JDBC connection pool configuration using the Oracle Thin Driver.
|
GeneralTab |
|
|
tiger (Displayed as ***** when typed, hidden thereafter; this value overrides any password defined in Properties as a name value pair) |
|
The following table shows an example of a Tx Data Source configuration using the Oracle Thin Driver.
The following table shows an example of a JDBC connection pool configuration for distributed transactions using the Pointbase JDBC driver.
Note: PointBase Server is an all-Java DBMS product included in the WebLogic Server distribution solely in support of WebLogic Server evaluation, either in the form of custom trial applications or through packaged sample applications provided with WebLogic Server. Non-evaluation development and/or production use of the Pointbase Server requires a separate license be obtained by the end user directly from PointBase.
|
GeneralTab |
|
|
public (Displayed as ****** when typed, hidden thereafter; this value overrides any password defined in Properties as a name value pair) |
|
Configure the Tx Data Source for use with a Pointbase driver as follows.
WebLogic jDriver for Oracle/XA Data Source Properties
Table 8-18 lists the data source properties supported by the WebLogic jDriver for Oracle. The JDBC 2.0 column indicates whether a specific data source property is a JDBC 2.0 standard data source property (S) or a WebLogic Server extension to JDBC (E).
The Optional column indicates whether a particular data source property is optional or not. Properties marked with Y* are mapped to the corresponding fields of the Oracle xa_open string (value of the openString property) as listed in Table 8-18. If they are not specified, their default values are taken from the openString property. If they are specified, their values should match those specified in the openString property. If the properties do not match, a SQLException is thrown when you attempt to make an XA connection.
Mandatory properties marked with N* are also mapped to the corresponding fields of the Oracle xa_open string. Specify these properties when specifying the Oracle xa_open string. If they are not specified or if they are specified but do not match, an SQLException is thrown when you attempt to make an XA connection.
Property Names marked with ** are supported but not used by WebLogic Server.
Table 8-19 lists the mapping between Oracle's xa_open string fields and data source properties.
Note: You must specify Threads=true in Oracle's xa_open string.
For a complete description of Oracle's xa_open string fields, see your Oracle documentation.
Additional XA Connection Pool Properties
When using connections from a connection pool in distributed transactions, you may need to set additional properties for the connection pool so that the connection pool handles the connection properly within WebLogic Server in the context of the transaction. You set these properties in the configuration file (config.xml) within the JDBCConnectionPool tag. By default, all additional properties are set to false. You set the properties to true to enable them.
In many cases, WebLogic Server automatically sets the proper value for these properties internally so that you do not have to set them manually.
Some DBMSs require that you start and end a transaction in the same physical database connection. In some cases, a transaction in WebLogic Server may start in one physical database connection and end in another physical database connection. To force a connection pool to reserve a physical connection and provide the same connection to an application throughout transaction processing until the transaction is complete, you set KeepXAConnTillTxComplete="true". For example:
<JDBCConnectionPool KeepXAConnTillTxComplete="true" DriverName="com.sybase.jdbc2.jdbc.SybXADataSource" CapacityIncrement="5" InitialCapacity="10" MaxCapacity="25" Name="demoXAPool" Password="{3DES}vIF8diu4H0QmdfOipd4dWA==" Properties="User=dbuser;DatabaseName=dbname;ServerName=server_name_or_IP_address;PortNumber=serverPortNumber;NetworkProtocol=Tds;resourceManagerName=Lrm_name_in_xa_config;resourceManagerType=2" />Note: This property is required to support distributed transactions with DB2 and Sybase.
Configuring Non-XA JDBC Drivers for Distributed Transactions
When configuring the JDBC connection pool to allow non-XA JDBC drivers to participate with other resources in distributed transactions, select the Emulate Two-Phase Commit for non-XA Driver attribute (EnableTwoPhaseCommit in the JDBCTxDataSource MBean) for the JDBC Tx Data Source. This parameter is ignored by resources that support the XAResource interface. Note that only one non-XA connection pool may participate in a distributed transaction.
If you are using only one non-XA driver and it is the only resource in the transaction, leave the Emulate Two-Phase Commit for non-XA Driver option unselected in the Console (accept the default EnableTwoPhaseCommit = false). In this case, WebLogic Server ignores the setting and the Transaction Manager performs a one-phase optimization.
Non-XA Driver/Multiple Resources
If you are using one non-XA JDBC driver with other XA resources, select Emulate Two-Phase Commit in the Administration Console (EnableTwoPhaseCommit = true).
When the Emulate Two-Phase Commit for non-XA Driver option is selected (EnableTwoPhaseCommit is set to true), the non-XA JDBC resource always returns XA_OK during the XAResource.prepare() method call. The resource attempts to commit or roll back its local transaction in response to subsequent XAResource.commit() or XAResource.rollback() calls. If the resource commit or rollback fails, a heuristic error results. Application data may be left in an inconsistent state as a result of a heuristic failure.
When the Emulate Two-Phase Commit for non-XA Driver option is not selected in the Console (EnableTwoPhaseCommit is set to false), the non-XA JDBC resource causes XAResource.prepare() to fail. This mechanism ensures that there is only one participant in the transaction, as commit() throws a SystemException in this case. When there is only one resource participating in a transaction, the one phase optimization bypasses XAResource.prepare(), and the transaction commits successfully in most instances.
This non-XA JDBC driver support is often referred to as the "JTS driver" because WebLogic Server uses the WebLogic JTS Driver internally to support the feature. For more information about the WebLogic JTS Driver, see "Using the WebLogic JTS Driver" in Programming WebLogic JDBC.
Limitations and Risks When Using a Non-XA Driver in Global Transactions
WebLogic Server supports the participation of non-XA JDBC resources in global transactions, but there are limitations that you must consider when designing applications to use such resources. Because a non-XA driver does not adhere to the XA/2PC contracts and only supports one-phase commit and rollback operations, WebLogic Server (through the JTS driver) has to make compromises to allow the resource to participate in a transaction controlled by the Transaction Manager.
Heuristic Completions and Data Inconsistency
When Emulate Two-Phase Commit is selected for a non-XA resource, (enableTwoPhaseCommit = true), the prepare phase of the transaction for the non-XA resource always succeeds. Therefore, the non-XA resource does not truly participate in the two-phase commit (2PC) protocol and is susceptible to failures. If a failure occurs in the non-XA resource after the prepare phase, the non-XA resource is likely to roll back the transaction while XA transaction participants will commit the transaction, resulting in a heuristic completion and data inconsistencies.
Because of the data integrity risks, the Emulate Two-Phase Commit option should only be used in applications that can tolerate heuristic conditions.
Cannot Recover Pending Transactions
Because a non-XA driver manipulates local database transactions only, there is no concept of a transaction pending state in the database with regard to an external transaction manager. When XAResource.recover() is called on the non-XA resource, it always returns an empty set of Xids (transaction IDs), even though there may be transactions that need to be committed or rolled back. Therefore, applications that use a non-XA resource in a global transaction cannot recover from a system failure and maintain data integrity.
Possible Performance Loss with Non-XA Resources in Multi-Server Configurations
Because WebLogic Server relies on the database local transaction associated with a particular JDBC connection to support non-XA resource participation in a global transaction, when the same JDBC data source is accessed by an application with a global transaction context on multiple WebLogic Server instances, the JTS driver will always route JDBC operations to the first connection established by the application in the transaction. For example, if an application starts a transaction on one server, accesses a non-XA JDBC resource, then makes a remote method invocation (RMI) call to another server and accesses a data source that uses the same underlying JDBC driver, the JTS driver recognizes that the resource has a connection associated with the transaction on another server and sets up an RMI redirection to the actual connection on the first server. All operations on the connection are made on the one connection that was established on the first server. This behavior can result in a performance loss due to the overhead associated with setting up these remote connections and making the RMI calls to the one physical connection.
When a non-XA resource (with Emulate Two-Phase Commit selected) is registered with the WebLogic Server Transaction Manager, it is registered with the name of the class that implements the XAResource interface. Since all non-XA resources with Emulate Two-Phase Commit selected use the JTS driver for the XAResource interface, all non-XA resources (with Emulate Two-Phase Commit selected) that participate in a global transaction are registered with the same name. If you use more than one non-XA resource in a global transaction, you will see naming conflicts or possible heuristic failures.
Non-XA Connection Pool and Tx Data Source Configuration Example
The following table shows configuration attributes for a sample JDBC connection pool using a non-XA JDBC driver.
|
GeneralTab |
|
|
tiger (Displayed as ***** when typed, hidden thereafter; this value overrides any password defined in Properties as a name value pair) |
|
The following table shows configuration attributes for a sample Tx Data Source using a non-XA JDBC driver.
Increasing Performance with the Prepared Statement Cache
For each connection pool that you create in WebLogic Server, you can enable prepared statement caching by setting the prepared statement cache size or XA prepared statement cache size. When you enable prepared statement caching, WebLogic Server caches a set number of prepared and callable statements used in applications and EJBs. When an application or EJB calls any of the prepared or callable statements stored in the cache, WebLogic Server reuses the statement stored in the cache. Reusing statements eliminates the need for parsing statements in the database, which reduces CPU usage on the database machine, improving performance for the current statement and leaving CPU cycles for other tasks.
Statements are cached per connection, not per connection pool. For example, if you set the prepared statement cache size to 10, WebLogic Server will store 10 prepared statements called by applications or EJBs that use that particular connection.
With the release of WebLogic Server 7.0 Service Pack 3, the prepared statement cache feature was changed so that the cache behaves differently for connection pools that use an XA (transaction aware) JDBC driver to create database connections instead of a non-XA JDBC driver. You must set the appropriate cache size attribute depending on the type of JDBC driver used to create database connections in the JDBC connection pool: PreparedStatementCacheSize for connection pools that use a non-XA JDBC driver and XAPreparedStatementCacheSize for connection pools that use an XA JDBC driver. See Non-XA Prepared Statement Cache below and XA Prepared Statement Cache for more details.
Non-XA Prepared Statement Cache
For the non-XA prepared statement cache, WebLogic Server uses a Fixed algorithm to determine which statements to store in the cache for each connection in the connection pool: WebLogic Server caches prepared and callable statements used on the connection until the prepared statement cache size is reached. When additional statements are used, they are not cached.
Note: You can use the JMX API to clear the statement cache. See the clearStatementCache() method in the Javadocs for WebLogic Classes.
This statement cache is used only for connection pools that use a non-XA JDBC driver to create database connections. If the connection pool uses an XA JDBC driver for database connections, the cache settings are ignored.
The default value for the non-XA prepared statement cache size is 5. You can use the following methods to set the prepared statement cache size for a connection pool:
To set the non-XA prepared statement cache size for a connection pool using the configuration file, before starting the server, open the config.xml file in an editor, then add an entry for the PreparedStatementCacheSize attribute in the JDBCConnectionPool tag. For example:
<JDBCConnectionPool CapacityIncrement="5"
DriverName="com.pointbase.jdbc.jdbcUniversalDriver"
InitialCapacity="5" MaxCapacity="20" Name="demoPool"
Password="{3DES}ANfMduXgaaGMeS8+CR1xoA=="
PreparedStatementCacheSize="20" Properties="user=examples"
RefreshMinutes="0" ShrinkPeriodMinutes="15"
ShrinkingEnabled="true" Targets="examplesServer"
TestConnectionsOnRelease="false"
TestConnectionsOnReserve="false"
URL="jdbc:pointbase:server://localhost/demo"/>
For the XA prepared statement cache, WebLogic Server uses a least recently used (LRU) algorithm to determine which statements to store in the cache for each connection in the connection pool: WebLogic Server caches prepared and callable statements used on the connection until the statement cache size is reached. When an application calls Connection.prepareStatement(), WebLogic Server checks to see if the statement is stored in the prepared statement cache. If so, WebLogic Server returns the cached statement (if it is not already being used). If the statement is not in the cache, and the cache is full (number of statements in the cache = XAPreparedStatementCacheSize), Weblogic Server determines which existing statement in the cache was the least recently used and replaces that statement in the cache with the new statement.
Note: You can use the JMX API to clear the statement cache. See the clearStatementCache() method in the Javadocs for WebLogic Classes.
The XA prepared statement cache is used only for connection pools that use an XA JDBC driver to create database connections. If the connection pool uses a non-XA JDBC driver for database connections, the cache settings are ignored.
The default value for the XA prepared statement cache size is 5. You can use the following methods to set the XA prepared statement cache size for a connection pool:
To set the XA prepared statement cache size for a connection pool using the configuration file, before starting the server, open the config.xml file in an editor, then add an entry for the PreparedStatementCacheSize attribute in the JDBCConnectionPool tag. For example:
<JDBCConnectionPool CapacityIncrement="5"
DriverName="com.pointbase.xa.xaDataSource"
InitialCapacity="5" MaxCapacity="20" Name="demoXAPool"
Password="{3DES}ANfMduXgaaGMeS8+CR1xoA=="
XAPreparedStatementCacheSize="20"
Properties="user=examples;
DatabaseName=jdbc:pointbase:server://localhost/demo"
RefreshMinutes="0" ShrinkPeriodMinutes="15"
ShrinkingEnabled="true" Targets="examplesServer"
TestConnectionsOnRelease="false"
TestConnectionsOnReserve="false"
URL="jdbc:pointbase:server://localhost/demo"/>
Usage Restrictions for the Prepared Statement Cache
Using the prepared statement cache can increase performance, but you must consider its limitations before you decide to use it. Please note the following restrictions when using the prepared statement cache. These restrictions apply to both the XA and non-XA prepared statement caches.
There may be other issues related to caching prepared statements that are not listed here. If you see errors in your system related to prepared statements, you should set the prepared statement cache size to 0, which turns off prepared statement caching, to test if the problem is caused by caching prepared statements.
Calling a Stored Prepared Statement After a Database Change May Cause Errors
Prepared statements stored in the cache refer to specific database objects at the time the prepared statement is cached. If you perform any DDL (data definition language) operations on database objects referenced in prepared statements stored in the cache, the statements will fail the next time you run them. For example, if you cache a statement such as select * from emp and then drop and recreate the emp table, the next time you run the cached statement, the statement will fail because the exact emp table that existed when the statement was prepared, no longer exists.
Likewise, prepared statements are bound to the data type for each column in a table in the database at the time the prepared statement is cached. If you add, delete, or rearrange columns in a table, prepared statements stored in the cache are likely to fail when run again.
Using setNull In a Prepared Statement
When using the WebLogic jDriver for Oracle to connect to the database, if you cache a prepared statement that uses a setNull bind variable, you must set the variable to the proper data type. If you use a generic data type, as in the following example, the statement will fail when it runs with a value other than null.
java.sql.Types.Long sal=null
.
.
.
if (sal == null)
setNull(2,int)//This is incorrect
else
setLong(2,sal)
if (sal == null)
setNull(2,long)//This is correct
else
setLong(2,sal)
This issue occurs consistently when using the WebLogic jDriver for Oracle. It may occur when using other JDBC drivers.
Prepared Statements in the Cache May Reserve Database Cursors
When WebLogic Server caches a prepared statement, the prepared statement may open a cursor in the database. If you cache too many statements, you may exceed the limit of open cursors for a connection. To avoid exceeding the limit of open cursors for a connection, you can change the limit in your database management system or you can reduce the prepared statement cache size for the connection pool.
Determining the Proper Prepared Statement Cache Size
To determine the optimum setting for the prepared statement cache size, you can emulate your server workload in your development environment and then run the Oracle statspack script. In the output from the script, look at the number of parses per second. As you increase the prepared statement cache size, the number of parses per second should decrease. Incrementally increase the prepared statement cache size until the number or parses per second no longer decreases.
Note: Consider the usage restrictions for the prepared statement cache before you decide to use it in your production environment. See Usage Restrictions for the Prepared Statement Cache for more information.
Using a Startup Class to Load the Non-XA Prepared Statement Cache
To make the best use of the non-XA prepared statement cache and to get the best performance, you may want to create a startup class that calls each of the prepared statements that you want to store in the prepared statement cache. WebLogic Server caches prepared statements in the order that they are used and stops caching statements when it reaches the prepared statement cache size limit. By creating a startup class that calls the prepared statements that you want to cache, you can fill the cache with statements that your applications will reuse, rather than with statements that are called only a few times, thus getting the best performance increase with the least number of cached statements. You can also avoid caching prepared statements that my be problematic, such as those described in Usage Restrictions for the Prepared Statement Cache.
Even if the startup class fails, WebLogic Server loads and caches the statements for future use.
Note that each connection has it's own cache of statements. If you use a startup class to cache statements, you must create the class in such a way that it gets each connection from the pool and calls the prepared statements that you want to cache on each statement.
If you enable the connection pool to grow as demand for connections increases, new connections will cache statements as the statements are used. The startup class cannot load the prepared statement cache for new connections. If you enable the connection pool to shrink, the connection pool will close connections after the shrink period has been met and connections are available. There is now way to specify which connections to close first. Therefore, the connections for which you loaded the prepared statement cache may close before non-loaded connections close.
Also note that when starting a server, EJBs are deployed before startup classes are run. Prepared statements in CMP entity beans and prepared statements that EJBs use during deployment will be stored in the cache before prepared statements in a startup class. To work around this, you can clear the prepared statement cache for your connection pool after all EJBs and applications are deployed, and then run cache-priming code. See the Javadoc for the clearStatementCache method in the weblogic.jdbc.extensions package.
Because the XA prepared statement cache uses a Least Recently Used algorithm to replace statements in the cache, preloading the cache with a startup class will not be effective.
|
|
|
|
||
 |
|
|
 |
 |
 |
|
||