> 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.
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
Hello Barry and Steven,
Thanks for quick reply. It works for me with TEIID designer 8.6.