Prevent Infinite Looping in Q Replication

[RdR]

Hi,

Has anyone encountered infinite looping in Q Replication? This happens when
I have a source DB2 table A going to a target DB2 table B, it also happens
that the samne target table B is replicated back to source table A (true
bi-directional replication scenario). Once I start replication on a master
to master scenario the changes in A gets replicated to B but that change
gets replicated back to A and so on creating an infinite loop. Is there
something I can define to prevent this from happening.

Also, any collision detection mechanisms I can use?

Thanks,

RdR

[hikums]

With my limited exposure,

Do you see any errors in the IBMQREP_EXCEPTI ONS?

Also, check if commit interval will be of any help.

[RdR]

Hi Hikums,

What happens is the change I made in A gets replicated to B but it goes back
to A ( B see it as a legitimate change - which technically it is a
legitimate change) because they were setup to replicate like a Master to
Master type of setup. I was hoping I can use something to identify that a
change was made in A so that when B APPLYs that change, it will not send it
back to A. I think I can do that by adding a new column and use that column
as a filter to identify that it originated in A. A trigger to populate that
cna do it but first, it is intrusive, meaning I have to change my table
structures, and secondly, adding a trigger will add to CPU usage and
latency.

For commitment control, the commit interval will not work because what I am
really watching out for is what happens if the change happens at the same
time on both A and B. I need something to compare the before and after
images and make sure that they match before I do an update or a delete.

Thanks,

RdR
<hi****@gmail.c om> wrote in message
news:11******** **************@ f14g2000cwb.goo glegroups.com.. .

With my limited exposure,

Do you see any errors in the IBMQREP_EXCEPTI ONS?

Also, check if commit interval will be of any help.

[laxmipalani99]

In the case of bidirectional Replication changes occur between tables
on two servers. Changes that are made to one copy of a table are
replicated to a second copy of that table, and changes that are made to
the second copy are replicated back to the first copy. This is the
intention. you can think of it as a backup server.

The choices that you make for conflict rules and conflict actions
affect the behavior of how rows are applied. The conflict rules
determine how much of the data is checked to detect a conflict and the
types of conflicts that are detected.

Bidirectional replication uses data values to detect and resolve
conflicts. You can choose which data values are used to detect
conflicts. You can choose for the Q Apply program to check one of the
following groups of columns when determining conflicts:

Only key columns values
Key columns and changed columns values
All columns values

For each server, you can choose what action each server takes when a
conflict occurs. Each server can either force the conflicting row into
its target table or ignore the conflict. These options of force and
ignore can be paired in two different ways to provide different
behaviors for the Q Apply program.
The Q Apply program logs all conflicts in the IBMQREP_EXCEPTI ONS table
and continues processing. Over time, the two copies of a logical table
will diverge.

Looking at what you are expecting you may want to have a peer to peer
subscription involving 2 nodes instead of a bidirectional replication.
In a Peer to peer replication the updates can occur at both the
databases and version columns are used to resolve conflicts.

Thanks,
LP

RdR wrote:

Hi,

Has anyone encountered infinite looping in Q Replication? This happens when
I have a source DB2 table A going to a target DB2 table B, it also happens
that the samne target table B is replicated back to source table A (true
bi-directional replication scenario). Once I start replication on a master
to master scenario the changes in A gets replicated to B but that change
gets replicated back to A and so on creating an infinite loop. Is there
something I can define to prevent this from happening.

Also, any collision detection mechanisms I can use?

Thanks,

RdR

[hikums]

>From RdR's note, it appears that changes in source server is applied to
target server, then target server picks it as a change in that server
to replicate back to source, thereby looping infinitely.

Where in a bidirectional or peer will this be setup!! Is it a restart
queue function/setup issue??

I have a gut feeling that it does not end up in IBMQREP_EXCEPTI ONS,
RdR, is that the case?

[RdR]

Hi LP and Hikums,

That is what I expect, changes on A gets replicated to B and do not come
back again to A starting the infinite loop. And since this is not a
collision it does not go to the IBMQREP_EXCEPTI ONS table, collisions would
but the loop won't because it seems like Q Replication sees it as a
legitimate change. I was hoping there will be a switch that will say, if the
change in A gets replicated to B, B should not send it back or is Q
Replication not a good solution for a Master to Master type of Environment
wherein changes can happen on both sides.

Thanks,

RdR
<la***********@ yahoo.com> wrote in message
news:11******** **************@ o13g2000cwo.goo glegroups.com.. .

In the case of bidirectional Replication changes occur between tables
on two servers. Changes that are made to one copy of a table are
replicated to a second copy of that table, and changes that are made to
the second copy are replicated back to the first copy. This is the
intention. you can think of it as a backup server.

The choices that you make for conflict rules and conflict actions
affect the behavior of how rows are applied. The conflict rules
determine how much of the data is checked to detect a conflict and the
types of conflicts that are detected.

Bidirectional replication uses data values to detect and resolve
conflicts. You can choose which data values are used to detect
conflicts. You can choose for the Q Apply program to check one of the
following groups of columns when determining conflicts:

Only key columns values
Key columns and changed columns values
All columns values

For each server, you can choose what action each server takes when a
conflict occurs. Each server can either force the conflicting row into
its target table or ignore the conflict. These options of force and
ignore can be paired in two different ways to provide different
behaviors for the Q Apply program.
The Q Apply program logs all conflicts in the IBMQREP_EXCEPTI ONS table
and continues processing. Over time, the two copies of a logical table
will diverge.

Looking at what you are expecting you may want to have a peer to peer
subscription involving 2 nodes instead of a bidirectional replication.
In a Peer to peer replication the updates can occur at both the
databases and version columns are used to resolve conflicts.

Thanks,
LP

RdR wrote:

Hi,

Has anyone encountered infinite looping in Q Replication? This happens
when
I have a source DB2 table A going to a target DB2 table B, it also
happens
that the samne target table B is replicated back to source table A (true
bi-directional replication scenario). Once I start replication on a
master
to master scenario the changes in A gets replicated to B but that change
gets replicated back to A and so on creating an infinite loop. Is there
something I can define to prevent this from happening.

Also, any collision detection mechanisms I can use?

Thanks,

RdR

[hikums]

I am pretty sure Q Replication takes care of updates on source as well
as target servers.

Let's hope someone who has more insight come out!!

hi****@gmail.co m wrote:

From RdR's note, it appears that changes in source server is applied to

target server, then target server picks it as a change in that server
to replicate back to source, thereby looping infinitely.

Where in a bidirectional or peer will this be setup!! Is it a restart
queue function/setup issue??

I have a gut feeling that it does not end up in IBMQREP_EXCEPTI ONS,
RdR, is that the case?