Define a connection pool in WebSphere
You can define new connection pools in WebSphere®.
In the WebSphere administrative console, create a new connection pool with the following attributes.
| Attribute | Value |
|---|---|
| Minimum pool size | Initial number of connections to create for the connection pool. If the pool is allowed to shrink, it does not shrink below this number. See below for recommendations. |
| Maximum pool size | Maximum number of connections that can be created for this pool. See below for recommendations. |
| Statement cache size | The maximum number of prepared statements to cache for the data source. |
IBM® recommends that you benchmark your application before migration to production to ensure that these values are set optimally. IBM also recommends that you continually monitor the connection pool usage levels to ensure that these parameters are set optimally.
Minimum pool size
Bear in mind the following guidelines when setting the minimum pool size attribute:
- You should set the minimum pool size to satisfy your daily average connection requirements. This level can be derived by monitoring your actual pool usage levels.
- You may want to set the minimum pool size to a higher number if your system experiences frequent traffic bursts.
- You may not want to set minimum pool size to a very high number because both WebSphere and the database server needs to maintain a high number of connections.
Maximum pool size
This attribute sets the maximum number of connections the pool can grow to within a single WebSphere instance. If you set this value to 20 and you have ten WebSphere instances, in theory, WebSphere could create 200 database connections.
Bear in mind the following guidelines when setting the maximum pool size attribute:
- If you are not using the sharding feature, each Sterling™ Order Management System Software transaction generally requires one connection. Therefore, you need one connection per active thread. In practice, set the maximum capacity to be around the active thread count plus a small number (for example, 2 or 5) for a safety buffer.
- If you are using the sharding feature, you can configure a separate connection pool for each of the metadata, configuration, transaction, master and statistics shards. Doing so may provide you some flexibility, albeit with more management overhead. Alternatively, you can assign the shards to shared connection pools to reduce the number of connection pools. It is recommended to set the maximum capacity to be equal to the active thread count plus a small number (for example, 2 or 5) for a safety buffer.
- Monitor your application in production to confirm that the ratio of connection usage follows the guidelines mentioned.
- Benchmark your application to see if custom code, user exits, and so forth require additional connections.
Statement cache size
This attribute tells WebSphere to create a cache at the data source level to store prepared statements up to the value specified.
Prepared statements are precompiled SQL statements that can be repeatedly invoked with different parameter values. Prepared statements reduce the need to compile the SQL statements.
To disable prepared statement caching, set the prepared statement cache size to 0. To use the cache, you can set a value to a higher value.