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

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
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s