How many JDBC connections do we create?


We have over 100+ integrations with one Oracle system. A dedicated IS will be used only for this Oracle facing. Consider 20% complex integrations. All the integrations are small to medium size from the data size perspective. While creating JDBC connection for this oracle system, I can think of 3 options.

Option 1: Create ONLY one JDBC connection for this Oracle system.
Adv: Simple; low effort; easy maintenance.
Shortcomings: Tuning the connection settings will be a great challenge. Non-availability of connections for individual integrations hence potential delay in processing integrations.
Option 2: Create one JDBC connection for each integration.
Adv: Based on the connections settings, we can control the processing of individual integrations depending on their processing time/priority.
Shortcomings: Huge dev & maintenance effort
Option 3: Create one JDBC connection for each vertical (e.g. Order Mgmt, Shipping, Inventory, Finance)
Mix of both option 1&2.

Looking for the best practice/recommendation in regards to how many JDBC connections do we create in this scenario (which option to go).

Note: We will also have an IS cluster to handle these integrations.

Thanks in advance,

One thing beforehand: I can’t give you a dedicated advise remotely, because the best solution depends on your local environment and needings.

Sayd that, I personally tend to prefer solution 3.

Things to consider:

  • Having onlyy one solution makes the maintenance of the connection data easier, but beside reducing the possibility to tune it also makes diagnosis of problems more difficult. If you use several connections with different users, diagnosis is much easier, because your dba’s can identify processes easier. Overall the advanatages of having different connections even outweight the effort of the maintenance.
  • Talk to your DBA’s about there preferences, especially because of the previous point.
  • The connections should be oriented on the logic of your integration on the database side, not the remote systems beeing delivered. Quite often Integrations are seen still in a point to point view, leading the connections set up per remote system. But this should not be the idea an integration with webMethods (or any other integration platform) should be made. Remote systems should be transparent to the connection setup you choose.
  • For deployment: I propose using deployer with substitutions in th deployment maps to make the maintenance effort in changes lower. it is also a good idea to separate the connections in a separate package alone, so with deployment of changed services you do not risk to overwrite the connection data.

Thanks mwroblinski for your inputs. I’ll keep in mind your suggestion.

good points from mwroblinski…

but connections can be kept in one single package for easy maintenance… of course, when we use deployer, we are not supposed to write the entire package rather choose only the connection that needs to be deployed…

advantage: connections, canonical documents, utilities can be maintained in separate packages… would be easier to move to next clustered environment as they are common across all servers…

in your case, if you are choosing 3rd option as suggested, then you can think of creating different vertical level folders inside connections.jdbc… if you create connection for each interface, that is option 2 in your list which i also would not prefer personally…