Gen EJB under JBoss TIRM170E ("java.sql.SQLException: IJ031070")
search cancel

Gen EJB under JBoss TIRM170E ("java.sql.SQLException: IJ031070")

book

Article ID: 141786

calendar_today

Updated On:

Products

Gen Gen - Run Time Distributed

Issue/Introduction

A model with multiple (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
*****

Environment

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

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

*****


Note the Red Hat solution page contents for hyperlink 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.

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.

=== 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

Disclaimer: Links contained herein to external website(s) are provided for convenience only. Red Hat has not reviewed the links and is not responsible for the content or its availability. The inclusion of any link to an external website does not imply endorsement by Red Hat of the website or their entities, products or services. You agree that Red Hat is not responsible or liable for any loss or expenses that may result due to your use of (or reliance on) the external site or content.

  • Many databases do not support XA recovery out-of-the-box and must be manually configured to enable such support.
  • Review Understanding XA Transactions (Microsoft MSDN) for additional details.
  • Ensure that the xa_install.sql is executed successfully as an administrator of the SQL Server instance.
  • Finally, contact Microsoft Support directly for further information regarding necessary configuration to enable XA.

Root Cause

The database server is not configured to support the use of XA datasources.
*****

Per the above Microsoft link Understanding XA Transactions (now a https://learn.microsoft.com/ page) 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.

Additional Information

Powered by Wolken Software