Not able add hints in transformation query using TEIID Designer 8.5

[akshy_harale]

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.

[shawkins]

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

This is a known issue - [TEIIDDES-2383] Transformation editor removes cache hint in SQL - JBoss Issue Tracker

If you use a later Designer, 9.0+ or 8.6.1 it should work as expected.

> We are getting following exception for the above procedure call:

The hint certainly appears to be correct, but you'd want to confirm that it's getting into the index metadata correctly since a direct edit of the xmi file isn't really supported in Designer.  If you are using Teiid 8.8 or later you can use [TEIID-3018] Reflect view transformation sql into SYSTEM schema - JBoss Issue Tracker to directly query a system table to inspect the view definition.  Otherwise you can increase the log level to see how the matview load is processed.  And of course you should be able to use a later Designer where this should just work out of the box.

[blafond]

This was fixed in 9.0 Designer via:  TEIIDDES-2383

I'd suggest moving to that release unless you have a need to stay with Eclipse Kepler release.  In that case you could install upgrade to : Index of /jbosstools/updates/release/kepler/integration-stack/teiiddesigner/8.6.1.Final

Note that the XMI files contained in a VDB archive are for Design time modelling purposes. The actual metadata delivered to the Teiid Server is delivered through the binary *.INDEX files under the archive's runtime-inf folder. So any hand-editing of the *.xmi files will not be reflected in the deployed VDB

[akshy_harale]

Hello Barry and Steven,

Thanks for quick reply. It works for me with TEIID designer 8.6.