|
|
|
|
|
|
| | | |
Managing JDBC Connectivity
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 (database connectivity with Java). For most JDBC administrative functions, which include creating, managing and monitoring connectivity, systems administrators use the Administrative Console or the command-line interface. Application developers may want to use the JDBC API.
Frequently performed tasks to set and manage connectivity include:
About the Administrative Console
Your primary way to set and manage JDBC connectivity is through the Administration Console. Using the Administration Console, you set connectivity statically prior to starting the server. For more information, see Administration Console.
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. For information on how to use the command-line interface, see WebLogic Server Command-Line Interface Reference.
About the JDBC API
For information on setting and managing connectivity programatically, see Programming WebLogic JDBC.
Related Information
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:
Administration and Management
JDBC and WebLogic jDrivers
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.
Transactions (JTA)
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 16-1 JDBC Components in WebLogic Server
Connection Pools
A Connection Pool contains named groups of JDBC connections that are created when the Connection Pool is registered, usually when starting up WebLogic Server. 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 at
All of the settings you make with the Administration Console are static; that is, all settings are made before WebLogic Server starts. You can create dynamic Connection Pools—after the server starts—using the command line (see WebLogic Server Command-Line Interface Reference,) or programatically using the API (see Creating a Dynamic Connection Pool in Programming WebLogic JDBC).
MultiPools
MultiPools aid in either:
All of the connections in a particular Connection Pool are identical; that is, they are attached to a single database. The Connection Pools within a MultiPool may, however, be associated with different DBMS. Read more about MultiPools in Programming WebLogic JDBC.
Data Sources
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.
JDBC Configuration Guidelines for Connection Pools, MultiPools and DataSources
This section describes JDBC configuration guidelines for 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 and, for dynamic connection pools, at the command line. There are three types of transactions:
The following table describes how to use these objects in local and distributed transactions:
|
Description/Object |
Local Transactions
|
Distributed Transactions XA Driver |
Distributed Transactions Non-XA Driver |
|---|---|---|---|
|
JDBC driver |
|||
|
Data Source |
Data Source object |
Tx Data Source |
Tx Data Source required. Set |
|
Connection Pool |
Requires Data Source object when configuring in the Administration Console. |
Requires TXData Source. |
Requires TXData Source. |
|
MultiPool |
Connection Pool and Data Source required. |
Requires TXData Source. |
Requires TXData Source. |
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 Enable Two-Phase Commit 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
java.sql), including the WebLogic jDrivers for Oracle, Microsoft SQL Server, and Informix. 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
javax.sql.XADataSource, javax.sql.XAConnection, javax.transaction.xa.XAResource), including the WebLogic jDriver for Oracle/XA.
Configuring JDBC Drivers for Local Transactions
To configure JDBC drivers for local transactions, set up the JDBC Connection Pool as follows:
java.sql.Driver interface.
In the Administration Console, you specify connection properties as a name=value pair with each property on its own line. In the configuration file (config.xml), connection properties are listed in a string separated by semicolons. For example:
Properties="user=SCOTT;server=DEMO"
For more information on WebLogic two-tier JDBC drivers, refer to the BEA documentation for the specific driver you are using: Installing and Using WebLogic jDriver for Oracle, Installing and Using WebLogic jDriver for Microsoft SQL Server, or Installing and Using WebLogic jDriver for Informix. 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: New Property: "Password." This 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 and can be used to avoid storing cleartext passwords in that file.
The following table shows a sample Data Source configuration using the WebLogic jDriver for Oracle.
|
Attribute Value |
|
|---|---|
|
Name |
|
|
Targets |
|
|
JNDIName |
|
|
PoolName |
|
The following table shows a sample Connection Pool configuration using the WebLogic jDriver for Microsoft SQL Server.
The following table shows a sample Data Source configuration using the WebLogic jDriver for Microsoft SQL Server.
|
Attribute Value |
|
|---|---|
|
Name |
|
|
Targets |
|
|
JNDIName |
|
|
PoolName |
|
The following table shows a sample Connection Pool configuration using the WebLogic jDriver for Informix.
The following table shows a sample Data Source configuration using the WebLogic jDriver for Informix.
|
Attribute Value |
|
|---|---|
|
Name |
|
|
Targets |
|
|
JNDIName |
|
|
PoolName |
|
Configuring XA JDBC Drivers for Distributed Transactions
To allow XA JDBC drivers to participate in distributed transactions, configure the JDBC Connection Pool as follows:
Driver Classname attribute as the name of the class supporting the javax.sql.XADataSource interface.
XADataSource as data source properties. For more information on data source properties for the WebLogic jDriver for Oracle, see WebLogic jDriver for Oracle/XA Data Source Properties. For information about data source properties for third-party drivers, see the vendor documentation.
The following attributes are an example of a JDBC Connection Pool configuration using the WebLogic jDriver for Oracle in XA mode.
The following attributes are an example of a Tx Data Source configuration using the WebLogic jDriver for Oracle in XA mode.
|
Attribute Value |
|
|---|---|
|
Name |
|
|
Targets |
|
|
JNDIName |
|
|
PoolName |
|
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.
The following attributes are an example of a Tx Data Source configuration using the Oracle Thin Driver.
|
Attribute Value |
|
|---|---|
|
Name |
|
|
Targets |
|
|
JNDIName |
|
|
PoolName |
|
Configure the JDBC Connection Pool for use with a Cloudscape driver as follows.
Configure the Tx Data Source for use with a Cloudscape driver as follows.
|
Attribute Value |
|
|---|---|
|
Name |
|
|
Targets |
|
|
JNDIName |
|
|
PoolName |
|
WebLogic jDriver for Oracle/XA Data Source Properties
Table 16-14 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 (Y) or a WebLogic Server extension to JDBC (N).
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 16-14. 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 16-15 lists the mapping between Oracle's xa_open string fields and data source properties.
|
Oracle xa_open String Field Name |
JDBC 2.0 Data Source Property |
Optional |
|---|---|---|
|
acc |
user, password |
N |
|
sqlnet |
ServerName |
|
Note also that users must specify Threads=true in Oracle's xa_open string. For 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.
KeepXAConnTillTxComplete
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_nam
e_or_IP_address;PortNumber=serverPortNumber;NetworkProtocol=Tds;r
esourceManagerName=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, specify the Enable Two-Phase Commit attribute 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 at a time may participate in a distributed transaction.
Non-XA Driver/Single Resource
If you are using only one non-XA driver and it is the only resource in the transaction, leave the Enable Two-Phase Commit option unselected in the Administration 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 Enable Two-Phase Commit in the Console (enableTwoPhaseCommit = true).
When 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 Enable Two-Phase Commit is not selected (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 Enable 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 Enable 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.
Only One Non-XA Participant
When a non-XA resource (with enableTwoPhaseCommit = true) 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 enableTwoPhaseCommit = true use the JTS driver for the XAResource interface, all non-XA resources (with enableTwoPhaseCommit = true) 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 shows configuration attributes for a sample JDBC Connection Pool using a non-XA JDBC driver.
The following table shows configuration attributes for a sample Tx Data Source using a non-XA JDBC driver.
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 16-19 for descriptions of the configuration tasks and links to the Administration Console Online Help.
In this section, we define configuration as including these processes:
Creating the JDBC Objects
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 pool or MultiPool, then 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 permanently associates that Data Source with a specific Connection Pool or MultiPool ("pool").
Assign the JDBC Objects
Once you configure the Data Source and associated Connection Pool (or MultiPool), you then assign each object to the same servers or clusters. Some common scenarios are as follows:
See Configuring JDBC Connectivity Using the Administration Console for a description of the tasks you perform.
Refer to the following table for more information on association and assignment in the configuration process.
(You can assign more than one Data Source to a pool, but there is no practical purpose for this.) You can assign these Data Source/pool combinations to more than one server or cluster, but they must be assigned in combination. For example, you can't assign a DataSource 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 command-line interface. See JDBC Configuration Tasks Using the Command-Line Interface. You can also configure dynamic Connection Pools programatically using the API (see Creating a Dynamic Connection Pool in Programming WebLogic JDBC).
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, complete the following procedure:
The following table shows the connectivity tasks, listed in typical order in which you perform them. You may change the order; just remember you must configure an object before associating or assigning it.
|
|
JDBC Component/ Task |
Description |
|---|---|---|
|
1 |
On the Configuration tabs, you set the attributes for the Connection Pool, such as Name, URL, and database Properties. |
|
|
2 |
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. |
|
|
3 |
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. |
|
|
4 |
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.) |
|
|
5 |
Configure a Tx Data Source (and Associate 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). |
|
6 |
Using the Target tab, you assign the Connection Pool to one or more Servers or Clusters. See Table 16-18 Association and Assigninment Scenarios. |
|
|
7 |
Using the Target tab, you assign the configured MultiPool to Servers or Clusters. |
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. WebLogic Server includes the following fields on the JDBC Connection Pool
Password. Use this field to set the database password. If set, this value overrides any password value defined in the Properties passed to the tier-2 JDBC Driver when creating physical database connections. The value is encrypted in the config.xml file (stored as the Password attribute in the JDBCConnectionPool tag) and is hidden on the administration console.
Open String Password. Use this field to set the password in the open string that the transaction manager in WebLogic Server uses to open a database connection. If set, this value overrides any password defined as part of the open string in the Properties field. The value is encrypted in the config.xml file (stored as the XAPassword attribute in the JDBCConnectionPool tag) and is hidden on the Administration Console. At runtime, WebLogic Server reconstructs the open string with the password you specify in this field. The open string in the Properties field should follow this format:
openString=Oracle_XA+Acc=P/userName/+SesTm=177+DB=demoPool+Thre
ads=true=Sqlnet=dvi0+logDir=.
Note that after the userName there is no password.
You can include these passwords in the Properties field on the JDBC Connection Poolconfig.xml). To avoid displaying and storing these passwords in clear text, you can enter the passwords in their respective fields.
The value for Password and Open String Password do not need to be the same. Also, if you use these fields, you should omit the respective values in the Properties field. For example, if you specify a value in the Password field, do not include password=password in the Properties field.
Note: Values that you enter in the Password and Open String Password fields override corresponding values in the Properties field. For example, if you enter tiger in the Password field and you enter password=smith in the Properties field, WebLogic Server will use tiger as the password to make connections to the database.
JDBC Configuration Tasks Using the Command-Line Interface
The following table shows what methods you use to create a dynamic Connection Pool.
|
If you want to . . . |
Then use the . . . |
|---|---|
|
Create a dynamic Connection Pool |
|
For more information, see WebLogic Server Command-Line Interface Reference, and "Creating a Dynamic Connection Pool" 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:
|
If you want to . . . |
Do this . . . in the Administration Console |
|---|---|
|
Reassign a Connection Pool to a Different Server or Cluster |
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. |
|
Reassign a MultiPool to a Different Cluster |
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. |
|
Delete a Connection Pool |
See Delete a Connection Pool in the Online Help. |
|
Delete a MultiPool |
|
|
Delete a Data Source |
|
|
Monitor a Connection Pool |
|
|
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..
Increasing Performance with the Prepared Statement Cache
For each connection pool that you create in WebLogic Server, you can specify a prepared statement cache size. When you set the prepared statement cache size, WebLogic Server stores each prepared statement used in applications and EJBs until it reaches the number of prepared statements that you specify. 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 the first 10 prepared statements called by applications or EJBs using that particular connection.
When an application or EJB calls any of the prepared statements stored in the cache, WebLogic Server reuses the statement stored in the cache. Reusing prepared 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.
The default value for prepared statement cache size is 0. You can use the following methods to set the prepared statement cache size for a connection pool:
config.xml).
To set the 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"/>
Usage Restrictions for the Prepared Statement Cache
Using the prepared statement cache can dramatically 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.
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)
Instead, use the following:
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 Prepared Statement Cache
To make the best use of the 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 in effect 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.
|
|
|
|
|
|
Copyright © 2001 BEA Systems, Inc. All rights reserved.
|