JDBC configuration part -II

JDBC Configuration :

WebLogic JDBC Drivers

The WebLogic Type 4 JDBC drivers are installed with Oracle WebLogic Server in the <WL_HOME>/server/lib folder, where <WL_HOME> is the directory in which you installed Oracle WebLogic Server. Driver class files are included in the manifest classpath in weblogic.jar, so the drivers are automatically added to your classpath on the server.

The WebLogic Type 4 JDBC drivers are installed by default when you perform a complete installation of Oracle WebLogic Server. If you choose a custom installation, ensure that the WebLogic JDBC Drivers check box is selected. If this option is deselected, the drivers are not installed.

The WebLogic Type 4 JDBC drivers included with Oracle WebLogic Server are provided from DataDirect.

This release includes support for Oracle 11g and 11g Real Application Clusters (RAC). Support for 11g RAC continues to rely on the well-proven integration architecture using multidata sources for X/Open Distributed Transaction Processing (XA) with load balancing.

Type 4 drivers are “all-Java” driver implementations that do not require client-side configuration.

Connection Pool:

Oracle WebLogic Server opens JDBC connections to the database during the WebLogic startup process and adds the connections to the pool. This is faster than creating a new connection on demand. The size of the pool is dynamic and can be fine-tuned.

The connection pool within a JDBC data source contains a group of JDBC connections that applications reserve, use, and then return to the pool. The connection pool and the connections within it are created when the connection pool is registered, usually when starting up Oracle WebLogic Server or when deploying the data source to a new target.

Benefits of Connection Pools

Making a DBMS connection is a very slow process when compared to assigning an existing connection. When Oracle WebLogic Server starts, connections from the connection pools are opened and are available to all clients. When a client closes a connection from a connection pool, the connection is returned to the pool and is available for other clients; the connection itself is not closed. There is little cost in opening and closing pool connections. The alternative is for application code to make its own JDBC connections as needed. A DBMS runs faster with dedicated connections than if it has to handle incoming connection attempts at run time.

Connection information, such as the JDBC driver class name, the database location (URL), and the username and password can be managed in one location using the Administration Console. Application developers can obtain a connection without having to worry about these details. 

Limiting the number of DBMS connections is important if you have a licensing limitation for DBMS connections or a resource concern.

Clients use a connection pool by “borrowing” a connection, using it, and then returning it to the pool by closing it. The connection pool can grow or shrink dynamically to accommodate demand. The Administration Console is used to set a connection pool’s initial capacity, maximum capacity, and capacity increment.

Modular Configuration and Deployment of JDBC Resources

Example of a JDBC configuration:

<?xml version="1.0" encoding="UTF-8"?>

<jdbc-data-source xsi:schemaLocation="http://xmlns.oracle.com/weblogic/jdbc-data-source http://xmlns.oracle.com/weblogic/jdbc-data-source/1.0/jdbc-data-source.xsd" xmlns="http://xmlns.oracle.com/weblogic/jdbc-data-source" xmlns:sec="http://xmlns.oracle.com/weblogic/security" xmlns:wls="http://xmlns.oracle.com/weblogic/security/wls" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <name>MedRecGlobalDataSourceXA</name>

  <jdbc-driver-params>

    <url>jdbc:oracle:thin:@localhost:1521:orcl</url>

    <driver-name>oracle.jdbc.xa.client.OracleXADataSource</driver-name>

    <properties>

      <property>

        <name>user</name>

        <value>medrec</value>

      </property>

    </properties>

    <password-encrypted>{AES}fyOq41+FkMM+ZhcliHQTX21fDGIyKOvdNwHi1B8P528=</password-encrypted>

  </jdbc-driver-params>

  <jdbc-connection-pool-params>

    <initial-capacity>5</initial-capacity>

    <max-capacity>10</max-capacity>

    <capacity-increment>1</capacity-increment>

  </jdbc-connection-pool-params>

  <jdbc-data-source-params>

    <jndi-name>jdbc/MedRecGlobalDataSourceXA</jndi-name>

    <global-transactions-protocol>TwoPhaseCommit</global-transactions-protocol>

  </jdbc-data-source-params>

</jdbc-data-source>

Creating a Data Source Using the Administration Console

You can create data sources via the Administration Console (as shown here) or WLST. Make sure that the JDBC drivers that you want to use to create database connections are installed on all the servers on which you want to configure database connectivity. Some JDBC drivers are installed with Oracle WebLogic Server, including the WebLogic Type 4 JDBC drivers for DB2, Informix, MS SQL Server, Oracle, and Sybase.

1.In the Domain Structure tree, expand Services > JDBC and then select Data Sources.

2.On the Summary of Data Sources page, click New.

3.On the JDBC Data Source Properties page, enter or select the following information and click Next:

-Name: Enter a configuration name for this JDBC data source.

-JNDI Name: Enter the JNDI path to which this JDBC data source will be bound. Applications look up the data source on the JNDI tree by this name when reserving a connection.

-Database Type: Select the database that you want to connect to. If your DBMS is not listed, select Other.

-Database Driver: Select the JDBC driver that you want to use to connect to the database. The list includes common JDBC drivers for the selected DBMS. For example, the non-XA driver was selected, but you could have selected the XA driver. The non-XA will show an extra page for configuration.

 

Non-XA Configuration

If you selected a non-XA JDBC driver, you are presented with two transaction options: Supports Global Transactions and Supports Local Transactions. If you select the non-XA option, WebLogic can use several alternative strategies to emulate XA on your non-XA driver.

Data Source Connection Properties

On the Connection Properties page, enter values for the following properties and click Next:

•Database Name: This field is overloaded, which means there are multiple kinds of information that could go in this field depending on the context. It is not always the name of the database that you want to connect to. Exact database name requirements vary by JDBC driver and by DBMS. If you used Oracle’s Driver for Service Connections, the service name would be the full name orcl.example.com; if you used Oracle’s Driver for Instance Connections, the instance name would be just orcl. RAC naming is different as well. In any case for Oracle, it is not the database name.

•Host Name: Enter the DNS name or IP address of the server that hosts the database.

•Port: Enter the port on which the database server listens for connections requests. For Oracle databases, you can verify this by entering lsnrctl status.

•Database User Name: Enter the database user account name that you want to use for each connection in the data source.

•Password/Confirm Password: Enter the password for the database user account.

Test Configuration

On the Test Database Connection page, review the connection parameters and click Test Configuration. WebLogic attempts to create a connection from the Administration Server to the database. Results from the connection test are displayed at the top of the page. If the test is unsuccessful, you should correct any configuration errors and retry the test.

Selecting a target is optional. You can click Finish after testing without assigning a target. The JDBC source will be configured, but not deployed. If you skip selecting the target, there is a chance to deploy the JDBC source later. Select a server target (or not), and then click Finish.

Connection Pool Configuration

The screenshot in the slide shows how you can modify a connection pool after the data source is created.

Before modifying a connection pool, you should know:

•The JDBC URL of the database

•The connection properties used to authenticate a user or optionally configure the driver

•The maximum number of connections that your application will be allowed by the DBA

After creating your initial data source configuration in the console, you can tune its connection pool settings:

1.  In the Domain Structure tree, expand Services > JDBC and then select Data Sources. After selecting your data source, click the Configuration > Connection Pool tab.

2.  Enter values for the available data source attributes.
Note: The exclamation mark in a yellow triangle means that changing these values requires restarting some components.

Connection Pool Advanced

Some of the key options are found under the Advanced section, including:

•Initial Capacity: This is the number of physical connections to create when deploying the connection pool. This is also the minimum number of physical connections that the connection pool will keep available.

•Maximum Capacity: This is the maximum number of physical connections that this connection pool can contain. For optimal performance, set the value of Initial Capacity equal to the value for Maximum Capacity, although that disables the dynamic resizing.

•Capacity Increment: When there are no more available physical connections to satisfy connection requests, Oracle WebLogic Server creates this number of additional physical connections and adds them to the connection pool up to the maximum capacity.

•Test Frequency: This is the number of seconds between when Oracle WebLogic Server tests unused connections. This requires that you specify a Test Table Name. DUAL is included in all Oracle database installations for such a purpose as this. Connections that fail the test are closed and reopened to reestablish a valid physical connection. If the test fails again, the connection is closed.