1 2 Previous Next 15 Replies Latest reply on Apr 4, 2016 10:02 AM by Steven Hawkins

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

    govindaraj s Novice

      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.

        • 1. Re: Getting error while refresh the internal materialized view which has two source model
          Ramesh Reddy Master

          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?

          • 2. Re: Getting error while refresh the internal materialized view which has two source model
            govindaraj s Novice

            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.

            • 3. Re: Getting error while refresh the internal materialized view which has two source model
              Steven Hawkins Master

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

              • 5. Re: Getting error while refresh the internal materialized view which has two source model
                Steven Hawkins Master

                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.

                • 6. Re: Getting error while refresh the internal materialized view which has two source model
                  Ramesh Reddy Master

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

                  • 7. Re: Getting error while refresh the internal materialized view which has two source model
                    govindaraj s Novice

                    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.

                    • 8. Re: Getting error while refresh the internal materialized view which has two source model
                      Steven Hawkins Master

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

                      • 9. Re: Getting error while refresh the internal materialized view which has two source model
                        Ramesh Reddy Master

                        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?

                        • 10. Re: Getting error while refresh the internal materialized view which has two source model
                          govindaraj s Novice

                          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.

                          • 11. Re: Getting error while refresh the internal materialized view which has two source model
                            Steven Hawkins Master

                            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.

                            • 12. Re: Getting error while refresh the internal materialized view which has two source model
                              govindaraj s Novice

                              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

                              • 13. Re: Getting error while refresh the internal materialized view which has two source model
                                Ramesh Reddy Master

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

                                • 14. Re: Getting error while refresh the internal materialized view which has two source model
                                  govindaraj s Novice

                                  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

                                  1 2 Previous Next