0

I have created a teiid vdb with sql server data source. The view model contains a materialized view, need to know how to refresh the materialized view manually(like daily or hourly basis) or automatically? Is there any sql command to trigger the refresh action?

lourdh
  • 449
  • 2
  • 12
  • 30

1 Answers1

1

Yes, teiid supports both on-demand and periodic refresh of materialized views:

  • use the SYSADMIN.refreshMatView procedure to refresh the view manually
  • use the /*+ cache(ttl:<milliseconds>) */ cache hint in the view transformation to enable periodic updates

See https://docs.jboss.org/author/display/teiid810final/Internal+Materialization for more information.

asmigala
  • 348
  • 2
  • 6
  • I have tried below SP call in my kettle transformation, Execute sql script step. `CALL SYSADMIN.refreshMatViewRow('PDB_MatVw',false);` "PDB_MatVw - name of my view model." but it shows 'TEIID30351 Group 'PDB_MatVw' not found' tried using schema.PDB_MatVw and got the same error. – lourdh Mar 24 '15 at 13:59
  • I think you are mixing two things: `refreshMatViewRow` is described in the "Updatable" section in the docs I linked, and takes the name of the table and the _key_ of the row to refresh. `refreshMatView` is described in the "Using System Procedure" section and takes the name of the table and _a boolean_ indicating whether to invalidate the cache.Could you try `CALL SYSADMIN.refreshMatView('PDB_MatVw',false);` ? – asmigala Mar 24 '15 at 14:10
  • Hi Asmigala, I have tried as mentioned and it works perfectly. but out of curiosity, how to use 'TTL Snapshot Refresh'. dont know whether its a callable statement or do i need to add the `+ cache(ttl:3600000)` to my view query? – lourdh Mar 25 '15 at 13:25