MQT on a nickname

[Arun Srinivasan]

I need to create an MQT on a nickname, the requirement is that I need
to transfer data from source to target when required (this is because
the nickname is slow - i can't implement required indices on the
source table - tooo large).
So when I tried
create table table1 as (select * from nickname) data initially
deferred refresh deferred;
it returned sql0142N error - sql statement not supported.

Both source and target dbs are db2 version 9.5.
I also tried aggregation in the select stmt of the above command

create table table1 (col1,col2...co ln,count1) as (select
col1,col2,col3, ...coln,count(* ) from nickname
group by col1,col2,col3. ....coln)

still no joy.

Have anyone come across this scenario or have any advise on how I
could tackle this?
I started to read about sql replication, it is just plain fat-slow
solution and my learning curve is high.
If someone has other ideas to tackle the situation , would be much
appreciated if you can share it.

Thanks
Arun

[Serge Rielau]

Arun Srinivasan wrote:

I need to create an MQT on a nickname, the requirement is that I need
to transfer data from source to target when required (this is because
the nickname is slow - i can't implement required indices on the
source table - tooo large).
So when I tried
create table table1 as (select * from nickname) data initially
deferred refresh deferred;
it returned sql0142N error - sql statement not supported.

Both source and target dbs are db2 version 9.5.
I also tried aggregation in the select stmt of the above command

create table table1 (col1,col2...co ln,count1) as (select
col1,col2,col3, ...coln,count(* ) from nickname
group by col1,col2,col3. ....coln)

still no joy.

Have anyone come across this scenario or have any advise on how I
could tackle this?
I started to read about sql replication, it is just plain fat-slow
solution and my learning curve is high.
If someone has other ideas to tackle the situation , would be much
appreciated if you can share it.

Arun,

We can not reproduce the issue.
Can you post the exact DDL and the error message as you receive it?

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

[Arun Srinivasan]

db2 =create table elg.artelgf as (select * from elg.artelgf_l2) data
initially deferred refresh deferred
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.

The table elg.artelgf_l2 is a nickname that points to a table in db2
version 9.1. My target is in db2 version 9.5. I also thought
the mismatch in user id may cause authentication problems and created
user mappings, still no joy.

db2 "create table elg.artelgf as (select client_cd, count(*) as cnt
from elg.artelgf_l2 group by client_cd) data initially deferred
refresh deferred"
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.

db2 "create table elg.artelgf(cli ent_cd,claim_id ) as (select
client_cd, count(*) as cnt from elg.artelgf_l2 group by client_cd)
data initially deferred refresh deferred"
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.

Any ideas or suggestions would be much appreciated.

Thank you
Arun

[Serge Rielau]

Arun Srinivasan wrote:

db2 =create table elg.artelgf as (select * from elg.artelgf_l2) data
initially deferred refresh deferred
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.

The table elg.artelgf_l2 is a nickname that points to a table in db2
version 9.1. My target is in db2 version 9.5. I also thought
the mismatch in user id may cause authentication problems and created
user mappings, still no joy.

db2 "create table elg.artelgf as (select client_cd, count(*) as cnt
from elg.artelgf_l2 group by client_cd) data initially deferred
refresh deferred"
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.

db2 "create table elg.artelgf(cli ent_cd,claim_id ) as (select
client_cd, count(*) as cnt from elg.artelgf_l2 group by client_cd)
data initially deferred refresh deferred"
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.

Does the query itself work without the CREATE TABLE?
Let's first exclude federation (license, limitations)as a cultprit).
What does a db2set from your shell show?
Is FEDERATED turned on (I think its a DBM CFG).

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Arun Srinivasan]

DB2_CAPTURE_LOC KTIMEOUT=ON
DB2RSHCMD=/usr/bin/ssh
DB2_EXTENDED_OP TIMIZATION=Y
DB2_ANTIJOIN=Y
DB2COMM=TCPIP
DB2_PARALLEL_IO =*:5

Sorry for the delayed response Serge, ..

I have run the query against the nickname 100s of times.

Also, Federated is turned on, since I am able to create nicknames and
run select statements against it. I think I'd have to open a PMR.