2 Replies Latest reply on Feb 24, 2017 9:50 AM by Van Halbert

    Facing difficulty while using rollBack operation with Sqlserver Xa-DataSource

    Kulbhushan Chaskar Master

      Hi,

       

      I have gone through below steps for sqlserver Xa-Datasource:

       

      1. Below steps used to configure sqlserver driver

       

      1. I have placed 'module.xml' file and (sqljdbc4-2.0.jar) sqlserver jar on beolw location

       

      teiid-9.1.3-wildfly-server\teiid-9.1.3\modules\system\layers\base\com\microsoft\main

       

      module.xml file content:

       

      <?xml version="1.0" encoding="UTF-8"?>

      <module xmlns="urn:jboss:module:1.0" name="com.microsoft">

        <resources>

          <resource-root path="sqljdbc4-2.0.jar"/>

        </resources>

        <dependencies>

          <module name="javax.api"/>

        <module name="javax.transaction.api"/>

        </dependencies>

      </module>

       

       

      2. Made  driver tag entry with name as 'sqlserver'(for non-xa datasource)  and 'sqlserverXA'(for xa-datasource)  on standalone-teiid.xml file:

       

         <driver name="sqlserver" module="net.sourceforge.jtds">

                       <driver-class>net.sourceforge.jtds.jdbc.Driver</driver-class>

         </driver>

       

      <driver name="sqlserverXA" module="com.microsoft">

                     <driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>

                      <xa-datasource-class>com.microsoft.sqlserver.jdbc.SQLServerXADataSource</xa-datasource-class>

         </driver>

       

       

      2. Below Steps Used to create Xa-Datasource for netezza through jboss admin console

       

      Configuration ->Subsystems->DataSources->XA->Add

       

      -Custom

      -PostgreSQL XA Datasource

      -MySQL XA Datasource

      -Oracle XA Datasource

      -Microsoft SQLServer XA Datasource

      -IBM DB2 XA Datasource

      -Sybase XA Datasource

       

      where I had chosen Microsoft SQLServer XA Datasource option to create xa-datasource

       

      Step 1/4: XA Datasoure Attributes

       

      Name: MSSQLXADS3

      JNDI Name: java:/MSSQLXADS3

       

      Step 2/4: Datasource Class

       

      Name                          : sqlserverXA

      Module Name              :com.microsoft

      Driver Class                 :com.microsoft.sqlserver.jdbc.SQLServerDriver

      XA DataSource Class  :com.microsoft.sqlserver.jdbc.SQLServerXADataSource

      Major Version              :4

      Minor Version              :6

       

      Step 3/4: XA Properties

       

      DatabaseName: test_db

      ServerName :  hostName/instance

       

      Step 4/4: Connection Settings

       

      Username : test_user

      Password : test_pwd

       

      I am successfully able to create  connection using Xa-datasource. But On teiid console I am getting below warning message very frequently:

       

      19:53:56,303 WARN  [com.arjuna.ats.jta] (Periodic Recovery)  ARJUNA016027: Local XARecoveryModule.xaRecovery got XA exception XAException.XAER_RMERR: javax.transaction.x

      a.XAException: com.microsoft.sqlserver.jdbc.SQLServerException: Failed to create the XA control connection. Error: "Could not find stored procedure 'master..xp_sqljdbc_x

      a_init_ex'."

              at com.microsoft.sqlserver.jdbc.SQLServerXAResource.DTC_XA_Interface(SQLServerXAResource.java:642)

              at com.microsoft.sqlserver.jdbc.SQLServerXAResource.recover(SQLServerXAResource.java:723)

              at org.jboss.jca.adapters.jdbc.xa.XAManagedConnection.recover(XAManagedConnection.java:362)

              at org.jboss.jca.core.tx.jbossts.XAResourceWrapperImpl.recover(XAResourceWrapperImpl.java:185)

              at com.arjuna.ats.internal.jta.recovery.arjunacore.XARecoveryModule.xaRecoveryFirstPass(XARecoveryModule.java:550)

              at com.arjuna.ats.internal.jta.recovery.arjunacore.XARecoveryModule.periodicWorkFirstPass(XARecoveryModule.java:190)

              at com.arjuna.ats.internal.arjuna.recovery.PeriodicRecovery.doWorkInternal(PeriodicRecovery.java:747)

              at com.arjuna.ats.internal.arjuna.recovery.PeriodicRecovery.run(PeriodicRecovery.java:375)

       

       

      After successfully creating xa-datasource ,below code is working fine:

       

        Connection connection = getDriverConnection();

        Statement statement = connection.createStatement();

       

          String insertQuery = "INSERT INTO tdm_Emp(id,Emp_Name,Emp_Phone_Number,Emp_Card_Number)"

           + "values('13','Mandy','+919368407572','4916899496189')";

        try{

         statement.executeUpdate(insertQuery);

        }catch(Exception ex){

          ex.printStackTrace();

        }

      statement.close();

      connection.close();

       

      Below code is throwing exception at '  statement.executeUpdate(insertQuery);' :

       

      Connection connection = getDriverConnection();

      Statement statement = connection.createStatement();

       

      connection.setAutoCommit(false);

       

          String insertQuery = "INSERT INTO tdm_Emp(id,Emp_Name,Emp_Phone_Number,Emp_Card_Number)"

           + "values('13','Mandy','+919368407572','4916899496189')";

        try{

         statement.executeUpdate(insertQuery);

        }catch(Exception ex){

          ex.printStackTrace();

        }

       

      connection.commit();

      statement.close();

        connection.close();

       

       

      Exception:

       

      org.teiid.jdbc.TeiidSQLException: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 sqlServerConn: TEIID11009 java.sql.SQLException: javax.resource.ResourceException: IJ000457: Unchecked throwable in managedConnectionReconnected() cl=org.jboss.jca.core.connectionmanager.listener.TxConnectionListener@5c8cab60[state=DESTROYED managed connection=org.jboss.jca.adapters.jdbc.xa.XAManagedConnection@690a19f4 connection handles=0 lastReturned=1487946705295 lastValidated=1487946705295 lastCheckedOut=1487946705295 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@51639b42 mcp=SemaphoreConcurrentLinkedQueueManagedConnectionPool@781e82bf[pool=MSSQLXADS3] xaResource=XAResourceWrapperImpl@11333fff[xaResource=org.jboss.jca.adapters.jdbc.xa.XAManagedConnection@690a19f4 pad=false overrideRmValue=false productName=Microsoft SQL Server productVersion=11.00.2100 jndiName=java:/MSSQLXADS3] txSync=null]

        at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:135)

        at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:71)

        at org.teiid.jdbc.StatementImpl.postReceiveResults(StatementImpl.java:723)

        at org.teiid.jdbc.StatementImpl.access$100(StatementImpl.java:65)

        at org.teiid.jdbc.StatementImpl$2.onCompletion(StatementImpl.java:561)

        at org.teiid.client.util.ResultsFuture.done(ResultsFuture.java:135)

        at org.teiid.client.util.ResultsFuture.access$200(ResultsFuture.java:40)

        at org.teiid.client.util.ResultsFuture$1.receiveResults(ResultsFuture.java:79)

        at org.teiid.net.socket.SocketServerInstanceImpl.receivedMessage(SocketServerInstanceImpl.java:285)

        at org.teiid.net.socket.SocketServerInstanceImpl.read(SocketServerInstanceImpl.java:323)

        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

        at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

        at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

        at java.lang.reflect.Method.invoke(Unknown Source)

        at org.teiid.net.socket.SocketServerConnectionFactory$ShutdownHandler.invoke(SocketServerConnectionFactory.java:98)

        at com.sun.proxy.$Proxy1.read(Unknown Source)

        at org.teiid.net.socket.SocketServerInstanceImpl$RemoteInvocationHandler$1.get(SocketServerInstanceImpl.java:422)

        at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:570)

        at org.teiid.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1091)

        at org.teiid.jdbc.StatementImpl.executeUpdate(StatementImpl.java:360)

        at com.bitwise.tdm.teiid.WithTeiid.executeSqlUpdate(WithTeiid.java:214)

        at com.bitwise.tdm.teiid.WithTeiid.insertInSqlServerDb(WithTeiid.java:60)

        at com.bitwise.tdm.teiid.WithTeiid.main(WithTeiid.java:41)

      Caused by: org.teiid.core.TeiidProcessingException: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 sqlServerConn: TEIID11009 java.sql.SQLException: javax.resource.ResourceException: IJ000457: Unchecked throwable in managedConnectionReconnected() cl=org.jboss.jca.core.connectionmanager.listener.TxConnectionListener@5c8cab60[state=DESTROYED managed connection=org.jboss.jca.adapters.jdbc.xa.XAManagedConnection@690a19f4 connection handles=0 lastReturned=1487946705295 lastValidated=1487946705295 lastCheckedOut=1487946705295 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@51639b42 mcp=SemaphoreConcurrentLinkedQueueManagedConnectionPool@781e82bf[pool=MSSQLXADS3] xaResource=XAResourceWrapperImpl@11333fff[xaResource=org.jboss.jca.adapters.jdbc.xa.XAManagedConnection@690a19f4 pad=false overrideRmValue=false productName=Microsoft SQL Server productVersion=11.00.2100 jndiName=java:/MSSQLXADS3] txSync=null]

        at org.teiid.dqp.internal.process.DataTierTupleSource.exceptionOccurred(DataTierTupleSource.java:401)

        at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:161)

        at org.teiid.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:392)

        at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:282)

        at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:145)

        at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:151)

        at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:114)

        at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:164)

        at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:146)

        at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:477)

        at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:349)

        at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51)

        at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:275)

        at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:282)

        at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)

        at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)

        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)

        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)

        at java.lang.Thread.run(Thread.java:745)

      Caused by: org.teiid.core.TeiidException: TEIID11009 Remote org.teiid.translator.TranslatorException: TEIID11009 java.sql.SQLException: javax.resource.ResourceException: IJ000457: Unchecked throwable in managedConnectionReconnected() cl=org.jboss.jca.core.connectionmanager.listener.TxConnectionListener@5c8cab60[state=DESTROYED managed connection=org.jboss.jca.adapters.jdbc.xa.XAManagedConnection@690a19f4 connection handles=0 lastReturned=1487946705295 lastValidated=1487946705295 lastCheckedOut=1487946705295 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@51639b42 mcp=SemaphoreConcurrentLinkedQueueManagedConnectionPool@781e82bf[pool=MSSQLXADS3] xaResource=XAResourceWrapperImpl@11333fff[xaResource=org.jboss.jca.adapters.jdbc.xa.XAManagedConnection@690a19f4 pad=false overrideRmValue=false productName=Microsoft SQL Server productVersion=11.00.2100 jndiName=java:/MSSQLXADS3] txSync=null]

        at org.teiid.translator.jdbc.JDBCExecutionFactory.getConnection(JDBCExecutionFactory.java:282)

        at org.teiid.translator.jdbc.JDBCExecutionFactory.getConnection(JDBCExecutionFactory.java:69)

        at org.teiid.translator.ExecutionFactory.getConnection(ExecutionFactory.java:205)

        at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:331)

        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

        at java.lang.reflect.Method.invoke(Method.java:497)

        at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:220)

        at com.sun.proxy.$Proxy27.execute(Unknown Source)

        at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306)

        at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:142)

        ... 17 more

      Caused by: java.sql.SQLException: Remote java.sql.SQLException: javax.resource.ResourceException: IJ000457: Unchecked throwable in managedConnectionReconnected() cl=org.jboss.jca.core.connectionmanager.listener.TxConnectionListener@5c8cab60[state=DESTROYED managed connection=org.jboss.jca.adapters.jdbc.xa.XAManagedConnection@690a19f4 connection handles=0 lastReturned=1487946705295 lastValidated=1487946705295 lastCheckedOut=1487946705295 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@51639b42 mcp=SemaphoreConcurrentLinkedQueueManagedConnectionPool@781e82bf[pool=MSSQLXADS3] xaResource=XAResourceWrapperImpl@11333fff[xaResource=org.jboss.jca.adapters.jdbc.xa.XAManagedConnection@690a19f4 pad=false overrideRmValue=false productName=Microsoft SQL Server productVersion=11.00.2100 jndiName=java:/MSSQLXADS3] txSync=null]

        at org.jboss.jca.adapters.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:146)

        at org.jboss.as.connector.subsystems.datasources.WildFlyDataSource.getConnection(WildFlyDataSource.java:66)

        at org.teiid.translator.jdbc.JDBCExecutionFactory.getConnection(JDBCExecutionFactory.java:278)

        ... 28 more

      Caused by: org.teiid.core.TeiidRuntimeException: Remote javax.resource.ResourceException: IJ000457: Unchecked throwable in managedConnectionReconnected() cl=org.jboss.jca.core.connectionmanager.listener.TxConnectionListener@5c8cab60[state=DESTROYED managed connection=org.jboss.jca.adapters.jdbc.xa.XAManagedConnection@690a19f4 connection handles=0 lastReturned=1487946705295 lastValidated=1487946705295 lastCheckedOut=1487946705295 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@51639b42 mcp=SemaphoreConcurrentLinkedQueueManagedConnectionPool@781e82bf[pool=MSSQLXADS3] xaResource=XAResourceWrapperImpl@11333fff[xaResource=org.jboss.jca.adapters.jdbc.xa.XAManagedConnection@690a19f4 pad=false overrideRmValue=false productName=Microsoft SQL Server productVersion=11.00.2100 jndiName=java:/MSSQLXADS3] txSync=null]

        at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.reconnectManagedConnection(AbstractConnectionManager.java:933)

        at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.allocateConnection(AbstractConnectionManager.java:750)

        at org.jboss.jca.adapters.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:138)

        ... 30 more

      Caused by: org.teiid.core.TeiidRuntimeException: Remote javax.resource.ResourceException: IJ000461: Could not enlist in transaction on entering meta-aware object

        at org.jboss.jca.core.connectionmanager.tx.TxConnectionManagerImpl.managedConnectionReconnected(TxConnectionManagerImpl.java:560)

        at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.reconnectManagedConnection(AbstractConnectionManager.java:928)

        ... 32 more

      Caused by: org.teiid.core.TeiidRuntimeException: Remote javax.transaction.SystemException: IJ000356: Failed to enlist: java.lang.Throwable: Unabled to enlist resource, see the previous warnings. tx=TransactionImple < ac, BasicAction: 0:ffff0a0a7fee:-454c8fae:58b0141f:446 status: ActionStatus.ABORT_ONLY >

        at org.jboss.jca.core.connectionmanager.listener.TxConnectionListener$TransactionSynchronization.checkEnlisted(TxConnectionListener.java:942)

        at org.jboss.jca.core.connectionmanager.listener.TxConnectionListener.enlist(TxConnectionListener.java:394)

        at org.jboss.jca.core.connectionmanager.tx.TxConnectionManagerImpl.managedConnectionReconnected(TxConnectionManagerImpl.java:553)

        ... 33 more