Getting error while refresh the internal materialized view which has two source model

[govindarajs]

Hi,

I have two source model and its SQLSERVER datasource in the VDB, I have create one view in view model by joining the two source model tables and make it internal materialized view,

For refresh the internal materialize views I have create one stored procedure INITIAL_MATVIEWS_REFRESH() it will refresh all materialize views

The stored procedures contain the following statement to refresh the internal materialized views

EXEC SYSADMIN.refreshMatView('viewname', FALSE);

If I run the INITIAL_MATVIEWS_REFRESH() through the JDBC connection it is working fine, it refreshes all mat views

But if I run the INITIAL_MATVIEWS_REFRESH() through the OData connection it is not working fine, it shows the following error in the browser

Internal Exception: org.teiid.jdbc.TeiidSQLException: TEIID30504 VDBSource: TEIID11009 java.sql.SQLException: javax.resource.ResourceException: IJ000457: Unchecked throwable in managedConnectionReconnected() cl=org.jboss.jca.core.connectionmanager.listener.TxConnectionListener@7603db5b[state=NORMAL managed connection=org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@4e9c955b connection handles=0 lastUse=1436781141610 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.PoolBySubject@fa85d53 pool internal context=SemaphoreArrayListManagedConnectionPool@263d1c29[pool=VDBSource] xaResource=LocalXAResourceImpl@67df5188[connectionListener=7603db5b connectionManager=170e546a warned=false currentXid=null productName=Microsoft SQL Server productVersion=10.50.1600 jndiName=java:/VDBSource] txSync=null]

Error Code: 30504

Call: {CALL INITIAL_MATVIEWS_REFRESH()}

Thanks & Regards

Govindaraj s.

[rareddy]

Looks like it is trying to execute the INITIAL_MATVIEWS_REFRESH procedure against the SQLServer. Is this defined on a source model or view model?

[govindarajs]

Ramesh,

The INITIAL_MATVIEWS_REFRESH defined in view model, it is nothing but it get the internal materialized views details from sysadmin.matviews and refresh all materialized views in the same view model, so that the application performance is good in initially also

Thanks & Regards

Govindaraj s.

[shawkins]

This probably has to do with the container doing some unintended transaction management.  Is the source XA?

[govindarajs]

No, it is local datasource connection

[shawkins]

So you could try either making it an XA source, or to dig in more about when a transaction is being started and how it is being coordinated.

[rareddy]

Probably if you set  "UPDATECOUNT" property on this procedure to zero then the XA transaction will not be wrapped.

[govindarajs]

Hi Ramesh,

     I have tried different value of UPDATECOUNT as 'ZERO', 'AUTO', 'MULTIPLE', 'ONE', but it is not working in ODATA connection

     Here my question is:

     The same stored procedure is working fine through the jdbc connection, but why it is not working through the ODATA connection?

     I have tried xa-datasource also it shows some other error like

     Internal Exception: org.teiid.jdbc.TeiidSQLException: TEIID30498 Capabilities for Srcjndi were not avaialable. The command could not be planned properly.

     Jboss EAP 6.1

     Teiid server 8.7

     Teiid Designer 9.0

Thanks & Regards

Govindaraj s.

[shawkins]

> The same stored procedure is working fine through the jdbc connection, but why it is not working through the ODATA connection?

Over odata a transaction is being started to coordinate the work of the procedure.  This is apparently not happening over JDBC.  So the approach is either to determine why a transaction is being started, or to make sure the datasource is XA and therefore can participate in a transaction.

>   I have tried xa-datasource also it shows some other error like

That likely means that it is not setup correctly and no connections are available.

[rareddy]

The only difference would be LocalConnection over SocketConnection when used OData Vs JDBC. Not sure what else will start a XA transaction. Is it possible for you provide a testcase or VDB?

[govindarajs]

Hi Ramesh,

     Please find the sample vdb in the attachment,

     Where we are using two source model with two different datasource and one view model which is derived from the two source model

     We have one stored procedure INITIAL_MATVIEWS_REFRESH() in the view model, which is used to refresh all internal materialized view in the vdb, we run the sp when the vdb deployed, so we feel the application perform good in first time also

     If I run the sp through the jdbc connection it is working fine, but if i run the sp through the odata connection it throw error

Thanks & Regards

Govindaraj s.

[shawkins]

Can you run your scenario with detail logging enabled?  Of particular interest is the org.teiid.TXN_LOG context, which should give more insight to the transactional behavior.

[govindarajs]

Sorry for the delay responding, it took long time to finding the root cause of this issue

Actually I have a VDB, which contains two source models and one view model (combination of two source model) and I have created one Stored Procedure by joining two source model table and it is working fine through JDBC connector as well as teiid OData also, but we are using Jersey web service for some extra features, SP is not working when I call through Jersey web service, regarding this when I discussed with my team, They said something like function wrapper, distributed transaction,..., afterward I didn't get any chance to touch with them, Do you have any idea about function wrapper in teiid?

Thanks & Regards

Govindaraj Sanjeevi

[rareddy]

Can you provide the logs as requested in prior comment? Otherwise there is no new information to go with.

[govindarajs]

Please find the following Transaction log, which I got while running the SP with multiple source on Jersey oDATA

04:16:47,349 DEBUG [org.teiid.TXN_LOG] (http-/0.0.0.0:8080-1) before begin:org.teiid.dqp.internal.process.TransactionServerImpl@f6169a8(Xe0IqM/m7WFe)

04:16:47,349 DEBUG [org.teiid.TXN_LOG] (http-/0.0.0.0:8080-1) after begin : Xe0IqM/m7WFe LOCAL ID:TransactionImple < ac, BasicAction: 0:ffff2238cb8c:-15cd65da:56fce01a:3f status: ActionStatus.RUNNING >

04:16:47,353 DEBUG [org.teiid.TXN_LOG] (Worker2_QueryProcessorQueue112) before getOrCreateTransactionContext:org.teiid.dqp.internal.process.TransactionServerImpl@f6169a8(Xe0IqM/m7WFe)

04:16:47,354 DEBUG [org.teiid.TXN_LOG] (Worker2_QueryProcessorQueue112) after getOrCreateTransactionContext : Xe0IqM/m7WFe LOCAL ID:TransactionImple < ac, BasicAction: 0:ffff2238cb8c:-15cd65da:56fce01a:3f status: ActionStatus.RUNNING >

04:16:47,354 DEBUG [org.teiid.TXN_LOG] (Worker2_QueryProcessorQueue112) before resume:org.teiid.dqp.internal.process.TransactionServerImpl@f6169a8(Xe0IqM/m7WFe LOCAL ID:TransactionImple < ac, BasicAction: 0:ffff2238cb8c:-15cd65da:56fce01a:3f status: ActionStatus.RUNNING >)

04:16:47,355 DEBUG [org.teiid.TXN_LOG] (Worker2_QueryProcessorQueue112) after resume : null

04:16:47,356 DEBUG [org.teiid.TXN_LOG] (Worker2_QueryProcessorQueue112) before suspend:org.teiid.dqp.internal.process.TransactionServerImpl@f6169a8(Xe0IqM/m7WFe LOCAL ID:TransactionImple < ac, BasicAction: 0:ffff2238cb8c:-15cd65da:56fce01a:3f status: ActionStatus.RUNNING >)

04:16:47,356 DEBUG [org.teiid.TXN_LOG] (Worker2_QueryProcessorQueue112) after suspend : null

04:16:47,356 DEBUG [org.teiid.TXN_LOG] (Worker2_QueryProcessorQueue112) before suspend:org.teiid.dqp.internal.process.TransactionServerImpl@f6169a8(Xe0IqM/m7WFe LOCAL ID:TransactionImple < ac, BasicAction: 0:ffff2238cb8c:-15cd65da:56fce01a:3f status: ActionStatus.RUNNING >)

04:16:47,356 DEBUG [org.teiid.TXN_LOG] (Worker2_QueryProcessorQueue112) after suspend : null

04:16:47,362 DEBUG [org.teiid.TXN_LOG] (Worker3_QueryProcessorQueue113) before suspend:org.teiid.dqp.internal.process.TransactionServerImpl@f6169a8(Xe0IqM/m7WFe LOCAL ID:TransactionImple < ac, BasicAction: 0:ffff2238cb8c:-15cd65da:56fce01a:3f status: ActionStatus.RUNNING >)

04:16:47,362 DEBUG [org.teiid.TXN_LOG] (Worker3_QueryProcessorQueue113) after suspend : null

04:16:47,383 DEBUG [org.teiid.TXN_LOG] (Worker2_QueryProcessorQueue114) before getOrCreateTransactionContext:org.teiid.dqp.internal.process.TransactionServerImpl@f6169a8(Xe0IqM/m7WFe)

04:16:47,383 DEBUG [org.teiid.TXN_LOG] (Worker2_QueryProcessorQueue114) after getOrCreateTransactionContext : Xe0IqM/m7WFe LOCAL ID:TransactionImple < ac, BasicAction: 0:ffff2238cb8c:-15cd65da:56fce01a:3f status: ActionStatus.RUNNING >

04:16:47,383 DEBUG [org.teiid.TXN_LOG] (Worker2_QueryProcessorQueue114) before resume:org.teiid.dqp.internal.process.TransactionServerImpl@f6169a8(Xe0IqM/m7WFe LOCAL ID:TransactionImple < ac, BasicAction: 0:ffff2238cb8c:-15cd65da:56fce01a:3f status: ActionStatus.RUNNING >)

04:16:47,383 DEBUG [org.teiid.TXN_LOG] (Worker2_QueryProcessorQueue114) after resume : null

04:16:47,422 DEBUG [org.teiid.TXN_LOG] (Worker2_QueryProcessorQueue114) before suspend:org.teiid.dqp.internal.process.TransactionServerImpl@f6169a8(Xe0IqM/m7WFe LOCAL ID:TransactionImple < ac, BasicAction: 0:ffff2238cb8c:-15cd65da:56fce01a:3f status: ActionStatus.ABORT_ONLY >)

04:16:47,422 DEBUG [org.teiid.TXN_LOG] (Worker2_QueryProcessorQueue114) after suspend : null

04:16:47,423 DEBUG [org.teiid.TXN_LOG] (Worker2_QueryProcessorQueue114) before suspend:org.teiid.dqp.internal.process.TransactionServerImpl@f6169a8(Xe0IqM/m7WFe LOCAL ID:TransactionImple < ac, BasicAction: 0:ffff2238cb8c:-15cd65da:56fce01a:3f status: ActionStatus.ABORT_ONLY >)

04:16:47,423 DEBUG [org.teiid.TXN_LOG] (Worker2_QueryProcessorQueue114) after suspend : null

04:16:47,425 DEBUG [org.teiid.TXN_LOG] (Worker2_QueryProcessorQueue116) before getOrCreateTransactionContext:org.teiid.dqp.internal.process.TransactionServerImpl@f6169a8(Xe0IqM/m7WFe)

04:16:47,425 DEBUG [org.teiid.TXN_LOG] (Worker2_QueryProcessorQueue116) after getOrCreateTransactionContext : Xe0IqM/m7WFe LOCAL ID:TransactionImple < ac, BasicAction: 0:ffff2238cb8c:-15cd65da:56fce01a:3f status: ActionStatus.ABORT_ONLY >

04:16:47,426 DEBUG [org.teiid.TXN_LOG] (Worker2_QueryProcessorQueue116) before resume:org.teiid.dqp.internal.process.TransactionServerImpl@f6169a8(Xe0IqM/m7WFe LOCAL ID:TransactionImple < ac, BasicAction: 0:ffff2238cb8c:-15cd65da:56fce01a:3f status: ActionStatus.ABORT_ONLY >)

04:16:47,426 DEBUG [org.teiid.TXN_LOG] (Worker2_QueryProcessorQueue116) after resume : null

04:16:47,432 DEBUG [org.teiid.TXN_LOG] (Worker2_QueryProcessorQueue116) before suspend:org.teiid.dqp.internal.process.TransactionServerImpl@f6169a8(Xe0IqM/m7WFe LOCAL ID:TransactionImple < ac, BasicAction: 0:ffff2238cb8c:-15cd65da:56fce01a:3f status: ActionStatus.ABORT_ONLY >)

04:16:47,432 DEBUG [org.teiid.TXN_LOG] (Worker2_QueryProcessorQueue116) after suspend : null

04:16:47,433 DEBUG [org.teiid.TXN_LOG] (Worker2_QueryProcessorQueue116) before suspend:org.teiid.dqp.internal.process.TransactionServerImpl@f6169a8(Xe0IqM/m7WFe LOCAL ID:TransactionImple < ac, BasicAction: 0:ffff2238cb8c:-15cd65da:56fce01a:3f status: ActionStatus.ABORT_ONLY >)

04:16:47,433 DEBUG [org.teiid.TXN_LOG] (Worker2_QueryProcessorQueue116) after suspend : null

04:16:47,434 DEBUG [org.teiid.TXN_LOG] (Worker3_QueryProcessorQueue117) before suspend:org.teiid.dqp.internal.process.TransactionServerImpl@f6169a8(Xe0IqM/m7WFe LOCAL ID:TransactionImple < ac, BasicAction: 0:ffff2238cb8c:-15cd65da:56fce01a:3f status: ActionStatus.ABORT_ONLY >)

04:16:47,435 DEBUG [org.teiid.TXN_LOG] (Worker3_QueryProcessorQueue117) after suspend : null

04:16:47,453 DEBUG [org.teiid.TXN_LOG] (Worker3_QueryProcessorQueue118) before rollback:org.teiid.dqp.internal.process.TransactionServerImpl@f6169a8(Xe0IqM/m7WFe)

04:16:47,458 DEBUG [org.teiid.TXN_LOG] (Worker3_QueryProcessorQueue118) after rollback : null

04:16:47,459 DEBUG [org.teiid.TXN_LOG] (http-/0.0.0.0:8080-1) before cancelTransactions:org.teiid.dqp.internal.process.TransactionServerImpl@f6169a8(Xe0IqM/m7WFe,false)

04:16:47,459 DEBUG [org.teiid.TXN_LOG] (http-/0.0.0.0:8080-1) after cancelTransactions : null