🚀 go-pugleaf

The N-way replication you describe below now works on my PC-- I got it to
work with 2 remote DBs and a central server DB.  I indeed had many things
backwards.

Many thanks for the help Vince.

"Vince Duggan" <vince@replication.co.za> wrote in message
news:3BD03872.5DF29938@replication.co.za...
> Mike,
>
> A couple of points:
>
> Firstly, as you are probably aware if you have a two database scenarion,
> then all logins should be REPL, which avoids the bouncing problem.
> However I am aware that you are using this as a stepping stone to more
> databases:
>
> Secondly, you have a few things backwards:
> If you have two databases A and B, replicating bi-directionally:
>
> Schema 1 A-> B
> Source Login - AnyUserName
> Target Login - UserA   (as in coming from Database A)
> Row Level Condition: USER <> 'USERB'
>
> Schema 2 B -> A
> Source Login - AnyUserName
> Target Login - UserB   (as in coming from Database B)
> Row Level Condition: USER <> 'USERA'
>
>
> Vince
>
> Mike Senkovich wrote:
> >
> > I discovered that if I change the Table Settings of any of the tables, I
> > need to remove and create the system objects in the source database.
That
> > will update the triggers in the Source and Target DBs with the correct
"IF"
> > tests.
> >
> > However, I have done this and things are still not working as I expect.
> >
> > The "After Insert" system triggers for the source table is:
> > CREATE TRIGGER REPL$BID521_I_27_6 FOR BID
> > ACTIVE AFTER INSERT POSITION 32767
> > AS BEGIN IF (USER <> 'REPL') THEN BEGIN   INSERT INTO
> > REPL_LOG(REPLNO,PUBDBNO,SUBDBNO,RELATIONNO,REPTYPE,NEWKEY)
> >   SELECT REPLNO,PUBDBNO,SUBDBNO,RELATIONNO,'I',NEW.BIDKEY
> >     FROM REPL_SEPARATOR
> >    WHERE REPLNO=27
> >      AND PUBDBNO=6
> >      AND RELATIONNO=521;
> >
> > END
> > END
> >  ^
> > The "After Insert" system trigger for the Target table is:
> > CREATE TRIGGER REPL$BID583_I_28_7 FOR BID
> > ACTIVE AFTER INSERT POSITION 32767
> > AS BEGIN IF (USER <> 'REPL') THEN BEGIN
> > IF (USER <> 'REPL1') THEN BEGIN
> >
> >   INSERT INTO REPL_LOG(REPLNO,PUBDBNO,SUBDBNO,RELATIONNO,REPTYPE,NEWKEY)
> >   SELECT REPLNO,PUBDBNO,SUBDBNO,RELATIONNO,'I',NEW.BIDKEY
> >     FROM REPL_SEPARATOR
> >    WHERE REPLNO=28
> >      AND PUBDBNO=7
> >      AND RELATIONNO=583;
> > END
> >
> > END
> > END
> >  ^
> >
> > Replicating a single record from the target table to the source (New
York to
> > Chicago) results in the record in REPL_LOG, which means it will be
> > replicated back to the Target DB on the next replication execution.
> >
> > What am I missing?  The replication USER can only be REPL or REPL1.  My
> > application is logged in as a different IB user (MYLOGIN).  Does my
> > application need to be using the REPL1 IB login too?
> >
> > Thanks
> > -Mike
> >
> > "Mike Senkovich" <mikes@decisionsys.com> wrote in message
> > news:3bce065e_2@dnews...
> > > Hello all.
> > >
> > > I'm having some problems getting the N-Way replication scenario to
work.
> > > For now I am testing this with 2 DBs.  When I get it to work with
these 2,
> > I
> > > will try it with 3.
> > >
> > > Using the "Preventing Rows from bouncing back in an N-Way model"
section
> > of
> > > the Internals doc as a guide, I have created 2 Replication schema:
> > >
> > > Schema 1:
> > > Source-to-Target (similar to Chicago-to-New York)
> > > Source DB -- Replication User Name is REPL1 (similar to REPL_CHICAGO)
> > > Target DB -- Replication User Name is REPL
> > >
> > > Schema 2:
> > > Target-to-Source (similar to New York-to-Chicago)
> > > Source DB -- Replication User Name is REPL
> > > Target DB -- Replication User Name is REPL1
> > >   Also, in the Target DB, I added the following Row Level condition in
the
> > > tables I am replicating:
> > >   USER <> 'REPL1'
> > >
> > > The idea is that I want the Source DB to be able to replicate its
changes
> > to
> > > the Target DB, the Target DB to replicate it's changes to the Source
DB,
> > and
> > > I don't want the Source DB data that was already replicated to the
Dest DB
> > > to be replicated back to the Source DB.  Note that I will be adding
other
> > > Source DBs, so just using the REPL user by itself won't help me.
> > >
> > > I create a single record in the same table in each database (each with
a
> > > unique primary key).  I click the replication button and in this first
> > pass
> > > the source and dest values replicate correctly (both source-to-target
and
> > > target-to-source), but there are still values in the Repl_Log on the
> > Source
> > > side.  So I click the replication button again, and it replicates
what's
> > in
> > > the Repl_Log table.  The result is a "Primary Key Violation -
Converted to
> > > UPDATE STATEMENT in the LOG file" because these values have already
been
> > > replicated in the 1st pass.
> > >
> > > 1) Why is replication of the same data happening twice?
> > > 2) Do I have the User Names or Row Level conditions in each schema set
up
> > > incorrectly?
> > >
> > > I am using the IB5 Replication server.  The source and target DBs have
> > > identical metadata (except for what the Replication manager has
added).
> > The
> > > DBs also have permissions granted on all tables for users REPL and
REPL1.
> > >
> > > Thanks in advance for any help you can provide.
> > > -Mike
> > >
> > >