Sun GlassFish Enterprise Server v2.1.1 Administration Guide

Working with JDBC Connection Pools

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 creating the pool, you must first install and integrate the 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

Creating a JDBC Connection Pool

You can use the Admin Console for creating the JDBC connection pool.

Creating a JDBC Connection Pool and JDBC Resource Using the Admin Console

Copy the JDBC driver for database connectivity to as-install/domains/domain_name/lib/ext. Alternatively, add the path to the JDBC driver to classpath-prefix and restart the server.

In the Admin Console, click Create JDBC Connection Pool in the Common Tasks page.

Provide a name for the connection pool, select the resource type, select the database vendor. Click Next.

The Admin Console lists the default pool settings and the properties of datasource class provided by the vendor.

Click the Ping button to test the JDBC connection pool.

In the left-hand pane of the Admin Console, click Resources > JDBC Resources.

Click New.

In the New JDBC Resource page, provide the JNDI name for the resource and the JDBC comnnection pool with which the JDBC resource needs to be associated.

Click OK.

Creating a JDBC Connection Pool and JDBC Resource Using the CLI

Use the asadmin create-jdbc-connection-pool command to create a JDBC connection pool.

Sample command to create a JDBC connection pool:

asadmin create-jdbc-connection-pool --datasourceclassname oracle.jdbc.pool.OracleDataSource --restype javax.sql.DataSource --property user=dbuser:password=dbpassword:url="jdbc:oracle\:thin\:@localhost\:1521\:ORCL" myjdbc_oracle-pool

Use the asadmin create-jdbc-resource command to create a JDBC resource assicated with this connection pool.

Sample command to create a JDBC resource:

asadmin create-jdbc-connection-pool --connectionpoolid myjdbc_oracle-pool jdbc/oracle-resource

Test the connection settings are correct by pinging the connection pool using the asadmin ping-connection-pool command.

asadmin ping-connection-pool myjdbc_oracle-pool

To edit JDBC connection pool properties, use the asadmin list, get, and set commands.

Sample list command to list a JDBC connection pool:

asadmin list myjdbc_oracle-pool

The sample output of this command:

domain.resources.jdbc-connection-pool.oracle-pool

Sample get command to list all properties of a JDBC connection pool:

asadmin list myjdbc_oracle-pool

Sample get command to get all properties of a JDBC connection pool:

asadmin get domain.resources.jdbc-connection-pool.myjdbc_oracle-pool

Sample get command to set a properties of a JDBC connection pool:

asadmin set domain.resources.jdbc-connection-pool.myjdbc_oracle-pool.steady-pool-size=20

Editing 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.

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).

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.
Maximum Pool Size	The maximum number of connections in the pool.
Pool Resize Quantity	When the pool scales up and scales down towards the maximum and minimum pool sizes respectively, it is resized in batches. This value determines the number of connections in the batch. Making this value too large delays connection creation and 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.

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. 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 – 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. 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.
Allow Non Component Callers	Click this check box if you want to enable the pool for use by non-component callers such as Servlet Filters and Lifecycle modules.

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
Non-transactional Connections	Click the check box if you want Application Server to return all non-transactional connections.
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.

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.

Editing JDBC Connection Pool Advanced Attributes

To help diagnose connection leaks and improve ease-of–use, Application Server 9.1 provides several new attributes to configure a connection pool at the time of its creation.

Open the Advanced tab and specify the following attributes.

Attribute	Description
Name	Name of the JDBC connection pool whose properties you want to edit. You cannot change the pool name, however.
Statement Timeout	Time in seconds after which abnormally long running queries will be terminated. Application Server will set "QueryTimeout" on the statements created. The default value of -1 implies that the attribute is not enabled.
Wrap JDBC Objects	When set to true, application will get wrapped jdbc objects for Statement, PreparedStatement, CallableStatement, ResultSet, DatabaseMetaData. The default value is false.

Specify the Connection Settings as explained in the following table.

Attribute	Description
Validate Atmost Once	Amount of time, in seconds, after which a connection is validated at most once. This will help reduce the number of validation requests by a connection. The default value 0 implies that connection validation is not enabled.
Leak Timeout	Amount of time, in seconds, to trace connection leaks in a connection pool. The default value 0 means that connection leak tracing is disabled. If connection leak tracing is enabled, you can get statistics on the number of connection leaks in the Monitoring Resources tab. To view this tab, go to Application Server > Monitoring > Resources.
Leak Reclaim	If this option is enabled, leaked connections will be restored to the pool after leak connection tracing is complete.
Creation Retry Attempts	Number of attempts that will be made if there is a failure in creating a new connection. The default value of 0 implies that no attempts will be made to create the connection again.
Retry Interval	Specify the interval, in seconds, between two attempts to create a connection. The default value is 10 seconds. This attribute is used only if the value of Creation Retry Attempts is greater than 0.
Lazy Connection Enlistment	Enable this option to enlist a resource to the transaction only when it is actually used in a method.
Lazy Association	Connections are lazily associated when an operation is performed on them. Also, they are disassociated when the transaction is completed and a component method ends, which helps reuse of the physical connections. Default value is false.
Associate with Thread	Enable this option to associate a connection with the thread such that when the same thread is in need of a connection, it can reuse the connection already associated with that thread, thereby not incurring the overhead of getting a connection from the pool. Default value is false.
Match Connections	Use this option to switch on/off connection matching for the pool. It can be set to false if the administrator knows that the connections in the pool will always be homogeneous and hence a connection picked from the pool need not be matched by the resource adapter. Default value is false.
Max Connection Usage	Specify the number of times a connection should be reused by the pool. Once a connection is reused for the specified number of times, it will be closed. This is useful, for instance, to avoid statement-leaks. The default value of 0 implies that no connections will be reused.