Creating a federated materialized query table

You use materialized query tables to cache data locally and to improve the performance of your queries. You can use nicknames from relational and nonrelational data sources to create materialized query tables.

About this task

Restrictions

Data source specific restrictions for materialized query tables
If a query has a function template in a predicate or a select list, the function template must be part of the materialized query table.
Restrictions on using materialized query tables with nicknames

Procedure

To create a materialized query table, issue a CREATE TABLE statement that references the nicknames that represent the remote data source objects that you want to include.

You can populate a user-maintained materialized query table by using an INSERT statement in a subselect statement. For example:

insert into my_mqt (select ..from n1, n2 where ..)

where the select portion of the query matches the materialized query table definition. The optimizer might use my_mqt to replace the select portion of the query. In that case, the statement becomes:

insert into my_mqt (select .. from my_mqt);

In this case, the materialized query table becomes the source of the insert operation. To prevent this from happening, you can by issue one of the following commands to temporarily disable the materialized query table:

SET CURRENT REFRESH AGE 0 
SET CURRENT MAINTAINED TABLE TYPE FOR OPTIMIZATION SYSTEM