Software AG Products 10.11 | Administering Integration Server | Connecting Integration Server to Database Components in an External RDBMS | Creating a Connection Pool
 
Creating a Connection Pool
You can create a connection pool by copying an existing pool using Copy a Pool Alias Definition or by specifying new field values as instructed in this section.
*To create a connection pool by specifying new field values
1. Open the Integration Server Administrator if it is not already open.
2. Go to Settings > JDBC pools.
3. Click Create a Pool Alias Definition, and complete the fields.
Parameter
Specify
Alias Name
Name to use for the connection pool. The name can include any characters that are valid for a file name in your operating system.
Alias Description
Brief description of the pool.
Associated Driver Alias
Database driver for Integration Server to use to connect to the pool.
Database URL
URL for the database server.
*Use the DataDirect Connect connection option MaxPooledStatements=35 on all database URLs except those for Trading Networks. This connection option improves performance by caching prepared statements. (Trading Networks caches its prepared statements using its own pooling mechanism).
*For DB2, if Integration Server will connect to a schema other than the default schema for the specified database user, you must specify these connection options in the URL:

;AlternateId=schema;"InitializationString=
(SET CURRENT PATH=current_path,schema)"; MaxPooledStatements=35
AlternateID is the name of the default schema that is used to qualify unqualified database objects in dynamically prepared SQL statements.
*For MySQL Community Edition, you must specify connection options for relaxAutoCommit, useLegacyDatetimeCode, and serverTimezone parameters in the URL. For example, you can provide the connection options as follows:

jdbc:mysql://<server>:<3306|port> /databaseName?
relaxAutoCommit=true& useLegacyDatetimeCode=false& serverTimezone
Note:
MySQL Community Edition 8.x does not support the relaxAutoCommit and useLegacyDatetimeCode parameters in the URL because they are deprecated.
*For Tibero, specify: jdbc:tibero:thin:@<server>:<8629|port>:databaseName
Diagnostics
DataDirect diagnostic feature for DataDirect Connect JDBC drivers used by Integration Server to interact with an external database.
Option
Description
Spy
Enables the DataDirect Spy diagnostic feature for DataDirect Connect JDBC drivers. DataDirect Spy logs JDBC calls and SQL statement interactions between Integration Server and an external RDBMS.
Snoop
Enables the DataDirect Snoop tool for DataDirect Connect JDBC drivers. The DataDirect Snoop tool logs network packets between Integration Server and an external RDBMS. You can use the resulting log file for tracing and diagnostic purposes.
Spy Attributes
Name and location of the log file where Integration Server will log diagnostic data collected by the DataDirect Spy diagnostic feature. This value also defines DataDirect Spy attributes. The default value is:

SpyAttributes=(log=(file)/logs/spy/<alias_name>.log; logTName=yes;timestamp=yes)
Where <alias_name> is the name of the JDBC connection pool alias.
Note:
Typically, you do not need to change the default value. However, you can modify the value if the attributes do not meet the needs of your system. Be aware that the diagnostic tool collects data from the Integration Server_directory /instances/instanceName/logs/spy directory. If you change the log file location, the diagnostic utility might not import the data logged by DataDirect Spy. For more information about using the diagnostic utility, see Using the Diagnostic Utility. For more information about setting DataDirect Spy attributes, consult the documentation on the DataDirect website.
Note:
MySQL Community Edition does not support this option.
Snoop Logging Parameters
Name and location of the log file where Integration Server will log diagnostic data collected by the DataDirect Snoop tool. This value also defines DataDirect Snoop tool attributes. The default value is:

ddtdbg.ProtocolTraceEnable=true;ddtdbg.Protocol TraceMaxline=16;ddtdbg.ProtocolTraceLocation=/logs/snoop/ alias_name.log;ddtdbg.ProtocolTraceShowTime=true
Where alias_name is the name of the JDBC connection pool alias.
Note:
For DB2, include the following command at the end of the value:
ddtdbg.ProtocolTraceEBCDIC=true
Typically, you do not need to change the default value. However, you can modify the value if the attributes do not meet the needs of your system. Be aware that the diagnostic tool collects data from the Integration Server_directory /instances/instanceName/logs/snoop directory. If you change the log file location, the diagnostic utility might not import the data logged by the DataDirect Snoop tool. For more information about using the diagnostic utility, see Using the Diagnostic Utility. For more information about setting the DataDirect Snoop tool attributes, consult the documentation on the DataDirect website.
Note:
MySQL Community Edition does not support this option.
User ID
Database user for Integration Server to use to connect to the database.
Password
Password for Integration Server to use to connect to the database. If no password is required, leave this field blank.
Minimum Connections
Minimum number of connections the pool must keep open at all times.
If multiple database components use this pool, each pool instance keeps the specified number of connections open. For example, if the ISCoreAuditLog and the DocumentHistory database components both use this pool, and you specify keeping at least 3 connections open, the pool keeps a total of 6 connections open - 3 for the ISCoreAuditLog pool instance and 3 for the DocumentHistory pool instance.
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.
Maximum Connections
Maximum number of connections the pool can have open at one time. When the number of connection requests reaches this value, Integration Server blocks the requests.
Calculate this value as part of the total possible number of connections that could be opened simultaneously by all functional aliases 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.
To continue the example described for Minimum Connections, if Trading Networks also writes to the database and has a pool that could open up to 5 connections, you could specify only 17 as the maximum number of connections for the current pool. The ISCoreAuditLog pool instance could use up to 17 connections, and the DocumentHistory pool instance could use the remaining 5 connections.
Available Connections Warning Threshold
Number of connections, expressed as a percentage of Maximum Connections, that should be available in the pool at all times.
When the number of connections falls to or below this number, Integration Server logs a message to the server log. If the number of connections later rises above this number, Integration Server logs another message to the server log stating that the connection pool threshold has been cleared. To disable this threshold, set the value to 0.
Waiting Thread Threshold Count
Maximum number of requests for connection that can be waiting at one time.
When this number is exceeded, Integration Server logs a message to the server log and starts a 5-minute interval timer. If the number of requests still exceeds this number at the end of the interval, Integration Server logs another message to the server log. To disable this threshold, set the value to 0.
Idle Timeout
Length of time, in milliseconds, the pool can keep an unused connection open. After the specified period of time elapses, the pool closes unused connections that are not needed to satisfy the Minimum Connections value. The default expiration time is 60000 milliseconds.
4. Make sure Integration Server can connect to the database by clicking Test Connection.
5. Click Save Settings.
6. Check the values for the connection pool for the ISCoreAudit database component. If the database user specified in the User ID field is not the database user that created the ISCoreAudit database component, set the watt.server.audit.schemaName property to the name of the schema that contains the ISCoreAudit database component (see Server Configuration Parameters).