WebLogic connection pool: define data source in Sterling Order Management System Software

You can define a data source in Sterling™ Order Management System Software.

To define the data source name to Sterling Order Management System Software, add the following entry in the <INSTALL_DIR>/properties/customer_overrides.properties file (see Property file):


   jdbcService.<dbvendor>Pool.datasource=<datasource name>

Where <dbvendor> = db2 or oracle

For example: jdbcService.oraclePool.datasource=MetadataDS

Additionally, to use the WebLogic datasource, ensure that you set the -Dvendor JVM argument to weblogic. For example: -Dvendor=weblogic

Note: In a sharded environment, you must provide the Metadata shard’s datasource as the value.

At initialization, Sterling Order Management System Software uses the datasource name to find the connection pool in WebLogic. In the example above, the datasource name is MetadataDS.

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.

Initial capacity

Bear in mind the following guidelines when setting the initial capacity attribute:

You should set the initial capacity 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 initial capacity to a higher number if your system experiences frequent traffic bursts.
You may not want to set initial capacity to a very high number because both WebLogic and database server need to maintain a high number of connections. For example, assume you have 8 managed server instances, each with 15 execute threads. If you set initial and maximum connection at 17, WebLogic creates and maintain 136 database connections.

Note: The Oracle WebLogic Server Performance and Tuning manual (see Tune the Number of Database Connections) recommends setting the initial connection pool capacity equal to the maximum capacity. Unfortunately, if you to follow that recommendation, you can not determine the current pool usage levels since the pool usage would be equal to the initial and the maximum. As a result:

You can not determine if there is a connection leak - for example, if the current connection pool usage is higher than your work manager thread utilization.
You can not know if your current connection pool usage is close to the maximum capacity.

For that reason, we prefer to keep the initial capacity lower than the maximum capacity.

Maximum capacity

This attribute sets the maximum number of connections your pool can grow to within a single WebLogic Server instance. If you set this value to 27 and you have eight WebLogic Server instances, in theory, WebLogic could create up to 216 database connections.

Bear in mind the following guidelines when setting the maximum capacity 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.

Allow shrinking and shrink frequency

This attribute pair informs WebLogic to release inactive connections if they have been idle for the period as specified by "shrink frequency". This has the advantage of releasing resources both at the WebLogic and database server end.

Prepared statement cache size

This attribute tells WebLogic to create a cache for each database connection that can 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.