Configure the BEA WebLogic 8.1 jdbc Connection

This article describes the various JDBC-related features in the BEA WebLogic 8.1 server configuration. Although we use Oracle 8.1.7 as our sample database management system (DBMS), it is easy to transfer generic concepts to these databases as long as other relational databases provide connectivity through JDBC.

JDBC is a standard Java API, and almost all of the EE applications that need access to the database use it directly or indirectly. In Bea WebLogic 8.1, configuring a JDBC connection includes creating and configuring two major artifact--jdbc connection pools and data sources. Related to these two major artifacts are other minor configurable artifacts, such as multiple pools or data source factories.

Connection pool or multiple pools?

Before delving into how to create and configure a connection pool, we need to take a brief look at the reasons why we need to use connection pooling.

If you do not use the JDBC Connection pool to access the database, then in order to establish a connection to the database, you will need the following steps:

Load the JDBC driver class.

Create an instance of the JDBC driver and register it in the driver manager.

Obtain a connection to the database from the driver Manager by providing a database URL and other required parameters.

Close the connection when finished.

The connection pool is an existing collection of database connections and can be reclaimed in multiple active Java applications, compared to this one-time connection method. Connections in the connection pool are open and can be used for access. After the application uses a connection, the connection is not destroyed, but is returned to the connection pool, which can then be used by other applications. Connection pooling improves the performance of database access, especially during peak load times, by saving computing resources associated with the frequent establishment and disconnection of database connections.

Dochi is a collection of connection pools. The primary purpose of multiple pools is to increase availability and load balancing across a set of connection pools.

Why use a data source?

A data source is a Java naming and directory interface (Java naming and directory Interface,jndi) object that provides access to connection pooling or multiple pools. Connection pooling or multiple pools are required for configuring a data source. You can use transaction properties to configure a data source. Non-transactional data sources are used for local transactions, while transactional data sources are used for distributed transactions.

The data source encapsulates access to the database, hiding the details involved in accessing the connection pool or multiple pools. In addition, data sources make it easy to configure the transactional nature of database connections.

The JDBC Data Source Factory is a Jndi object that enterprise applications use to obtain connections from the application-wide connection pool.

JDBC Driver settings

The JDBC drivers needed to establish a connection between the WebLogic server and the DBMS fall into two types: type 2 and Type 4. The Type 2 JDBC driver requires a local library. When using the Type 2 JDBC driver, you need to <weblogic>\server\bin\oci817_8 the path to the WEBLOGIC shared library (or dynamic link library) directory and to the path to the ORACLE client library directory <oracle >\bin added to the beginning of the system variable < PATH >.

The WebLogic 8.1 server is installed in the < WebLogic > directory. The Oracle 8.1.7 database is installed in the < Oracle > directory. < path > is the weblogic Server path variable.

For type 2 and type 4 drivers, in WebLogic Server classpath, the relevant JDBC driver classes are required to configure the connection pool or data source. Add the <oracle>\jdbc\lib\classes12.zip file containing the ORACLE type 2 JDBC driver class to the < in the <domain>/startweblogic script file Classpath> variable.

<classpath>:weblogic Server CLASSPATH variable.

<domain>:weblogic Server domain directory.

Configuring the JDBC Connection pool

Now, we'll consider how to create a connection pool and configure the various parameters associated with it.

To create a JDBC connection pool, right-click the Jdbc>connection pools node in the administration console and select Configure a new JDBC Connection pool.

This will display a configure a JDBC Connection pool screen. Select Database type–oracle for the Oracle database. Select a database Driver, and then click the Continue button (see Figure 1).

Figure 1

The Define Connection Properties screen is then displayed. In this screen, specify a database Name, a database User name, and a password. Click the Continue button.

The test DataBase Connection is then displayed. In this screen, specify the values for the driver Classname, URL, and properties fields. Driver ClassName is the name of the driver class used to obtain a database connection. A URL is a database URL that is used to obtain a connection. Properties is a list of attributes used to create a connection.

The driver classname and URL settings for different Oracle JDBC drivers are specified in table 1.

Table 1 driver class name and URL settings

,< host > is the host value specified in the <oracle>/network/admin/tnsnames.ora file in the URL setting,<port> is the port value specified in the Tnsnames.ora file, and <database> is the database instance name.

The settings in the Properties text box are:

User=<user>

Server=<database>

<user> Specifies the user name to log in to the Oracle database. <database> is the Oracle database instance name. If you use the Oracle Thin (Type 4) driver, you should not specify server properties.

Click the test Driver Configuration button to test the JDBC Connection Pool. If the driver configuration test succeeds, a "Connection successful" message is displayed. If there is an error in the driver configuration, an error message is displayed.

The Create and deploy screen is then displayed. In this screen, select a server to deploy the connection pool, and then click the Create and Deploy button to create and deploy a JDBC connection pool. This will create and deploy a connection pool on the selected server. You can also deploy it on multiple servers. The Jdbc>connection pools node in the administration console will add a <jdbcconnection pool> node. <jdbcconnection Pool> is the name of the connection pool.

To modify the JDBC Connection Pool configuration, click Jdbc>connection pools><jdbcconnection pool> node, and then select the Configuration tab. To modify the target server, select the Target and Deploy tab. Select a target server and click the Apply button. Select the Connections tab to configure the JDBC Connections property.

Connection Properties

Initial Capacity: The number of database connections created when the connection pool was created.

Maximum Capacity: Maximum number of connections in the connection pool.

Capacity Increment: The increment of the connection pool capacity within the maximum capacity limit.

Logindelay: The number of seconds to delay before creating each physical database connection.

Allow shrinking: When this item is set to True, the connection pool is allowed to reduce capacity to initialcapacity if no additional connections are used.

Shrink Frequency: The number of seconds to wait before reducing the connection pool capacity. If you set the shrink frequency to true, you must also set the Allow shrinking to true.

Test Frequency: The number of seconds between database connection tests. After each refresh period interval, if Testtablename is set, the testtablename is used to test unused database connections.

Test Reserved connections: If this option is selected, the server tests the connection before it is provided to the client.

Test Created connections: If this option is selected, the JDBC connection is tested after you create a JDBC connection and before you add it to the list of available connections in the JDBC Connection pool.

Test released Connections: If this option is selected, the server tests the connection before returning it to the connection pool.

Test table name: The name of the database table used for the JDBC Connection test. The table name is required if test Frequency is specified and the test Reserved connections, test Created connections, or test released connections are selected.

After configuring the Connections screen, select the Apply button.

Configuring JDBC Multiple pools

A multiple pool is a collection of connection pools. Configure connection pooling for multiple pools before creating multiple pools. To create and configure a new multiple pool, right-click the Jdbc>multi pools node in the administration console and select Configure a new Multi pool.

The configuration screen is then displayed. In the Multi Pool configuration screen, specify the algorithm type and select the Create button. If you set the algorithm type to high availability, all of the available connections in a pool are exhausted before using a connection in another pool. If you set the algorithm type to Load balancing, multiple pools distribute connection requests evenly across all connection pools in multiple pools (see Figure 2).

Figure 2

Select the Pools tab, and then in the Pools screen, select the connection pool you want to add to the multiple pool. Click the Apply button. The selected connection pool will be added to the multiple pools. The connection pool used in the connection is selected based on the algorithm type.

Select the target and Deploy tab, and then select a target server in the target and deploy screen. Click the Apply button. You can deploy a multiple pool on multiple servers. The Jdbc>multi pools node in the administration console will add a <multi pool> node. <multi Pool> is the name of a multiple pool.

Configuring the JDBC Data source

A data source is a Jndi object that is used to access a connection pool or multiple pools. Connection pooling or multiple pools are required to create a data source. Connection pooling is required for creating transactional data sources. You should create a connection pool or multiple pools before you create the data source.

To create and configure a data source, right-click the Jdbc>data sources node in the Administration console and select Configure a new Jdbctxdata source.

The configuration screen is then displayed. Specify the settings for the field in the Data Source configuration screen. The Jndi name is the Jndi path to the data source.

Select the honor Global transactions to create a transactional (Tx) data source. If the honor Global transactions is not selected, the data source that is created is a non-TX data source (see Figure 3).

Figure 3

The Connect to Connection pool screen will then be displayed. In the Pool Name field, select a JDBC connection pool from the list of available connection pools, and then click the Continue button. The data source is connected to the selected connection pool.

The target's Data source screen is then displayed. In this screen, select a target server and click the Continue button. This will deploy the data source on the selected server, but it can also be deployed on multiple servers. The Jdbc>data sources node in the administration console will add a <jdbcdata source> node. <jdbcdata Source> is the name of the data source. To modify the data source configuration, click the Jdbc>data sources><jdbcdata source> node in the admin console and select the Configuration tab. To enable multiple rows of each result set to be fetched from the server to an external client in a single server visit, select Row Prefetch enabled and specify row Prefetch Size. To enable global transactions for a non-XA JDBC driver, select emulate two-phase Commit for non-XA drivers and select Honor global transactions. To modify the target server for the data source, select the Target and Deploy tab. Select a target server and click the Apply button.

Comparison of data sources and TX data sources

If the honor Global transactions is selected in the data source configuration, the data source is the TX data source. Data sources are used with connection pooling or multiple pools. The TX data source is used with connection pooling. The data source is used for local transactions. The TX data source is used for distributed transactions.

You should use the TX data source instead of the data source in the following situations:

Use a container-managed persistence entity Bean.

Access multiple resources during a transaction.

Multiple database updates are made in a transaction.

Use the same connection pool on multiple servers.

Configuring the JDBC Data source factory

The JDBC Data source Factory is a Jndi data source resource. The enterprise application uses the data source factory to obtain connections from the application-wide connection pool.

To create and configure a JDBC data source factory, right-click the Jdbc>data Source factories node in the administration console and select Configure a new Jdbcdatasourcefactory.

The configuration screen is then displayed. In this screen, specify the value of the URL, Driver Class name, Factory name, and properties field, and then click the Create button (see Figure 4).

Figure 4

The Jdbc>data source factories node in the administration console adds a <jdbcdata source factory> node. <jdbcdata Source Factory> is the name of the data source factory. To use the <jdbcdata Source factory>, you need to reboot the server.

JDBC Configuration and server performance

The following is a list of the recommendations for Bea's various JDBC configuration settings for WebLogic server.

To improve the performance of the server in production mode:

In the JDBC Connection pool configuration, set the value of initialcapacity to equal to maxcapacity. If the initialcapacity value is less than maxcapacity, the server creates an additional database connection when the load increases. In the case of increased load, resources must be allocated to create additional database connections, and resources are required to complete the JDBC client request.

Set the value of maxcapacity equal to the number of concurrent client sessions that require a JDBC connection: To determine the concurrent client session in a connection pool, select the Connection pool node. Select the Monitoring tab. The value in the Active connections column is the average number of concurrent client sessions. The value of connections high is the maximum number of concurrent client sessions. After you deploy the application and monitor the active connection, you can modify the value of the maxcapacity.

When an external client accesses a database using JDBC through WebLogic server, select Row Prefetch Enabled in the data source configuration: Row prefetching can improve performance by fetching multiple rows in a single server access.

Conclusion

As outlined above, the WebLogic developer should be able to configure the BEA WebLogic 8.1 server for the Oracle 8.1 database. Configuring other databases is similar to configuring Oracle 8.1. Add the Zip/jar file of the driver class containing the corresponding database to the CLASSPATH variable, and then specify the connection URL and driver class name in the JDBC Connection pool configuration.