What are the best practices to allow a Gen Java EJB/EJB Web service to connect to multiple databases under WebSphere/JDBC/DB2?
Component: CA Gen Workstation Toolset
JDBC database connectivity to each DB is done via a defined Application Server data source (data source) and a separate data source needs to be defined for each DB involved.
Refer to Java Generation > Multiple Database Support which states:
"Gen Java applications use JDBC and are designed to allow access to one or more databases within a given procedure step. Clients wishing to use this feature must create a database resource for each database to be accessed by the procedure step.
In contrast, Gen C applications can access only one database for a given procedure step. This is due to the use of the default database handle for all embedded SQL statements.
Commits and rollbacks for multiple database connections may or may not be performed using a two-phased commit facility. In the Java Web Client environment, two-phased commit is supported only when the deployment options for using JDBC DataSources and JTA transactions are set and properly configured. In the EJB environment, two-phased commit is supported only when utilizing XA-compliant JDBC and JTA drivers."
Therefore XA data sources are mandatory if commits/rollbacks use two-phased commit which is normally the preferred method when a transaction spans multiple DBs.
Similar principles should be valid for DB2 & WebSphere.
Therefore in summary when defining the data sources in WebSphere it is suggested to follow the above principles and other standard WebSphere recommendations for data source creation/property settings.