JBoss tuning: define data source in Sterling Selling and Fulfillment Foundation

To define the data source name to Sterling Selling and Fulfillment Foundation, add the following entry in the <INSTALL_DIR>/properties/customer_overrides.properties file:

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

Where <dbvendor> = db2 or oracle

For example: jdbcService.db2Pool.datasource=MetadataDS

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

At initialization, Sterling Selling and Fulfillment Foundation uses the datasource name to find the connection pool in JBoss. In the example above, the datasource name is MetadataDS.

Benchmark your application before migration to production to ensure that these values are set optimally. Also, you should 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 min-pool-size attribute:

We prefer to keep the min-pool-size attribute lower than the max-pool-size attribute.

Maximum pool size

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

Bear in mind the following guidelines when setting the max-pool-size attribute:

Prepared statement cache size

This attribute tells JBoss 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. To disable prepared statement caching, set the prepared statement cache size to 0. To use the cache, you can set to a higher value. You may not want to set the prepared statement cache size to a very high number. It can use a lot of heap space from your JVM heap as this value is set per datasource. This can cause considerable degradation in the throughput of the application. Also, if you are using the multi-schema feature, each Sterling Selling and Fulfillment Foundation transaction requires up to four connections and that would increase the usage a lot more. By default, the prepared statement caching is disabled.