Red Hat Training

A Red Hat training course is available for Red Hat JBoss Data Virtualization

3.4. External Materialization Options

If you are trying to load the materialized table "Portfolio.UpdateProduct", for which the materialization table is defined as "mv_view.UpdateProduct", use any JDBC Query tool like SquirreL and make a JDBC connection to the VDB you created and issue following SQL command: INSERT INTO mv_view.mv_UpdateProduct SELECT * FROM Portfolio.UpdateProduct OPTION NOCACHE
Here is how you would create an AdminShell script to automatically load the materialized table: 
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();
Use this command to execute the script: adminshell.sh . load.groovy

Note

  • If you want to set up a job to run this script frequently at regular intervals, then on Red Hat Enterprise Linux use "cron tab" or on Microsoft Windows use "Windows Scheduler" to refresh the rows in the materialized table. Every time the script runs it will refresh the contents.
  • This job needs to be run only when user access is restricted.

Important

There are some situation in which this process of loading the cache will not work. Here are some situations in which it will not work:
  • If it is updating all the rows in the materialized table, and you only need to update only few rows to avoid long refresh time.
  • If it takes an hour for your reload your materialized table, queries executed during that time will fail to povide correct results.
  • Also ensure that you create indexes on your materialization table after the data is loaded, as having indexes during the load process slows down the loading of data, especially when you are dealing with a large number of rows.