Optimize 10.7 | webMethods Optimize Documentation | Configuring BAM | Database Pool Configuration | Configuring a Database Pool
 
Configuring a Database Pool
*To configure database pools
1. In My webMethods, click Navigate > Applications > Administration > System-Wide > Environments > Database Pool Configuration.
2. Click Add Pool.
The Database Pool Configuration page displays fields for defining pool information, database connections, and pool settings.
3. In the Pool Information panel, complete the fields that are described in the following table.
Field
Entry
Name
Enter a name, or alias, for the connection pool in the Name field. The alias can include only characters that are valid for a file name in your operating system.
If you are defining the four database pools for Optimize, you might enter their aliases as Analysis, ProcessTracker, ProcessAuditLog,and MywebMethodsServer.
Description
Describe the connection pool.
In this box you could describe the purpose of the database connection pools and the location of the database schema and the Optimize database activity or activities that the pool is connecting.
4. In the Database Connection panel, complete the fields that are described in the following table.
Field
Entry
RDBMS
Choose the type of database.
Value
RDBMS
Oracle
Oracle
DB2
DB2 for Linux, UNIX, Windows
SQL Server
SQL Server
MySQL
MySQL Enterprise Edition
MySQL Community Edition
MySQL Community Edition
If you want to use a MySQL Community Edition database, you must install a JDBC Driver for MySQL Community Edition 5.7. For more information about installing a JDBC Driver for MySQL Community Edition 5.7, see Installing a JDBC Driver for MySQL.
PostgreSQL
PostgreSQL
URL
Enter the URL to the database schema that contains one or more database component sets. Below are sample formats.
Oracle sample format:
jdbc:wm:oracle://host_or_IPaddress:port;
serviceName=database_name
SQL Server sample format:
jdbc:wm:sqlserver://host_or_IPaddress:port;
databaseName=database_name;SelectMethod=cursor
DB2 UDB sample format:
jdbc:wm:db2://server_name_or_IP_address:port;
DatabaseName=database_name[;connection_option=value ...]
For DB2, you must add the following additional connection parameters:
AlternateId=schema;showSelectableTables=false
Here is the sample format with the two additional options:
jdbc:wm:db2://server-name-or-IPaddress:port;
(DatabaseName=databasename|LocationName=location-name)
[;AlternateID=schema;showSelectableTables=false]
AlternateId is the name of the default schema that is used to qualify unqualified database objects in dynamically prepared SQL statements. The schema parameter must be capitalized.
If you are installing to a schema other than the default schema for the specified database user, you must also add this option as [;connection_option=value ...]:
InitializationString="SET CURRENT PATH=schema"
MySQL sample format:
jdbc:wm:mysql://server-name-or-IPaddress:port
databaseName=database-name
[;connectOption=value ...]
MySQL Community Edition sample format:
jdbc:mysql://server-name-or-IPaddress:port/database-name
[;connectOption=value ...]
For MySQL Community Edition, you must add the following required connection parameters:
relaxAutoCommit=true
The relaxAutoCommit parameter prevents exceptions in the Connection.commit() and rollback() methods when the connection object is in autoCommit mode. If the relaxAutoCommit parameter is not specified, Analytic Engine does not start.
useSSL=false
By default, SSL connection is enabled. You must either provide truststore for server certificate verification, or set the useSSL parameter to false to disable the SSL connection.
useLegacyDatetimeCode=false
The useLegacyDatetimeCode parameter forces the driver to consistently convert the time zones of the server and the client.
For JDBC Driver for MySQL versions later than 5.1, the useLegacyDatetimeCode parameter is set to false by default.
serverTimezone=GMT
To ensure that the server is running in a time zone that does not use daylight saving time, set the value of the serverTimezone parameter to GMT.
Following is the sample format with the required parameters:
jdbc:mysql://server-name-or-IPaddress:port/
database-name?relaxAutoCommit=true&useSSL=false
&useLegacyDatetimeCode=false&serverTimezone=GMT
PostgreSQL sample format:
jdbc:wm:postgresql://server-name-or-IPaddress:port;
DatabaseName=database-name
[;connectOption=value ...]
Database User
Specify the database user who will communicate with the database.
Database Password
Set a password for the database user.
After you complete all of the fields in the Database Connection panel, you might use the Test button to test the database connection .
5. In the Pool Settings panel, inspect the default pool settings and make changes if needed, as described in the following table.
Field
Entry
Minimum Connections
Minimum number of connections that the connection pool must keep open at all times.
If you use this pool alias for more than one function, each connection pool instance keeps the specified number of connections open.
If your logging volume has sudden spikes, you can improve performance by making sure the connections needed to handle the increased volume open quickly. You can minimize connection startup time during spikes by setting this value higher, so that more connections remain open at all times.
The default value for minimum connections is 8.
Maximum Connections
Maximum number of connections that the connection pool can have open at one time.
Calculate this value as part of the total possible number of connections that could be opened simultaneously by all functions and applications that write to the database. Make sure the total number does not exceed the database's connection limit. If one of the applications opens more connections than the database allows, the database will reject subsequent requests for connections from any application.
The default value for maximum connections is 60.
Idle Connection Timeout
Period of time, in seconds, that the connection pool can keep an unused connection open. After the specified period of time, unused connections that are not needed to satisfy the Minimum Connections value are closed.
If your logging volume has sudden spikes, you can improve performance by making sure the connections needed to handle the increased volume open quickly. You can minimize connection startup time during spikes by setting this value higher, so that more connections remain open at all times.
The default value for the idle connection timeout is 20 seconds.
Ramp-up Delay
Period of time, in milliseconds, that the connection pool can wait between opening new connections.
When the connection pool is started, the pool attempts to create the connections needed to achieve the specified minimum connections. For an environment in which several connection pools start up at the same time and share the same database, the database server can be overwhelmed with requests. Even though the database may have the capacity, the database listener can be overwhelmed by the sudden volume of requests. Use this parameter to force a connection pool to wait after a connection is created.
The default value for the ramp-up delay is 0.
Connection Tries
Maximum number of times that the pool can attempt to open a connection.
If a connection cannot be opened, the pool writes an exception to the log.
Both the minimum value and the default value for connection tries is 1.
Retries Backoff
Period of time, in milliseconds, that the connection pool can keep trying after an unsuccessful attempt to open a connection.
The default value is 0. Set the value to a whole number greater than 1.
Note that during ramp-up, if a connection try is unsuccessful, the Retries Backoff value replaces the value set for Ramp-up Delay; the two values will not be aggregated.
Allow Statement Caching
This optional parameter specifies the maximum number of SQL statements to store in the cache for each connection.
Enabling this option can improve performance because an application that executes the same statement repeatedly will not have to be recompiled for each execution. Instead, the application re-uses the statement from the cache. When the application returns the connection, the cache is purged.
To enable statement caching, check the Allow Statement Caching check box and enter a whole number in the Max per Connection field.
6. Click Save.
The connection pool appears in the JDBC Pools list on the Database Pools Configuration page.
7. Repeat the preceding sequence of steps for each new database connection pool you want to configure.
To update or edit a connection pool, click the name of the pool in the JDBC Pools list or click the Edit icon (Edit icon) that appears beside the name of the pool.
Note:
You cannot change the Name of an existing database pool. When you edit the Name of an existing database pool and save the changes, Optimize does not update the existing database pool, but creates a new one.
To associate logical servers to connection pools, use the Map DB Pools tab in webMethods Central Configurator. For more information, see Managing webMethods Optimize Environments.