This feature allows you to cache remote data locally on your DB2 Universal 

Database instance. Remote data resides in databases that are supported by 

relational DBMS instances such as Oracle or Sybase, or even other instances 

of DB2 UDB.

MQTs can reference a combination of nicknames and local tables. Such 

materialized query tables can be created with the 

REFRESH DEFERRED 

option 

only. Queries against nicknames or tables are rewritten and optimized in 

relation to these MQTs.

Routing a query to the MQT when all criteria for matching and routing are 

satisfied, yields better performance than getting results from the remote table.

It is possible to query a nickname even if the remote table for which the 

nickname was created becomes unavailable. If this nickname has a 

materialized query table defined on it, and all routing criteria match, the query 

will only need to select data from the MQT.

Maintenance is performed locally by means of the 

REFRESH TABLE 

command. 

There is no way to keep track of updates to tables in a remote database. 

Maintenance is always deferred; refresh immediate materialized query tables 

(defined on nicknames) are not supported.

Incremental maintenance of materialized query tables using a staging 

table

You can incrementally refresh an MQT defined with the 

REFRESH DEFERRED 

option. If a refresh deferred MQT is to be incrementally maintained, it must 

have a staging table associated with it. The staging table associated with an 

MQT is created with the 

CREATE TABLE 

SQL statement.

When 

INSERT

,

 DELETE 

or

 UPDATE 

statements modify the underlying tables of 

an MQT, the changes resulting from these modifications are propagated, and 

are immediately appended to a staging table as part of the same statement. 

The propagation of these changes to the staging table is similar to the 

propagation of changes that occurs during the incremental refresh of 

immediate MQTs.

A 

REFRESH TABLE

 statement is used to incrementally refresh the MQT. If a 

staging table is associated with the MQT, the system may be able to use the 

staging table that supports the MQT to incrementally refresh it. The staging 

table is pruned when the refresh is complete. Prior to Version 8, a refresh 

deferred MQT was regenerated from scratch when performing a refresh table 

operation. MQTs can now be incrementally maintained, providing significant 

performance improvement. For information about the situations under which 

a staging table will not be used to incrementally refresh an MQT, see the 

SQL 

Reference 

manual.

You can also use this new facility to eliminate the high lock contention caused 

by the immediate maintenance of refresh immediate MQTs. If the data in the 

330 

MySQL to DB2 UDB Conversion Guide