Invalid fullselect specified for MQT, more info available?

**Paul Vernon** · Nov 12 '05, 06:13 AM

Re: Invalid fullselect specified for MQT, more info available?

"Paul Reddin" <paul@abacus.co .uk> wrote in message
news:1fd2a603.0 401140434.2877d 207@posting.goo gle.com...[color=blue]
> 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?[/color]

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_exp r>".

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/homepages/graeme_birchall/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** · Nov 12 '05, 06:14 AM

Re: Invalid fullselect specified for MQT, more info available?

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** · Nov 12 '05, 06:14 AM

Re: Invalid fullselect specified for MQT, more info available?

Known issue. We are looking into it.

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

**PM \(pm3iinc-nospam\)** · Nov 12 '05, 06:14 AM

Re: Invalid fullselect specified for MQT, more info available?

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" <paul@abacus.co .uk> a écrit dans le message de
news:1fd2a603.0 401150304.23d80 f1a@posting.goo gle.com...[color=blue]
> 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.[/color]

**PM \(pm3iinc-nospam\)** · Nov 12 '05, 06:14 AM

mqt's

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\)** · Nov 12 '05, 06:14 AM

Re: mqt's

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

PM

**Paul Reddin** · Nov 12 '05, 06:15 AM

Re: mqt's

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)@sympati co.ca> wrote in message news:<SMANb.397 6$c1.670814@new s20.bellglobal. com>...[color=blue]
> Sorry, this should have been a new thread...
>
> PM[/color]

**Bill Pellett** · Nov 12 '05, 06:15 AM

Re: mqt's

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)@sympati co.ca> wrote in message
news:UKzNb.3494 $c1.661458@news 20.bellglobal.c om...[color=blue]
> 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[/color]
a[color=blue]
> 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
>
>[/color]

Invalid fullselect specified for MQT, more info available?

Invalid fullselect specified for MQT, more info available?

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment