WSO2 EI - XA Data Sources for Oracle and MsSQL

The WSO2 EI server has the capability of interacting with RDBMS data sources in order to conduct data integration operations. The main functionalities of data integration in the EI server are inherited through the DSS server [1] of WSO2.

The following document [2] contains information on the configurations required to define XA data sources for MySQL in order to expose a single data service that connects to multiple RDBMSs to perform operations in a coordinated manner.

This article contains some information on configuring XA data sources for Oracle and MsSQL data sources.

As explained in [2] the relevant JDBC drivers should be added to the <EI-HOME>/lib directory and the EI server should be restarted before configuring the data sources.

Oracle XA data source

Datasource configurations can be added through the management console of the EI server as explained in [2]. With regard to Oracle, please note the dataSourceClassName below. The following configurations will be available in the <EI_HOME>/repository/deployment/server/dataservices directory as a .dbs file upon creating the data service.

<config enableOData="false" id="OraId">
      <property name="dataSourceClassName">oracle.jdbc.xa.client.OracleXADataSource</property>
      <property name="dataSourceProps">
         <property name="URL">jdbc:oracle:thin:@xx.xx.xx.xx:xxxx/ora11g</property>
         <property name="User">xxxxx</property>
         <property name="Password">xxxxx</property>
      </property>
   </config>

MsSQL XA data source

In the same manner, an MsSQL XA data source can be created through the management console of the EI server and the configurations in the .dbs file will be as follows. Please note the dataSourceClassName.

<config enableOData="false" id="MsSqlId">
      <property name="dataSourceClassName">com.microsoft.sqlserver.jdbc.SQLServerXADataSource</property>
      <property name="dataSourceProps">
         <property name="URL">jdbc:sqlserver://xx.xx.xx.xx:xxxx;databaseName=xxxx</property>
         <property name="User">xxxxx</property>
         <property name="Password">xxxxx</property>
      </property>
   </config>

With regard to the MsSQL XA data source, XA transactions should be enabled in the MsSQL server under the Microsoft Distributed Transaction Coordinator (DTC) properties.

In a Windows 2012 server, the above DTC properties can be accessed as follows.

1. Select Control Panel > Administrative Tools > Component Services.
2. Select Component Services > Computers > My Computer > Distributed Transaction Coordinator.
3. Right-click Local DTC and then select Properties.
4. Click the Security tab on the Local DTC Properties window.
5. Select the Enable XA Transactions check box, and click OK. This will restart the MS DTC service.
6. Click OK again to close the Properties window, and then close Component Services.

A sample .dbs file (in <EI_HOME>/repository/deployment/server/dataservices) which contains data service configurations for Oracle and MsSQL XA data sources will be as follows.

<data disableLegacyBoxcarringMode="true" enableBoxcarring="true" name="msSqlOracleDsTest" transports="http https local">
   <config enableOData="false" id="OraId">
      <property name="dataSourceClassName">oracle.jdbc.xa.client.OracleXADataSource</property>
      <property name="dataSourceProps">
         <property name="URL">xxxxx</property>
         <property name="user">xxxxx</property>
         <property name="password">xxxxx</property>
      </property>
   </config>
   <config enableOData="false" id="MsSqlID">
      <property name="dataSourceClassName">com.microsoft.sqlserver.jdbc.SQLServerXADataSource</property>
      <property name="dataSourceProps">
         <property name="URL">xxxxx</property>
         <property name="User">xxxxx</property>
         <property name="Password">xxxxx</property>
      </property>
   </config>
   <query id="oraInsertOfficeQuery1" useConfig="OraId">
      <sql>INSERT INTO OFFICES (OFFICECODE) VALUES (5)</sql>
   </query>
   <query id="MsSqlTestQuery1" useConfig="MsSqlID">
      <sql>insert into Offices (OfficeCode,AddressLine1,AddressLine2,City,State,Country,Phone) values(:OfficeCode,:AddressLine1,'test','test','test','USA','test')</sql>
      <param name="OfficeCode" sqlType="STRING"/>
      <param name="AddressLine1" sqlType="STRING"/>
   </query>
   <operation name="oraInsertOfficeOp1">
      <call-query href="oraInsertOfficeQuery1"/>
   </operation>
   <operation name="MsSqlTestOp1">
      <call-query href="MsSqlTestQuery1">
         <with-param name="OfficeCode" query-param="OfficeCode"/>
         <with-param name="AddressLine1" query-param="AddressLine1"/>
      </call-query>
   </operation>
</data>

Once the data service is configured, as explained in [3] the “request_box” operation available in the “tryit” tool of the EI server can be used to invoke the data service. To enable the “request_box” operation, “Enable Boxcarring” checkbox for the data service needs to be checked along with the “Disable Legacy Boxcarring Mode” checkbox. With the above configurations, the deprecated operations (begin_boxcar,end_boxcar,abort_boxcar) will not be visible in the “tryit” console. Following are a couple of screenshots for reference.

Notes:

1. When the above data sources are configured through the management console of the EI server, the “Test Connection” button will not work for older versions of EI and DSS due to an incompatibility in the front end.
2. If XA transactions are not enabled for the MsSQL server, the tm.out file (in <EI_HOME>/repository/logs) which logs information related to “Atomikos” which is an implementation of the Java Transaction API (JTA) which executes multiple operations as a single unit in a transactional manner will log the following XAException with the latter in the wso2carbon.log

tm.out log

javax.transaction.xa.XAException: com.microsoft.sqlserver.jdbc.SQLServerException: Could not find stored procedure 'master..xp_sqljdbc_xa_start'.
	at com.microsoft.sqlserver.jdbc.SQLServerXAResource.DTC_XA_Interface(SQLServerXAResource.java:742)
	at com.microsoft.sqlserver.jdbc.SQLServerXAResource.start(SQLServerXAResource.java:774)
	at com.atomikos.datasource.xa.XAResourceTransaction.resume(XAResourceTransaction.java:498)
	at com.atomikos.icatch.jta.TransactionImp.enlistResource(TransactionImp.java:329)
	at org.wso2.carbon.dataservices.core.description.config.SQLConfig.createConnection(SQLConfig.java:202)
	at org.wso2.carbon.dataservices.core.description.query.SQLQuery.createConnection(SQLQuery.java:663)
	at org.wso2.carbon.dataservices.core.description.query.SQLQuery.processPreStoredProcQuery(SQLQuery.java:914)
	at org.wso2.carbon.dataservices.core.description.query.SQLQuery.runPreQuery(SQLQuery.java:2213)
	at org.wso2.carbon.dataservices.core.description.query.Query.execute(Query.java:297)

wso2carbon.log

ERROR {org.wso2.carbon.dataservices.core.DBInOutMessageReceiver} -  Error in in-out message receiver {org.wso2.carbon.dataservices.core.DBInOutMessageReceiver}
DS Code: DATABASE_ERROR
Nested Exception:-
javax.xml.stream.XMLStreamException: DS Fault Message: Error in 'SQLQuery.processStoredProcQuery': DS Fault Message: Error in getting current transaction: Unexpected error during enlist
DS Code: UNKNOWN_ERROR
Nested Exception:-
com.atomikos.icatch.jta.ExtendedSystemException: Unexpected error during enlist

DS Code: DATABASE_ERROR
Source Data Service:-
Name: dss.bt.con
Location: /dss.bt.con.dbs
Description: N/A
Default Namespace: http://bessemer.com/con/data
Current Request Name: updateMobileCws_op
Current Params: {ret=1, CellPhoneNumber=99999999, ContactNumber=S07247}
Nested Exception:-
DS Fault Message: Error in getting current transaction: Unexpected error during enlist
DS Code: UNKNOWN_ERROR
Nested Exception:-
com.atomikos.icatch.jta.ExtendedSystemException: Unexpected error during enlist

[1]. https://docs.wso2.com/display/DSS351/About+Data+Services+Server

[2] https://docs.wso2.com/display/EI650/Handling+Distributed+Transactions

[3]. https://docs.wso2.com/display/EI650/Invoking+Multiple+Operations+via+Request+Box