Previous Contents Index Next |
iPlanet Application Server 6.5 SP1, Enterprise Edition Administrator's Guide |
Updated: June 02, 2003 |
Chapter 8 Administering Database Connectivity
iPlanet Application Server applications can access database(s), to add, retrieve, and modify data. This chapter describes how to configure data access drivers and apply settings to database connectivity parameters.
The following topics are included in this chapter:
About Data Access Drivers
iPlanet Application Server applications often require database access. Database access is achieved through a data access driver, software written either by the database vendor or a third-party vendor. The following types of data access drivers can be configured with iPlanet Application Server to provide database connectivity:
- ODBC
Make sure that data access drivers are installed before installing iPlanet Application Server. This way, iPlanet Application Server can automatically configure the drivers.
All third party JDBC drivers conforming to the JDBC 2.0 Extensions specifications are supported by iPlanet Application Server. If your database driver does not comply with the JDBC 2.0 Extensions framework, it will not work with iPlanet Application Server.
Configuring Third Party JDBC Drivers
Third party JDBC drivers need be identified to iPlanet either during application server installation, or via registration tools after installation. Registration must occur on each application server instance housing applications with third party JDBC driver datasources. For example, if you are configuring a two node cluster of iPlanet Application Server, and you are making an application available on both nodes in the cluster, then you must register the third party JDBC driver with each instance. (Existing iPlanet Type 2 drivers have the same requirement).
This section describes the following topics:
During Installation
You can configure the third party JDBC drivers only through the Custom installation option. If you use the Express or Typical installation, see the next section for configuring the third party JDBC drivers after installation of the application server.
During application server Custom installation, you can only choose to configure iPlanet Type 2, third party JDBC, or no JDBC drivers. Although you can only configure either the Type 2 driver or third party JDBC driver during installation, you can choose to configure both after installation.
After Installation
Configure third party JDBC drivers after installation through the iPlanet Application Server Administration Tool.
Registration of iPlanet Type 2 JDBC drivers for the supported database platforms is automatic since iPlanet automatically recognizes the presence of the supported native client libraries.
For more information on registering database drivers after installation, see iPlanet Application Server Administration Guide.
Setting up Third Party JDBC drivers
JDBC is the Javasoft specification of a standard API that allows Java programs to access Database Management Systems (DBMSs). The JDBC API consists of a set of interfaces and classes that can be used to perform the following procedures:
- Write applications and applets that connect to databases.
- Send queries written in Structured Query Language (SQL).
- Process the results.
You can configure native and third party JDBC drivers when you install iPlanet Application Server. If you want to do this after installation, use the iPlanet Application Server Administration Tool.
If you need to use command line tools to set up third party drivers, see Using Command Line Tools to Set up Third Party JDBC drivers.
Certified Third Party Drivers
The following table lists the third party JDBC drivers certified to work with iPlanet Application Server 6.5.
Table 8-1    Certified third party drivers on Solaris and Windows
Driver
Platform
To Set up Third Party JDBC Drivers
To configure third party JDBC drivers on Solaris and Windows, perform the following steps:
- Start the iPlanet Application Server Administration Tool.
- On Solaris, go to iASInstallDir/ias/bin
./ksvradmin
- on Windows, go to Start > iPlanet Application Server 6.5 > iAS Administration Tool.
The Administration Tool starts up.
- Select Database from the menu options.
- Click the + sign next to the registered iPlanet Application Server to see all the database options.
- Select External JDBC Drivers > click Add.
- Enter a Driver Name for the driver you are configuring, for example driver1.
The Driver Name is a logical name by which you identify the driver to iPlanet Application Server. This name is used to link datasource definitions back to a physical driver type. The name can be of any string value you choose. Examples include: driver1,ora-type4, ora-type2, and jconnect.
- Enter driver classpath, for example Oracle_Home/jdbc/lib/classes12.zip
The fully qualified path to the driver classes, JAR, or ZIP file. This zip file holds the library classes for the driver. Specify the complete path as shown in the following example:
usr/oraclient/jdbc/classes/lib/classes12.zip.
On Windows, the Driver Classpath will look like this: D:\orant\jdbc\lib\classes12.zip.
The following table gives typical Solaris and Windows CLASSPATHs:
Table 8-2    Typical Solaris and Windows CLASSPATHs
Driver
Solaris CLASSPATH
Windows CLASSPATH
- Enter Pooled Datasource Classname, for example oracle.jdbc.pool.OracleConnectionPoolDataSource
- Enter the XA Datasource Classname, for example oracle.jdbc.xa.client.OracleXADataSource
Note This is optional and need to be specified if you want to use global transactions.
- Click OK to complete the driver registration process.
To Modify JDBC Driver Settings
Follow these steps to modify driver properties from the Administration Tool:
- Select the driver you want to modify in the left pane of the Administration Tool.
- Click Modify in the right pane.
- Click OK to commit the changes.
Deleting a JDBC Driver
Follow these steps to delete a driver from the Administration Tool:
- Select the driver you want to delete in the left pane of the Administration Tool.
- Click Yes when you are asked to confirm the deletion.
Using Command Line Tools to Set up Third Party JDBC drivers
The following sections describe how to configure third party JDBC drivers on both Solaris and Windows machines:
- To Set up Third Party JDBC Drivers on Solaris Using db_setup.sh
- To Set Up Third Party JDBC Drivers on Windows Using jdbcsetup.exe
Note The dbsetup.exe utility on Windows has been deprecated and is no longer part of iPlanet Application Server installation.
To Set up Third Party JDBC Drivers on Solaris Using db_setup.sh
To configure third party JDBC drivers, perform the following steps:
db_setup.sh
- Go to <iasinstall>/ias/bin. Run the following script:
- Choose the driver you want to install. You can see the following options:
1. iPlanet Type 2 JDBC Drivers
2. Third Party JDBC Drivers
Type (2) to configure native JDBC drivers, and press Enter.
- Specify the directory on your machine in which iPlanet Application Server has been installed, for example, /usr/iplanet/ias6. Press Enter.
- Specify the number of drivers you want to configure and press Enter.
These drivers can be configured for the same or different database clients.
- Enter a Driver Name for the driver you are configuring, for example driver1.
The Driver Name is a logical name by which you identify the driver to iPlanet Application Server. This name is used to link datasource definitions back to a physical driver type. The name can be of any string value you choose. Examples include: driver1,ora-type4, ora-type2, and jconnect.
- Enter driver classpath, for example Oracle_Home/jdbc/lib/classes12.zip
The fully qualified path to the driver classes, JAR, or ZIP file. This zip file holds the library classes for the driver. Specify the complete path as shown in the following example:
usr/oraclient/jdbc/classes/lib/classes12.zip.
See Table 8-2 for typical Solaris classpaths.
- Enter Pooled Datasource Classname, for example oracle.jdbc.pool.OracleConnectionPoolDataSource
- Enter the XA Datasource Classname, for example oracle.jdbc.xa.client.OracleXADataSource
Note This is optional and need to be specified if you want to use global transactions.
- You will be prompted to set the environment variables for your driver in the iASInstallDir/ias/env/iasenv.ksh file after installation.
To Set Up Third Party JDBC Drivers on Windows Using jdbcsetup.exe
Perform the following steps to set up native or third party JDBC drivers on a Windows system:
- Navigate to the iASinstallDir/ias6/ias/bin path. Run the following script:
- Enter a Driver Identifier for the driver, for example drive2.
The Driver Name is a logical name by which you identify the driver to iPlanet Application Server. This name is used to link datasource definitions back to a physical driver type. The name can be of any string value you choose. Examples include: driver2, ora-type4, ora-type2, and jconnect.
- Enter the Driver class path, for example Oracle_Home/jdbc/lib/classes12.zip
The Driver class path is the fully qualified path to the driver classes, JAR, or ZIP file. This zip file holds the library classes for the driver. Specify the complete path as shown in the following example:
d:\oracle\jdbc\lib\classes12.zip.
SeeTable 8-2 for typical classpath examples.
- Enter the Pooled Datasource class name, for example oracle.jdbc.pool.OracleConnectionPoolDataSource.
- Enter the XA Datasource classname, for example Database.Jdbc.xa.client.DatabaseXADatasource. If your database is Oracle, type oracle.xa.jdbc.client.OracleConnectionXADataSource.
- Click Add to register the driver.
You can choose to configure more drivers or click Cancel to abort the process.
Click OK to commit the changes.
Registry Settings for Third Party JDBC Drivers
As you register third party JDBC drivers in iPlanet, you will see the following iPlanet Registry settings:
Driver Entry
SOFTWARE\iPlanet\Application Server\6.5\CCS0\DAE3\DRIVERS\<driver name>\
Under this key, you will find the driver's class name.
For more information registry key settings, see iPlanet Application Server Registry Guide.
Configuring Datasources
A datasource contains information pertaining to the database client that the database driver needs to know, to connect to the database. In iPlanet Application Server 6.5, Datasource is a factory class for getting JDBC connections. It supports both iPlanet native and third party JDBC drivers, as well as PooledDataSource and XADataSource of third party driver implementations.
The datasource provides wrappers to support pooled connections for drivers which only support driver manager.
Note You must first install and configure database driver(s) on your machine before adding a datasource.
The following topics are described in this section:
- To Add a Datasource to an External JDBC Driver
- To Add Datasource To a Native Database Driver
- To Add a Datasource Using Deploy Tool
- To Add a Datasource Using Command-line Tools
New Datasource Features
The following new datasource features are available in iPlanet Application Server 6.5:
- Provides connection sharing for JDBC connections used in transactions and provides mechanism to perform sanity check on connections being returned to the user. If a pool is corrupted, it is automatically destroyed and a new pool is created.
- Supports dynamic configuration for few datasource properties such as:
- tableName
Note iPlanet Application Server 6.0 SPx based datasource XML format has been deprecated. Use the Deployment Tool to migrate your old datasource XML files to the new one.
For more information in migrating datasource XML files to 6.5, see To Migrate 6.x Deployment Descriptor XML File.
Registering Datasources
Use a third party JDBC driver for datasource-based access in iPlanet Application Server for J2EE JNDI, connection pooling and, local and global transaction management capabilities.
Once you have registered a third party JDBC driver with the application server, you must define JDBC datasources for applications to be able to interact with your database management system.
This section describes the information required to register a JDBC datasource associated with a third party or native JDBC driver in iPlanet Application Server. Once you've registered a JDBC datasource in iPlanet, you can modify the connection pooling settings from the Administration Tool.
The following sections explain how you can add a datasource, to both external third party and native database drivers:
To Add a Datasource to an External JDBC Driver
To add a datasource to an external JDBC driver, such as a Type 4 JDBC driver, perform the following tasks:
- In the iASAT toolbar, click Databases.
The database connectivity settings that have been set for the machine are displayed.
- Select the External JDBC Datasources node , as shown in the following figure:
- Click Add in the right pane of the window. The Datasource Registration window appears, as shown in the following figure:
When you register the datasource, a node is created in iASAT with this name.
- JNDI Name
The JNDI Name uniquely identifies the datasource within the JNDI namespace of the application server. For example, a JNDI name of dbc/estore/EstoreDB would be referenced in ias-web.xml and ias-ejb-jar.xml files <resource-ref> entries of J2EE applications.
The <resource-ref> entries map resource names used by J2EE applications to JNDI names defined within the application server.
Note The JNDI name should be unique for each datasource that you add.
- Driver Type
The driver-type maps to the logical name assigned to a third party JDBC driver.
- Driver Name
A logical name by which you identify the driver to iPlanet. This name is used to link datasource definitions back to a physical driver type. The name can be of any string value you choose. Examples include: "ora-type4", "ora-type2", and "jconnect".
- Username
The username is supplied to the RDBMS when iPlanet makes a connection to the database. Username and password are optional, since they can be supplied either programmatically, or on the connection URL.
- Password
The password is supplied to the RDBMS when iPlanet ApplicatioN Server makes a connection to the database.
- Under Vendor Specific properties, provide the JDBC driver vendor specific data, for example, Property Name as URL and Property Value in the database URL format.
- Click OK to commit the vendor property value.
- Click OK (in the main screen) to complete datasource registration.
To Add Datasource To a Native Database Driver
To add a datasource to a Native database driver, such as a Type 2 JDBC driver, perform the following tasks:
- Start iASAT. On Windows, from the Start menu, choose Programs>iPlanet Application Server 6.5>iPlanet Application Server Administration Tool.
On Solaris, navigate to <iASInstallDir>/ias/bin/ and type the following command:
kvsradmin
- In the iASAT toolbar, click Databases.
- In the left pane of the window, select the iPlanet Application Server instance for which you want to add a datasource. You can see all the database connectivity settings that have been set for that machine.
- Select the iPlanet Type 2 JDBC Datasources folder, as shown in the following figure:
- To add a datasource, click Add in the right pane of the Databases window.
- In the JNDI name field, enter a name for the datasource, for example, BankDB. When you register the datasource, a node is created with this name.
Note The JNDI name should be unique for each datasource that you add.
- In the Driver Type drop-down list, select the driver for which you want to add a datasource.
Note For a native driver, you need to install the database client on your machine.
- In the DataSource field, enter the relevant value depending on the database client for which you are adding a datasource. The database connects to the driver using this value. The following table provides the values for each database type that iPlanet Application Server supports:
- In the DataBase field, provide the name of the database, for example, Oracle. For Sybase, the database name should be prefixed with the server name, in order to connect to multiple sybase servers.
- In the Username field, enter the user name that was provided when the database client was installed.
- In the Password field, enter the password for that user name.
- Click OK to register the datasource. You need to stop and start iPlanet Application Server, for the changes to take effect. See , for more information on starting and stopping iPlanet Application Server.
To Add a Datasource Using Deploy Tool
Follow these steps to register a datasource using the Deploy Tool:
- Start the Deployment Tool (deploytool)
- Go to Tools->Register Datasource.
The following dialog box appears:
Figure 8-1    Registering datasources through Deploy Tool
- Enter the database driver parameters, connection pooling parameters, and connection sanity parameters.
For detailed explanation of these fields, see Datasource Configuration Parameters.
- Click Save As.
Enter a name for the new datasource deployment descriptor.
- Select Register to register the datasource with one or more application server instances.
To Migrate 6.x Deployment Descriptor XML File
iPlanet Application Server 6.5 contains enhancements to the database connection infrastructure. To take advantage of the new features and to continue to use you older datasource configuration you will have to migrate to the new datasource XML DTD.
- Choose Tools > Register Datasource.
The datasource registration dialog will appear.
- Click Open > select the XML file which contains the datasource descriptions.
The Deployment Tool will import the values present in the old XML file and add default values for the new fields available in 6.5. in the XML file.
- Accept the default values or modify them for your datasource.
- Enter the database driver parameters, connection pooling parameters, and connection sanity parameters.
For detailed explanation of the fields, see Datasource Configuration Parameters.
- Click Save.
The Deployment Tool will overwrite the datasource deployment descriptors with the values you have specified, in the file you have opened.
Note: Select Save As to save the updated XML file with another name, and in a different location.
- Click Register.
The Deployment Tool will update the datasource deployment descriptors with the values you have specified. Now your datasource will be able to take advantage of the new features in iPlanet Application Server 6.5.
To Add a Datasource Using Command-line Tools
Use the iasdeploy and regdatasource command line tools to register a datasource.
The iasdeploy tool is recommended for command line registration of datasources. Example:
iasdeploy resreg EstoreDB.xml
If you are registering against a local server, you can use the resreg command:
resreg EstoreDB.xml
Datasource Configuration Parameters
These parameters are based on the JDBC 2.0 specification. Most of the properties provided under these parameters are optional.
The following tables describe the various parameters on the datasource properties' screen.
Configuration Parameters
Description
Default Values
The number of connections obtained at the pool creation time.
The number of physical connections the pool should keep irrespective of the maxIdleTime set.
The minPoolSize criteria will be ensured once the pool reaches the minPoolSize.
The initialPoolSize could be less than minPoolSize as the minPoolSize criteria will be applied only when the pool reaches the minPooSize number.
The maximum number of physical connections that the pool should contain.
Once the pool reaches the maxPoolSize, and if the waitQueue is enabled, then the connection requests will be queued. If not, an SQLException will be thrown.
The number of physical connections obtained when there are no free connections available in the pool and user requests for a connection.
If the Pool has already reached its maxPoolSize, then SQLException will be thrown.
The time, in seconds, that a physical connection should remain unused in the pool before the connection is closed.
This criteria will be applied only if the number of unused, or free connections in the pool, are more than minPoolSize.
The time till when the pool will reclaim a connection used by an application, in seconds.
Reclaim Time comes into picture when an application acquires a connection but forget to close the connection. If the application forgets to close the connection, then the connection pool forcefully closes the connection.
By default, the value is set very high so that legitimate programs are not effected.
Therefore, Reclaim Time should be used only for those buggy applications which does not close the connection explicitly by calling connection.close.
The time for which the pool will keep connections in the queue till a connection is available to the database, in seconds.
The number of connections that will be maintained in the queue.
Enables the collection of statistics about the connection pool.
If Trace is enabled, then both current and cumulative statistics are written to the KJS log when the connection pool cleanup thread is executed.
For more information, see "Datasource Monitoring".
Enables the tracing of connection pool properties. The Trace option writes whatever function the connection pool is currently performing to KJS logs. The messages are self-explanatory.
Trace should be enabled to output current and cumulative statistics to KJS logs.
Connection Sanity Parameters
Table 8-6    Datasource connection sanity parameters
Configuration Parameters
Description
Default Values
Apart from dynamically changing all pool configuration parameters, you can also modify pool configuration parameters for multiple datasources simultaneously. For more information on modifying the datasource properties, see "Modifying Third Party Datasources".
For more information on the parameters in this dialog box, please see the JDBC 2.0 Extensions specification.
The following table lists a few examples of database URL formats:
Datasource Registry Settings
As you register datasources associated with third party JDBC drivers, you will see the following iPlanet Registry settings under
SOFTWARE\iPlanet\Application Server\6.5\DataSource\<jndi-name>\.
Modifying Third Party Datasources
Use iASAT to modify the various parameters associated with the registered datasources. The following parameters can be modified for each datasource:
You can choose to modify either a single datasource at one time or, multiple datasources simultaneously. The option to select and modify multiple datasources at once allows you to set global connection pooling parameters for the selected datasources.
The following section describes how to modify datasources - either a single one or multiple datasources at the same time.
To Modify a Single Datasource
Follow these steps to modify a single datasource.:
- From the iASAT menu bar, select Databases.
- In the left pane, select External JDBC Datasource.
- Select the datasource you want to modify in the DataSource Selection Box.
- You can modify the properties that are available in the right pane.
- After completing the modifications, click Apply Changes to save the changes.
To Modify Multiple Datasources
By modifying multiple datasources, you can set global connection pool parameters for a number of datasources.
Follow these steps to modify multiple datasources.
- Follow the first two steps from "To Modify a Single Datasource".
- Select the datasources you want to modify by pressing down the Shift key.
You will notice that the Datasource Parameters entries have been greyed out.
When you select multiple datasources, only the Connection Sanity Parameters and Connection Pool Parameters can be modified.
Note When you select multiple datasources, the Connection Pool Parameters are returned to their default values.
- Make the changes you want to the Connection Pool Parameters that will be common to the selected datasources.
- After you have finished making the changes, click Apply Changes to save the changes.
Datasource Monitoring
iASAT provides the functionality to collect statistics of all the active connection pools.
To view the statistics for a particular datasource, select the datasource in the left pane of iASAT. If multiple KJS' are configured, choose the KJS for which you want to view the statistics from the Engine ID drop-down box.
Administrators can monitor, configure, and manage the connection pools created for a particular datasource. The admin tool provides options to enable and disable pool statistics. Statistics can be enabled from this screen, as well as from the main datasource panel.
It provides two types of statistics for pools:
Current Statistics
The administration tool displays current statistics for the selected datasource irrespective of whether the Enable Stats check box is enabled.The parameters monitored under Current statistics are:
Cumulative Statistics
Cumulative statistics provides information on the connection pool from the time Enable Stats is enabled. To view cumulative statistics from the admin tool, both Stats and Trace needs to be enabled. The output is written to the KJS log when the connection pool cleanup thread is executed.
Note Enabling Stats alone does not produce any output in the KJS logs, Trace must also be enabled. When Stats and Trace is enabled, both current and cumulative statistics are written to the KJS logs.
The value displayed for each parameter is a cumulative value of all the pools created for that datasource. The parameters monitored under cumulative statistics are:
- Peak value for total connections in Pool.
- Connections obtained after wait.
- Connections dropped after wait.
- Connections released after idle time.
Statistics in Detail
You can view all the above mentioned parameters in detail for each pool by clicking on the Detail button. This option, apart from the above parameters, shows the pool name and pool type (XA, Normal).
The other options that the admin tool provides are:
Destroy Pool
You have the option to destroy the connection pool by selecting the Destroy button. The Destroy pool option will remove all the connections from the pool.
Flush Pool
You have the option to flush the connection pool by selecting the Flush button. The Flush pool option will remove all the idle connections from the pool.
To Delete a Datasource
Follow these steps to delete a datasource:
- From the iPlanet Application Server Administration Tool select the datasource you want to delete.
- Press the Delete button on the Keyboard.
- Click Yes when asked for confirmation.
When a datasource is deleted from the Administration Tool, the corresponding datasource object is also deleted from the initial context.
Datasource XML Format
The new XML format provided in iPlanet Application Server 6.5 is flexible and supports standard JDBC datasource properties and driver specific datasource properties. Old XML formats used by iPlanet Application Server 6.0 SPx for datasource registration are automatically converted to the new XML format. The converted XML file is stored as xmlfilename.new.
The following is an example of the new XML datasource format:
Code Example 8-1    New datasource XML format <jndi-name>jdbc/dsName</jndi-name>
<database-url>jdbc:oracle:oci8:@rcl</database-url>
<driver-name>oracle_xa</driver-name>
Add extra data here, for example, port number <port>1050</port> <initialPoolSize>1</initialPoolSize>
<waitQueueEnabled>true</waitQueueEnabled>
<reclaimTime>1200</reclaimTime>
<maxPoolSize>abc</maxPoolSize>
<maxIdleTime>120</maxIdleTime>
<waitTimeInQueue>120</waitTimeInQueue>
<incrementPoolSize>1</incrementPoolSize>
Examples for other database vendors are available in iPlanet Application Server Developer's Guide.
Previous Contents Index Next
Copyright © 2002 Sun Microsystems, Inc. All rights reserved.
Last Updated June 02, 2003