CA Gen EJB deployed to JBoss EAP 7.1 accessing multiple SQL Server databases receives error TIRM170E ("java.sql.SQLException: IJ031070")

Products

CA Gen CA Gen - Run Time Distributed

Issue/Introduction

A model with 2 databases DB1 & DB2 contains a server Procedure Step which reads data from tables E1 & E2 in DB1 & DB2 respectively.

Databases DB1 & DB2 have been created on the SQL Server instance and the DDL installed.
The Server Manager was generated for JVM/JDBC/JAVA/EJB and assembled to create an EJB ear file which was deployed to JBoss 7.1 EAP where SQL Server datasources have been created for databases DB1 & DB2
On execution the read from database DB1 was successful but the read from database DB2 fails with this error:

===

TIRM170E: An error was encountered on the server
BaseDBMSManager.getStatement() caught ABException getting connection for: DB2 Exception: com.ca.gen.abrt.ABException: [Function: DataSourceDBMSManager.connect]Error attempting to determine if transactions are supported: java.sql.SQLException: IJ031070: Transaction cannot proceed: STATUS_MARKED_ROLLBACK

===

Cause

1. Search on the error IJ031070 gives an indication of the root cause e.g. Red Hat solution: "Transaction cannot proceed: STATUS_MARKED_ROLLBACK" in JBoss EAP (requires Red Hat account to access):
===

Resolution

Identify and address the reason the transaction was marked for rollback.
Correct code that fails to check transaction state/abort work after a transaction is marked for rollback.

Root Cause

The transaction was previously marked for rollback but something is attempting to continue using a connection that was enlisted in that transaction. The reason the transaction was initially marked for rollback may be one of the following

Explicit calls to transaction management APIs
Implicit calls (by the EAP container) to transaction management setRollbackOnly() that result from exceptions such as:
- Unsafe attempt to enlist of multiple non-XA resources
- Application exceptions not caught/handled by application code (e.g. during EJB invocations)

===
Note the "Unsafe attempt to enlist of multiple non-XA resources"
In this case non-XA datasources had been defined for DB1 & DB2 instead of XA datasources.
In the JBoss EAP 7.1 Administration Console> Configuration > Datasources it states:
Non-XA Datasources : Manage non-XA datasources, which are used for applications that do not use transactions, or applications that use transactions with a single database.
XA Datasources : Manage XA datasources, which are used by applications whose transactions are distributed across multiple databases

2. If not already performed, additional configuration may be required for Microsoft Distributed Transaction Coordinator (MS DTC) and SQL Server Database to enable XA transactions.

Environment

Release : 8.6
Component : CA Gen Enterprise Java Beans
JBoss EAP 7.x

Resolution

STEP 1:

a. Delete the existing non-XA datasources and re-add them as XA datasources.This can be done via the JBoss Administration Console> Configuration > Datasources or via CLI command steps.
An example of using CLI command steps is given below. They also include the addition and registration of the Microsoft SQL Server JDBC driver as a core module which would have been previously done via core module addition or via jar file deployment, in order to be able to create the non-XA datasources in the first instance.
From the Red Hat JBoss 7.1 documentation see section "12.15.8. Example Microsoft SQL Server XA Datasource" to use CLI (file jboss-cli.bat(.sh)) to create the SQL Server XA datasources:

https://access.redhat.com/documentation/en-us/red_hat_jboss_enterprise_application_platform/7.1/html/configuration_guide/datasource_management#example_microsoft_sqlserver_xa_datasource

=== Add Microsoft SQL Server JDBC driver as core module and register it ===

module add --name=com.microsoft --resources=C:/PROGRA~1/MICROS~1.4FO/sqljdbc_7.4/enu/mssql-jdbc-7.4.1.jre8.jar --dependencies=javax.api,javax.transaction.api,javax.xml.bind.api

/subsystem=datasources/jdbc-driver=sqlserver:add(driver-name=sqlserver,driver-module-name=com.microsoft,driver-xa-datasource-class-name=com.microsoft.sqlserver.jdbc.SQLServerXADataSource)

===

=== Add a Microsoft SQL Server XA datasource for DB1 and DB2 (these examples use the SQL Server "sa" account) ===

xa-data-source add --name=DB1 --jndi-name=java:/DB1 --driver-name=sqlserver --user-name=sa --password=sa_password --validate-on-match=true --background-validation=false --background-validation=false --valid-connection-checker-class-name=org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLValidConnectionChecker --exception-sorter-class-name=org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLExceptionSorter --same-rm-override=false --xa-datasource-properties={"ServerName"=>"hostname","DatabaseName"=>"DB1","SelectMethod"=>"cursor"}

xa-data-source add --name=DB2 --jndi-name=java:/DB2 --driver-name=sqlserver --user-name=sa --password=sa_password --validate-on-match=true --background-validation=false --background-validation=false --valid-connection-checker-class-name=org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLValidConnectionChecker --exception-sorter-class-name=org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLExceptionSorter --same-rm-override=false --xa-datasource-properties={"ServerName"=>"hostname","DatabaseName"=>"DB2","SelectMethod"=>"cursor"}
===

b. From JBoss Administration Console> Configuration > Datasources use "Test Connection" for the new XA datasources DB1 & DB2 and verify they are successful.
Also check the JBoss server.log, because although the connection is reported as successful, there may a SQL Server stored procedure error reported:
javax.transaction.xa.XAException: com.microsoft.sqlserver.jdbc.SQLServerException: Failed to create the XA control connection. Error: "Could not find stored procedure 'master..xp_sqljdbc_xa_init_ex'.", XAException.XAER_RMERR
If such an error exists the Gen EJB transaction will also likely fail to to connect to the first database DB1 with the same original error code IJ031070 i.e.

===

TIRM170E: An error was encountered on the server
BaseDBMSManager.getStatement() caught ABException getting connection for: DB1 Exception: com.ca.gen.abrt.ABException: [Function: DataSourceDBMSManager.connect]Error attempting to determine if transactions are supported: java.sql.SQLException: IJ031070: Transaction cannot proceed: STATUS_MARKED_ROLLBACK
===

If these errors occur please continue to STEP 2 below.

STEP 2:
The SQL Server stored procedure error symptom is documented in this Red Hat solution:
"SQLServerException: Failed to create the XA control connection" warning is caught when deploying a SQL datasource using XA
===

Resolution

This warning message indicates that there is some configuration required on SQLServer in order for it to support XA transaction recovery. Many databases do not support XA recovery out of the box and must be manually configured to enable such support. You have to contact the vendor support for more information on how to perform the necessary configuration.

Root Cause

XA datasource is configured in JBoss but XA is not supported on database side. Check this article Understanding XA Transactions from Microsoft MSDN for more details.
===

Per the above Microsoft link https://docs.microsoft.com/en-us/sql/connect/jdbc/understanding-xa-transactions?view=sql-server-ver15, follow the steps to enable XA in the Microsoft Distributed Transaction Coordinator (MS DTC) and enable XA for JDBC on the SQL Server instance i.e.
"Running the MS DTC service" & "Configuring the JDBC distributed transaction components" steps 1 & 2

After those steps are complete, the JBoss Test Connection step for the datasources should show no errors in the JBoss server.log file and the Gen EJB transaction execution against both databases should also then be successful.