CREATE TABLE status
(
  VDBName varchar(50) not null,
  VDBVersion integer not null,
  SchemaName varchar(50) not null,
  Name varchar(256) not null,
  TargetSchemaName varchar(50),
  TargetName varchar(256) not null,
  Valid boolean not null,
  LoadState varchar(25) not null,
  Cardinality long,
  Updated timestamp not null,
  LoadNumber long not null,
  PRIMARY KEY (VDBName, VDBVersion, SchemaName, Name)
);

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<vdb name="sakila" version="1">
    <description>Shows how to call JPA entities</description>
 
    <model name="pg">
        <source name="pg" translator-name="postgresql-override" connection-jndi-name="java:/sakila-ds"/>
    </model>
 
    <model name="sakila" type="VIRTUAL">
    <metadata type="DDL"><![CDATA[
        CREATE VIEW actor (
           actor_id integer,
           first_name varchar(45) NOT NULL,
           last_name varchar(45) NOT NULL,
           last_update timestamp NOT NULL
        ) OPTIONS (MATERIALIZED 'TRUE', UPDATABLE 'TRUE',
                MATERIALIZED_TABLE 'pg.public.mat_actor',
                "teiid_rel:MATERIALIZED_STAGE_TABLE" 'pg.public.mat_actor_staging',
                "teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true',
                "teiid_rel:MATVIEW_STATUS_TABLE" 'pg.public.status',
                "teiid_rel:MATVIEW_BEFORE_LOAD_SCRIPT" 'execute pg.native(''truncate table mat_actor_staging'');',
                "teiid_rel:MATVIEW_AFTER_LOAD_SCRIPT" 'execute pg.native(''ALTER TABLE mat_actor RENAME TO mat_actor_temp'');execute pg.native(''ALTER TABLE mat_actor_staging RENAME TO mat_actor'');execute pg.native(''ALTER TABLE mat_actor_temp RENAME TO mat_actor_staging;'')',
                "teiid_rel:MATVIEW_SHARE_SCOPE" 'NONE',
                "teiid_rel:MATVIEW_ONERROR_ACTION" 'THROW_EXCEPTION',
                "teiid_rel:MATVIEW_TTL" 300000,
                "teiid_rel:ON_VDB_DROP_SCRIPT" 'DELETE FROM pg.public.status WHERE Name=''actor'' AND schemaname = ''sakila''')
            AS SELECT actor_id, first_name, last_name, last_update from pg."public".actor;
     
    </metadata>
    </model>
    <translator name="postgresql-override" type="postgresql">
        <property name="SupportsNativeQueries" value="true"/>
    </translator>
</vdb>

sql=connect(${url}, ${user}, ${password});  
sql.execute("DELETE FROM mv_view.mv_UpdateProduct");  
sql.execute("INSERT INTO mv_view.mv_UpdateProduct SELECT * FROM Portfolio.UpdateProduct OPTION NOCACHE");  
sql.close();  

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<vdb name="sakila" version="1">
 
    <model name="pg">
        <source name="pg" translator-name="postgresql" connection-jndi-name="java:/sakila-ds"/>
    </model>
 
    <model name="sakila" type="VIRTUAL">
    <metadata type="DDL"><![CDATA[
        CREATE VIEW actor (
           actor_id integer,
           first_name varchar(45) NOT NULL,
           last_name varchar(45) NOT NULL,
           last_update timestamp NOT NULL
        ) OPTIONS (materialized true, "teiid_rel:MATVIEW_TTL" 120000,
               "teiid_rel:MATVIEW_PREFER_MEMORY" 'true',
               "teiid_rel:MATVIEW_UPDATABLE" 'true',
               "teiid_rel:MATVIEW_SCOPE" 'vdb')
            AS SELECT actor_id, first_name, last_name, last_update from pg."public".actor;
     ]>
    </metadata>
    </model>
</vdb>

CALL SYSADMIN.refreshMatView(viewname=>'schema.matview', invalidate=>true)

/*+ cache(ttl:3600000) */ select t.col, t1.col from t, t1 where t.id = t1.id

/*+ cache(updatable) */ select t.col, t1.col from t, t1 where t.id = t1.id

CALL SYSADMIN.refreshMatViewRow(viewname=>'schema.matview', key=>5)

  
   lookup('ISOCountryCodes', 'CountryCode', 'CountryName', 'United States')
   

  
	 SELECT (SELECT CountryCode From MatISOCountryCodes WHERE CountryName = tbl.CountryName) as cc FROM tbl
	 

  
   public class ChanageDataCapture {
 
    public void invalidate() {
        InitialContext ic = new InitialContext();
        EventDistributor ed = ((EventDistributorFactory)ic.lookup("teiid/event-distributor-factory")).getEventDistributor();
 
        // this below line indicates that Customer table in the "model-name" schema has been changed.
        // this result in cache reload.
        ed.dataModification("vdb-name", "version", "model-name", "Customer");
    }
}
   

		Properties info = new Properties();

...

info.setProperty("ResultSetCacheMode", "true");

Connection conn = DriverManager.getConnection(url, info);

  
  ...
PreparedStatement ps = connection.prepareStatement("/*+ cache */ select col from t where col2 = ?");
ps.setInt(1, 5);
ps.execute();
...
  

  
  /*+ cache(pref_mem ttl:60000) */ select col from t
  

/*+ cache */
BEGIN
    ...
END

<vdb name="vdbname" version="1">
    <model name="Customers">
        <property name="teiid_rel:data-ttl" value="0"/>
        ...</para>
        

  
   connectAsAdmin()
clearCache("QUERY_SERVICE_RESULT_SET_CACHE")
...
   

  
   SELECT * from vg1, vg2, vg3 WHERE … OPTION NOCACHE
   

  
   SELECT * from vg1, vg2, vg3 WHERE … OPTION NOCACHE vg1, vg3