Learn more about the materialized views of DB2

The DB2 Materialized View (MQT) is a special type of view. The following describes the materialized view of DB2 for your reference. It is helpful for you to learn about the DB2 database.

MQT can be considered as a materialized view. Both the view and MQT are defined based on a query. When a view is referenced, the view-Based Query runs. However, MQT actually saves the query results as data. You can use the data in MQT instead of the data in the underlying table.

Materialized query tables can significantly improve query performance, especially the performance of complex queries. If the optimizer determines that a part of the query can be solved using an MQT, the query will be rewritten to use MQT. MQT can be defined when a table is created, system-maintained MQT, or user-maintained MQT.

When creating the DB2 materialized view maintained by the system, you can specify whether the table data is refresh immediate or refresh deferred. You can use the REFRESH keyword to specify how to maintain data. DEFERRED means that the data in the TABLE can be refreshed at any time through the refresh table statement. Whether it is refresh deferred or the MQT maintained by the refresh immediate system, the insert, update, or delete operations on them are not allowed. However, for the refresh immediate-type MQT maintained by the system, you can update it by performing the insert, update, or delete operation on the underlying table.

 
 

  
  
create table course_query as   
  
  
(   
  
  
select courseware_id, other_courseware_name from   
  
  
(   
  
  
select distinct courseware_id, other_courseware_name   
  
  
from metadata   
  
  
where other_courseware_name in   
  
  
(   
  
  
select distinct Other_courseware_name   
  
  
from METADATA   
  
  
where is_delete like '0'   
  
  
)   
  
  
)    
  
  
)  
  
  
data initially deferred refresh deferred;  
  
  
refresh table course_query;  
 
 

DB2 logfilsiz parameter settings

DB2 command line connection

Implementation of DB2 circular Query

How to view DB2 dynamic SQL statements

Learn about the DB2 Index Structure