3 Replies Latest reply on Apr 6, 2015 10:17 AM by Akshay Harale

    Not able add hints in transformation query using TEIID Designer 8.5

    Akshay Harale Newbie

      Hello,

       

      I am using teiid designer 8.5 to create materialzed view on static VDB. We want to update the cache of materialized view for single row.

      To achieve this we tried adding hint /*+ cache(updatable) */. But when I modify the transformation query and save the project then " /*+ cache(updatable) */ " get removed from the query.

      Why hints getting removed from the transformation query while saving the project in teiid designer?

       

       

      Then as a work around, we modified the view.xmi and added the hints at the start of query using text editor. Like this

      :xmi:uuid="mmuuid:e93bdcbb-d3c3-459e-80ea-bcee8ca9c3b6" selectSql="/*+ cache(updatable) */ SELECT
		*
	FROM
		LocalMysql.student"/>
      
      
      
      

      Now after doing update operation on the view we fired following query to update the cache.

      {call refreshMatViewRow('MySqlView.student', '123')}

      We are getting following exception for the above procedure call:

      Error: TEIID30232 Remote org.teiid.api.exception.query.QueryProcessingException: TEIID30232 Materialized view MySqlView.student cannot have a row refreshed because it's cache hint did not specify "updatable".

       

      Please provide the solution on how to add the hints using the TEIID designer.

       

      Thanks,

      Akshay.