Configuring Access to Database Systems

Overview

Before a server can connect to a database, the server must first receive information about the database. You supply database connection information by configuring a database alias. You must configure database aliases if you want to create database flow services.

This chapter describes what a database alias consists of and how to identify, update, and delete a database alias in the Integration Server Administrator.

Note: The WmDB package is not included in the main “Program” component of webMethods Integration Server. If you need this package, be sure to select WmDB on the list of installable components during installation.

Supported Databases

The webMethods Integration Server can connect to the following databases using WmDB:

  • Oracle 8i and 9i
  • SQL Server 2000
  • DB2 UDB 8.1
  • MS SQL 7
  • MS Access

WmDB connects to these databases using the JDBC driver provided by the vendor.

Important: The WmDB package does not support Sybase ASE 12.5.

Database Connection Information

A database alias consists of the following information:

  • The type of database and where it is located. JDBC uses a URL naming scheme to locate and identify databases. Each JDBC driver responds to a slightly different syntax for these URLs. Consult the documentation for the driver you are using for details. If you are using the JDBC-ODBC bridge driver, the URL is jdbc:odbc:your-datasource .
  • The user name and password required to connect to the database. If the database requires a user name and password to connect to it, you must specify the server with the user name and password it should supply.
  • The JDBC driver. You identify the fully qualified Java class name of the JDBC driver to use for this connection (for example, com.company.jdbc.Driver). This class must be in the server’s classpath.

The server uses the database connection information to connect to a database when:

  • You use the Integration Server Administrator to display information about database tables when creating a database service (Generate from table). The server must connect to the database to get information about the structure of the database.
  • The server executes a database service that was created using the Integration Server Administrator.
Note: For information about creating services that access a database, see Accessing Databases with Services.

About the Server Configuration File

The server configuration file (server.cnf), located in the Integration Server_directory \instances\instance_name\config directory, contains the parameters you will add to control the server connection to the database.

Note: Typically, you will use the Settings > Extended page from the Integration Server Administrator to update this file, but there might be times when you need to edit the file directly using a text editor. If you edit the file directly, you should first shut down the Integration Server before updating the file. After you make the changes, restart the server.

The following table describes the parameters that you must add to server.cnf to specify database connection settings:

This parameter... Specifies...
watt.server.db.connectionCache How the server manages connections to a database.
  • Specifying server tells the server to maintain a pool of connections for each database that is defined to the server through an alias. If a request cannot be satisfied because the pool has reached its maximum number of connections, the server blocks the request and tries again later.
  • Specifying session tells the server to treat requests from each service individually. That is, when it receives a request, the server immediately requests a connection from the database. If the database has no available connections, the request fails. This is the default.
Although enabling database connection pooling creates a pool for each database defined to your server, you can control the characteristics of each pool individually by using the Edit Alias Information page of the Integration Server Administrator. For more information, see Enabling Database Connection Pooling.
watt.server.jdbc.defaultDriver The name of the Java class for the driver you want to use to connect to databases when no driver name is supplied for a database alias. For more information, see Identifying Database Aliases.
watt.server.jdbc.driverList A comma-delimited list of JDBC drivers you want the server to load when it initializes. There is no default. For more information, see Preloading JDBC Drivers at Server Initialization.

Preloading JDBC Drivers at Server Initialization

About this task

You can have the server preload the JDBC drivers when it initializes. Loading the JDBC drivers at initialization increases the performance of the first run of the first database service that requires a specific JDBC driver. If the server does not have a JDBC driver loaded when a database service requires it, the server must load the JDBC driver when the database server executes. Also, if you preload JDBC drivers, the server displays the list of drivers in a drop down list on the pages that require a JDBC driver name. When the drop down list is available, you can select a driver from it rather than type the name of a driver.

To have the server load JDBC drivers when it initializes, specify a comma-delimited list of JDBC drivers that you want preloaded in the watt.server.jdbc.driverList field in the server.cnf file in the Integration Server_directory \instances\instance_name\config directory. If this field does not exist in the server.cnf file, you can add it. To change this setting use the Settings > Extended page from the Integration Server Administrator as described in Working with Extended Configuration Settings.

Making the Java Classes for JDBC Drivers Accessible

About this task

The server requires access to the Java classes for each JDBC driver that it is to use. You need to place the Java classes in a location that the server can access. Typically, you place the Java classes in the server’s classpath.

To place the classes in the server’s classpath, place the .zip or .jar file containing the classes in the Integration Server_directory \lib\jars\custom directory. If the jars or custom subdirectories do not exist, create them. The server will automatically add the .zip or .jar libraries to its classpath.

Note: If you want to make the Java classes available only to a specific Integration Server instance, copy the extracted .jar file into this location: Integration Server_directory /instances/instance_name/lib/jars/custom.

If a patch you are unzipping or “unjarring” overwrites existing classes, be sure to back up the existing classes in case the patch does not work.

Note: If you are using the Oracle JRE, the JDBC-ODBC bridge driver is sun.jdbc.odbc.JdbcOdbcDriver. An ODBC data source corresponding to your database must exist.

If the server does not have access to the Java classes for the JDBC driver, users receive the following error when the server attempts to connect to the database: 

Could not connect: No suitable driver

If the driver loads successfully but cannot connect to the database for some reason, the resulting error message usually includes some driver-specific codes or messages. The most common source of such errors is an invalid user name or password on the database. Consult your driver documentation for details.

Identifying Database Aliases

About this task

Identify a database alias to specify the connection information that the server must supply to connect to a database. Set up aliases if:
  • You want developers to be able to define database flow services using the webMethods Integration Server Administrator.
  • You want to maintain all database connection parameters in one place to allow for easier maintenance. For example, you can easily update the information if a password changes or a database moves.

To better manage requests for database connections, you can specify that the server use database connection pools. With database connection pools in effect, the Integration Server controls the number of database connections it makes to a database, preventing the database from exceeding its connection limit and rejecting requests. If a request exceeds a database connection pool’s limit, the server blocks it and tries the connection request again later.

See Enabling Database Connection Pooling for more information about how database connection pools work and how to set them up.

Note: Before you configure a database alias, make sure the WmDB package is enabled. For information about enabling packages, see Enabling a Package.

To identify a database alias

Procedure

  1. Open the Integration Server Administrator if it is not already open.
  2. Go to Adapters > WmDB > Alias Management.
  3. Click Add. The server displays the New DB Alias page.
  4. Set the new alias parameters as follows:
    For this parameter... Specify...
    Alias The alias name that you want to use for the database. You can specify any combination of letters, numbers, and the underscore character for an alias name; there is no restriction on the number or of characters you can specify.
    DB URL The URL for the database. For example: jdbc:odbc:Support.
    DB Username The user name that the server must supply to log on to the specified database. If a user name and password are not required, leave this field blank.
    DB Password The password that the server must supply to log on to the selected database. If a user name and password are not required, leave this field blank.
    DB Driver The name of the Java class for the JDBC driver.

    If the server has any drivers currently loaded, the server displays a Loaded Drivers field that has a drop down list containing the drivers that are loaded. If you select a driver from the list, the server places the driver name in the DB Driver field.

    If you do not specify a driver, the server uses the default driver specified in the watt.server.jdbc.defaultDriver parameter in the server.cnf file.

    To see the value of the default driver, use the Settings > Extended page from the Integration Server Administrator as described in webMethods Integration Server Administrator’s Guide.

    Important: Be sure the server has access to the Java classes for the driver. For more information about making the Java classes available, see Making the Java Classes for JDBC Drivers Accessible.

    If you are using the Oracle JRE on a Windows platform, the server has access to the Java classes for the JDBC-ODBC bridge driver named sun.jdbc.odbc.JdbcOdbcDriver.

    Minimum Connections The minimum number of connections to maintain in the database connection pool. The server creates these connections when it first receives a request to connect to the database. If more connections are needed, the server creates additional connections until the value for Maximum Connections, described below, is reached.

    The default is 0.

    Note: This field displays only if the server is configured for database connection pooling (that is, if the watt.server.db.connectionCache property in the server.cnf file is set to server).

    See Enabling Database Connection Pooling for more information.

    Maximum Connections The maximum number of connections to maintain in the database connection pool. When the number of connection requests reaches this value, the server blocks the requests.

    The default is 1.

    Note: This field displays only if the server is configured for database connection pooling (that is, if the watt.server.db.connectionCache property in the server.cnf file is set to server).

    See Enabling Database Connection Pooling for more information.

    Expiration Time (ms) The amount of time the server waits before discarding an inactive connection from the database connection pool. The server only discards connections if the pool contains more than the minimum number of connections. Afterwards, if more connections are needed than exist in the pool, the server creates new ones.

    The default expiration time is 60000 milliseconds.

    Note: This field displays only if the server is configured for database connection pooling (that is, if the watt.server.db.connectionCache property in the server.cnf file is set to server).

    See Enabling Database Connection Pooling for more information.

  5. Click Submit.

Enabling Database Connection Pooling

About this task

For greater scalability you can pool database connections. With this feature, the server creates a pool of connections for each database defined to the webMethods Integration Server. The server maintains these connections, creating and dropping them as needed.

The pool controls the number of connections a server can have to the database at any given time. For example, if your database allows 50 connections and you have five servers that connect to the database, you can limit each server’s database connection pool to 10. This way, the number of connections to the database never exceeds 50 and no requests will be rejected due to lack of available slots. If the number of connection requests to a server exceeds 10, that server will block the request and try it again later. The request will not be rejected.

Without database connection pooling, each server will obtain as many connections to the database as requested until the database reaches its limit of 50. Subsequent requests will be rejected by the database until the number of database connections drops down below 50.

When you enable database connection pooling, it applies to all databases defined to the server. In other words, the server will maintain a connection pool for each database for which an alias is defined. You can, however, control the characteristics of each pool individually. For example, you might specify more connections for a large, busy database than for a small, relatively quiet one.

Use the following procedure to enable database connection pooling for your server.

To enable database connection pooling

Procedure

  1. Open the Integration Server Administrator if it is not already open.
  2. Go to Settings > Extended. The server displays a page that lists configuration parameters specified in the server.cnf file.
  3. Do one of the following:
    • If the Extended Settings lists watt.server.db.connectionCache=server then connection pooling is already enabled for your server.
    • If the watt.server.db.connectionCache property is not set to server or does not appear in the Extended Settings list, click Edit Extended Settings and specify the following: 
      watt.server.db.connectionCache=server
  4. Click Save Changes.
  5. If you want to change the alias parameters for the minimum and maximum number of connections in the connection pool and the connection expiration time, see Updating Information for Database Aliases.
  6. Restart the server for the changes to take effect.

Updating Information for Database Aliases

About this task

If the connection information changes for a database, update the database alias information to identify the new connection information.
Note: This procedure assumes the database aliases have already been defined. If they have not, see Identifying Database Aliases for instructions.

To update information for a database alias

Procedure

  1. Open the Integration Server Administrator if it is not already open.
  2. Go to Adapters > WmDB > Alias Management.
  3. From the Current Data Sources field, select the database alias for the database you want to update.
  4. Click Edit. The server displays the Edit Alias Information page.
  5. Set the alias parameters. For information about what to specify for each of the fields, refer to the procedure in Identifying Database Aliases.
  6. Click Submit.
  7. If you changed the Minimum Connections or Maximum Connections alias parameters, restart the server for the changes to take effect.

Deleting Database Aliases

About this task

When you no longer need access to a database, you can delete the database alias for the database.

To delete a database alias

Procedure

  1. Open the Integration Server Administrator if it is not already open.
  2. Go to Adapters > WmDB > Alias Management.
  3. From the Current Data Sources list, select the database alias you want to delete.
  4. Click Delete. The server issues a prompt to verify that you want to delete the database alias.
  5. Click OK to delete the database alias.