Sun Java System Application Server Enterprise Edition 8.1 2005Q2 Administration Guide

- Previous: Setting Up Database Access
- Next: About JDBC Resources

# About JDBC Connection Pools

- To create a JDBC connection pool

- To edit a JDBC connection pool

- To verify connection pool settings

- To delete a JDBC connection pool

## To create a JDBC connection pool

A JDBC connection pool is a group of reusable connections for a particular database. When creating the pool with the Admin Console, the Administrator is actually defining the aspects of a connection to a specific database.

##### Before You Begin

Before creating the pool, you must first install and integrate the JDBC driver. See To integrate a JDBC driver.

When building the Create Connection Pool pages, certain data specific to the JDBC driver and the database vendor must be entered. Before proceeding, gather the following information:

- Database vendor name

- Resource type, such as `javax.sql.DataSource` (local transactions only) `javax.sql.XADataSource` (global transactions)

- Data source class name

- Required properties, such as the database name (URL), user name, and password

1. In the Admin Console’s tree view (left pane), expand the Resources node.

2. Under Resources, expand the JDBC node.

3. Under JDBC, select the Connection Pools node.

4. On the Connection Pools page, click New.

5. On the first Create Connection Pool page, specify the following general settings:

   1. In the Name field, enter a logical name for the pool.

      Specify this name when creating a JDBC resource.

   2. Select an entry from the Resource Type combo box.

   3. Select an entry from the Database Vendor combo box.

6. Click Next.

7. On the second Create Connection Pool page, specify the value for the DataSource Class Name field.

   If the JDBC driver has a DataSource class for the resource type and database vendor specified in the previous page, then the value of the DataSource Class Name field is provided.

8. Click Next.

9. On the third and last Create Connection Pool page, perform these tasks:

   1. In the General Settings section, verify that the values are correct.

   2. For the fields in the Pool Settings, Connection Validation, and Transaction Isolation sections, retain the default values.

      It is most convenient to change these settings at a later time. See To edit a JDBC connection pool.

   3. In the Additional Properties table, add the required properties, such as database name (URL), user name, and password.

10. Click Finish.

##### Equivalent asadmin command

`create-jdbc-connection-pool`

## To edit a JDBC connection pool

The Edit JDBC Connection Pool page provides the means to change all of the settings for an existing pool except its name.

1. In the tree component, expand the Resources node.

2. Under Resources, expand the JDBC node.

3. Under JDBC, expand the Connection Pools node.

4. Select the node for the pool you want to edit.

5. On the Edit JDBC Connection Pool page, make any necessary changes.

   1. Change general settings.

      The values of the general settings depend on the specific JDBC driver that is installed. These settings are the names of classes or interfaces in the Java programming language.

      Parameter | Description
      --- | ---
      DataSource Class Name | The vendor-specific class name that implements the `DataSource` and / or `XADataSource` APIs. This class is in the JDBC driver.
      Resource Type | Choices include `javax.sql.DataSource` (local transactions only), `javax.sql.XADataSource` (global transactions), and `java.sql.ConnectionPoolDataSource` (local transactions, possible performance improvements).

   2. Change pool settings.

      A set of physical database connections reside in the pool. When an application requests a connection, the connection is removed from the pool, and when the application releases the connection, it is returned to the pool.

      Parameter | Description
      --- | ---
      Initial and Minimum Pool Size | The minimum number of connections in the pool. This value also determines the number of connections placed in the pool when the pool is first created or when application server starts.
      Maximum Pool Size | The maximum number of connections in the pool.
      Pool Resize Quantity | When the pool shrinks toward the minimum pool size it is resized in batches. This value determines the number of connections in the batch. Making this value too large delays connection recycling; making it too small will be less efficient.
      Idle Timeout | The maximum time in seconds that a connection can remain idle in the pool. After this time expires, the connection is removed from the pool.
      Max Wait Time | The amount of time the application requesting a connection will wait before getting a connection timeout. Because the default wait time is long, the application might appear to hang indefinitely.

   3. Change connection validation settings.

      Optionally, the application server can validate connections before they are passed to applications. This validation allows the application server to automatically reestablish database connections if the database becomes unavailable due to network failure or database server crash. Validation of connections incurs additional overhead and slightly reduces performance.

      Parameter | Description
      --- | ---
      Connection Validation | Select the Required checkbox to enable connection validation.
      Validation Method | The application server can validate database connections in three ways: auto-commit, metadata, and table. auto-commit and metadata - The application server validates a connection by calling the `con.getAutoCommit()` and `con.getMetaData()` methods. However, because many JDBC drivers cache the results of these calls, they do not always provide reliable validations. Check with the driver vendor to determine whether these calls are cached or not. auto-commit validation makes use of two methods for validating the connection. `getAutoCommit()` is used to retrieve the current state of auto-commit and `setAutoCommit()` to change the state of auto-commit. This allows actual contact with the database to take place. `getAutomCommit()` might or might not contact the database, depending on the implementation. Actual physical connection will be wrapped for different purposes, such as a connection pool. Note – Databases such as Oracle do some caching for `setAutoCommit()`. For such databases, actual connection validation might not happen, so table-based validation is recommended. table - The application queries a database table that are specified. The table must exist and be accessible, but it doesn't require any rows. Do not use an existing table that has a large number of rows or a table that is already frequently accessed.
      Table Name | If you selected table from the Validation Method combo box, then specify the name of the database table here.
      On Any Failure | If you select the checkbox labelled Close All Connections, if a single connection fails, then the application server closes all connections in the pool and then reestablish them. If you do not select the checkbox, then individual connections are reestablished only when they are used.

   4. Change transaction isolation settings.

      Because a database is usually accessed by many users concurrently, one transaction might update data while another attempts to read the same data. The isolation level of a transaction defines the degree to which the data being updated is visible to other transactions. For details on isolation levels, refer to the documentation of the database vendor.

      Parameter | Description
      --- | ---
      Transaction Isolation | Makes it possible to select the transaction isolation level for the connections of this pool. If left unspecified, the connections operate with default isolation levels provided by the JDBC driver.
      Guaranteed Isolation Level | Only applicable if the isolation level has been specified. If you select the Guaranteed checkbox, then all connections taken from the pool have the same isolation level. For example, if the isolation level for the connection is changed programmatically (with `con.setTransactionIsolation`) when last used, this mechanism changes the status back to the specified isolation level.

   5. Change properties.

      In the Additional Properties table, it is possible to specify properties, such as the database name (URL), user name, and password. Because the properties vary with database vendor, consult the vendor’s documentation for details.

6. Click Save.

##### See Also

- To create a JDBC connection pool

- To verify connection pool settings

- To delete a JDBC connection pool

## To verify connection pool settings

1. Start the database server.

2. Click Ping.

   The Admin Console attempts to connect to the database. If an error message displays, check to see if the database server was restarted.

## To delete a JDBC connection pool

1. In the tree component, expand the Resources node.

2. Under Resources, expand the JDBC node.

3. Under JDBC, select the Connection Pools node.

4. On the Connection Pools page, select the checkbox for the pool to be deleted.

5. Click Delete.

##### Equivalent asadmin command

`delete-jdbc-connection-pool`

- Previous: Setting Up Database Access
- Next: About JDBC Resources

- © 2010, Oracle Corporation and/or its affiliates