Internal Materialization : Dependent materialized views are not getting updated consistently

[ragava28]

Hi Guys,

I have a view(ex: VIEW_TOPLEVEL) in which Internal Materialization is enabled with TTL as 5 Minutes, this view is referring 3 other views (ex: CHILD_VIEW1,CHILD_VIEW2,CHILD_VIEW3) in which Internal Materialization is enabled with TTL as 5 Minutes.

When run VIEW_TOPLEVEL view for the first time it is automatically triggering caching job for CHILD_VIEW1,CHILD_VIEW2. But it is not triggering caching job for CHILD_VIEW3.  But after 5 minutes(after expiry of initially loaded view data ) when we re-run VIEW_TOPLEVEL view , this time it is triggering caching job for CHILD_VIEW3 .

I am not able to understand why did job for CHILD_VIEW3 didn't start very first time . why did it start next time .

Any thoughts on this?

Regards

Raghu

[ragava28]

Interesting While doing search I found this thread Re: TEIID does not refreshes after TTL have been passed   in which it is mentioned that

"

the "proactive" materialized views will refresh them self anyway, even if noone uses them, and whenever you need the view, it will always be fast.

"

Do we have such option to automatically load materialized  views with out user access ?  Can any one share settings to configure this setting in Teiid Designer.

[rareddy]

Raghu,

I would suspect that CHILD_VIEW3 is already once loaded before you issued the query for VIEW_TOPLEVEL thus the anomaly, otherwise if that is first time loading then it would have behaved exactly similar to other CHILD_VIEWS. The scheduler for cache refreshes is not exact up to the millisecond, it is close approximation of time.

Now, internal materialization at the end of the TTL, the cache gets invalidated not refreshed. Only when a user query comes in looking for data then it sees the invalidated flag, then refreshes the cache and serves the contents. During re-load/refresh your query will be waiting for the job to finish.

You can proactively call / schedule (using cron job, or windows scheduler) a refresh of cache, using "exec SYSADMIN.refreshMatview", which will keep the cache always fresh. If you are using latest versions of Teiid, you can also use in built scheduler to keep these caches fresh, see our documentation on ALLOW_MATVIEW_MANAGEMENT. I will also answer your related question on this here How to Set Up teiid_rel:ALLOW_MATVIEW_MANAGEMENT property in Teiid Designer based VDB

HTH

Ramesh..