Configuring JDBC Connectivity

The Administration Console allows you to configure various JDBC resources: connection pools, multipools, and data sources. First you need to create one or more connection pools. You may want to create a multipool in order to benefit from additional load-balancing or failover capabilities. Next, set up the data sources, which provide access to the JDBC pools. These resources then can be targeted to multiple servers and/or clusters and accessed by multiple enterprise applications. After this, you can use either the Administration Console or the command-line weblogic.Admin tool to manage and monitor JDBC connectivity.

An alternative approach would be to configure an application-specific data source and connection pool, in which case the JDBC resources are isolated to a particular enterprise application. In this instance, the J2EE application fully captures the configuration of all JDBC resources that it needs, without having to rely on the proper configuration of the servers to which the application is deployed.

5.2.1 Using Third-Party JDBC Drivers

Once you have installed a third-party JDBC driver, you need to make the driver classes visible to WebLogic Server when it starts up. This means that you need to modify WebLogic startup scripts so that the system classpath includes the libraries for the JDBC driver. For instance, if you wish to use Microsoft's SQL Server 2000 JDBC Driver, you need to modify the startWeblogic script to include the library JARs:

set JDBC_LIB=f:sharedjdbclib set CLASSPATH=%JDBC_LIB%msbase.jar;%JDBC_LIB%msutil.jar; %JDBC_LIB%mssqlserver.jar;%CLASSPATH%

In this case, the JDBC driver library JARs are in the f:sharedjdbclib directory. Some JDBC drivers also require you to install native code libraries. Ensure that these libraries are included in the system PATH before you start WebLogic.

However, if you intend to use the Oracle Thin Driver 9.2.0 (ojdbc14.jar) or Sybase jConnect Driver v4.5 (jConnect.jar) or v5.5 (jconn2.jar)which are shipped with your WebLogic distributionyou don't need to adjust the system classpath. These drivers are installed in the WL_HOMEserverlib directory, and the manifest file for weblogic.jar is configured to automatically load them. WebLogic also includes a version of the Oracle driver that provides additional debugging support. This driver can be found in the WL_HOMEserverextjdbcoracle920 directory. If you want to use this particular driver, or any other updates to these libraries, you can either overwrite the library loaded from the WL_HOMEserverlib directory, or prepend its location to the classpath, just as you would for any other installed driver.

You can configure new connection pools to use the installed JDBC driver once the library classes for the JDBC driver are made available to the server.

5.2.2 Configuring a Connection Pool

A connection pool provides the database connectivity for applications deployed on WebLogic Server. You can use the Administration Console to do the following:

• Create a new connection pool
• Adjust the connection properties of an existing pool, including the connection reservation policy
• Assign the pool to servers and/or clusters
• Test, monitor, and control the state of all active JDBC pools

Let's take a closer look at how to manage connection pools on WebLogic Server.

5.2.2.1 Creating a pool

In WebLogic 8.1, the Administration Console provides assistants to help set up JDBC connection pools and data sources. These assistants simplify the configuration of the connection pools for the more popular JDBC drivers, by automatically populating crucial settings such as the driver classname, the connection URL, and driver properties.

In order to set up a connection pool, you need to navigate to the Services/JDBC/Connection Pools node from the left pane of the Administration Console. Here, you can select the "Configure a new JDBC Connection Pool" option to create a new pool. You must choose the database type (Oracle, DB2, Informix, MySQL, etc.), after which you are presented with a list of JDBC drivers available for that particular database. Choose the one that matches your installed JDBC driver. Note that most installed JDBC drivers come in two flavors: the ordinary JDBC driver, which supports local transactions, and an alternative version of the JDBC driver, whose connections can participate fully in distributed transactions. The latter drivers usually have an "XA" prefix.

Finally, you are prompted for the database name, the hostname, and the port number for the database server, as well as the login credentials (i.e., username and password) of a valid database user. WebLogic will use this supplied data to automatically construct a connection pool with the appropriate driver classname, driver URL, and driver properties. Once you've created the pool, you can create and test a connection. If you don't want to use these assistants, simply select Other as the database type. In that case, you must manually configure values for all the driver properties, including the fully qualified class name of the driver and the driver-specific JDBC URL.

You also can right-click an existing connection pool in the Administration Console and choose the Clone Connection Pool option. This creates a new pool with the same configuration, which you then can reconfigure as per your needs.

A connection pool may be targeted to multiple servers in the domain, effectively creating multiple copies of the pool. This makes the pool available to all server-side applications deployed to that server. A server-side application may access a local connection pool using either a data source configured for the JDBC pool, or the Pool and JTS wrapper drivers. An external client also can access a server-side connection pool using either a data source or the wrapper RMI Driver.

5.2.2.2 General pool configuration

The Configuration/General tab for a chosen connection pool displays the general settings for the connection pool. Most of them are listed in Table 5-1. You will already have encountered most of them while setting up the connection pool.

The properties that are defined for a connection pool are driver-specific. Typically, you use the Properties field to configure connection attributes such as the database name, database user, server name, and port number. For instance, if you must configure a JDBC pool using Microsoft's SQL Server 2000 JDBC Driver, the following entry in the domain's config.xml file illustrates the bare configuration settings for the connection pool:

Properties="DatabaseName=master;User=sa;SelectMethod=Cursor" Password="{3DES}WvzaSHwL1Dj1oMVWjtybVw==" Targets="myServer" />

If you intend to set up a pool using IBM's JDBC Driver for Informix, the following entry in the domain's config.xml file describes the settings for the connection pool:

Similarly, if you intend to set up a pool of XA connections, you must use the XA version of the JDBC driver. For instance, Microsoft's SQL Server 2000 Driver for JDBC supports distributed transactions, provided the connection pool is configured as follows:

Properties="DatabaseName=master;User=sa;SelectMethod=Cursor;ServerName=dbserver; PortNumber=1433" Password="{3DES}WvzaSHwL1Dj1oMVWjtybVw==" Targets="myServer" />

In this case, the DriverName property for the JDBC pool has been set to a class that implements the javax.sql.XADataSource interface.

5.2.2.3 Controlling the pool size

Table 5-2 lists configuration operations that control the size of the pool. These options are available in the Configuration/Connections tab of a selected connection pool.

By default, the maximum capacity for a newly created pool is 15 if the server runs in development mode, and 25 if it runs in production mode. In fact, these are also the default number of threads configured for the server's default execute queue. In general, the size of the connection pool need not be any greater than the maximum concurrency configured for your application server.

By default, the initial capacity for a connection pool is set to 1. This means that only a single physical connection is created when the pool is initialized. If your server runs in a production environment, you will pay a price for the new connections that are created while servicing client requests. Clearly, this is not an ideal situation. Instead, you should set the pool's initial capacity equal to its maximum capacity, in which case the pool will be fully populated with physical connections before any client requests are made.

A connection pool grows whenever its existing capacity is unable to satisfy new connection requests i.e., all the connections in the pool are busy when a new connection request arrives. A connection pool will never grow to more than its maximum capacity. It also can shrink when the extra capacity is no longer being used. Shrinking involves removing idle connections from the pool. By default, WebLogic tries to shrink the pool after every 900 seconds, either to its initial capacity or to the number of connections that are currently in use, whichever is the greater.

Be careful when you set the initial and maximum capacity for a connection pool, especially when it will be assigned to multiple servers in the domain. When you target a JDBC pool to multiple servers, the pool is initialized separately on each server. For instance, if you assign a connection pool to three Managed Servers, you effectively create three separate pools, one for each server. When all the servers are up and running, you will end up with three times as many physical database connections configured for the pool.

5.2.2.4 Connection reservation

The ability to control how connections within the pool are reserved is useful, particularly if you want finer control over transaction timeouts and the behavior of the pool under load. Table 5-3 lists the important configuration parameters that influence the pool's connection reservation behavior.

Let's observe how WebLogic reserves a JDBC connection from a pool, in response to a client connection request. If there is an available connection in the pool, it is simply returned to the client. If there are no connections available and the pool has not yet reached its maximum capacity, WebLogic increases the size of the pool by manufacturing the number of new connections specified in the Capacity Increment setting before returning one of these new connections to the client.

If the pool has reached its maximum capacity, WebLogic instructs the client to wait for the number of seconds specified in the Connection Reserve Timeout setting for one to be made available. No more than the number of requests to the pool specified in the Maximum Waiting for Connection setting may wait for a connection in this manner. If a connection becomes available during this wait period, it's returned to the client. Otherwise, the client's connection request fails with a PoolLimitSQLException.

You should set a sensible limit on the number of connection requests to the pool that may wait in this fashion. Your application performance could degrade if too many connection requests wait for a connection to become available. By default, any request to reserve a connection from the pool times out after 10 seconds. This reservation timeout has implications on the transactional behavior of applications that use the connection pool. If the connection from the pool is involved in a transaction and the reservation timeout expires, an exception is raised and the transaction is rolled back.

None of these pool settings affects the way in which a multipool operates. For example, if a client attempts to reserve a connection from a multipool, and the pool has reached its maximum capacity, it will simply wait for a connection to become available, just like a client of a connection pool would do. Just because a connection pool is at maximum capacity and has no available connections to service the connection request, does not mean that WebLogic will attempt to reserve a connection from the next pool in the multipool.

5.2.2.5 Connection testing

In order to maintain a healthy connection pool, you need to maintain the health of its connections. You can configure a connection pool to automatically test its connections, and re-create new ones if a test fails. Table 5-4 lists the configuration parameters.

Connection testing occurs only if you have configured the Test Table Name parameter, and either selected a Test Frequency or specified exactly when the pool's connection ought to be tested. When a test fails, WebLogic closes the physical connection, removes it from the pool, and creates a new physical connection. Be careful when you enable connection testing, as it will cause a delay. For example, if you enable the Test Reserved Connection flag for a connection pool, WebLogic executes the configured test whenever the pool receives a connection request from a client. A ConnectionDeadSQLException exception is generated if a client tries to reserve a connection and the connection test fails.

5.2.2.6 Advanced distributed transaction control

If you configure a connection pool using an XA-aware JDBC driver, WebLogic provides additional parameters under the Advanced options in the Configuration/Connections tab. All these settings default to false, though WebLogic does choose appropriate values for specific settings if you use the JDBC assistants to create your connection pools. The XA-specific parameters for a connection pool are listed in Table 5-5.

5.2.2.7 Initializing pool connections

You also can use the Init SQL setting for the connection pool to specify SQL that must be executed whenever a new connection is created. In this way, you can prime the JDBC connections in some DBMS-specific way. Any string that you supply should begin with SQL. Here is an example:

SQL SET LOCK MODE TO WAIT

5.2.2.8 Monitoring JDBC pools

The Monitoring tab lets you monitor each connection pool on a per-server basis. WebLogic captures a number of statistics on the pool, including the number of active connections, the number of clients waiting for a connection, the average length of the wait time, the current capacity, and the state of the pool (whether it is active, suspended, or unhealthy).

5.2.2.9 Managing the connection pool

The Control tab for a connection pool gives you explicit, immediate control over the connection pool. Here you can perform any of the following operations:

Shrink

Select this option to immediately shrink the connection pool.

Reset

Use this option to close and re-create all the physical connections in the selected pool. You may need to reset the pool, for instance, if your database server has crashed. Of course, you should reset the pool only after the database server has been restarted.

Clear Statement Cache

Use this option to clear the statement cache for each connection in the pool.

Suspend, Force Suspend, and Resume

Use this option to suspend a connection pool, which effectively suspends all operations on the pool's connections, until you later resume them. If you forcibly suspend a pool, the server additionally disconnects all users from the pool. Any applications that continue to use a connection from a suspended pool will get an exception.

Destroy and Force Destroy

Use this option if you choose to destroy a pool. All connections are released and the pool will no longer be available. This operation fails if any of the pool connections are in use, unless you forcibly destroy the pool, in which case all current users of the pool also are disconnected.

Note that if an application attempts to reserve a connection from a suspended connection pool, it will receive a PoolDisabledSQLException exception.

5.2.2.10 Using the physical connection

WebLogic provides you with a way to retrieve the physical connection associated with a logical connection. The weblogic.jdbc.extensions.WLConnection interface provides this access:

// ds is a reference to a DataSource object... java.sql.Connection con = ds.getConnection( ); java.sql.Connection pCon = ((WLConnection)con)).getVendorConnection( )

In general, you should not use the underlying physical connection because the approach suffers from a number of limitations:

• You lose all the benefits WebLogic's connection management features, including support for error handling, prepared statement caching, connection testing, and distributed transactions.
• Only server-side applications may use the actual physical connection obtained from the pool.
• WebLogic is unable to reuse the physical connections. By default, when you close a physical connection, it is not returned to the pool. Instead, WebLogic discards it and replaces it with a new connection in the pool. Thus, your applications suffer a performance loss because the pool's connections cannot be reused. In addition, any statement cache that may have been active for the previous connection is no longer valid for the new connection in the pool.

For the core hacker, WebLogic does allow you to alter this default behavior and ensure that the physical connection is returned to the pool when you close it. If you set the MBean attribute RemoveInfectedConnectionsEnabled for the connection pool to false, WebLogic places the physical connection back into the pool when you close it. In this case, you must ensure that the actual JDBC connection is fit for use by other clients of the connection pool. Later, we will see how this same interface provides direct access to the prepared statement cache.

5.2.3 Configuring a Data Source

Now that you have created a connection pool, you can associate a data source with it and make it available in the JNDI tree. The data source then becomes the conduit for accessing the connection pool. You can create data source objects by navigating to the Services/JDBC/Data Sources node in the left frame of the Administration Console. Choose "Configure a new Data Source" to create a new data source.

Table 5-6 lists the configuration settings for a data source.

Thus, in order to properly configure a data source, you must specify the JNDI name to which the data source will be available in the server's JNDI tree, as well as the name of the JDBC pool that will be used to supply the connections. The data source refers to a connection pool or multipool that you've already configured.

The row-prefetch options determine how an external client of the data source behaves when it invokes the ResultSet.next( ) method. This is explained later in this chapter in the Section 5.3.1."

5.2.3.1 Setting up an XA-aware data source

As described in Chapter 6, there are two types of transactions. A local transaction is one whose scope is bound by the lifetime of a single connection to a resource (e.g., a JDBC connection or a transacted JMS session). A distributed transaction is one that involves updates to multiple resources (e.g., a JDBC connection, a JMS server), coordinated using a two-phase commit protocol.

Consequently, WebLogic supports two kinds of data source objects: those that are configured to only support local transactions, and those that can participate in distributed transactions. The Honors Global Transactions flag determines whether the configured data source can participate in distributed transactions. By default, this flag is enabled, which means that by default, any data source you create is configured to participate in distributed transactions. We'll use the term XA-aware data source to refer to a data source that also can participate in distributed transactions.

If you set the Honors Global Transactions flag to false, any connection obtained from the data source can never participate in distributed transactions. You may explicitly disable support for distributed transactions in this way, if you are certain your application can perform multiple JDBC updates satisfactorily within the lifetime of a single JDBC connection. You will need to configure an XA-aware data source if your application has any of the following requirements:

• Your code uses the JTA to support multiple database updates within a single transaction. That is, your code uses client-initiated transactions or EJBs that support bean-managed transactions.
• Your application uses EJBs that support container-managed transactions (e.g., CMP entity beans).
• Your code uses multiple resources such as databases, JMS, etc., within the scope of a single transaction.

If multiple EJBs are involved in database updates, you will certainly need transactions that can span multiple connections. There are two ways to configure XA-aware data sources:

• Associate the data source with a connection pool that has been created using an XA-aware JDBC driver. This yields a first-class XADataSource that can participate fully in any distributed transaction, just like any other XA resource.
• If the connection pool has been configured using a JDBC driver that does not support XA, you can still create an XADataSource in this scenario, so long as you also enable the Emulate Two-Phase Commit for non-XA Driver flag for the data source. You can find this option in the advanced section of the Configuration tab for a data source. Obviously, you do not need to enable this flag if your connection pool is XA-aware.

There are a number of caveats associated with two-phase commit emulation, which we discuss in Chapter 6.

5.2.3.2 Using a configured data source

Now that you have configured the data source and its connection pool, you can use it to obtain a connection from the pool from within any server-side application. The following example shows how a servlet can look up the configured data source and use it to request a JDBC connection from the pool:

public void doGet(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException { DataSource ds = null; Connection con = null; try { //typically, you would obtain a reference to the data source //in the servlet's init method InitialContext ctx = new InitialContext( ); ds = (DataSource) ctx.lookup("myds"); con = ds.getConnection( ); //use the database connection to service the request } catch (NamingException ene) { //handle naming exceptions } catch (SQLException esql) { //handle SQL exceptions } finally { /* release connection and other JDBC resources here, ensure JDBC objects are properly closed, even if an exception occurs */ try { if (con != null) con.close( ); } catch(IOException eio) {} } }

For an external client, the only change is the way in which you set up the initial JNDI context:

try { Hashtable env = new Hashtable( ); env.put(Context.INITIAL_CONTEXT_FACTORY, "weblogic.jndi.WLInitialContextFactory"); env.put(Context.PROVIDER_URL, "t3://server:port"); //URL of a running WebLogic Server instance InitialContext ctx = new InitialContext(env); DataSource ds = (DataSource) ctx.lookup("mytxds"); javax.transaction.UserTransaction tx = (javax.transaction.UserTransaction) ctx.lookup("java:comp/UserTransaction"); tx.begin( ); Connection con = ds.getConnection( ); //you now have a handle that wraps the actual physical connection //make sure to release connection and related resources when you are done tx.commit( ); } catch (NamingException ene) { //handle naming exceptions } catch (SQLException esql) { //handle SQL exceptions try { tx.rollback( ); } catch (SystemException ese) { }

Notice how the JDBC connection is being used within the context of a JTA transaction. In this case, we are assuming that an XA-aware data source has been configured and bound to the server's JNDI tree under the name mytxds.

5.2.3.3 Targeting the data source

In order to deploy a data source to the server, you must target both the data source and its associated JDBC pool to the server. The connection pool is then accessible to all server-side applications, as well as to any clients of the server. In fact, you can target both the data source and the connection pool to one or more Managed Servers. To do this, you must choose the data source (or the connection pool) from the left pane of the Administration Console, and then select the Targets and Deploy tab to assign the JDBC resource to the selected Managed Servers (or cluster).

Remember, a client can access a data source only if both the data source and the JDBC pool are assigned to the server as a combination. You must not target a data source to a Managed Server without also targeting its JDBC pool to the same server. In a WebLogic cluster, you can configure the data source in two ways:

• Assign the data source and connection pool uniformly to the cluster by targeting them both to every Managed Server belonging to the cluster.
• Target the data source and the connection pool to the cluster. In this case, the data source is now "cluster-aware." When a client requests a connection from the cluster-aware data source, the request can be redirected to any one of the Managed Servers in the cluster.

Because a connection pool isn't cluster-aware, targeting a JDBC pool to a cluster merely assigns it to all the members of the cluster. It is a convenience feature only. You will create as many instances of the connection pool as there are members in the cluster. Thus, you can target the data source and connection pool to different servers or clusters, but they must be applied to each server as a combination. Later, we will examine the implications of using data sources and JDBC pools in a clustered environment.

5.2.4 Application-Scoped Data Sources and Connection Pools

Connection pools and data sources generally are treated as managed objects. You must explicitly configure the server with the necessary data sources and connection pools needed by all applications deployed on that server. In this case, the deployed applications rely on proper configuration of the servers to which they are deployed, for any JDBC resources they may require. Alternatively, you can create data sources and connection pools that are specific to an enterprise application. The weblogic-application.xml descriptor file for an enterprise application lets you configure application-scoped pools and data sources that are, in effect, created when the application is deployed. The advantage of this approach is that the enterprise application carries the configuration of all JDBC resources that it needs. A similar mechanism also is available for XML parser factories, which we explore in Chapter 18.

WebLogic eases the configuration of application-scoped JDBC resources by allowing you to also refer to a data source factory created in your WebLogic domain. A data source factory stores the default values for any application-scoped connection pools. These defaults can be overridden in the weblogic-application.xml deployment descriptor when you define the application-scoped pool.

Remember, WebLogic creates an instance of the application-scoped pool whenever it creates an instance of the enterprise application. Thus, WebLogic creates an instance of the pool on all servers to which the owning application is deployed.

5.2.4.1 Creating a data source factory

To create a data source factory, choose the Services/JDBC/Data Source Factories node from the left pane of the Administration Console and select the "Create a new Data Source Factory" option. You will be asked to supply a name for the factory, together with the configuration details for the connection poolnamely, the JDBC URL, the driver classname, driver properties, and the username and password that should be used to create the connections. Note, however, that a data source factory doesn't actually create a connection pool it merely provides the default values for any application-scoped pool that chooses to reference this factory.

5.2.4.2 Editing the deployment descriptor

Application-scoped data sources and connection pools can be defined using only the weblogic-application.xml descriptor file for an enterprise application. The easiest way to edit the descriptor file is to use WebLogic Builder. The entire configuration occurs under the jdbc-connection-pool element. The following portion from the weblogic-application.xml descriptor file shows how to configure an application-scoped data source and pool:

myDS myds_factory sa jdbc:microsoft:sqlserver://10.0.10.10:1443 com.microsoft.jdbc.sqlserver.SQLServerDriver databaseName DYNOLIFE password pssst

As you can see, the descriptor file simply holds an XML description of a connection pool and its runtime behavior. In this case, the optional factory-name element refers to a data source factory already configured in your WebLogic domain. Once you reference a data source factory from within an application-scoped pool, you need to set only those connection properties not covered already by the data source factory, since the pool automatically inherits the defaults defined in the data source factory. Alternatively, an application-scoped pool can override any of the default values inherited from the factory. If an application-scoped pool does not reference a data source factory, you must explicitly set all of the required connection properties in the descriptor file. Otherwise, you will get a configuration error when you later deploy the enterprise application.

5.2.4.3 Using an application-scoped data source

The data-source-name subelement defines the name of an application-scoped data source that will be associated with the pool. Once an application-scoped data source has been targeted to a server, it can then use this name to look up the data source from the local Environment Naming Context (ENC), available under java:comp/env. In the previous example, any component within the enterprise application can access the application-scoped data source as follows:

DataSource ds = (DataSource) ctx.lookup("java:comp/env/myDS"); java.sql.Connection con = ds.getConnection( ); //...

The Administration Console allows you to access the configuration of application-scoped JDBC resources within a deployed application EAR. If you select the application EAR from the under the Deployments/Applications node, the Administration Console lists all the modules that are contained in the EAR, including any EJBs or WARs. In this case, you also can view the configuration of the application-scoped data source and pool. If you click the application-scoped data source, you get direct access to the configuration of the connection pool associated with the application-scoped data source. This includes the same properties and operations that are available for any traditional, server-specific connection pool. For example, you can monitor the connection pool and target the data source to one or more servers (or clusters). Be sure to target the data source to the same servers that will run code that accesses the data source.

5.2.4.4 Encrypting the passwords

When you define an application-scoped connection pool in the weblogic-application.xml descriptor file, you will notice that the login credentials of the database user appear in clear text:

password pssst

This is a potential security risk because the credentials of the database user associated with the connection pool are now in full view of anyone who has access to the descriptor file. WebLogic provides a utility that you can use to encrypt the database password. The utility simply searches for all database passwords and replaces them with their encrypted versions. To run the utility, enter the following command:

java weblogic.j2ee.PasswordEncrypt descriptorFile domainDir

Here, the descriptorFile argument refers to the location of the weblogic-application.xml descriptor file, and the domainDir argument refers to the location of the root directory of your domain. After running the utility, the descriptor file portions that contain the database passwords will change into something like this:

password {3DES}WvzaSHwL1Dj1oMVWjtybVw==

If you want to change the database password, you need to simply change it in the weblogic-application.xml descriptor and rerun the utility. You must rerun the utility if you choose to move the application to a different installation of WebLogic Server, or if you deploy the application under a domain directory different from the one that was referenced when you ran the utility. Even if you delete the domain directory and create another one with the same name, you still will have to rerun the utility.

5.2.5 Managing the Statement Cache

WebLogic can be configured to maintain a statement cache for each connection in a connection pool. Whenever you create a prepared statement (or callable statement) using a connection obtained from the pool, WebLogic caches the compiled statement so that it can be reused later. Statement caching occurs transparently, without affecting any clients of the pool. In this way, WebLogic can avoid recompiling the prepared statement the next time it is used. This improves the performance of most JDBC applications.

Thus, when an application creates a prepared or callable statement on a connection obtained from the pool, WebLogic attempts to use cached copy of the statement, if it exists. Otherwise, WebLogic stores the new, compiled statement in the cache so that other clients of the pool can reuse it. WebLogic uses the precise SQL command and the result set type and concurrency options (if any) as the key to later retrieve the statement from the cache. If the statement cache is full, WebLogic evicts the least-recently used statement from the cache before introducing the new statement. WebLogic also supports an immutable statement cache per connection, which remains unchanged as soon as the statement cache is full, and until the physical connection is closed or the statement cache is cleared.

5.2.5.1 Enabling statement caching

By default, statement caching is enabled for any connection pool. In order to configure the statement cache for the pool, select the connection pool from the left frame of the Administration Console, and then navigate to the Configuration/Connections tab in the right frame. Here, you can adjust the following configuration settings for the statement cache:

Statement Cache Size

This parameter determines the number of prepared or callable statements that are cached for each connection in the pool. By default, WebLogic caches 10 statements per pool connection. You can disable statement caching simply by setting the cache size to 0.

Statement Cache Type[1]

Use this parameter to configure the eviction policy for the connection pool. By default, WebLogic supports an LRU cache, which means that it replaces the least-recently used statement in the cache with the new statement when the cache is full. Generally, this option offers optimum JDBC performance. Alternatively, you can set the cache type to Fixed, in which case the prepared or callable statements are cached only until the cache becomes full. Once the cache is full, its contents no longer will change, until the physical connection is closed or the statement cache is cleared.

Remember, the statement cache is available to all clients of the pool. WebLogic caches the prepared or callable statements regardless of who created the statement. You should experiment with the cache size to find the optimal setting for your application.

5.2.5.2 Clearing the statement cache

The WLConnection interface also lets you remove prepared or callable statements from the cache. When a prepared or callable statement is inserted into the cache, it is indexed by the precise SQL that was used and the result set type and concurrency options (if any). Given this information, you then can use either of these methods to remove a statement from the cache:

boolean WLConnection.clearCallableStatement(String sql, int rsType, int rsConcurrency); boolean WLConnection.clearPreparedStatement(String sql, int rsType, int rsConcurrency);

Here is an example:

boolean succeeded = ((WLConnection) con).clearPreparedStatement("SELECT * FROM foo WHERE goo > ?", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

For a statement that doesn't return a result set, you need to supply only the SQL that was used to create the statement:

boolean WLConnection.clearCallableStatement(String sql); boolean WLConnection.clearPreparedStatement(String sql);

Each clearXXXStatement( ) method returns false if the statement could not be found within the cache. You also can clear the entire statement cache associated with the connection:

if (((WLConnection) con).clearStatementCache( )) { // successfully cleared the statement cache for the connection }

5.2.5.3 Limitations of statement caching

By default, WebLogic maintains a cache of 10 statements for each connection in the pool. Even though a statement cache can dramatically improve the performance of your applications, there are certain restrictions you should be aware of when caching prepared or callable statements:

• When using the setNull( ) method on a PreparedStatement object, make sure that you use the correct datatype for the parameter. For example, if the first parameter expects a value of type VARCHAR, you should use ps.setNull(1, java.sql.Types.VARCHAR) to set a null value.
• Beware of caching prepared statements that refer to database objects whose structure subsequently may be modified. If you perform a data definition language (DDL) operation on a database object that is referenced in a cached prepared statement, the prepared statement will fail the next time it is executed. For instance, if you already have cached a statement that refers to an SQL table, and then subsequently drop and re-create the table, the cached statement no longer will remain valid. A prepared statement is bound to database objects that were in existence when the statement was added to the cache, and not when it is executed.
• Similarly, a prepared statement is bound to the datatypes of columns that were in use when the statement was cached. If you subsequently add or delete columns, or modify any of the columns involved, the cached statement most likely will fail the next time it is used.
• Caching a prepared statement may require the DBMS to reserve a dedicated open cursor for the statement. Open cursors are finite resources, and most databases impose a certain limit on the number of open cursors. If you cache too many prepared statements, you may run out of open cursors. In that case, you must either increase the number of allowed cursors or reduce the size of the statement cache.

If you do encounter unexpected JDBC problems when using prepared or callable statements, try disabling the cache. Then trace the actual problem and determine whether the cache is the cause of the problem. Statement caching can be disabled simply by setting the cache's size to 0.

5.2.6 Validating Database Connections

WebLogic Server is equipped with utilities that allow you to test JDBC connections using installed two-tier and wrapper JDBC drivers. The dbping utility lets you test JDBC connections when you're using two-tier JDBC drivers shipped with WebLogic. In order to invoke the dbping utility, you need to adjust the environment so that the weblogic.jar and JDBC driver classes are included in the system classpath. The syntax for the dbping utility is:

java utils.dbping DBMS user password DB

Here, the value for the DBMS argument can be one of the following constants: ORACLE, ORACLE_THIN, JCONNECT, JCONN2, INFORMIX4, or MSSQLSERVER4. Thus, to test whether you can use WebLogic's jDriver to connect to your Oracle installation, you can run the following command:

java utils.dbping ORACLE joebloggs secretpassword mydb

To test whether you can use WebLogic's jDriver to connect to an MS SQL Server database, run the following command:

java utils.dbping MSSQLSERVER4 sa secretpassword mydb@localhost:1433

The t3dbping utility lets you test JDBC connections when you're using a third-party JDBC driver. The t3dbping utility uses the RMI Driver to access the underlying JDBC driver:

java utils.t3dbping weblogicURL username password DB driverClass driverURL

The command requires a T3 URL to connect to a WebLogic instance (e.g., t3://server:port), the username and password of a valid database user, the name of the database, and the fully qualified class name and URL for the JDBC driver. Here's how you can ping an SQL Server database using Microsoft's SQL Server 2000 JDBC Driver:

java utils.t3dbping t3://localhost:7001 sa somepassword myDB@localhost:1433 com.microsoft.jdbc.sqlserver.SQLServerDriver jdbc:microsoft:sqlserver

To ping an Oracle database using WebLogic's jDriver for Oracle, you would run the following command:

java utils.t3dbping t3://localhost:7001 joebloggs somepassword myDB weblogic.jdbc.oci.Driver jdbc:weblogic:oracle

In this way, you can check whether the third-party JDBC drivers have been installed properly on the server.