ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.

Gen EJB/EJB Web service connecting to multiple databases

book

Article ID: 226547

calendar_today

Updated On:

Products

Gen - Workstation Toolset

Issue/Introduction

What are the best practices to allow a Gen Java EJB/EJB Web service to connect to multiple databases under WebSphere/JDBC/DB2?

Environment

Release: 8.62

Component: CA Gen Workstation Toolset

 

Resolution

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.

Additional Information

Gen EJB (JBoss EAP) using multiple SQL Server DBs has error TIRM170E ("java.sql.SQLException: IJ031070")