MQT on a nickname
Question posted by: Arun Srinivasan
(Guest)
on
July 23rd, 2008 02:55 PM
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...coln,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
|
|
July 24th, 2008 09:25 PM
# 2
|
Re: MQT on a nickname
Arun Srinivasan wrote:
Quote:
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...coln,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
|
|
|
July 25th, 2008 08:55 PM
# 3
|
Re: MQT on a nickname
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(client_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
|
|
|
July 26th, 2008 01:45 PM
# 4
|
Re: MQT on a nickname
Arun Srinivasan wrote:
Quote:
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(client_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
|
|
|
July 28th, 2008 07:05 PM
# 5
|
Re: MQT on a nickname
DB2_CAPTURE_LOCKTIMEOUT=ON
DB2RSHCMD=/usr/bin/ssh
DB2_EXTENDED_OPTIMIZATION=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.
Not the answer you were looking for? Post your question . . .
190,472 Experts ready to help you find a solution.
Sign up for a free account, or Login (if you're already a member).
|
