467,118 Members | 961 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,118 developers. It's quick & easy.

MQT

Raj
I am trying to create an MQT using the following select statement and
refresh immediate option
SELECT c1,
c2,
MIN(c3) AS c3,
MIN(c4) AS c4

FROM t1
WHERE c5= 'xyz'
GROUP BY c1,c2
error:
--------------------------------------------------------------------------------------------------------------------------------------
The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it
returned:
SQL20058N The fullselect specified for the materialized query table is
not valid. Reason code = "4". SQLSTATE=428EC

db2 "? SQL20058N "
--------------------------------------------------------------------------------------------------------------------------------------
4 The fullselect must not contain references to functions that:

o depend on physical characteristics of the data, for example
DBPARTITIONNUM, HASHEDVALUE

o are defined as EXTERNAL ACTION

o are defined as LANGUAGE SQL, CONTAINS SQL, READS SQL DATA or
MODIFIES SQL DATA

Response
--------------------------------------------------------------------------------------------------------------------------------------
4 Correct the CREATE TABLE statement to ensure that no
unsupported functions are referenced.
--------------------------------------------------------------------------------------------------------------------------------------

Refresh deferred completes successfully.. Is there a limitation in db2
in selecting min's in a refresh immediate MQT?

May 2 '06 #1
  • viewed: 5273
Share:
4 Replies
Raj wrote:
I am trying to create an MQT using the following select statement and
refresh immediate option
SELECT c1,
c2,
MIN(c3) AS c3,
MIN(c4) AS c4

FROM t1
WHERE c5= 'xyz'
GROUP BY c1,c2
error:
----------------------------------------------------------------------
---------------------------------------------------------------- The
command was processed as an SQL statement because it was not a valid
Command Line Processor command. During SQL processing it returned:
SQL20058N The fullselect specified for the materialized query table
is not valid. Reason code = "4". SQLSTATE=428EC

db2 "? SQL20058N "
----------------------------------------------------------------------
---------------------------------------------------------------- 4
The fullselect must not contain references to functions that:

o depend on physical characteristics of the data, for example
DBPARTITIONNUM, HASHEDVALUE

o are defined as EXTERNAL ACTION

o are defined as LANGUAGE SQL, CONTAINS SQL, READS SQL DATA or
MODIFIES SQL DATA

Response
----------------------------------------------------------------------
---------------------------------------------------------------- 4
Correct the CREATE TABLE statement to ensure that no unsupported
functions are referenced.
----------------------------------------------------------------------
----------------------------------------------------------------

Refresh deferred completes successfully.. Is there a limitation in db2
in selecting min's in a refresh immediate MQT?


Indeed there is. From the CREATE TABLE documentation:

When a GROUP BY clause is specified, the following considerations apply:

When REFRESH IMMEDIATE is specified:
[... lots of other rules ...]
* The supported column functions are SUM, COUNT, COUNT_BIG and
GROUPING (without DISTINCT). The select list must contain a COUNT(*) or
COUNT_BIG(*) column. If the materialized query table select list
contains SUM(X), where X is a nullable argument, the materialized query
table must also have COUNT(X) in its select list. These column
functions cannot be part of any expressions.
* A HAVING clause is not allowed.
[... lots more rules ...]

Sorry! Looks like you'll have to stick with DEFERRED unless there's
another way around this?
Dave.
--

May 2 '06 #2
Hi Raj,

I think
SELECT c1,
c2,
MIN(c3) AS c3,
MIN(c4) AS c4
COUNT(*) as count
FROM t1
WHERE c5= 'xyz'
GROUP BY c1,c2

should work. The COUNT(*) is an obligation in your case in immediate mode.
If you don't want it, you need to use DEFERRED.

Hope this helps,

JM

"Dave Hughes" <da**@waveform.plus.com> a écrit dans le message de
news:44***********************@ptn-nntp-reader03.plus.net...
Raj wrote:
I am trying to create an MQT using the following select statement and
refresh immediate option
SELECT c1,
c2,
MIN(c3) AS c3,
MIN(c4) AS c4

FROM t1
WHERE c5= 'xyz'
GROUP BY c1,c2
error:
----------------------------------------------------------------------
---------------------------------------------------------------- The
command was processed as an SQL statement because it was not a valid
Command Line Processor command. During SQL processing it returned:
SQL20058N The fullselect specified for the materialized query table
is not valid. Reason code = "4". SQLSTATE=428EC

db2 "? SQL20058N "
----------------------------------------------------------------------
---------------------------------------------------------------- 4
The fullselect must not contain references to functions that:

o depend on physical characteristics of the data, for example
DBPARTITIONNUM, HASHEDVALUE

o are defined as EXTERNAL ACTION

o are defined as LANGUAGE SQL, CONTAINS SQL, READS SQL DATA or
MODIFIES SQL DATA

Response
----------------------------------------------------------------------
---------------------------------------------------------------- 4
Correct the CREATE TABLE statement to ensure that no unsupported
functions are referenced.
----------------------------------------------------------------------
----------------------------------------------------------------

Refresh deferred completes successfully.. Is there a limitation in db2
in selecting min's in a refresh immediate MQT?


Indeed there is. From the CREATE TABLE documentation:

When a GROUP BY clause is specified, the following considerations apply:

When REFRESH IMMEDIATE is specified:
[... lots of other rules ...]
* The supported column functions are SUM, COUNT, COUNT_BIG and
GROUPING (without DISTINCT). The select list must contain a COUNT(*) or
COUNT_BIG(*) column. If the materialized query table select list
contains SUM(X), where X is a nullable argument, the materialized query
table must also have COUNT(X) in its select list. These column
functions cannot be part of any expressions.
* A HAVING clause is not allowed.
[... lots more rules ...]

Sorry! Looks like you'll have to stick with DEFERRED unless there's
another way around this?
Dave.
--

May 2 '06 #3
Jean-Marc Blaise wrote:
Hi Raj,

I think
SELECT c1,
c2,
MIN(c3) AS c3,
MIN(c4) AS c4
COUNT(*) as count
FROM t1
WHERE c5= 'xyz'
GROUP BY c1,c2

should work. The COUNT(*) is an obligation in your case in immediate mode.
If you don't want it, you need to use DEFERRED.


This doesn't work - at least not for me. I think the problem is simply that
the MIN values cannot be automatically maintained when tuples in the table
T1 change. INSERT statements can easily be calculated, but for delete or
update operations, you'll have to scan the complete table in case that the
row with the minimum was deleted/updated and DB2 would have to find the new
min. (I don't know if this is the correct explanation, though.)

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
May 2 '06 #4
Hi Knut,

My mistake :-), I buy your explanation.

Cheers,

JM

"Knut Stolze" <st****@de.ibm.com> a écrit dans le message de
news:e3**********@lc03.rz.uni-jena.de...
Jean-Marc Blaise wrote:
Hi Raj,

I think
SELECT c1,
c2,
MIN(c3) AS c3,
MIN(c4) AS c4
COUNT(*) as count
FROM t1
WHERE c5= 'xyz'
GROUP BY c1,c2

should work. The COUNT(*) is an obligation in your case in immediate mode. If you don't want it, you need to use DEFERRED.
This doesn't work - at least not for me. I think the problem is simply

that the MIN values cannot be automatically maintained when tuples in the table
T1 change. INSERT statements can easily be calculated, but for delete or
update operations, you'll have to scan the complete table in case that the
row with the minimum was deleted/updated and DB2 would have to find the new min. (I don't know if this is the correct explanation, though.)

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

May 2 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by William C. White | last post: by
2 posts views Thread by Albert Ahtenberg | last post: by
3 posts views Thread by James | last post: by
reply views Thread by Ollivier Robert | last post: by
1 post views Thread by Richard Galli | last post: by
4 posts views Thread by Albert Ahtenberg | last post: by
1 post views Thread by inderjit S Gabrie | last post: by
2 posts views Thread by Jack | last post: by
3 posts views Thread by Sandwick | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.