Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
Thoughts on replicating a view?
53 views
Skip to first unread message
Lennart Jonsson
Apr 10, 2012, 9:11:33 AM4/10/12
to
I'm trying to set up - what at first sight appears to be - a simple
replication scenario. I would like to replicate a view to a materialized
table on target.
On the src-db:
create table db2inst1.t1
( x int not null primary key
, y int not null ) ;
create table db2inst1.t2
( x int not null primary key
, z int not null ) ;
create view db2inst1.v as
select t1.x, t1.y, t2.z
from db2inst1.t1
join db2inst1.t2
on t1.x = t2.x ;
On the target-db:
create table db2inst1.v
( x int not null primary key
, y int not null
, z int not null ) ;
I manage to replicate t1 and t2 but not v. I actually don't want any of
the tables to be replicated, but it appears that one has to replicate at
least one of them.
Has anyone succeeded with a setup such as the one above?
/Lennart
Frederik Engelen
Apr 10, 2012, 2:05:37 PM4/10/12
to
On 10 apr, 15:11, Lennart Jonsson <erik.lennart.jons...@gmail.com>
wrote:
It works with SQL Replication. The wizards can be difficult about
generating the necessary SQL, but it should work.
--
Frederik Engelen
wrote:
generating the necessary SQL, but it should work.
--
Frederik Engelen
Ian
Apr 10, 2012, 2:24:53 PM4/10/12
to
Lennart Jonsson <erik.lenna...@gmail.com> writes:
> I'm trying to set up - what at first sight appears to be - a simple
> replication scenario. I would like to replicate a view to a materialized
> table on target.
>
> [...]
> I'm trying to set up - what at first sight appears to be - a simple
> replication scenario. I would like to replicate a view to a materialized
> table on target.
>
>
> I manage to replicate t1 and t2 but not v. I actually don't want any of
> the tables to be replicated, but it appears that one has to replicate at
> least one of them.
>
>
> Has anyone succeeded with a setup such as the one above?
I believe what you're looking for is using federation to define a
> I manage to replicate t1 and t2 but not v. I actually don't want any of
> the tables to be replicated, but it appears that one has to replicate at
> least one of them.
>
>
> Has anyone succeeded with a setup such as the one above?
nickname for the view in the target database, and then building an MQT
against the nickname to cache the data for you:
-- in the target database
-- wrapper/server/user mapping not shown
create nickname nn.v for source.db2inst1.v;
create table db2inst1.v;
as select * from nn.v
data initially deferred
refresh deferred;
refresh table db2inst1.v;
Good luck,
Ian
--- Posted via news://freenews.netfront.net/ - Complaints to ne...@netfront.net ---
Lennart Jonsson
Apr 11, 2012, 2:21:06 AM4/11/12
to
On 04/10/2012 08:05 PM, Frederik Engelen wrote:
[...]
machine via ssh over vpn), but I will try installing the replication
centre locally and see if I can generate sql that way
/Lennart
[...]
> It works with SQL Replication. The wizards can be difficult about
> generating the necessary SQL, but it should work.
>
I forgot to mention that I'm using the mighty asnclp (only access to the
> generating the necessary SQL, but it should work.
>
machine via ssh over vpn), but I will try installing the replication
centre locally and see if I can generate sql that way
/Lennart
Lennart Jonsson
Apr 11, 2012, 3:10:28 AM4/11/12
to
On 04/10/2012 08:24 PM, Ian wrote:
> Lennart Jonsson <erik.lenna...@gmail.com> writes:
>
>> I'm trying to set up - what at first sight appears to be - a simple
>> replication scenario. I would like to replicate a view to a materialized
>> table on target.
>>
>> [...]
>>
>> I manage to replicate t1 and t2 but not v. I actually don't want any of
>> the tables to be replicated, but it appears that one has to replicate at
>> least one of them.
>>
>>
>> Has anyone succeeded with a setup such as the one above?
>
> I believe what you're looking for is using federation to define a
> nickname for the view in the target database, and then building an MQT
> against the nickname to cache the data for you:
>
>
> -- in the target database
>
> -- wrapper/server/user mapping not shown
>
> create nickname nn.v for source.db2inst1.v;
>
> create table db2inst1.v;
> as select * from nn.v
> data initially deferred
> refresh deferred;
>
> refresh table db2inst1.v;
>
>
Hi Ian, thanks for your thoughts. I can see a number of issues though:
> Lennart Jonsson <erik.lenna...@gmail.com> writes:
>
>> I'm trying to set up - what at first sight appears to be - a simple
>> replication scenario. I would like to replicate a view to a materialized
>> table on target.
>>
>> [...]
>>
>> I manage to replicate t1 and t2 but not v. I actually don't want any of
>> the tables to be replicated, but it appears that one has to replicate at
>> least one of them.
>>
>>
>> Has anyone succeeded with a setup such as the one above?
>
> I believe what you're looking for is using federation to define a
> nickname for the view in the target database, and then building an MQT
> against the nickname to cache the data for you:
>
>
> -- in the target database
>
> -- wrapper/server/user mapping not shown
>
> create nickname nn.v for source.db2inst1.v;
>
> create table db2inst1.v;
> as select * from nn.v
> data initially deferred
> refresh deferred;
>
> refresh table db2inst1.v;
>
>
a) I probably wont be allowed to create nicknames that way (target is
located in a less secure area than src)
b) I have had som bad experience with full refresh of mqt in the past
(locking)
c) We are currently on 9.5 Workgroup Edition. We are entitled to use
mqt's via "grandfather option", since we created those back in 8.1.
http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=%2Fcom.ibm.db2.luw.licensing.doc%2Fdoc%2Fr0053245.html
From what I understand IBM will remove that possibility in the future
(motive seems to be that mqt is a dw concept and should be licensed that
way). I can't find the link to that information at the moment, but
looking at 9.7 feature chart it seems that mqt's are only included in
Enterprise edition:
http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.licensing.doc/doc/r0053238.html?resultof=%22%6d%61%74%65%72%69%61%6c%69%7a%65%64%22%20%22%6d%61%74%65%72%69%22%20%22%71%75%65%72%79%22%20%22%71%75%65%72%69%22%20%22%74%61%62%6c%65%22%20%22%74%61%62%6c%22%20%22%6c%69%63%65%6e%73%65%22%20%22%6c%69%63%65%6e%73%22%20
Upgrading to Enterprise Edition will increase the license cost quite a
lot, and the client seems unwilling to take that path. In fact we are
looking at ways to get rid of the mqt's that still exists due to this
issue. I'm not exactly sure that "grandfather" can't be used in 9.7 and
beyond, but the functionality we use them for can be replaced with only
slight changes, so it won't be a big deal. Anyhow, I don't think it will
be wise to introduce new functionality that relies on mqt's for this
reason
My original thought was to create nicknames the other way around and use
a trigger to populate the nickname. I never managed to figure out how to
get two-phase commit to work in that context though, and that was when I
started investigating replication. My experience so far is that asnclp
is not for the heart of fainted ;-) I will look into Frederiks
suggestion on replication centre and see where that leads. Thanks for
your thoughts.
/Lennart
Frederik Engelen
Apr 11, 2012, 5:42:37 AM4/11/12
to
On Apr 11, 9:10 am, Lennart Jonsson <erik.lennart.jons...@gmail.com>
wrote:
> http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=...
>
> - Show quoted text -
Note that replicating a view has a higher impact as for each
participating table, each change will be replicated while being joined
to the other table. That depends on the amount of updates of course
but we ran into issues when the amount of CD records after certain
batches ran on the source data was big enough that DB2 considered hash
joining it to a 120M and 150M records table :-) Also note the issue of
double deletes, described in the InfoCenter.
--
Frederik Engelen
wrote:
>
> From what I understand IBM will remove that possibility in the future
> (motive seems to be that mqt is a dw concept and should be licensed that
> way). I can't find the link to that information at the moment, but
> looking at 9.7 feature chart it seems that mqt's are only included in
> Enterprise edition:
>
> http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db...
> From what I understand IBM will remove that possibility in the future
> (motive seems to be that mqt is a dw concept and should be licensed that
> way). I can't find the link to that information at the moment, but
> looking at 9.7 feature chart it seems that mqt's are only included in
> Enterprise edition:
>
>
> Upgrading to Enterprise Edition will increase the license cost quite a
> lot, and the client seems unwilling to take that path. In fact we are
> looking at ways to get rid of the mqt's that still exists due to this
> issue. I'm not exactly sure that "grandfather" can't be used in 9.7 and
> beyond, but the functionality we use them for can be replaced with only
> slight changes, so it won't be a big deal. Anyhow, I don't think it will
> be wise to introduce new functionality that relies on mqt's for this
> reason
>
> My original thought was to create nicknames the other way around and use
> a trigger to populate the nickname. I never managed to figure out how to
> get two-phase commit to work in that context though, and that was when I
> started investigating replication. My experience so far is that asnclp
> is not for the heart of fainted ;-) I will look into Frederiks
> suggestion on replication centre and see where that leads. Thanks for
> your thoughts.
>
> /Lennart- Hide quoted text -
> Upgrading to Enterprise Edition will increase the license cost quite a
> lot, and the client seems unwilling to take that path. In fact we are
> looking at ways to get rid of the mqt's that still exists due to this
> issue. I'm not exactly sure that "grandfather" can't be used in 9.7 and
> beyond, but the functionality we use them for can be replaced with only
> slight changes, so it won't be a big deal. Anyhow, I don't think it will
> be wise to introduce new functionality that relies on mqt's for this
> reason
>
> My original thought was to create nicknames the other way around and use
> a trigger to populate the nickname. I never managed to figure out how to
> get two-phase commit to work in that context though, and that was when I
> started investigating replication. My experience so far is that asnclp
> is not for the heart of fainted ;-) I will look into Frederiks
> suggestion on replication centre and see where that leads. Thanks for
> your thoughts.
>
>
> - Show quoted text -
Note that replicating a view has a higher impact as for each
participating table, each change will be replicated while being joined
to the other table. That depends on the amount of updates of course
but we ran into issues when the amount of CD records after certain
batches ran on the source data was big enough that DB2 considered hash
joining it to a 120M and 150M records table :-) Also note the issue of
double deletes, described in the InfoCenter.
--
Frederik Engelen
Lennart Jonsson
Apr 11, 2012, 7:49:54 AM4/11/12
to
On 04/11/2012 11:42 AM, Frederik Engelen wrote:
[...]
tables and put the view on the target (since I'm going to have to
replicate the tables anyhow)?.
There will of course be a penalty for lookup, but I think that will be
acceptable. The two tables typically have a cardinality of less than 1M
and are joined on a single column.
[...]
>
> Note that replicating a view has a higher impact as for each
> participating table, each change will be replicated while being joined
> to the other table. That depends on the amount of updates of course
> but we ran into issues when the amount of CD records after certain
> batches ran on the source data was big enough that DB2 considered hash
> joining it to a 120M and 150M records table :-) Also note the issue of
> double deletes, described in the InfoCenter.
>
In your opinion, would it be a better setup to just replicate the base
> Note that replicating a view has a higher impact as for each
> participating table, each change will be replicated while being joined
> to the other table. That depends on the amount of updates of course
> but we ran into issues when the amount of CD records after certain
> batches ran on the source data was big enough that DB2 considered hash
> joining it to a 120M and 150M records table :-) Also note the issue of
> double deletes, described in the InfoCenter.
>
tables and put the view on the target (since I'm going to have to
replicate the tables anyhow)?.
There will of course be a penalty for lookup, but I think that will be
acceptable. The two tables typically have a cardinality of less than 1M
and are joined on a single column.
Lennart Jonsson
Apr 11, 2012, 10:27:01 AM4/11/12
to
federated tables. This relates to the problem that I'm trying to solve,
so I'm posting it in this thread:
It's easy to set up a server for two-phase commit so that one can update
both databases in the same transaction:
connect to s ;
create table t1
( x int not null primary key
, y int not null ) ;
connect to m ;
, y int not null ) ;
create table t1
( x int not null primary key
, y int not null ) ;
CREATE WRAPPER DRDA
, y int not null ) ;
OPTIONS( DB2_FENCED 'N');
CREATE SERVER S TYPE DB2/UDB VERSION 9
WRAPPER DRDA
AUTHORIZATION "db2inst1" PASSWORD "********"
OPTIONS( DBNAME 'S', PASSWORD 'Y', DB2_TWO_PHASE_COMMIT 'Y' );
CREATE USER MAPPING FOR "db2inst1" SERVER "S"
OPTIONS ( REMOTE_AUTHID 'db2inst1', REMOTE_PASSWORD '*******');
CREATE NICKNAME S.T1
FOR s.db2inst1.t1;
db2 +c "insert into t1(x,y) values (1,1)"
DB20000I The SQL command completed successfully.
[db2inst1@nya-09 ~/nobackup/ltjn/tek122/federation]$ db2 +c "insert into
s.t1(x,y) values (1,1)"
DB20000I The SQL command completed successfully.
[db2inst1@nya-09 ~/nobackup/ltjn/tek122/federation]$ db2 commit
DB20000I The SQL command completed successfully.
alles gut. However, it is not possible to directly create a trigger on
t1 that updates s.t1:
SQL30090N Operation invalid for application execution environment. Reason
code = "22". LINE NUMBER=3. SQLSTATE=25000
Not sure what tha rationale behind this is. Here is a - ugly - way of
overcoming this restriction:
create procedure db2inst1.proc (in_x int, in_y int)
language sql
begin atomic
declare stmt varchar(100);
set stmt = 'insert into s.t1 (x,y) values (' ||
char(in_x) || ',' || char(in_y) || ')';
execute immediate stmt;
end @
create trigger db2inst1.trg
after insert on db2inst1.t1
referencing new as n
for each row
call db2inst1.proc(n.x, n.y)
@
[db2inst1@nya-09 ~/nobackup/ltjn/tek122/federation]$ db2 +c "insert into
s.t1(x,y) values (2,2)"
DB20000I The SQL command completed successfully.
[db2inst1@nya-09 ~/nobackup/ltjn/tek122/federation]$ db2 commit
DB20000I The SQL command completed successfully.
[db2inst1@nya-09 ~/nobackup/ltjn/tek122/federation]$ db2 "select * from
s.t1"
X Y
----------- -----------
1 1
2 2
2 record(s) selected.
/Lennart
Frederik Engelen
Apr 12, 2012, 2:41:23 AM4/12/12
to
On Apr 11, 4:27 pm, Lennart Jonsson <erik.lennart.jons...@gmail.com>
wrote:
Nice one :-)
--
Frederik Engelen
wrote:
> /Lennart- Hide quoted text -
>
> - Show quoted text -
>> call db2inst1.proc(n.x, n.y)
>
> - Show quoted text -
Nice one :-)
--
Frederik Engelen
Lennart Jonsson
Apr 30, 2012, 12:45:05 PM4/30/12
to
[...]
sales rep we can continue to use MQT.s using the "grandfather option"
in 9.7 as well as in 10.
/Lennart
> c) We are currently on 9.5 Workgroup Edition. We are entitled to use
> mqt's via "grandfather option", since we created those back in 8.1.
>
> http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=...
> mqt's via "grandfather option", since we created those back in 8.1.
>
>
> From what I understand IBM will remove that possibility in the future
> (motive seems to be that mqt is a dw concept and should be licensed that
> way).
In case anyone else has the same considerations, according to our
> From what I understand IBM will remove that possibility in the future
> (motive seems to be that mqt is a dw concept and should be licensed that
> way).
sales rep we can continue to use MQT.s using the "grandfather option"
in 9.7 as well as in 10.
/Lennart
0 new messages