error code – P

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

Tag: error code

The fullselect specified for the materialized query table “SCHEMA.TABLE” is not valid