Configure BEA WebLogic 8.1 JDBC connection

Configure BEA WebLogic 8.1 JDBC connection
-- WebLogic 8.1 JDBC configuration used by Oracle 8.1.7

This article describes the configuration of JDBC-related functions in BEA WebLogic 8.1 server. Although we use ORACLE 8.1.7 as our example Database Management System (DBMS), as long as other relational databases provide JDBC connections, it is easy to move general concepts to these databases.

JDBC is a standard Java API, Which is used directly or indirectly by almost all J2EE applications that need to access the database. In BEA WebLogic 8.1, configuring JDBC connection includes creating and configuring two main artifacts: JDBC connection pool and data source. The two major artifacts are related to other secondary configurable artifacts, such as multi-pool or data source factories.

Connection pool or multi-pool?
Before discussing how to create and configure a connection pool, we need to take a brief look at the reasons for using the connection pool.
If you do not use the JDBC connection pool to access the database, perform the following steps to establish a connection to the database:

• 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 after completion.

Compared with this method, the connection pool is an existing set of database connections, and these database connections can be recycled in multiple active J2EE applications. Connections in the connection pool are open and can be accessed. After an application uses a connection, the connection is not destroyed, but returned to the connection pool, which can then be used by other applications. By saving computing resources related to frequent establishment and disconnection of database connections, the connection pool improves database access performance, especially during load peaks.
Multiple pools are the set of connection pools. The main purpose of multiple pools is to improve availability and achieve load balancing between a set of connection pools.

Why use a data source?
A data source is a Java Naming and Directory Interface (JNDI) object that provides access to the connection pool or multiple pools. A connection pool or multiple pools are required to configure the data source. You can use transaction properties to configure the 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, the data source makes it easy to configure the transactional features of database connections.
JDBC data source factory is a JNDI object, which is used by enterprise applications to obtain connections from the connection pool within the application scope.

JDBC driver settings
The JDBC driver required to establish a connection between the WebLogic Server and DBMS is divided into two types: Type 2 and type 4. Type 2 JDBC driver requires a local library. When using the Type 2 JDBC driver, you need to go to the Weblogic shared library (or Dynamic Link Library) directory path <WebLogic>/Server/bin/oci817_8 and path <Oracle>/bin added to the beginning of the system variable <path>.
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 the Type 2 and type 4 drivers, in WebLogic Server classpath, The JDBC Driver Class is 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 <classpath> variable in the <domain>/startweblogic script file.

• <Classpath>: WebLogic Server classpath variable.
• <Domain>: WebLogic Server domain directory.

Configure the JDBC connection pool
Now, we will consider how to create a connection pool and configure various parameters related to 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 click the continue button (see figure 1 ).

Figure 1

The define Connection Properties screen is displayed. In this screen, specify a database name, a database user name, and a password. Click "continue.
The test database connection is displayed. In this screen, specify the driver classname, URL, and properties fields. Driver classname is the name of the driver class used to obtain the database connection. The URL is used to obtain the URL of the connected database. Properties is the list of properties used to create a connection.
Table 1 specifies driver classname and URL settings for different oracle JDBC drivers.

Table 1 Driver Class Name and URL settings

In URL settings, The setting in the properties text box is:
User = <user>
Server = <database>
<User> specifies the username used to log on to the Oracle database. <Database> is the name of an Oracle database instance. If the Oracle thin (Type 4) driver is used, server attributes should not be specified.
Click the test driver configuration button to test the JDBC connection pool. If the driver configuration test is successful, a message "connection successful" is displayed. If the driver configuration is incorrect, an error message is displayed.
The create and deploy screen is displayed. In this screen, select a server to deploy the connection pool, and then click Create and deploy to create and deploy a JDBC connection pool. This creates and deploys a connection pool on the selected server. You can also deploy it on multiple servers. The JDBC> connection pools node in the Administration Console adds a <jdbcconnection pool> node. <Jdbcconnection pool> is the name of the connection pool.
To modify the JDBC connection pool configuration, click the JDBC> connection pools> <jdbcconnection pool> node and select the configuration tab. To modify the target server, select the target and deploy tab. Select a target server and click Apply. Select the Connections tab to configure the JDBC connections attribute.

Connection Property

• Initial Capacity: number of database connections created when the connection pool is created.
• Maximum capacity: the 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 before each physical database connection is created.
• Allow shrinking: When this option is set to true, if no additional connection is used, the connection pool is allowed to reduce the capacity to initialcapacity.
• Shrink frequency: the number of seconds to wait before reducing the connection pool capacity. If you set shrink frequency to true, you must also set allow shrinking to true.
• Test frequency: the number of seconds between database connection tests. After each refresh period, if testtablename is set, testtablename is used to test the unused database connection.
• Test reserved connections: If this option is selected, the server will test the connection before providing it to the client.
• Test created connections: If this option is selected, the JDBC connection is tested after a JDBC connection is created and before it is added to the list of available connections in the JDBC connection pool.
• Test released connections: If this option is selected, the server will test the connection before returning it to the connection pool.
• Test Table Name: name of the database table used for the JDBC connection test. If test frequency is specified and test reserved connections, test created connections, or test released connections is selected, table name is required.

After configuring the connections screen, select the Apply button.

Configure JDBC multi-pool
A multi-pool is a collection of connection pools. Configure the connection pool for multiple pools before creating multiple pools. To create and configure a new multi-pool, right-click the JDBC> multi pools node in the administration console, and select configure a New Multi pool.
The configuration screen is displayed. In the multi pool configuration screen, specify the algorithm type and select the create button. If you set algorithm type to "High Availability", all available connections in one pool should be used up before using connections in another pool. If you set algorithm type to "Load Balancing", multiple pools evenly distribute connection requests to all connection pools in multiple pools (see figure 2 ).

Figure 2

Select the pools tab, and then select the connection pool to be added to multiple pools in the pools screen. Click Apply. The selected connection pool will be added to multiple pools. The connection pool used in the connection pool is selected based on algorithm type.
Select the target and deploy tab, and then select a target server from the target and deploy screens. Click Apply. You can deploy multiple pools on multiple servers. A <multi pool> node is added to the JDBC> multi pools node in the administration console. <Multi pool> is the name of a Multi-pool.

Configure the JDBC Data Source
A data source is a JNDI object used to access a connection pool or multiple pools. A connection pool or multiple pools are required to create a data source. The connection pool is required to create a transactional data source. A connection pool or multiple pools should be created before the data source is created.
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 displayed. Specify the field settings on the data source configuration screen. The JNDI name is the JNDI path of the data source.
Select honor global transactions to create a transactional (TX) data source. If honor global transactions is not selected, the created data source is a non-TX data source (see figure 3 ).

Figure 3

The connect to connection pool screen is displayed. In the pool Name field, select a JDBC connection pool from the list of available connection pools, and click the continue button. The data source is connected to the selected connection pool.
The target data source screen is displayed. In this screen, select a target server and click "continue. 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 adds 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 management console and select the configuration tab. To retrieve multiple rows of each result set from the server to an external client during a server access, select row prefetch enabled and specify row prefetch size. To enable global transactions for non-Xa JDBC drivers, select emulate two-phase commit for non-Xa drivers and select honor global transactions. To modify the target server of the data source, select the target and deploy tab. Select a target server and click Apply.

Comparison between data sources and TX data sources
If honor global transactions is selected in the data source configuration, the data source is the Tx data source. The data source is used with the connection pool or multiple pools. The TX data source and connection pool are used together. The data source is used for local transactions. The TX data source is used for distributed transactions.
In the following cases, you should use the Tx data source instead of the Data source:

• Use the persistent Entity Bean hosted by the container.
• Access Multiple resources during a transaction.
• Update multiple databases in a transaction.
• Use the same connection pool on multiple servers.

Configure the JDBC data source factory
The JDBC data source factory is a JNDI data source resource. Enterprise applications use the data source factory to obtain connections from application-wide connection pools.
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 jdbcperformancefactory.
The configuration screen is displayed. In this screen, specify the values of the URL, Driver Class Name, factory name, and properties fields, and then click the create button (see figure 4 ).

Figure 4

A <jdbcdata source factory> node is added to the JDBC> data source factories node in the administration console. <Jdbcdata source factory> is the name of the data source factory. To use <jdbcdata source factory>, restart the server.

JDBC configuration and server performance
The following lists Bea's suggestions for various JDBC configuration settings for WebLogic Server.
To improve the server performance in production mode:

• In the JDBC connection pool configuration, set initialcapacity to maxcapacity. If initialcapacity is less than maxcapacity, the server creates additional database connections when the load increases. When the load increases, resources must be allocated to create additional database connections, and resources are required to complete JDBC client requests.
• Set maxcapacity to the number of concurrent client sessions that require JDBC connection: to determine the number of concurrent client sessions 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 connections high value is the maximum number of concurrent client sessions. After deploying the application and monitoring the active connection, you can modify the value of maxcapacity.
• When the external client accesses the database through WebLogic Server using JDBC, select row prefetch enabled in the data source configuration: by retrieving multiple rows in one server access, row prefetch can improve performance.

Conclusion
According to the process outlined above, WebLogic developers should be able to configure BEA WebLogic 8.1 server for Oracle 8.1 databases. Configuring other databases is similar to configuring Oracle 8.1. Add the zip/JAR file containing the driver class of the corresponding database to the classpath variable, and specify the connection URL and driver class name in the JDBC connection pool configuration.

References

• Programming WebLogic JDBC: http://e-docs.bea.com/wls/docs81/jdbc/