These are some common issues you can get while creating an MQT (Materialized Query Table) in DB2, especially when using a ‘REFRESH IMMEDIATE’ type of MQT.
Error: The fullselect specified for the materialized query table “PW999.PW999″ is not valid. Reason code = “6″.. SQLCODE=-20058, SQLSTATE=428EC
CREATE TABLE PW999.PW999 ( N_ID, D_DATUM, T_AAN, T_VAN, N_D1_IDF) AS (
SELECT DISTINCT A.N_I_ID AS N_ID,
...
Cause: The query that causes this error has a distinct statement, these are not allowed in an MQT. Your query must be designed in such way that each returned row is unique.
Error: The fullselect specified for the materialized query table “PW999.PW999″ is not valid. Reason code = “7″.. SQLCODE=-20058, SQLSTATE=428EC,
CREATE TABLE PW999.PW999 ( IDF, DIRECTIE_IDF, AANVRAAG, REFERTE ) AS (
SELECT A.IDF AS IDF,
A.DIRECTIE_IDF AS DIRECTIE_IDF,
TO_DATE(NULLIF(TRIM(D1.VALUE), ''), 'dd/mm/yyyy') AS AANVRAAG,
D2.VALUE AS REFERTE
FROM REQUEST A,
REQUEST_DATA D1,
REQUEST_DATA D2
WHERE A.REQUEST_TYPE = 215
AND (A.N_I_IDF = D1.REQUEST_IDF AND D1.REQUEST_FIELD_IDF = 36)
AND (A.N_I_IDF = D2.REQUEST_IDF AND D2.REQUEST_FIELD_IDF = 41)
) DATA INITIALLY DEFERRED REFRESH IMMEDIATE MAINTAINED BY SYSTEM
Cause: if you have an IMMEDIATE REFRESH MQT then you must select the primary keys of all referenced tables. In the previous query I did not select the primary keys for D1 and D2. Without these primary keys DB2 can’t do its incremental updates.
Error: The fullselect specified for the materialized query table “PW999.PW999″ is not valid. Reason code = “10″.. SQLCODE=-20058, SQLSTATE=428EC
CREATE TABLE PW999.PW999 ( IDF, DIRECTIE_IDF, AANVRAAG, REFERTE ) AS (
SELECT A.IDF AS IDF,
A.DIRECTIE_IDF AS DIRECTIE_IDF,
TO_DATE(NULLIF(TRIM(D1.VALUE), ''), 'dd/mm/yyyy') AS AANVRAAG,
D2.VALUE AS REFERTE
FROM REQUEST A
LEFT JOIN REQUEST_DATA D1 ON
(A.N_I_IDF = D1.REQUEST_IDF AND D1.REQUEST_FIELD_IDF = 36)
LEFT JOIN REQUEST_DATA D2 ON
(A.N_I_IDF = D2.REQUEST_IDF AND D2.REQUEST_FIELD_IDF = 41)
WHERE A.REQUEST_TYPE = 215
) DATA INITIALLY DEFERRED REFRESH IMMEDIATE MAINTAINED BY SYSTEM
Cause: the explicit JOIN statement is not allowed, just must join using where clauses.
The correct way of creating the MQT:
CREATE TABLE PW999.PW999 (IDF, DIRECTIE_IDF, AANVRAAG, REFERTE, D1_IDF, D2_IDF) AS (
SELECT A.IDF AS IDF,
A.DIRECTIE_IDF AS DIRECTIE_IDF,
TO_DATE(NULLIF(TRIM(D1.VALUE), ''), 'dd/mm/yyyy') AS AANVRAAG,
D2.VALUE AS REFERTE,
D1.IDF AS D1_IDF,
D2.IDF AS D2_IDF
FROM REQUEST A,
REQUEST_DATA D1,
REQUEST_DATA D2
WHERE A.REQUEST_TYPE = 215
AND (A.N_I_IDF = D1.REQUEST_IDF AND D1.REQUEST_FIELD_IDF = 36)
AND (A.N_I_IDF = D2.REQUEST_IDF AND D2.REQUEST_FIELD_IDF = 41)
) DATA INITIALLY DEFERRED REFRESH IMMEDIATE MAINTAINED BY SYSTEM
And it’s correct because:
- No JOIN statement
- No DISTINCT statement
- All primary keys are selected
- All returned rows are unique
)
.
