Sun GlassFish Enterprise Server v3 Prelude Administration Guide

Chapter 5 Administering Database Connectivity

This chapter provides procedures for performing database connectivity tasks in the Sun GlassFishTM Enterprise Server v3 Prelude environment by using the asadmin command-line utility.

The following topics are addressed here:

Instructions for accomplishing these tasks by using the Admin Console are contained in the Admin Console online help.

Setting Up the Database

Most applications use relational databases to store, organize, and retrieve data. Applications access relational databases through the JavaTM Database Connectivity (JDBC) API.

The following topics are addressed here:

ProcedureTo Install the Database and Database Driver

  1. Install a supported database product.

    To see the current list of database products supported by Enterprise Server, refer to the Sun GlassFish Enterprise Server v3 Prelude Release Notes.

  2. Install a supported JDBC driver for the database product.

    For a list of drivers supported by Enterprise Server, see Configuration Specifics for JDBC Drivers.

  3. Make the JAR file for the JDBC driver accessible to the domain administration server (DAS).

    See Integrating the JDBC Driver.

  4. Create the database.

    The application provider usually delivers scripts for creating and populating the database.

ProcedureTo Start the Database

Enterprise Server includes an implementation of Java DB, however, you can use any JDBC-compliant database. The database is not started automatically when you start Enterprise Server, so if you have applications that require a database, you need to start Java DB manually by using the local start-database command.

  1. Start the database by using the start-database(1) command.

    When the database server starts, or a client connects to it successfully, the following files are created at the location that is specified by the --dbhome option:

    • The derby.log file contains the database server process log along with its standard output and standard error information.

    • The database files contain your schema (for example, database tables).


Example 5–1 Starting a Database

The following example command starts Java DB on port 5001 of localhost:


asadmin start-database --dbhost=localhost --dbport=5001

Information similar to the followingt is displayed (partial output):


Database started in Network Server mode on host localhost and port 5001.
--------- Derby Network Server Information --------
Version: CSS10020/10.2.2.1 - (538595)  Build: 538595  DRDA Product Id: CSS10020
-- listing properties --
derby.drda.traceDirectory=C:\prelude\v3_prelude_release\distrib...
derby.drda.maxThreads=0
derby.drda.keepAlive=true
derby.drda.minThreads=0
derby.drda.portNumber=5001
derby.drda.logConnections=false
derby.drda.timeSlice=0
derby.drda.startNetworkServer=false
derby.drda.host=localhost
derby.drda.traceAll=false
.
.
.
Starting database in the background.
Log redirected to C:\prelude\v3_prelude_release\distributions\web\target\glassfi
sh\databases\derby.log.
Command start-database executed successfully.

See Also

To see the full syntax and options of the command, type asadmin start-database --help at the command line.

ProcedureTo Stop the Database

The local stop-database command enables you to stop Java DB on a specified port. A single host can have multiple database server processes running on different ports.

  1. If necessary, notify users that the database is being stopped.

  2. Stop the database by using the stop-database(1) command.


Example 5–2 Stopping a Database

The following example command stops Java DB on port 5001 of localhost:


asadmin stop-database --dbhost=localhost --dbport=5001

Information similar to the following is displayed:


onnection obtained for host: localhost, port number 5001.
Apache Derby Network Server - 10.2.2.1 - (538595) shutdown at 2008-10-17 23:34:2
7.218 GMT
Command stop-database executed successfully.

Troubleshooting

For a laptop that roams between networks, you might have trouble shutting down the database. If you start Java DB and then change your IP address, you will not be able to stop Java DB unless you add a specific --dbhost argument. For example, if you run asadmin start-database --dbhost = 0.0.0.0, and then disconnect Ethernet and switch to wifi, you should run a command similar to the following to stop the database:

asadmin stop-database --dbhost localhost

See Also

To see the full syntax and options of the command, type asadmin stop-database --help at the command line.

Java DB Utility Scripts

The Java DB configuration that is available for use with Enterprise Server includes scripts that can help you use Java DB. The following scripts are available in the as-install/javadb/frameworks/NetworkServer/bin directory:

startNetworkServer,startNetworkServer.bat

Script to start the network server

stopNetworkServer,stopNetworkServer.bat

Script to stop the network server

ij,ij.bat

Interactive JDBC scripting tool

dblook,dblook.bat

Script to view all or part of the DDL for a database

sysinfo, sysinfo.bat

Script to display versioning information about the Java DB environment

NetworkServerControl,NetworkServerControl.bat

Script to execute commands on the NetworkServerControl API

ProcedureTo Configure Your Environment to Run Java DB Utility Scripts

  1. Ensure that the JAVA_HOME environment variable specifies the directory where the JDK is installed.

  2. Set the DERBY_HOME environment variable to point to the as-install/javadb directory.

See Also

For more information about these utilities, see the following documentation:

Configuring Access to the Database

After establishing the database, you are ready to set up access for Enterprise Server applications. Before an application can access a database, the application must get a connection. At runtime, the following sequence occurs 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.

    Using the JNDI name of the resource, the naming and directory service locates the JDBC resource. Each JDBC resource specifies a connection pool.

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

    Enterprise 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. After the database connection is established, the application can read, modify, and add data to the database.

    The application accesses 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 the application is finished accessing the database, the application closes the connection and returns the connection to the connection pool.

The following topics are addressed here:

Administering JDBC Connection Pools

A JDBC connection pool is a group of reusable connections for a particular database. Because creating each new physical connection is time consuming, Enterprise 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.

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:

ProcedureTo Create a JDBC Connection Pool

The remote create-jdbc-connection-pool command enables you 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 command option to specify user, password, or other connection information using the asadmin utility, or specify the connection information in the XML descriptor file.

When you are building the connection pool, certain data specific to the JDBC driver and the database vendor will be required. You can find some of the following specifics inConfiguration Specifics for JDBC Drivers:

Creating a JDBC connection pool is a dynamic event and does not require server 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.

  1. Ensure that the server is running.

    Remote commands require a running server.

  2. Create the JDBC connection pool by using the create-jdbc-connection-pool(1) command.


Example 5–3 Creating a JDBC Connection Pool

The following example command 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

Information similar to the following is displayed:


Command create-jdbc-connection-pool executed successfully.

See Also

To see the full syntax and options of the command, type asadmin create-jdbc-connection-pool --help at the command line.

ProcedureTo List JDBC Connection Pools

The remote list-jdbc-connection-pools command enables you to list all existing JDBC connection pools.

  1. Ensure that the server is running.

    Remote commands require a running server.

  2. List the JDBC connection pools by using the list-jdbc-connection-pools(1) command.


Example 5–4 Listing JDBC Connection Pools

The following example command lists the JDBC connection pools that are on localhost:


asadmin list-jdbc-connection-pools

Information similar to the following is displayed:


sample_derby_pool2
poolA
__TimerPool
DerbyPool
sample_derby_pool
Command list-jdbc-connection-pools executed successfully.

See Also

To see the full syntax and options of the command, type asadmin list-jdbc-connection-pools --help at the command line.

ProcedureTo Contact (Ping) a Connection Pool

The remote ping-connection-pool command tests 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 command before the application is deployed.

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.

  1. Ensure that the server is running.

    Remote commands require a running server.

  2. Ping a connection pool by using the ping-connection-pool(1) command.


Example 5–5 Contacting a Connection Pool

The following example command tests to see if the DerbyPool connection pool is usable:


asadmin ping-connection-pool DerbyPool

Information similar to the following is displayed if the connection pool is usable:


Command ping-connection-pool executed successfully

See Also

To see the full syntax and options of the command, type asadmin ping-connection-pool --help at the command line.

ProcedureTo Delete a JDBC Connection Pool

The remote delete-jdbc-connection-pool command enables you 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.

  1. Ensure that the server is running.

    Remote commands require a running server.

  2. Obtain the exact name of the JDBC connection pool that you are deleting.

    To list the existing JDBC connection pools:


    asadmin list-jdbc-connection-pools
    
  3. If necessary, notify users that the JDBC connection pool is being deleted.

  4. Delete the connection pool by using the delete-jdbc-connection-pool(1) command.


Example 5–6 Deleting a JDBC Connection Pool

The following example command deletes the JDBC connection pool named DerbyPool:


asadmin delete-jdbc-connection-pool jdbc/DerbyPool

Information similar to the following is displayed if the connection pool is usable:


Command delete-jdbc-connection-pool executed successfully.

See Also

To see the full syntax and options of the command, type asadmin delete-jdbc-connection-pool --help at the command line.

Administering JDBC Resources

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.

A JDBC resource is created by specifying the connection pool with which the resource will be associated . Unse 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:

ProcedureTo Create a JDBC Resource

The remote create-jdbc-resource command enables you 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 is used. 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.

  1. Ensure that the server is running.

    Remote commands require a running server.

  2. Create a JDBC resource by using the create-jdbc-resource(1) command.

  3. If necessary, notify users that the new resource has been created.


Example 5–7 Creating a JDBC Resource

The following example command creates a JDBC resource named DerbyPool:


asadmin create-jdbc-resource --connectionpoolid DerbyPool jdbc/DerbyPool

Information similar to the following is displayed if the connection pool is usable:


Command create-jdbc-resource executed successfully.

See Also

To see the full syntax and options of the command, type asadmin create-jdbc-resource --help at the command line.

ProcedureTo List JDBC Resources

The remote list-jdbc-resources command enables you to list the existing JDBC resources.

  1. Ensure that the server is running.

    Remote commands require a running server.

  2. List JDBC resources by using the list-jdbc-resources(1) command.


Example 5–8 Listing JDBC Resources

The following example command lists JDBC resources for localhost:


asadmin list-jdbc-resources

Information similar to the following is displayed:


jdbc/__TimerPool
jdbc/DerbyPool
jdbc/__default
jdbc1
Command list-jdbc-resources executed successfully.

See Also

To see the full syntax and options of the command, type asadmin list-jdbc-resources --help at the command line.

ProcedureTo Delete a JDBC Resource

This remote command enables you 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.

  1. Ensure that the server is running.

    Remote commands require a running server.

  2. Obtain the exact name of the JDBC resource that you are deleting.

    To list the existing JDBC resources:


    asadmin list-jdbc-resources
    
  3. If necessary, notify users that the JDBC resource is being deleted.

  4. Delete a JDBC resource by using the delete-jdbc-resource(1) command.


Example 5–9 Deleting a JDBC Resource

The following example command deletes a JDBC resource named DerbyPool:


asadmin delete-jdbc-resource jdbc/DerbyPool

Information similar to the following is displayed:


Command delete-jdbc-resource executed successfully.

See Also

To see the full syntax and options of the command, type asadmin delete-jdbc-resource --help at the command line.

Integrating the JDBC Driver

After setting up the connection pool and resources, integrate the JDBC driver in either of the following ways:

Configuration Specifics for JDBC Drivers

Enterprise Server is designed to support connectivity to any database management system by using a corresponding JDBC driver.

JDBC Drivers, Full Support

The following JDBC driver and database combinations have been tested and are supported for container-managed persistence:

To see the most current list of supported JDBC drivers, refer to the Sun GlassFish Enterprise Server v3 Prelude Release Notes.

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

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:

Java DB/Derby Type 4 Driver

The JAR file for the Java DB/Derby driver is derbyclient.jar. (Java DB is based upon Apache Derby.) 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:

JDBC Drivers, Limited Support

The following JDBC drivers can also be used with Enterprise Server, but have not been fully tested. Although Sun offers no product support for these drivers, Sun does offer limited support for the use of these drivers with Enterprise Server:


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 v3 Prelude Reference Manual.


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:

IBM Informix Type 4 Driver

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:

MM MySQL Type 4 Driver (XA Only)

The JAR file for the MySQLTM 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:

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:

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

The JAR file for the Oracle driver is ojdbc14.jar.


Note –

When using this driver, keep in mind that you cannot insert more than 2000 bytes of data into a column. To circumvent this problem, use the OCI driver (JDBC type 2).


Configure the connection pool using the following settings: