test

Sun GlassFish Enterprise Server 2.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.

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.

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

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:

Creating a JDBC Connection Pool

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

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

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

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

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

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

  5. Click the Ping button to test the JDBC connection pool.

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

  7. Click New.

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

  9. Click OK.

ProcedureCreating a JDBC Connection Pool and JDBC Resource Using the CLI

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

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

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

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

  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. 

    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.

    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.  

  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. 

Configurations for Specific JDBC Drivers

Enterprise Server 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 Enterprise Server and are found to be Java EE compatible.

Other JDBC drivers can be used with Enterprise Server , 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 Enterprise Server .

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 GlassFish Enterprise Server 2.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 GlassFish Enterprise Server 2.1 Reference Manual.

Java DB Type 4 Driver

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

The JAR file for the Java DB driver is derbyclient.jar.

Configure the connection pool using the following settings:

Sun GlassFish 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 GlassFish 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 GlassFish 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 GlassFish 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 8.1 Type 2 Driver

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

LD_LIBRARY_PATH=/usr/db2user/sqllib/lib:${Java EE.home}/lib
DB2DIR=/opt/IBM/db2/V8.1
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:

MySQL Type 4 Driver

The JAR file for the MySQL driver is mysql-connector-java-version-bin-g.jar, for example, mysql-connector-java-5.0.5-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: