Getting error when accessing 2 data source

[allenyu6]

Hi ALL, I WAS ABLE to create a JDV view, and able to connect by exposing it as jdbc source to a report client, however, if the view is coming from one data source, that everything is working fine, if it was coming two data sources, then I am getting the following error

Anyone can shed a light?

>>>>

--jboss dv server error log
16:40:04,802 WARN  [org.teiid.CONNECTOR] (Worker13_QueryProcessorQueue168) Connector worker process failed for atomic-request=O3IWy28Bjl3D.18.3.16: org.teiid.translator.TranslatorException: TEIID11009 java.sql.SQLException: javax.resource.ResourceException: IJ000457: Unchecked throwable in managedConnectionReconnected() cl=org.jboss.jca.core.connectionmanager.listener.TxConnectionListener@47a6ca01[state=NORMAL managed connection=org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@6de7f171 connection handles=0 lastUse=1433191204802 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@323eb807 pool internal context=SemaphoreArrayListManagedConnectionPool@49f93d3a[pool=ORD_PRD] xaResource=LocalXAResourceImpl@4368650d[connectionListener=47a6ca01 connectionManager=73b8be03 warned=false currentXid=null productName=Oracle productVersion=Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management and Real Application Testing options jndiName=java:/ORD_PRD] txSync=null]
at org.teiid.translator.jdbc.JDBCExecutionFactory.getConnection(JDBCExecutionFactory.java:285)
at org.teiid.translator.jdbc.JDBCExecutionFactory.getConnection(JDBCExecutionFactory.java:57)
at org.teiid.translator.ExecutionFactory.getConnection(ExecutionFactory.java:194) [teiid-api-8.7.1.redhat-8.jar:8.7.1.redhat-8]
at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:289) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]
at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:298) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]
at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:135) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]
at org.teiid.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:369) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:278) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]
at org.teiid.query.processor.BatchIterator.finalRow(BatchIterator.java:69) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]
at org.teiid.common.buffer.AbstractTupleSource.getCurrentTuple(AbstractTupleSource.java:70) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]
at org.teiid.query.processor.BatchIterator.getCurrentTuple(BatchIterator.java:84) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]
at org.teiid.common.buffer.AbstractTupleSource.hasNext(AbstractTupleSource.java:92) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]
at org.teiid.query.processor.relational.MergeJoinStrategy.process(MergeJoinStrategy.java:163) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]
at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:211) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:278) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]
at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:146) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:278) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]
at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:136) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]
at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:151) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]
at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:114) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]
at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:159) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]
at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:141) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]
at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:444) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]
at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:326) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]
at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]
at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:254) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]
at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:274) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]
at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]
at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_60]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_60]
at java.lang.Thread.run(Thread.java:745) [rt.jar:1.7.0_60]
Caused by: java.sql.SQLException: javax.resource.ResourceException: IJ000457: Unchecked throwable in managedConnectionReconnected() cl=org.jboss.jca.core.connectionmanager.listener.TxConnectionListener@47a6ca01[state=NORMAL managed connection=org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@6de7f171 connection handles=0 lastUse=1433191204802 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@323eb807 pool internal context=SemaphoreArrayListManagedConnectionPool@49f93d3a[pool=ORD_PRD] xaResource=LocalXAResourceImpl@4368650d[connectionListener=47a6ca01 connectionManager=73b8be03 warned=false currentXid=null productName=Oracle productVersion=Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management and Real Application Testing options jndiName=java:/ORD_PRD] txSync=null]
at org.jboss.jca.adapters.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:151)
at org.teiid.translator.jdbc.JDBCExecutionFactory.getConnection(JDBCExecutionFactory.java:281)
... 31 more
Caused by: javax.resource.ResourceException: IJ000457: Unchecked throwable in managedConnectionReconnected() cl=org.jboss.jca.core.connectionmanager.listener.TxConnectionListener@47a6ca01[state=NORMAL managed connection=org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@6de7f171 connection handles=0 lastUse=1433191204802 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@323eb807 pool internal context=SemaphoreArrayListManagedConnectionPool@49f93d3a[pool=ORD_PRD] xaResource=LocalXAResourceImpl@4368650d[connectionListener=47a6ca01 connectionManager=73b8be03 warned=false currentXid=null productName=Oracle productVersion=Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management and Real Application Testing options jndiName=java:/ORD_PRD] txSync=null]
at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.reconnectManagedConnection(AbstractConnectionManager.java:631) [ironjacamar-core-impl-1.0.28.Final-redhat-1.jar:1.0.28.Final-redhat-1]
at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.allocateConnection(AbstractConnectionManager.java:494) [ironjacamar-core-impl-1.0.28.Final-redhat-1.jar:1.0.28.Final-redhat-1]
at org.jboss.jca.adapters.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:143)
... 32 more
Caused by: 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:490) [ironjacamar-core-impl-1.0.28.Final-redhat-1.jar:1.0.28.Final-redhat-1]
at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.reconnectManagedConnection(AbstractConnectionManager.java:626) [ironjacamar-core-impl-1.0.28.Final-redhat-1.jar:1.0.28.Final-redhat-1]
... 34 more
Caused by: javax.transaction.SystemException: IJ000356: Failed to enlist: java.lang.Throwable: Unabled to enlist resource, see the previous warnings. tx=TransactionImple < ac, BasicAction: 0:ffff0a3cb738:-d8f5479:556cae9a:178 status: ActionStatus.ABORT_ONLY >
at org.jboss.jca.core.connectionmanager.listener.TxConnectionListener$TransactionSynchronization.checkEnlisted(TxConnectionListener.java:669) [ironjacamar-core-impl-1.0.28.Final-redhat-1.jar:1.0.28.Final-redhat-1]
at org.jboss.jca.core.connectionmanager.listener.TxConnectionListener.enlist(TxConnectionListener.java:368) [ironjacamar-core-impl-1.0.28.Final-redhat-1.jar:1.0.28.Final-redhat-1]
at org.jboss.jca.core.connectionmanager.tx.TxConnectionManagerImpl.managedConnectionReconnected(TxConnectionManagerImpl.java:483) [ironjacamar-core-impl-1.0.28.Final-redhat-1.jar:1.0.28.Final-redhat-1]
... 35 more

16:40:04,912 WARN  [org.teiid.PROCESSOR] (Worker13_QueryProcessorQueue168) TEIID30020 Processing exception for request O3IWy28Bjl3D.18 'TEIID30504 ORD_PRD: TEIID11009 java.sql.SQLException: javax.resource.ResourceException: IJ000457: Unchecked throwable in managedConnectionReconnected() cl=org.jboss.jca.core.connectionmanager.listener.TxConnectionListener@47a6ca01[state=NORMAL managed connection=org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@6de7f171 connection handles=0 lastUse=1433191204802 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@323eb807 pool internal context=SemaphoreArrayListManagedConnectionPool@49f93d3a[pool=ORD_PRD] xaResource=LocalXAResourceImpl@4368650d[connectionListener=47a6ca01 connectionManager=73b8be03 warned=false currentXid=null productName=Oracle productVersion=Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management and Real Application Testing options jndiName=java:/ORD_PRD] txSync=null]'. Originally TeiidProcessingException 'IJ000356: Failed to enlist: java.lang.Throwable: Unabled to enlist resource, see the previous warnings. tx=TransactionImple < ac, BasicAction: 0:ffff0a3cb738:-d8f5479:556cae9a:178 status: ActionStatus.ABORT_ONLY >' TxConnectionListener.java:669. Enable more detailed logging to see the entire stacktrace.
 
 

[rareddy]

Are you trying update any values? or executing the procedure?

What the error saying is, it can not spin a transaction around both sources. Typically one needs to create XA based source connections for the update scenarios or you can disable transactions (though not recommended for production systems).

[allenyu6]

no, trying to pulling data (readonly access), specifically, we created a jdbc data source in Spotfire, and I was able to pull the data from the virtual data model created under one single data source, but for a simple joint view from two different data sources, it fails, and it gave the above error message. Any ideas if there is any kind of setting or config should be done on client or Jboss DV side?

[shawkins]

> Any ideas if there is any kind of setting or config should be done on client or Jboss DV side?

As Ramesh is saying you can use XA Datasources, rather than local or no transaction datasources.  Alternatively you can determine why a translation has been started.  Is your client explicitly starting a transaction or being enlisted in an xa transaction?  Or is the Teiid autoCommitTxn logic detecting that a transaction needs started in auto commit true?  You can for example tell Teiid not to start transactions on it's own with setting the connection property autoCommitTxn=off.

[allenyu6]

Not sure if I understand why you suggest here. BUt I was able to connect to the same data source (with 2+) from another client such as db visualizer and pull out data w/o problem. regarding "tell Teiid not to start transactions on it's own with setting the connection property autoCommitTxn=off - See more at: https://developer.jboss.org/message/932668?et=watches.email.thread#sthash.gvwIn4ri.dpuf"

where and how should I set this?

BTW, again this is only for read access to the logical view on top of the multiple data source.

Thanks all for the help!

[shawkins]

> BTW, again this is only for read access to the logical view on top of the multiple data source.

Yes, but it still matters what the client is doing wrt starting a transaction.  If the Teiid connection is in a transaction, then JCA will attempt to ensure that your datasources will be accessed in a transactionally safe manner.  This means that xa datasources will be enlisted and at most 1 local transaction datasource can be included in the transaction.

If you are doing only read only scenarios, then you could also just change your datasources to be just no transaction instead.

> where and how should I set this?

Again autoCommitTxn will only come into play here if it's Teiid that is starting the txn on your behalf - which hasn't been determined.  See the property definition here:  Driver Connection - Teiid 8.11 (draft) - Project Documentation Editor

[allenyu6]

here is the error on client

InformationModelException at Spotfire.Dxp.Data:

Failed to execute query: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 ORD_PRD: TEIID11009 java.sql.SQLException: javax.resource.ResourceException: IJ000457: Unchecked throwable in managedConnectionReconnected() cl=org.jboss.jca.core.connectionmanager.listener.TxConnectionListener@31734f7b[state=NORMAL managed connection=org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@6847d1a8 connection handles=0 lastUse=1433345287547 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@4315a214 pool internal context=SemaphoreArrayListManagedConnectionPool@5a965bd6[pool=ORD_PRD] xaResource=LocalXAResourceImpl@45676d10[connectionListener=31734f7b connectionManager=9295909 warned=false currentXid=null productName=Oracle productVersion=Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Automatic Storage Management and Real Application Testing options jndiName=java:/ORD_PRD] txSync=null] (HRESULT: 80131500)

what other options do you see I can try?

[shawkins]

> what other options do you see I can try?

You should also make an attempt to understand what is starting the transaction.  If that exception is with autoCommitTxn=off, then it's not Teiid.

If you are doing only read only scenarios, then you could also just change your datasources to be just no transaction instead.

And if the client is calling setAutoCommit(false) or issuing "START TRANSACTION", then you can use the connection property disableLocalTxn.

[zyla.andrzej]

Hi Allen,

Have you solved the issue and willing to share? I am experiencing the same issue.

regards,

Andrzej

[rareddy]

Andrej,

Have you tried the suggestions from above? If it read only scenario this should not be occurring, if this some kind write then we need to make sure the you configured the sources correctly

Ramesh..

[zyla.andrzej]

Well, Spotfire is a reporting / analytical tool. It should be read only, when writing to temporary tables is disabled, but the case is that it starts transaction for some reason. But this is an issue to other community :-)

For now,  I just setup a data source to be compatible with XA Transactions (SQL Server), which solved the issue. You can find description how to do it here.

regards,

Andrzej

[rareddy]

You can turn on command logs to see what is being sent by Teiid to the sources for execution, as that will give some explanation why this is occurring. Also, if the execution is involves a virtual procedure, it may also start a transaction, unless the "UPDATECOUNT" property on virtual procedure is set to 0.

Ramesh..