All types of materialized query tables (MQTs) are supported with partitioned tables. When working with partitioned MQTs, there are a number of guidelines that can help you to administer attached and detached data partitions most effectively.
Example 1: Converting a partitioned MQT to an ordinary table
Although the ATTACH operation is not directly supported on partitioned MQTs, you can achieve the same effect by converting a partitioned MQT to an ordinary table, performing the desired roll-in and roll-out of table data, and then converting the table back into an MQT. The following CREATE TABLE and ALTER TABLE statements demonstrate the effect:
CREATE TABLE lineitem (
l_orderkey DECIMAL(10,0) NOT NULL,
l_quantity DECIMAL(12,2),
l_shipdate DATE,
l_year_month INT GENERATED ALWAYS AS (YEAR(l_shipdate)*100 + MONTH(l_shipdate)))
PARTITION BY RANGE(l_shipdate)
(STARTING ('1/1/1992') ENDING ('12/31/1993') EVERY 1 MONTH);
CREATE TABLE lineitem_ex (
l_orderkey DECIMAL(10,0) NOT NULL,
l_quantity DECIMAL(12,2),
l_shipdate DATE,
l_year_month INT,
ts TIMESTAMP,
msg CLOB(32K));
CREATE TABLE quan_by_month (
q_year_month, q_count) AS
(SELECT l_year_month AS q_year_month, COUNT(*) AS q_count
FROM lineitem
GROUP BY l_year_month)
DATA INITIALLY DEFERRED REFRESH IMMEDIATE
PARTITION BY RANGE(q_year_month)
(STARTING (199201) ENDING (199212) EVERY (1),
STARTING (199301) ENDING (199312) EVERY (1));
CREATE TABLE quan_by_month_ex(
q_year_month INT,
q_count INT NOT NULL,
ts TIMESTAMP,
msg CLOB(32K));
SET INTEGRITY FOR quan_by_month IMMEDIATE CHECKED;
CREATE INDEX qbmx ON quan_by_month(q_year_month);
ALTER TABLE quan_by_month DROP MATERIALIZED QUERY;
ALTER TABLE lineitem DETACH PARTITION part0 INTO li_reuse;
ALTER TABLE quan_by_month DETACH PARTITION part0 INTO qm_reuse;
SET INTEGRITY FOR li_reuse OFF;
ALTER TABLE li_reuse ALTER l_year_month SET GENERATED ALWAYS
AS (YEAR(l_shipdate)*100 + MONTH(l_shipdate));
LOAD FROM part_mqt_rotate.del OF DEL MODIFIED BY GENERATEDIGNORE
MESSAGES load.msg REPLACE INTO li_reuse;
DECLARE load_cursor CURSOR FOR
SELECT l_year_month, COUNT(*)
FROM li_reuse
GROUP BY l_year_month;
LOAD FROM load_cursor OF CURSOR MESSAGES load.msg
REPLACE INTO qm_reuse;
ALTER TABLE lineitem ATTACH PARTITION STARTING '1/1/1994'
ENDING '1/31/1994' FROM li_reuse;
SET INTEGRITY FOR lineitem ALLOW WRITE ACCESS IMMEDIATE CHECKED
FOR EXCEPTION IN lineitem USE lineitem_ex;
ALTER TABLE quan_by_month ATTACH PARTITION STARTING 199401
ENDING 199401 FROM qm_reuse;
SET INTEGRITY FOR quan_by_month IMMEDIATE CHECKED
FOR EXCEPTION IN quan_by_month USE quan_by_month_ex;
ALTER TABLE quan_by_month ADD MATERIALIZED QUERY
(SELECT l_year_month AS q_year_month, COUNT(*) AS q_count
FROM lineitem
GROUP BY l_year_month)
DATA INITIALLY DEFERRED REFRESH IMMEDIATE;
SET INTEGRITY FOR QUAN_BY_MONTH ALL IMMEDIATE UNCHECKED;
Use the SET INTEGRITY statement with the IMMEDIATE CHECKED option to check the attached data partition for integrity violations. This step is required before changing the table back to an MQT. The SET INTEGRITY statement with the IMMEDIATE UNCHECKED option is used to bypass the required full refresh of the MQT. The index on the MQT is necessary to achieve optimal performance. The use of exception tables with the SET INTEGRITY statement is recommended, where appropriate.
Typically, you create a partitioned MQT on a large fact table that is also partitioned. If you do roll out or roll in table data on the large fact table, you must adjust the partitioned MQT manually, as demonstrated in Example 2.
Example 2: Adjusting a partitioned MQT manually
ALTER TABLE quan_by_month DROP MATERIALIZED QUERY;
ALTER TABLE lineitem DETACH PARTITION part0 INTO li_reuse;
LOAD FROM part_mqt_rotate.del OF DEL MESSAGES load.msg REPLACE INTO li_reuse;
ALTER TABLE quan_by_month DETACH PARTITION part0 INTO qm_reuse;
Prune qm_reuse before doing the insert. This deletes the detached data before inserting the subselect data. This is accomplished with a load replace into the MQT where the data file of the load is the content of the subselect.
db2 load from datafile.del of del replace into qm_reuse
INSERT INTO qm_reuse
(SELECT COUNT(*) AS q_count, l_year_month AS q_year_month
FROM li_reuse
GROUP BY l_year_month);
ALTER TABLE lineitem ATTACH PARTITION STARTING '1/1/1994'
ENDING '1/31/1994' FROM TABLE li_reuse;
SET INTEGRITY FOR lineitem ALLOW WRITE ACCESS IMMEDIATE CHECKED FOR
EXCEPTION IN li_reuse USE li_reuse_ex;
ALTER TABLE quan_by_month ATTACH PARTITION STARTING 199401
ENDING 199401 FROM TABLE qm_reuse;
SET INTEGRITY FOR quan_by_month IMMEDIATE CHECKED;
ALTER TABLE quan_by_month ADD MATERIALIZED QUERY
(SELECT COUNT(*) AS q_count, l_year_month AS q_year_month
FROM lineitem
GROUP BY l_year_month)
DATA INITIALLY DEFERRED REFRESH IMMEDIATE;
SET INTEGRITY FOR QUAN_BY_MONTH ALL IMMEDIATE UNCHECKED;
The data is not accessible until it has been validated by the SET INTEGRITY statement. Although the REFRESH TABLE operation is supported, this scenario demonstrates the manual maintenance of a partitioned MQT through the ATTACH PARTITION and DETACH PARTITION operations. The data is marked as validated by the user through the IMMEDIATE UNCHECKED clause of the SET INTEGRITY statement.