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?

[Dave Hughes]

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.
--

[Jean-Marc Blaise]

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.
--

[Knut Stolze]

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

[Jean-Marc Blaise]

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