Provides you with an in-depth understanding of the DB2 materialized query table.

DB2 materialized query table MQT) is a special type of table in the DB2 database. The following describes the knowledge of the materialized query table in DB2 and hopes to help you.

1. Differences between materialized query tables and views in DB2
A view is a query statement. A materialized query table is a query result set.

2. Create a DB2 materialized query table
Materialized query tables can be defined when a temporary table is created. There are two types.
· MQT maintained by the System
There are two types of mqts maintained by the system: refresh immediate and refresh deferred. The former can be automatically refreshed after you modify the underlying table, and the latter can pass
Refresh table statement. For refresh immediate MQT, select must contain at least one unique key for each table FROM. If the unique key is not included, the following error occurs: SQLSTATE: 428EC is the specific query table, and the specified full query is invalid ).
PS: the statement that adds the uniqueness constraint is:
Alter table dm_gj add unique (gj_dm)

Create an MQT statement:
Create table topicis. mqt_test
(Select a. gj_dm country code, a. gj_mc country name from topicis. dm_gj a) data initially deferred refresh immediate

The data initially deferred refresh immediate is translated into Chinese.

After the creat table is created, the MQT is in the suspended state and data must be filled for query:
Statement:
Set integrity for topicis. mqt_test immediate checked not incremental

Set integrity
Immediate cheched checks the data according to the defined SELECT and refreshes
Not incremental checks the integrity of the entire table

After the set, the MQT can be used for query.

Country code country name
-------- Success ------------------------------------------------------------------------------------------------------------
004 Asia-Afghanistan
008 Europe-Albania
012 Africa-Algeria
016 Oceania-American Samoa
020 Europe-Andorra
024 Africa-Angola
028 Latin America-Guatemala
032 Latin America-Argentina
036 Oceania-Australia
040 Europe-Austria
044 Latin America-Bahamas
048 Asia-Bahrain
050 Asia-Bangladesh
052 Latin America-Barbados
056 Europe-Belgium
060 North America-Bermuda
064 Asia-Bhutan
068 Latin America-Bolivia
072 Africa-Botswana
074 others-buvi Island
......
It should be noted that the insert, update, and delete operations on the materialized query tables maintained by the system are not allowed. You can change the MQT data through insert, update, and delete operations on the underlying table.
User-maintained MQT
The difference between user-maintained MQT and system maintenance is that the system-maintained MQT can use the refresh table command to refresh the data, and the user-maintained MQT cannot. The user-maintained MQT can be directly inserted, updated, or deleted, and the MQT maintained by the system cannot.
Create an MQT statement:
Create table topicis. mqt_test
(Select a. gj_dm country code, a. gj_mc country name from topicis. dm_gj a) data initially deferred refresh deferred maintained by user
The user-maintained MQT must be refresh deferred. The word maintained means persistence. Maintenance. maintained by user means user maintenance.

Like the MQT maintained by the system, set integrity is also required.
Set integrity for topicis. mqt_test materialized query immediate unchecked

Materialized: materialized
Immediate unchecked indicates that integrity constraints are not checked for the table.
At this time, if you query the MQT, you will find that there are 0 data records. Because there is no data in the MQT. You need to insert data manually, or use the SELECT statement to export data from the underlying table, and then import the data.
In general, the user-maintained DB2 materialized query table is equivalent to creating a new entity table based on several original underlying tables.
 

Rollback of A DB2 partitioned Database

Three types of DB2 database backup solutions

Implementation of batch SQL script execution in DB2

Online Implementation of changing the DB2 page size

DB2 tablespace silent status