Invalid fullselect specified for MQT, more info available?

[Paul Reddin]

Hi,

I am trying to investigate if some of our fairly complex views
couldn't be materialised with MQT's.

Unfortunately the error message returned when the fullselect is
invalid is as
shown at the bottom of this post.

This message does little/nothing to help me(the user) understand what
is precluding the MQT. I then have to wade through all the if-buts
etc... to do with MQTs (very time consuming!)

Couldn't a more useful message of exactly why the definition is
unacceptable
be given? Surely, db2 knows exactly what the rule that has been broken
is ?

Thanks.

Paul

"SQL20058N The fullselect specified for the materialized query
table "<table-name>" is not valid.

Explanation:

The materialized query table definition has specific rules
regarding the contents of the fullselect. Some rules are based
on the materialized query table options (REFRESH DEFERRED or
REFRESH IMMEDIATE) while others are based on whether or not the
table is replicated. The fullselect in the CREATE TABLE
statement that returned this condition violates at least one of
the rules as described in the SQL Reference.

The statement cannot be processed.

User Response:

Change the fullselect in the CREATE TABLE statement so that it
conforms to the rules based on the materialized query table
options and whether or not the materialized query table is
replicated. "

[Paul Vernon]

"Paul Reddin" <pa**@abacus.co.uk> wrote in message
news:1f**************************@posting.google.c om...

Hi,

I am trying to investigate if some of our fairly complex views
couldn't be materialised with MQT's.

Unfortunately the error message returned when the fullselect is
invalid is as
shown at the bottom of this post.

This message does little/nothing to help me(the user) understand what
is precluding the MQT. I then have to wade through all the if-buts
etc... to do with MQTs (very time consuming!)

Couldn't a more useful message of exactly why the definition is
unacceptable be given?

I'm sure it could, but then the standard

SQL0104N An unexpected token "END-OF-STATEMENT" was found following "lect
from plain_text". Expected tokens may include: "<table_expr>".

can be prety obtuse also.

An interactive, client-side, as-you-type parser would get my vote.

In the meantime, Greame Birchall's DB2 cookbook arguably lists the MQT
restrictions in a more easy to read format.
See page 211 on his latest copy (updated 1st Jan 2004)
http://ourworld.compuserve.com/homep...l/HTM_COOK.HTM
E.g. is
"Every select list element must have a name"

easier to understand than

"Every column selected must have a name."

?
Regards
Paul Vernon
Business Intelligence, IBM Global Services

[Paul Reddin]

Paul,

Thanks for your thoughts, I agree, and yes Graeme's version of things
can be much easier to read/decipher more quickly.

In this case though, I'm not sure he hasn't got a couple of
restrictions wrong

e.g
• The SELECT statement must contain a GROUP BY, unless REPLICATED is
specified,
in which case a GROUP BY is not allowed.
• The SELECT must have a COUNT(*) or COUNT_BIG(*) column.

??

At least judging by my reading of the Redbook on MQTs

i.e it reads like a GROUP BY is now optional, but if you do include
one then you must include a COUNT(*)

Which all goes to show that really the error message ideally needs to
define
exactly what the violation is, otherwise you are down to checking
through
many versions of rules which may or may not be correct/relevant
etc.....

Paul.

[Serge Rielau]

Known issue. We are looking into it.

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[PM \(pm3iinc-nospam\)]

There's been some restriction liftings from time to time.
Join is now also possible in mqt's.

Graeme can't update his books on every fixpack though.

No count(), no group by, ...

db2 => CREATE TABLE TEST.SAMPBASE (
db2 (cont.) => Machine SMALLINT NOT NULL,
db2 (cont.) => SerialNo CHAR(10) NOT NULL,
db2 (cont.) => Color CHAR(10) NOT NULL,
db2 (cont.) => Weight SMALLINT NOT NULL,
db2 (cont.) => DateMade DATE NOT NULL,
db2 (cont.) => PRIMARY KEY (SerialNo)) @
DB20000I The SQL command completed successfully.
db2 =>
db2 =>
db2 => CREATE SUMMARY TABLE TEST.SAMPSUMM
db2 (cont.) => AS
db2 (cont.) => (
db2 (cont.) => SELECT * FROM TEST.SAMPBASE
db2 (cont.) => )
db2 (cont.) => DATA INITIALLY DEFERRED REFRESH deferred enable QUERY
OPTIMIZATION@
DB20000I The SQL command completed successfully.
db2 =>
PM
"Paul Reddin" <pa**@abacus.co.uk> a écrit dans le message de
news:1f**************************@posting.google.c om...

Paul,

Thanks for your thoughts, I agree, and yes Graeme's version of things
can be much easier to read/decipher more quickly.

In this case though, I'm not sure he hasn't got a couple of
restrictions wrong

e.g
. The SELECT statement must contain a GROUP BY, unless REPLICATED is
specified,
in which case a GROUP BY is not allowed.
. The SELECT must have a COUNT(*) or COUNT_BIG(*) column.

??

At least judging by my reading of the Redbook on MQTs

i.e it reads like a GROUP BY is now optional, but if you do include
one then you must include a COUNT(*)

Which all goes to show that really the error message ideally needs to
define
exactly what the violation is, otherwise you are down to checking
through
many versions of rules which may or may not be correct/relevant
etc.....

Paul.

[PM \(pm3iinc-nospam\)]

Well, while the Paul s were talking about mqt's, i came across this doc
snippet while reviewing the subject.

(v8 fp4's doc)
In general a materialized query table, or a replicated materialized query
table, is used for optimization of a query if the isolation level of the
materialized query table, or the replicated materialized query table, is
higher than or equal to the isolation level of the query. For example, if a
query is running under the cursor stability (CS) isolation level, only
materialized query tables, and replicated materialized query tables, that
are defined under CS or higher isolation levels are used for optimization.
Should i be able to find a column in some syscat.* view where i'd find a
table name (TABNAME) with a defined isolation level (ISOLATION)?

If not (and it's probably the case), how do i know the isolation level an
mqt was defined under?

As far as i know, there is no such thing as a table's isolation level.

Any comments?

PM

[PM \(pm3iinc-nospam\)]

Sorry, this should have been a new thread...

PM

[Paul Reddin]

Yes,

I saw this too and was very curious about it, but put it to one
side in amongst all the other things about MQTs I was trying to decipher/remember!
I mean it is the consumer that has a isolation level associated not a database
object?

Paul.

"PM \(pm3iinc-nospam\)" <PM(pm3iinc-nospam)@sympatico.ca> wrote in message news:<SM******************@news20.bellglobal.com>. ..

Sorry, this should have been a new thread...

PM

[Bill Pellett]

I didn't realize a table had an isolation level defined against it either.
However, if you issue the command "db2cat -d db-name -s schema-name -n
tab-name" (where schema-name and tab-name is your MQT), in the "PD extension
DESCRIPTION" area, there is an isolation level for the MQT.

"PM (pm3iinc-nospam)" <PM(pm3iinc-nospam)@sympatico.ca> wrote in message
news:UK******************@news20.bellglobal.com...

Well, while the Paul s were talking about mqt's, i came across this doc
snippet while reviewing the subject.

(v8 fp4's doc)
In general a materialized query table, or a replicated materialized query
table, is used for optimization of a query if the isolation level of the
materialized query table, or the replicated materialized query table, is
higher than or equal to the isolation level of the query. For example, if a query is running under the cursor stability (CS) isolation level, only
materialized query tables, and replicated materialized query tables, that
are defined under CS or higher isolation levels are used for optimization.
Should i be able to find a column in some syscat.* view where i'd find a
table name (TABNAME) with a defined isolation level (ISOLATION)?

If not (and it's probably the case), how do i know the isolation level an
mqt was defined under?

As far as i know, there is no such thing as a table's isolation level.

Any comments?

PM