test

Sun Java System Application Server 9.1 Administration Guide

Chapter 3 JDBC Resources

This chapter explains how to configure JDBC resources, which are required by applications that access databases. This chapter contains the following sections:

JDBC Resources

To store, organize, and retrieve data, most applications use relational databases. Java EE applications access relational databases through the JDBC API.

A JDBC resource (data source) provides applications with a means of connecting to a database. Typically, the administrator creates a JDBC resource for each database accessed by the applications deployed in a domain. (However, more than one JDBC resource can be created for a database.)

To create a JDBC resource, specify a unique JNDI name that identifies the resource. (See the section JNDI Names and Resources.) Expect to find the JNDI name of a JDBC resource in java:comp/env/jdbc subcontext. For example, the JNDI name for the resource of a payroll database could be java:comp/env/jdbc/payrolldb. Because all resource JNDI names are in the java:comp/env subcontext, when specifying the JNDI name of a JDBC resource in the Admin Console, enter only jdbc/name. For example, for a payroll database specify jdbc/payrolldb.

JDBC Connection Pools

To create a JDBC resource, specify the connection pool with which it is associated. Multiple JDBC resources can specify a single connection pool.

A JDBC connection pool is a group of reusable connections for a particular database. Because creating each new physical connection is time consuming, the server maintains a pool of available connections to increase performance. When an application requests a connection, it obtains one from the pool. When an application closes a connection, the connection is returned to the pool.

The properties of connection pools can vary with different database vendors. Some common properties are the database’s name (URL), user name, and password.

See Also:

How JDBC Resources and Connection Pools Work Together

To store, organize, and retrieve data, most applications use relational databases. Java EE applications access relational databases through the JDBC API. Before an application can access a database, it must get a connection.

At runtime, here’s what happens when an application connects to a database:

  1. The application gets the JDBC resource (data source) associated with the database by making a call through the JNDI API.

    Given the resource’s JNDI name, the naming and directory service locates the JDBC resource. Each JDBC resource specifies a connection pool.

  2. Via the JDBC resource, the application gets a database connection.

    Behind the scenes, the application server retrieves a physical connection from the connection pool that corresponds to the database. The pool defines connection attributes such as the database name (URL), user name, and password.

  3. Now that it’s connected to the database, the application can read, modify, and add data to the database.

    The applications access the database by making calls to the JDBC API. The JDBC driver translates the application’s JDBC calls into the protocol of the database server.

  4. When it’s finished accessing the database, the application closes the connection.

    The application server returns the connection to the connection pool. Once it’s back in the pool, the connection is available for the next application.

Setting Up Database Access

To set up a database access:

  1. Install a supported database product.

    For a list of database products supported by the Application Server, see the Release Notes.

  2. Install a JDBC driver for the database product.

  3. Make the driver’s JAR file accessible to the domain’s server instance.

  4. Create the database.

    Usually, the application provider delivers scripts for creating and populating the database.

  5. Create a connection pool for the database.

  6. Create a JDBC resource that points to the connection pool.

Now to integrate the JDBC driver into an administrative domain, do either of the following:

  1. Make the driver accessible to the common class loader.

    Copy the driver’s JAR and ZIP files into the domain-dir/lib directory or copy its class files into the domain-dir/lib/ext directory.

  2. Restart the domain.

  3. Identify the fully-qualified path name for the driver's JAR file.

About 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:

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.

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

  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.

    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.  

  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  

    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.

  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.

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.

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

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

About JDBC Resources

A JDBC resource (data source) provides applications with a means of connecting to a database.

Before creating a JDBC resource, first create a JDBC connection pool.

When creating a JDBC resource, you must identify:

  1. The JNDI Name. By convention, the name begins with the jdbc/ string. For example: jdbc/payrolldb. Don’t forget the forward slash.

  2. Select a connection pool to be associated with the new JDBC resource.

  3. Specify the settings for the resource.

  4. Identify the targets (clusters and standalone server instance) on which the resource is available.

Configurations for Specific JDBC Drivers

Application Server 9.1 is designed to support connectivity to any database management system with a corresponding JDBC driver. The following JDBC driver and database combinations are supported. These combinations have been tested with Application Server 9.1 and are found to be Java EE compatible. They are also supported for CMP.

For an up to date list of currently supported JDBC drivers, see the Sun Java System Application Server 9.1 Release Notes.

Other JDBC drivers can be used with Application Server 9.1, but Java EE compliance tests have not been completed with these drivers. Although Sun offers no product support for these drivers, Sun offers limited support of the use of these drivers with Application Server 9.1.

For details about how to integrate a JDBC driver and how to use the Administration Console or the command line interface to implement the configuration, see the Sun Java System Application Server 9.1 Administration Guide.

Note –

An Oracle database user running the capture-schema command needs ANALYZE ANY TABLE privileges if that user does not own the schema. These privileges are granted to the user by the database administrator. For information about capture-schema, see Sun Java System Application Server 9.1 Reference Manual.

Derby Type 4 Driver

The Derby JDBC driver is included with the Application Server by default, except for the Solaris bundled installation, which does not include Derby. Therefore, unless you have the Solaris bundled installation, you do not need to integrate this JDBC driver with the Application Server.

The JAR file for the Derby driver is derbyclient.jar.

Configure the connection pool using the following settings:

Sun Java System JDBC Driver for DB2 Databases

The JAR files for this driver are smbase.jar, smdb2.jar, and smutil.jar. Configure the connection pool using the following settings:

Sun Java System JDBC Driver for Oracle 8.1.7 and 9.x Databases

The JAR files for this driver are smbase.jar, smoracle.jar, and smutil.jar. Configure the connection pool using the following settings:

Sun Java System JDBC Driver for Microsoft SQL Server Databases

The JAR files for this driver are smbase.jar, smsqlserver.jar, and smutil.jar. Configure the connection pool using the following settings:

Sun Java System JDBC Driver for Sybase Databases

The JAR files for this driver are smbase.jar, smsybase.jar, and smutil.jar. Configure the connection pool using the following settings:

IBM DB2 Type 2 Driver

The JAR files for the DB2 driver are db2jcc.jar, db2jcc_license_cu.jar, and db2java.zip. Set your environment variables. For ecample:

LD_LIBRARY_PATH=/usr/db2user/sqllib/lib:${j2ee.home}/lib
DB2DIR=/opt/IBM/db2/V8.2
DB2INSTANCE=db2user
INSTHOME=/usr/db2user
VWSPATH=/usr/db2user/sqllib
THREADS_FLAG=native

Configure the connection pool using the following settings:

JConnect Type 4 Driver for Sybase ASE 12.5 Databases

The JAR file for the Sybase driver is jconn2.jar. Configure the connection pool using the following settings:

MM MySQL Type 4 Driver (Non-XA)

The JAR file for the MySQL driver is mysql-connector-java-version-bin-g.jar, for example, mysql-connector-java-3.1.12-bin-g.jar. Configure the connection pool using the following settings:

MM MySQL Type 4 Driver (XA Only)

The JAR file for the MySQL driver is mysql-connector-java-version-bin-g.jar, for example, mysql-connector-java-3.1.12-bin-g.jar. Configure the connection pool using the following settings:

Inet Oraxo JDBC Driver for Oracle 8.1.7 and 9.x Databases

The JAR file for the Inet Oracle driver is Oranxo.jar. Configure the connection pool using the following settings:

Inet Merlia JDBC Driver for Microsoft SQL Server Databases

The JAR file for the Inet Microsoft SQL Server driver is Merlia.jar. Configure the connection pool using the following settings:

Inet Sybelux JDBC Driver for Sybase Databases

The JAR file for the Inet Sybase driver is Sybelux.jar. Configure the connection pool using the following settings:

Oracle Thin Type 4 Driver for Oracle 8.1.7 and 9.x Databases

The JAR file for the Oracle driver is ojdbc14.jar. Configure the connection pool using the following settings:

OCI Oracle Type 2 Driver for Oracle 8.1.7 and 9.x Databases

The JAR file for the OCI Oracle driver is ojdbc14.jar. Make sure that the shared library is available through LD_LIBRARY_PATH and that the ORACLE_HOME property is set. Configure the connection pool using the following settings:

IBM Informix Type 4 Driver

Configure the connection pool using the following settings:

CloudScape 5.1 Type 4 Driver

The JAR files for the CloudScape driver are db2j.jar, db2jtools.jar, db2jcview.jar, jh.jar, db2jcc.jar, and db2jnet.jar. Configure the connection pool using the following settings: