1. Overview of GlassFish Server Administration
Default Settings and Locations
Instructions for Administering GlassFish Server
4. Administering the Virtual Machine for the Java Platform
6. Administering Web Applications
7. Administering the Logging Service
8. Administering the Monitoring Service
9. Writing and Running JavaScript Clients to Monitor GlassFish Server
10. Administering Life Cycle Modules
11. Extending and Updating GlassFish Server
Part II Resources and Services Administration
12. Administering Database Connectivity
To Install the Database and Database Driver
To Configure Your Environment to Run Java DB Utility Scripts
Configuring Access to the Database
Administering JDBC Connection Pools
To Create a JDBC Connection Pool
To Contact (Ping) a Connection Pool
To Reset (Flush) a Connection Pool
To Update a JDBC Connection Pool
To Delete a JDBC Connection Pool
Configuring Specific JDBC Connection Pool Features
Enabling the jdbc/__default Resource in a Clustered Environment
To Enable the jdbc/__default Resource for a Clustered Environment
Configuration Specifics for JDBC Drivers
IBM DB2 Database Type 2 Driver
IBM DB2 Database Type 4 Driver
MySQL Server Database Type 4 Driver
DataDirect Type 4 Driver for IBM DB2 Database
DataDirect Type 4 Driver for IBM Informix
DataDirect Type 4 Driver for Microsoft SQL Server Database
DataDirect Type 4 Driver for MySQL Server Database
DataDirect Type 4 Driver for Oracle 11 Database
DataDirect Type 4 Driver for Sybase Database
Inet Oraxo Driver for Oracle Database
Inet Merlia Driver for Microsoft SQL Server Database
Inet Sybelux Driver for Sybase Database
JConnect Type 4 Driver for Sybase ASE 12.5 Database
13. Administering EIS Connectivity
14. Administering Internet Connectivity
15. Administering the Object Request Broker (ORB)
16. Administering the JavaMail Service
17. Administering the Java Message Service (JMS)
18. Administering the Java Naming and Directory Interface (JNDI) Service
19. Administering Transactions
After establishing the database, you are ready to set up access for GlassFish Server applications. The high-level steps include creating a JDBC connection pool, creating a JDBC resource for the connection pool, and integrating a JDBC driver into an administrative domain.
Instructions for performing these steps are contained in the following sections:
A JDBC connection pool is a group of reusable connections for a particular database. Because creating each new physical connection is time consuming, GlassFish Server maintains a pool of available connections. 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. JDBC connection pools can be globally accessible or be scoped to an enterprise application, web module, EJB module, connector module or application client module, as described in Application-Scoped Resources in Oracle GlassFish Server 3.1 Application Deployment Guide.
A JDBC resource is created by specifying the connection pool with which the resource is associated. Multiple JDBC resources can specify a single connection pool. The properties of connection pools can vary with different database vendors. Some common properties are the database name (URL), the user name, and the password.
The following tasks and information are used to administer JDBC connection pools:
Use the create-jdbc-connection-pool subcommand in remote mode to register a new JDBC connection pool with the specified JDBC connection pool name. A JDBC connection pool or a connector connection pool can be created with authentication. You can either use a subcommand option to specify user, password, or other connection information using the asadmin utility, or specify the connection information in the XML descriptor file.
One connection pool is needed for each database, possibly more depending on the application. When you are building the connection pool, certain data specific to the JDBC driver and the database vendor is required. You can find some of the following specifics inConfiguration Specifics for JDBC Drivers:
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 is a dynamic event and does not require server restart. However, there are some parameters that do require server restart. See Configuration Changes That Require Restart.
Before You Begin
Before creating the connection pool, you must first install and integrate the database and its associated JDBC driver. For instructions, see Setting Up the Database.
Remote subcommands require a running server.
Some parameters require server restart. See Configuration Changes That Require Restart.
Example 12-3 Creating a JDBC Connection Pool
This example creates a JDBC connection pool named sample_derby_pool on localhost.
asadmin> create-jdbc-connection-pool --datasourceclassname org.apache.derby.jdbc.ClientDataSource --restype javax.sql.XADataSource --property portNumber=1527:password=APP:user=APP:serverName= localhost:databaseName=sun-appserv-samples:connectionAttribut es=\;create\\=true sample_derby_pool Command create-jdbc-connection-pool executed successfully.
See Also
You can also view the full syntax and options of the subcommand by typing asadmin help create-jdbc-connection-pool at the command line.
Use the list-jdbc-connection-pools subcommand in remote mode to list all existing JDBC connection pools.
Remote subcommands require a running server.
Example 12-4 Listing JDBC Connection Pools
This example lists the JDBC connection pools that are on localhost.
asadmin> list-jdbc-connection-pools sample_derby_pool2 poolA __TimerPool DerbyPool sample_derby_pool Command list-jdbc-connection-pools executed successfully.
See Also
You can also view the full syntax and options of the subcommand by typing asadmin help list-jdbc-connection-pools at the command line.
Use the ping-connection-pool subcommand in remote mode to test if a connection pool is usable. For example, if you create a new JDBC connection pool for an application that is expected to be deployed later, you can test the JDBC pool with this subcommand before the application is deployed. Running a ping will force the creation of the pool if it hasn't already been created.
Before You Begin
Before you can contact a connection pool, the connection pool must be created with authentication, and the server or database must be running.
Remote subcommands require a running server.
Example 12-5 Contacting a Connection Pool
This example tests to see if the DerbyPool connection pool is usable.
asadmin> ping-connection-pool DerbyPool Command ping-connection-pool executed successfully
See Also
You can also view the full syntax and options of the subcommand by typing asadmin help ping-connection-pool at the command line.
You can also specify that a JDBC connection pool is automatically tested when created or reconfigured by setting its --ping option to true (the default is false). See To Create a JDBC Connection Pool or To Update a JDBC Connection Pool.
Use the flush-connection-pool in remote mode to reinitialize all connections established in the specified connection pool without the need for reconfiguring the pool. Connection pool reconfiguration can result in application redeployment, which is a time-consuming operation. The JDBC connection pool or connector connection pool is reset to its initial state. Any existing live connections are destroyed, which means that the transactions associated with these connections are lost and must be retried. The subcommand then recreates the initial connections for the pool, and restores the pool to its steady pool size.
Remote subcommands require a running server.
Example 12-6 Resetting (Flushing) a Connection Pool
This example resets the JDBC connection pool named __TimerPool to its steady pool size.
asadmin> flush-connection-pool __TimerPool Command flush-connection-pool executed successfully.
See Also
You can also view the full syntax and options of the subcommand by typing asadmin help flush-connection-pool at the command line.
You can change all of the settings for an existing pool except its name. Use the get and set subcommands to view and change the values of the JDBC connection pool properties.
For example:
asadmin get resources.jdbc-connection-pool.DerbyPool.property
For example:
asadmin set resources.jdbc-connection-pool.DerbyPool.steady-pool-size=9
Some parameters require server restart. See Configuration Changes That Require Restart.
See Also
For information about how to tune a connection pool, see the Oracle GlassFish Server 3.1 Performance Tuning Guide.
Use the delete-jdbc-connection-pool subcommand in remote mode to delete an existing JDBC connection pool. Deleting a JDBC connection pool is a dynamic event and does not require server restart.
Before You Begin
Before deleting a JDBC connection pool, all associations to the resource must be removed.
Remote subcommands require a running server.
Example 12-7 Deleting a JDBC Connection Pool
This example deletes the JDBC connection pool named DerbyPool.
asadmin> delete-jdbc-connection-pool jdbc/DerbyPool Command delete-jdbc-connection-pool executed successfully.
See Also
You can also view the full syntax and options of the subcommand by typing asadmin help delete-jdbc-connection-pool at the command line.
In GlassFish Server, JDBC Connection Pools support a variety of features to simplify administration, monitoring and performance tuning. The following topics address several of these features:
When the properties or attributes of a JDBC connection pool are changed, the connection pool is destroyed and re-created. Normally, applications using the connection pool must be redeployed as a consequence. This restriction can be avoided by enabling transparent JDBC connection pool reconfiguration. When this feature is enabled, applications do not need to be redeployed. Instead, requests for new connections are blocked until the reconfiguration operation completes. Connection requests from any in-flight transactions are served using the old pool configuration so as to complete the transaction. Then, connections are created using the pool's new configuration, and any blocked connection requests are served with connections from the re-created pool.
To enable transparent JDBC connection pool reconfiguration, set the dynamic-reconfiguration-wait-timeout-in-seconds property of the JDBC connection pool to a positive, nonzero value in one of the following ways:
Add it as a property in the Edit JDBC Connection Pool Properties page in the Administration Console. For more information, click the Help button in the Administration Console.
Specify it using the --property option in the create-jdbc-connection-pool subcommand. For more information, see create-jdbc-connection-pool(1).
Set it using the set subcommand. For example:
asadmin set resources.jdbc-connection-pool.pool-name.property.dynamic-reconfiguration-wait-timeout-in-seconds=15
This property specifies the time in seconds to wait for in-use connections to close and in-flight transactions to complete. Any connections in use or transaction in flight past this time must be retried.
You can specify a statement that executes each time a physical connection to the database is created (not reused) from a JDBC connection pool. This is useful for setting request or session specific properties and is suited for homogeneous requests in a single application. Set the Init SQL attribute of the JDBC connection pool to the SQL string to be executed in one of the following ways:
Enter an Init SQL value in the Edit Connection Pool Advanced Attributes page in the Administration Console. For more information, click the Help button in the Administration Console.
Specify the --initsql option in the asadmin create-jdbc-connection-pool command. For more information, see create-jdbc-connection-pool(1).
Specify the init-sql option in the asadmin set command. For example:
asadmin set domain1.resources.jdbc-connection-pool.DerbyPool.init-sql="sql-string"
An abnormally long running JDBC query executed by an application may leave it in a hanging state unless a timeout is explicitly set on the statement. Setting a statement timeout guarantees that all queries automatically time out if not completed within the specified period. When statements are created, the queryTimeout is set according to the statement timeout setting. This works only when the underlying JDBC driver supports queryTimeout for Statement, PreparedStatement, CallableStatement, and ResultSet.
You can specify a statement timeout in the following ways:
Enter a Statement Timeout value in the Edit Connection Pool Advanced Attributes page in the Administration Console. For more information, click the Help button in the Administration Console.
Specify the --statementtimeout option in the asadmin create-jdbc-connection-pool command. For more information, see create-jdbc-connection-pool(1).
If statements are not closed by an application after use, it is possible for the application to run out of cursors. Enabling statement leak detection causes statements to be considered as leaked if they are not closed within a specified period. Additionally, leaked statements can reclaimed automatically.
To enable statement leak detection, set Statement Leak Timeout In Seconds for the JDBC connection pool to a positive, nonzero value in one of the following ways:
Specify the --statementleaktimeout option in the create-jdbc-connection-pool subcommand. For more information, see create-jdbc-connection-pool(1).
Specify the statement-leak-timeout-in-seconds option in the set subcommand. For example:
asadmin set resources.jdbc-connection-pool.pool-name.statement-leak-timeout-in-seconds=300
When selecting a value for Statement Leak Timeout In Seconds, make sure that:
It is less than the Connection Leak Timeout; otherwise, the connection could be closed before the statement leak is recognized.
It is greater than the Statement Timeout; otherwise, a long running query could be mistaken as a statement leak.
After enabling statement leak detection, enable leaked statement reclamation by setting Reclaim Leaked Statements for the JDBC connection pool to a true value in one of the following ways:
Specify the --statementleakreclaim=true option in the create-jdbc-connection-pool subcommand. For more information, see create-jdbc-connection-pool(1).
Specify the statement-leak-reclaim option in the set subcommand. For example:
asadmin set resources.jdbc-connection-pool.pool-name.statement-leak-reclaim=true
Statement caching stores statements, prepared statements, and callable statements that are executed repeatedly by applications in a cache, thereby improving performance. Instead of the statement being prepared each time, the cache is searched for a match. The overhead of parsing and creating new statements each time is eliminated.
Statement caching is usually a feature of the JDBC driver. The GlassFish Server provides caching for drivers that do not support caching. To enable this feature, set the Statement Cache Size for the JDBC connection pool in one of the following ways:
Enter a Statement Cache Size value in the Edit Connection Pool Advanced Attributes page in the Administration Console. For more information, click the Help button in the Administration Console.
Specify the --statementcachesize option in the asadmin create-jdbc-connection-pool command. For more information, see create-jdbc-connection-pool(1).
Specify the statement-cache-size option in the asadmin set command. For example:
asadmin set domain1.resources.jdbc-connection-pool.DerbyPool.statement-cache-size=10
By default, this attribute is set to zero and the statement caching is turned off. To enable statement caching, you can set any positive nonzero value. The built-in cache eviction strategy is LRU-based (Least Recently Used). When a connection pool is flushed, the connections in the statement cache are recreated.
You can trace the SQL statements executed by applications that use a JDBC connection pool. Set the SQL Trace Listeners attribute to a comma-separated list of trace listener implementation classes in one of the following ways:
Enter an SQL Trace Listeners value in the Edit Connection Pool Advanced Attributes page in the Administration Console. For more information, click the Help button in the Administration Console.
Specify the --sqltracelisteners option in the asadmin create-jdbc-connection-pool command. For more information, see create-jdbc-connection-pool(1).
Specify the sql-trace-listeners option in the asadmin set command. For example:
asadmin set domain1.resources.jdbc-connection-pool.DerbyPool.sql-trace-listeners=listeners
The GlassFish Server provides a public interface, org.glassfish.api.jdbc.SQLTraceListener, that implements a means of recording SQLTraceRecord objects. To make custom implementations of this interface available to the GlassFish Server, place the implementation classes in as-install/lib.
The GlassFish Server provides an SQL tracing logger to log the SQL operations in the form of SQLTraceRecord objects in the server.log file. The module name under which the SQL operation is logged is javax.enterprise.resource.sqltrace. SQL traces are logged as FINE messages along with the module name to enable easy filtering of the SQL logs. A sample SQL trace record looks like this:
[#|2009-11-27T15:46:52.202+0530|FINE|glassfishv3.0|javax.enterprise.resource.sqltrace.com.sun.gjc.util |_ThreadID=29;_ThreadName=Thread-1;ClassName=com.sun.gjc.util.SQLTraceLogger;MethodName=sqlTrace; |ThreadID=77 | ThreadName=p: thread-pool-1; w: 6 | TimeStamp=1259317012202 | ClassName=com.sun.gjc.spi.jdbc40.PreparedStatementWrapper40 | MethodName=executeUpdate | arg[0]=insert into table1(colName) values(100) | arg[1]=columnNames | |#]
This trace shows that an executeUpdate(String sql, String columnNames) operation is being done.
When SQL statement tracing is enabled and JDBC connection pool monitoring is enabled, GlassFish Server maintains a tracing cache of recent queries and their frequency of use. The following JDBC connection pool properties can be configured to control this cache and the monitoring statistics available from it:
Specifies how long in minutes to keep a query in the tracing cache, tracking its frequency of use. The default value is 5 minutes.
Specifies how many of the most used queries, in frequency order, are listed the monitoring report. The default value is 10 queries.
Set these parameters in one of the following ways:
Add them as properties in the Edit JDBC Connection Pool Properties page in the Administration Console. For more information, click the Help button in the Administration Console.
Specify them using the --property option in the create-jdbc-connection-pool subcommand. For more information, see create-jdbc-connection-pool(1).
Set them using the set subcommand. For example:
asadmin set resources.jdbc-connection-pool.pool-name.property.time-to-keep-queries-in-minutes=10
A JDBC resource, also known as a data source, provides an application with a means of connecting to a database. Typically, you create a JDBC resource for each database that is accessed by the applications deployed in a domain. Multiple JDBC resources can be specified for a database. JDBC resources can be globally accessible or be scoped to an enterprise application, web module, EJB module, connector module or application client module, as described in Application-Scoped Resources in Oracle GlassFish Server 3.1 Application Deployment Guide.
A JDBC resource is created by specifying the connection pool with which the resource will be associated . Use a unique Java Naming and Directory Interface (JNDI) name to identify the resource. For example, the JNDI name for the resource of a payroll database might be java:comp/env/jdbc/payrolldb.
The following tasks and information are used to administer JDBC resources:
Use the create-jdbc-resource subcommand in remote mode to create a JDBC resource. Creating a JDBC resource is a dynamic event and does not require server restart.
Because all JNDI names are in the java:comp/env subcontext, when specifying the JNDI name of a JDBC resource in the Administration Console, use only the jdbc/name format. For example, a payroll database might be specified as jdbc/payrolldb.
Before You Begin
Before creating a JDBC resource, you must first create a JDBC connection pool. For instructions, see To Create a JDBC Connection Pool.
Remote subcommands require a running server.
Information about properties for the subcommand is included in this help page.
Example 12-8 Creating a JDBC Resource
This example creates a JDBC resource named DerbyPool.
asadmin> create-jdbc-resource --connectionpoolid DerbyPool jdbc/DerbyPool Command create-jdbc-resource executed successfully.
See Also
You can also view the full syntax and options of the subcommand by typing asadmin help create-jdbc-resource at the command line.
Use the list-jdbc-resources subcommand in remote mode to list the existing JDBC resources.
Remote subcommands require a running server.
Example 12-9 Listing JDBC Resources
This example lists JDBC resources for localhost.
asadmin> list-jdbc-resources jdbc/__TimerPool jdbc/DerbyPool jdbc/__default jdbc1 Command list-jdbc-resources executed successfully.
See Also
You can also view the full syntax and options of the subcommand by typing asadmin help list-jdbc-resources at the command line.
You can enable or disable a JDBC resource by using the set subcommand. The JDBC resource is identified by its dotted name.
For example:
Example 12-10 Updating a JDBC Resource
This example changes the res1 enabled setting to false.
asadmin>set resources.jdbc-resource.res1.enabled=false
Use the delete-jdbc-resource subcommand in remote mode to delete an existing JDBC resource. Deleting a JDBC resource is a dynamic event and does not require server restart.
Before You Begin
Before deleting a JDBC resource, all associations with this resource must be removed.
Remote subcommands require a running server.
Example 12-11 Deleting a JDBC Resource
This example deletes a JDBC resource named DerbyPool.
asadmin> delete-jdbc-resource jdbc/DerbyPool Command delete-jdbc-resource executed successfully.
See Also
You can also view the full syntax and options of the subcommand by typing asadmin help delete-jdbc-resource at the command line.
GlassFish Server 3.1 includes a preconfigured JDBC resource with the JNDI name jdbc/__default. This jdbc/__default resource is not enabled by default, so you need to explicitly enable it if you want to use it in a cluster.
Instructions for creating JDBC resources in general are provided in To Create a JDBC Resource. Use the following procedure to enable the preconfigured jdbc/__default resource for a clustered GlassFish Server environment.
asadmin create-resource-ref --target cluster-name jdbc/__default
asadmin set resources.jdbc-connection-pool.DerbyPool.property.serverName=DAS-machine-name
This step is only required if the cluster includes remote instances.
asadmin stop-cluster cluster-name asadmin stop-domain domain-name asadmin start-domain domain-name asadmin start-cluster cluster-name
To use JDBC features, you must choose a JDBC driver to work with the GlassFish Server, then you must set up the driver. This section covers these topics:
Supported JDBC drivers are those that have been fully tested by Oracle. For a list of the JDBC drivers currently supported by the GlassFish Server, see the Oracle GlassFish Server 3.1-3.1.1 Release Notes. For configurations of supported and other drivers, see Configuration Specifics for JDBC Drivers.
Note - Because the drivers and databases supported by the GlassFish Server are constantly being updated, and because database vendors continue to upgrade their products, always check with Oracle technical support for the latest database support information.
To integrate the JDBC driver into a GlassFish Server domain, copy the JAR files into the domain-dir/lib directory, then restart the server. This makes classes accessible to all applications or modules deployed on servers that share the same configuration. For more information about GlassFish Server class loaders, see Chapter 2, Class Loaders, in Oracle GlassFish Server 3.1 Application Development Guide.
If you are using an Oracle database with EclipseLink extensions, copy the JAR files into the domain-dir/lib/ext directory, then restart the server. For details, see Oracle Database Enhancements in Oracle GlassFish Server 3.1 Application Development Guide.
The Administration Console detects installed JDBC Drivers automatically when you create a JDBC connection pool. To create a JDBC connection pool using the Administration Console, open the Resources component, open the JDBC component, select Connection Pools, and click on the New button. This displays the New JDBC Connection Pool page.
Based on the Resource Type and Database Vendor you select on the New JDBC Connection Pool page, data source or driver implementation class names are listed in the Datasource Classname or Driver Classname field when you click on the Next button. When you choose a specific implementation class name on the next page, additional properties relevant to the installed JDBC driver are displayed in the Additional Properties section.