SQL statement not supported error SQL1042N for MQT defn on a nickname

[Arun Srinivasan]

I have a problem, I am getting SQL1042N The SQL statement is not
supported. error when I try to create a MQT against a nickname. The
source db is db2 version 9.1 fixpack 4, same as the target db and so
the SQL is definitely supported and both are in windows 2003 server.
Is there a way I could find why this error pops up.
My syntax is like

create mqt1 as
(select col1, col2, ..............., colN from nickname where
<predicate>) data initially deffered refresh deffered;

To add an aggregation, I also tried the following

create mqt1(count1,col1,col2...colN) as
(select count(*), col1, col2, ..............., colN from nickname
where <predicategroup by col1,col2...colN) data initially deffered
refresh deffered;
Still no joy.

Also, basically what I am trying to do is the following :
I have a table in one server, and since it is not a big table, I am
thinking of creating a replicated MQT (basically I need all the
columns of the table), in a linux cluster database of 8 nodes, so that
I have a local table and also since it is replicated, collocating
joins can be achieved.
I can go and load local table using db2 load command, but I'd have to
do this everytime I start a process, which is painful. Also, I can't
achieve this intra-db replication using local table, can I?
Any advise would be appreciated.

Thanks
Arun

[mirof007]

MQTs in DB2 are created using the CREATE TABLE statement, looks like
you're missing the TABLE part...

Regards,
Miro

[Arun Srinivasan]

On May 29, 4:18*pm, mirof007 <[e-mail]:

MQTs in DB2 are created using the CREATE TABLE statement, looks like
you're missing the TABLE part...

Regards,
Miro

I did use that, no probs with syntax buddy. If that was the case I'd
have got another error. All am facing now is problem with the whole
exercise, that it is not allowed in my environment and am trying to
find out why.

[Arun Srinivasan]

Can anyone care to advise me on this please?

[Serge Rielau]

Anrun,

Can you please post teh extact error message. I get under 1042 this:
SQL1042C An unexpected system error occurred.

Clearly NOT an MQT specific error message.
Do you loose the connection when this happens? Do you need to restart
DB2? Anything in the db2diag.log ("severe").

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Arun Srinivasan]

I needed to transfer data from one db to another in a seamless way, so
the idea I had was to build an MQT against a nickname, and refresh
data. When I created the mqt like,
create table mqt
as
(select col1, col2,.... from table1) data initially deferred refresh
deferred;

it gave back an error saying

DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0142N The SQL statement is not supported.

For not giving a fullselect, what I did was to include a dumb count(*)
and group by the primary key so as to use aggregation. But got back
the same error,
I am trying this option so that I won't have the hassles of load
utility to load the data into the local table everyweek, since the
data is not big.
So what I want to find is why db2 is throwing that error. SQL
statement is fine and I am using data deferred since it's a nickname
am building the MQT against.
No disconnects, and I've checked the diaglog, no messages. Still a big
mystery.

[Dave Hughes]

Arun Srinivasan wrote:

I needed to transfer data from one db to another in a seamless way, so
the idea I had was to build an MQT against a nickname, and refresh
data. When I created the mqt like,
create table mqt
as
(select col1, col2,.... from table1) data initially deferred refresh
deferred;

it gave back an error saying

DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0142N The SQL statement is not supported.

For not giving a fullselect, what I did was to include a dumb count(*)
and group by the primary key so as to use aggregation. But got back
the same error,
I am trying this option so that I won't have the hassles of load
utility to load the data into the local table everyweek, since the
data is not big.
So what I want to find is why db2 is throwing that error. SQL
statement is fine and I am using data deferred since it's a nickname
am building the MQT against.
No disconnects, and I've checked the diaglog, no messages. Still a big
mystery.

If you don't want the hassles of the LOAD utility - how about the
IMPORT utility instead? Nothing like as quick admittedly, but then it's
likely to be a good bit faster than using a federated MQT, and will
avoid all this trouble.

As for whether or not it's as "seamless" ... well, it may involve two
commands (EXPORT + IMPORT) instead of one (REFRESH TABLE) but that just
means it's a scheduled script with two lines instead of one ;-)
Cheers,

Dave.

--

[Arun Srinivasan]

On Jun 2, 6:12*pm, "Dave Hughes" <[e-mail]:

Arun Srinivasan wrote:

I needed to transfer data from one db to another in a seamless way, so
the idea I had was to build an MQT against a nickname, and refresh
data. When I created the mqt like,
create table mqt
as
(select col1, col2,.... from table1) data initially deferred refresh
deferred;

it gave back an error saying

DB21034E *The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. *During SQL processing it
returned:
SQL0142N *The SQL statement is not supported.

For not giving a fullselect, what I did was to include a dumb count(*)
and group by the primary key so as to use aggregation. But got back
the same error,
I am trying this option so that I won't have the hassles of load
utility to load the data into the local table everyweek, since the
data is not big.
So what I want to find is why db2 is throwing that error. SQL
statement is fine and I am using data deferred since it's a nickname
am building the MQT against.
No disconnects, and I've checked the diaglog, no messages. Still a big
mystery.

If you don't want the hassles of the LOAD utility - how about the
IMPORT utility instead? Nothing like as quick admittedly, but then it's
likely to be a good bit faster than using a federated MQT, and will
avoid all this trouble.

As for whether or not it's as "seamless" ... well, it may involve two
commands (EXPORT + IMPORT) instead of one (REFRESH TABLE) but that just
means it's a scheduled script with two lines instead of one ;-)

Cheers,

Dave.

--- Hide quoted text -

- Show quoted text -

Thanks Dave. But if you read my initial request, another great
advantage I have using MQTs is that I can replicate them across all
nodes.
'I have a table in one server, and since it is not a big table, I am
thinking of creating a replicated MQT (basically I need all the
columns of the table), in a linux cluster database of 8 nodes, so
that
I have a local table and also since it is replicated, collocating
joins can be achieved.
I can go and load local table using db2 load command, but I'd have to
do this everytime I start a process, which is painful. Also, I can't
achieve this intra-db replication using local table, can I? '

Can we do this using simple table and loading them (one in each
partition), and thereby achieve collocating joins?
Please advise.