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.

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.

Choose the type of database.

Oracle

DB2 for Linux, UNIX, Windows

SQL Server

MySQL Enterprise 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

Enter the URL to the database schema that contains one or more database component sets. Below are sample formats.

Oracle sample format:

SQL Server sample format:

DB2 UDB sample format:

For DB2, you must add the following additional connection parameters:

Here is the sample format with the two additional options:

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 ...]:

MySQL sample format:

MySQL Community Edition sample format:

For MySQL Community Edition, you must add the following required connection parameters:

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.

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.

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.

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:

PostgreSQL sample format:

Specify the database user who will communicate with the database.

Set a password for the database user.

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 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.

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.

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.

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.

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.

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.