2 Replies Latest reply on Nov 10, 2014 2:45 AM by Jan Stastny

    Issues with caching table using External Materialization

    Arpit Gupta Newbie

      Hi,

       

      I am trying to cache the data from one table in Shark into another table in H2.

      I am following the steps from here :

      https://docs.jboss.org/author/display/teiid87final/External+Materialization

       

      I am implementing it using the Dynamic VDB :

       

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

      <vdb name="sample" version="214">

       

          <description>Dynamic VDB</description>

         

      <model name="H2Model">

        <source name="H2Model" translator-name="h2" connection-jndi-name="java:/accountsNew-ds"/>

      </model>

       

      <model name="SharkModel">

        <source name="SharkModel" translator-name="hive" connection-jndi-name="java:/Shark"/>

      </model>

       

      <model name="sakila" type="VIRTUAL">

        <metadata type="DDL"><![CDATA[

      CREATE view nation_view

      (

      n_nationkey integer,

      n_name varchar,

      n_regionkey integer,

      n_comment varchar

      ) OPTIONS (MATERIALIZED 'TRUE', UPDATABLE 'TRUE',

                     MATERIALIZED_TABLE 'h2_nation_mat',

                     "teiid_rel:MATVIEW_BEFORE_LOAD_SCRIPT" 'execute H2Model.native()',

                     "teiid_rel:MATVIEW_AFTER_LOAD_SCRIPT" 'execute H2Model.native()',

                     "teiid_rel:ON_VDB_DROP_SCRIPT" 'DELETE FROM status WHERE Name=''nation'' AND schemaname = ''sakila''',

                     "teiid_rel:MATERIALIZED_STAGE_TABLE" 'h2_nation_mat',

                     "teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true',

                     "teiid_rel:MATVIEW_STATUS_TABLE" 'status',

                     "teiid_rel:MATVIEW_SHARE_SCOPE" 'NONE',

                     "teiid_rel:MATVIEW_ONERROR_ACTION" 'THROW_EXCEPTION')

      AS select * FROM SharkModel.nation;

          ]]>

         </metadata>

      </model>

             

      </vdb>

       

      When I run the above VDB it is showing the error :

       

      16:34:06,451 WARN  [org.teiid.PROCESSOR.MATVIEWS] (MSC service thread 1-8) null TEIID50100 Trigger execution result: org.jboss.as.controller.OperationFailedException [ "TEIID31099 VDB sample.213[H2Model{H2Model=H2Model, h2, java:/accountsNew-ds}, SharkModel{SharkModel=SharkModel, hive, java:/Shark}, caching{}] is not active.  Resubmit your query after loading has completed or after the errors have been corrected." ]: org.teiid.jdbc.TeiidSQLException: TEIID50100 Trigger execution result: org.jboss.as.controller.OperationFailedException [ "TEIID31099 VDB sample.213[H2Model{H2Model=H2Model, h2, java:/accountsNew-ds}, SharkModel{SharkModel=SharkModel, hive, java:/Shark}, caching{}] is not active.  Resubmit your query after loading has completed or after the errors have been corrected." ]

        at org.teiid.jboss.MaterializationManagementService$1.executeQuery(MaterializationManagementService.java:104) [teiid-jboss-integration-8.7.0.Final.jar:8.7.0.Final]

        at org.teiid.runtime.MaterializationManager$1.process(MaterializationManager.java:91) [teiid-runtime-8.7.0.Final.jar:8.7.0.Final]

        at org.teiid.runtime.MaterializationManager.doMaterializationActions(MaterializationManager.java:158) [teiid-runtime-8.7.0.Final.jar:8.7.0.Final]

        at org.teiid.runtime.MaterializationManager.beforeRemove(MaterializationManager.java:83) [teiid-runtime-8.7.0.Final.jar:8.7.0.Final]

        at org.teiid.deployers.VDBRepository.notifyBeforeRemove(VDBRepository.java:378) [teiid-runtime-8.7.0.Final.jar:8.7.0.Final]

        at org.teiid.deployers.VDBRepository.removeVDB(VDBRepository.java:256) [teiid-runtime-8.7.0.Final.jar:8.7.0.Final]

        at org.teiid.deployers.VDBRepository.removeVDB(VDBRepository.java:252) [teiid-runtime-8.7.0.Final.jar:8.7.0.Final]

        at org.teiid.jboss.VDBService.stop(VDBService.java:272) [teiid-jboss-integration-8.7.0.Final.jar:8.7.0.Final]

        at org.jboss.msc.service.ServiceControllerImpl$StopTask.stopService(ServiceControllerImpl.java:1911) [jboss-msc-1.0.4.GA.jar:1.0.4.GA]

        at org.jboss.msc.service.ServiceControllerImpl$StopTask.run(ServiceControllerImpl.java:1874) [jboss-msc-1.0.4.GA.jar:1.0.4.GA]

        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_55]

        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_55]

        at java.lang.Thread.run(Thread.java:744) [rt.jar:1.7.0_55]

       

      If I deploy the above VDB without the "OPTIONS" part while creating the VIEW then it runs perfectly and I can run query over the "nation_view".

       

      You can find the further details on error with the attached file.

      I have also attached the VDB I am using.