ER replication with WHERE clause
Frank asked whether ER's documented behaviour for replicates defined with a WHERE clause can be suppressed: when an update makes a row stop (or start) matching the filter, ER converts the update into a delete (or insert) on the target, whereas he wanted plain updates. His goal was to replicate only rows with status NEW or DONE and skip the intermediate status changes to cut replication volume. Andreas suggested trying 'cdr define replicate --ignoredel y'; Art argued the filter approach is wrong and recommended instead replicating everything but wrapping the intermediate updates in BEGIN WORK WITHOUT REPLICATION (possibly via a small ESQL/C helper). Frank said application changes weren't currently feasible, and the thread ends without a confirmed resolution.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi, IDS12.10 FC4, Linux http://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.erep.doc/ids_er p_023.htm ============== WHERE-clause column updates If a replicate includes a WHERE clause in its data selection, the WHERE clause imposes selection criteria for rows in the replicated table. - If an update changes a row so that it no longer passes the selection criteria on the source, it is deleted from the target table. Enterprise Replication translates the update into a delete and sends it to the target. - If an update changes a row so that it passes the selection criteria on the source, it is inserted into the target table. Enterprise Replication translates the update into an insert and sends it to the target. ================== The above said, if an update changes the row, so the row no longer pass the selection criteria on the source, it will delete the row of the remote site. If an update changes a row so that it passes the selection criteria , it will insert the row in remote site. My question is: Can we control the above behavior? i.e., No remote delete/insert , I want it does the same updates in the remote site too. Thanks Frank --94eb2c192308780921054135f6f4
Yes. Remove the WHERE clause. Art On Nov 13, 2016 17:08, "FRANK" <yunyaoqu@gmail.com> wrote: > Hi, > > IDS12.10 FC4, Linux > > > http://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/ > com.ibm.erep.doc/ids_erp_023.htm > > ============== > WHERE-clause column updates > > If a replicate includes a WHERE clause in its data selection, the WHERE > clause imposes selection criteria for rows in the replicated table. > > - If an update changes a row so that it no longer passes the selection > > criteria on the source, it is deleted from the target table. Enterprise > > Replication translates the update into a delete and sends it to the > > target. > > - If an update changes a row so that it passes the selection criteria on > > the source, it is inserted into the target table. Enterprise Replication > > translates the update into an insert and sends it to the target. > > ================== > > The above said, if an update changes the row, so the row no longer pass > the selection criteria on the source, it will delete the row of the remote > site. If an update changes a row so that it passes the selection > criteria , it will insert the row in remote site. > > My question is: Can we control the above behavior? i.e., No remote > delete/insert , I want it does the same updates in the remote site too. > > Thanks > Frank > > --94eb2c192308780921054135f6f4 > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --94eb2c12cad02339100541379b1d
Tried the "--ignoredel y" option of 'cdr define replicate'? Not sure it applies here but ... it should !? HTH, Andreas From: "FRANK" <yunyaoqu@gmail.com> To: ids@iiug.org Date: 13.11.2016 23:09 Subject: ER replication with WHERE clause [38133] Sent by: ids-bounces@iiug.org Hi,=20 IDS12.10 FC4, Linux=20 http://www.ibm.com/support/knowledgecenter/SSGU8G=5F12.1.0/com.ibm.erep.doc= /ids=5Ferp=5F023.htm=20 =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=20 WHERE-clause column updates=20 If a replicate includes a WHERE clause in its data selection, the WHERE=20 clause imposes selection criteria for rows in the replicated table.=20 - If an update changes a row so that it no longer passes the selection=20 criteria on the source, it is deleted from the target table. Enterprise=20 Replication translates the update into a delete and sends it to the=20 target.=20 - If an update changes a row so that it passes the selection criteria on=20 the source, it is inserted into the target table. Enterprise Replication=20 translates the update into an insert and sends it to the target.=20 =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=20 The above said, if an update changes the row, so the row no longer pass=20 the selection criteria on the source, it will delete the row of the remote = site. If an update changes a row so that it passes the selection=20 criteria , it will insert the row in remote site.=20 My question is: Can we control the above behavior? i.e., No remote=20 delete/insert , I want it does the same updates in the remote site too.=20 Thanks=20 Frank=20 --94eb2c192308780921054135f6f4=20 ***************************************************************************= ****=20 Forum Note: Use "Reply" to post a response in the discussion forum.=20
Hi, Art, Currently, we are not using WHERE clause. We want use the WHERE clause to replicate the only needed data to reduce the overall ER volume. Thanks Frank On Sun, Nov 13, 2016 at 7:06 PM, Art Kagel <art.kagel@gmail.com> wrote: > Yes. Remove the WHERE clause. > > Art > > On Nov 13, 2016 17:08, "FRANK" <yunyaoqu@gmail.com> wrote: > > > Hi, > > > > IDS12.10 FC4, Linux > > > > > > http://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/ > > com.ibm.erep.doc/ids_erp_023.htm > > > > ============== > > WHERE-clause column updates > > > > If a replicate includes a WHERE clause in its data selection, the WHERE > > clause imposes selection criteria for rows in the replicated table. > > > > - If an update changes a row so that it no longer passes the selection > > > > criteria on the source, it is deleted from the target table. Enterprise > > > > Replication translates the update into a delete and sends it to the > > > > target. > > > > - If an update changes a row so that it passes the selection criteria on > > > > the source, it is inserted into the target table. Enterprise Replication > > > > translates the update into an insert and sends it to the target. > > > > ================== > > > > The above said, if an update changes the row, so the row no longer pass > > the selection criteria on the source, it will delete the row of the > remote > > site. If an update changes a row so that it passes the selection > > criteria , it will insert the row in remote site. > > > > My question is: Can we control the above behavior? i.e., No remote > > delete/insert , I want it does the same updates in the remote site too. > > > > Thanks > > Frank > > > > --94eb2c192308780921054135f6f4 > > > > > > ************************************************************ > > ******************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --94eb2c12cad02339100541379b1d > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a114423266c2257054143bb96
I got that. But, if you only want a subset of rows that match the WHERE clause on the secondary server, why not delete any rows that are updated such that they no longer qualify to be on that server? Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Nov 14, 2016 at 9:34 AM, FRANK <yunyaoqu@gmail.com> wrote: > Hi, Art, > > Currently, we are not using WHERE clause. > We want use the WHERE clause to replicate the only needed data to > reduce the overall ER volume. > > Thanks > Frank > > On Sun, Nov 13, 2016 at 7:06 PM, Art Kagel <art.kagel@gmail.com> wrote: > > > Yes. Remove the WHERE clause. > > > > Art > > > > On Nov 13, 2016 17:08, "FRANK" <yunyaoqu@gmail.com> wrote: > > > > > Hi, > > > > > > IDS12.10 FC4, Linux > > > > > > > > > http://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/ > > > com.ibm.erep.doc/ids_erp_023.htm > > > > > > ============== > > > WHERE-clause column updates > > > > > > If a replicate includes a WHERE clause in its data selection, the WHERE > > > clause imposes selection criteria for rows in the replicated table. > > > > > > - If an update changes a row so that it no longer passes the selection > > > > > > criteria on the source, it is deleted from the target table. Enterprise > > > > > > Replication translates the update into a delete and sends it to the > > > > > > target. > > > > > > - If an update changes a row so that it passes the selection criteria > on > > > > > > the source, it is inserted into the target table. Enterprise > Replication > > > > > > translates the update into an insert and sends it to the target. > > > > > > ================== > > > > > > The above said, if an update changes the row, so the row no longer pass > > > the selection criteria on the source, it will delete the row of the > > remote > > > site. If an update changes a row so that it passes the selection > > > criteria , it will insert the row in remote site. > > > > > > My question is: Can we control the above behavior? i.e., No remote > > > delete/insert , I want it does the same updates in the remote site too. > > > > > > Thanks > > > Frank > > > > > > --94eb2c192308780921054135f6f4 > > > > > > > > > ************************************************************ > > > ******************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > --94eb2c12cad02339100541379b1d > > > > > > ************************************************************ > > ******************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --001a114423266c2257054143bb96 > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e0117610f59f04d0541442275
Hi, Art, For a row initially inserted in a site, it will be replicated to other sites(update-Anywhere) with a condition , e.g., data_status="NEW". Then the row will be updated numerous times in the remote sites , its data_status will be changed from "NEW" to "START", "PROCESS","STAGE","READY"......, eventually "DONE". We want the row stays in all sites during these status change, but NO need to replicate these changes back to other sites until the last status "DONE" which should be replicated to other sites. Thanks Frank On Mon, Nov 14, 2016 at 10:03 AM, Art Kagel <art.kagel@gmail.com> wrote: > I got that. But, if you only want a subset of rows that match the WHERE > clause on the secondary server, why not delete any rows that are updated > such that they no longer qualify to be on that server? > > Art > > Art S. Kagel, President and Principal Consultant > ASK Database Management > www.askdbmgt.com > > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on the IIUG, nor any other organization with which I am > associated either explicitly, implicitly, or by inference. Neither do > those opinions reflect those of other individuals affiliated with any > entity with which I am affiliated nor those of the entities themselves. > > On Mon, Nov 14, 2016 at 9:34 AM, FRANK <yunyaoqu@gmail.com> wrote: > > > Hi, Art, > > > > Currently, we are not using WHERE clause. > > We want use the WHERE clause to replicate the only needed data to > > reduce the overall ER volume. > > > > Thanks > > Frank > > > > On Sun, Nov 13, 2016 at 7:06 PM, Art Kagel <art.kagel@gmail.com> wrote: > > > > > Yes. Remove the WHERE clause. > > > > > > Art > > > > > > On Nov 13, 2016 17:08, "FRANK" <yunyaoqu@gmail.com> wrote: > > > > > > > Hi, > > > > > > > > IDS12.10 FC4, Linux > > > > > > > > > > > > http://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/ > > > > com.ibm.erep.doc/ids_erp_023.htm > > > > > > > > ============== > > > > WHERE-clause column updates > > > > > > > > If a replicate includes a WHERE clause in its data selection, the > WHERE > > > > clause imposes selection criteria for rows in the replicated table. > > > > > > > > - If an update changes a row so that it no longer passes the > selection > > > > > > > > criteria on the source, it is deleted from the target table. > Enterprise > > > > > > > > Replication translates the update into a delete and sends it to the > > > > > > > > target. > > > > > > > > - If an update changes a row so that it passes the selection criteria > > on > > > > > > > > the source, it is inserted into the target table. Enterprise > > Replication > > > > > > > > translates the update into an insert and sends it to the target. > > > > > > > > ================== > > > > > > > > The above said, if an update changes the row, so the row no longer > pass > > > > the selection criteria on the source, it will delete the row of the > > > remote > > > > site. If an update changes a row so that it passes the selection > > > > criteria , it will insert the row in remote site. > > > > > > > > My question is: Can we control the above behavior? i.e., No remote > > > > delete/insert , I want it does the same updates in the remote site > too. > > > > > > > > Thanks > > > > Frank > > > > > > > > --94eb2c192308780921054135f6f4 > > > > > > > > > > > > ************************************************************ > > > > ******************* > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > > > > --94eb2c12cad02339100541379b1d > > > > > > > > > ************************************************************ > > > ******************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > --001a114423266c2257054143bb96 > > > > > > ************************************************************ > > ******************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --089e0117610f59f04d0541442275 > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a114e3b7c6a8f140541445965
I tried WHERE clause, something like, Replicate tab_xxx WHERE data_status in ( "NEW","DONE"); But it deletes the rows or inserts the extra rows in remote sites which does not work for us. Thanks Frank On Mon, Nov 14, 2016 at 10:18 AM, FRANK <yunyaoqu@gmail.com> wrote: > Hi, Art, > > For a row initially inserted in a site, it will be replicated to other > sites(update-Anywhere) with a condition , e.g., data_status="NEW". > > Then the row will be updated numerous times in the remote sites , its > data_status will be changed from "NEW" to "START", > "PROCESS","STAGE","READY"......, eventually "DONE". We want the row > stays in all sites during these status change, but NO need to > replicate these changes back to other sites until the last status "DONE" > which should be replicated to other sites. > > Thanks > Frank > > > On Mon, Nov 14, 2016 at 10:03 AM, Art Kagel <art.kagel@gmail.com> wrote: > >> I got that. But, if you only want a subset of rows that match the WHERE >> clause on the secondary server, why not delete any rows that are updated >> such that they no longer qualify to be on that server? >> >> Art >> >> Art S. Kagel, President and Principal Consultant >> ASK Database Management >> www.askdbmgt.com >> >> Blog: http://informix-myview.blogspot.com/ >> >> Disclaimer: Please keep in mind that my own opinions are my own opinions >> and do not reflect on the IIUG, nor any other organization with which I am >> associated either explicitly, implicitly, or by inference. Neither do >> those opinions reflect those of other individuals affiliated with any >> entity with which I am affiliated nor those of the entities themselves. >> >> On Mon, Nov 14, 2016 at 9:34 AM, FRANK <yunyaoqu@gmail.com> wrote: >> >> > Hi, Art, >> > >> > Currently, we are not using WHERE clause. >> > We want use the WHERE clause to replicate the only needed data to >> > reduce the overall ER volume. >> > >> > Thanks >> > Frank >> > >> > On Sun, Nov 13, 2016 at 7:06 PM, Art Kagel <art.kagel@gmail.com> wrote: >> > >> > > Yes. Remove the WHERE clause. >> > > >> > > Art >> > > >> > > On Nov 13, 2016 17:08, "FRANK" <yunyaoqu@gmail.com> wrote: >> > > >> > > > Hi, >> > > > >> > > > IDS12.10 FC4, Linux >> > > > >> > > > >> > > > http://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/ >> > > > com.ibm.erep.doc/ids_erp_023.htm >> > > > >> > > > ============== >> > > > WHERE-clause column updates >> > > > >> > > > If a replicate includes a WHERE clause in its data selection, the >> WHERE >> > > > clause imposes selection criteria for rows in the replicated table. >> > > > >> > > > - If an update changes a row so that it no longer passes the >> selection >> > > > >> > > > criteria on the source, it is deleted from the target table. >> Enterprise >> > > > >> > > > Replication translates the update into a delete and sends it to the >> > > > >> > > > target. >> > > > >> > > > - If an update changes a row so that it passes the selection >> criteria >> > on >> > > > >> > > > the source, it is inserted into the target table. Enterprise >> > Replication >> > > > >> > > > translates the update into an insert and sends it to the target. >> > > > >> > > > ================== >> > > > >> > > > The above said, if an update changes the row, so the row no longer >> pass >> > > > the selection criteria on the source, it will delete the row of the >> > > remote >> > > > site. If an update changes a row so that it passes the selection >> > > > criteria , it will insert the row in remote site. >> > > > >> > > > My question is: Can we control the above behavior? i.e., No remote >> > > > delete/insert , I want it does the same updates in the remote site >> too. >> > > > >> > > > Thanks >> > > > Frank >> > > > >> > > > --94eb2c192308780921054135f6f4 >> > > > >> > > > >> > > > ************************************************************ >> > > > ******************* >> > > > Forum Note: Use "Reply" to post a response in the discussion forum. >> > > > >> > > > >> > > >> > > --94eb2c12cad02339100541379b1d >> > > >> > > >> > > ************************************************************ >> > > ******************* >> > > Forum Note: Use "Reply" to post a response in the discussion forum. >> > > >> > > >> > >> > --001a114423266c2257054143bb96 >> > >> > >> > ************************************************************ >> > ******************* >> > Forum Note: Use "Reply" to post a response in the discussion forum. >> > >> > >> >> --089e0117610f59f04d0541442275 >> >> >> ************************************************************ >> ******************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> > --001a114423262634d60541446958
Unless the "status" column is the one determining to where the row is replicated that's not a problem. The final "DONE" state will always replicate. The way to prevent the intermediate steps from replicating is not be defining the remote site to only replicate the initial "NEW" and "DONE' states but rather to code the application so that the intermediate status changes happen in a transaction wrapped in: BEGIN WORK WITHOUT REPLICATION; UPDATE ... COMMIT WORK; Don't know your development language, but this is available in ESQL/C only IB, but it wouldn't be hard to code a small app in ESQL/C for just this one task and call if from anything else if you are using a different development stack. As a standalone it's probably a 10 line program. I have not posted the following in a long time, so it is no only directed at you but to everyone: Do NOT post the solution that you thought you had found to your problem but that didn't work and ask us how to make it work! Instead post your original problem and ask us "This is what I need to happen. How can I do that?" It is a FAR more effective way to solve the initial issue! Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Nov 14, 2016 at 10:18 AM, FRANK <yunyaoqu@gmail.com> wrote: > Hi, Art, > > For a row initially inserted in a site, it will be replicated to other > sites(update-Anywhere) with a condition , e.g., data_status="NEW". > > Then the row will be updated numerous times in the remote sites , its > data_status will be changed from "NEW" to "START", > "PROCESS","STAGE","READY"......, eventually "DONE". We want the row stays > in all sites during these status change, but NO need to replicate these > changes back to other sites until the last status "DONE" which should be > replicated to other sites. > > Thanks > Frank > > On Mon, Nov 14, 2016 at 10:03 AM, Art Kagel <art.kagel@gmail.com> wrote: > > > I got that. But, if you only want a subset of rows that match the WHERE > > clause on the secondary server, why not delete any rows that are updated > > such that they no longer qualify to be on that server? > > > > Art > > > > Art S. Kagel, President and Principal Consultant > > ASK Database Management > > www.askdbmgt.com > > > > Blog: http://informix-myview.blogspot.com/ > > > > Disclaimer: Please keep in mind that my own opinions are my own opinions > > and do not reflect on the IIUG, nor any other organization with which I > am > > associated either explicitly, implicitly, or by inference. Neither do > > those opinions reflect those of other individuals affiliated with any > > entity with which I am affiliated nor those of the entities themselves. > > > > On Mon, Nov 14, 2016 at 9:34 AM, FRANK <yunyaoqu@gmail.com> wrote: > > > > > Hi, Art, > > > > > > Currently, we are not using WHERE clause. > > > We want use the WHERE clause to replicate the only needed data to > > > reduce the overall ER volume. > > > > > > Thanks > > > Frank > > > > > > On Sun, Nov 13, 2016 at 7:06 PM, Art Kagel <art.kagel@gmail.com> > wrote: > > > > > > > Yes. Remove the WHERE clause. > > > > > > > > Art > > > > > > > > On Nov 13, 2016 17:08, "FRANK" <yunyaoqu@gmail.com> wrote: > > > > > > > > > Hi, > > > > > > > > > > IDS12.10 FC4, Linux > > > > > > > > > > > > > > > http://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/ > > > > > com.ibm.erep.doc/ids_erp_023.htm > > > > > > > > > > ============== > > > > > WHERE-clause column updates > > > > > > > > > > If a replicate includes a WHERE clause in its data selection, the > > WHERE > > > > > clause imposes selection criteria for rows in the replicated table. > > > > > > > > > > - If an update changes a row so that it no longer passes the > > selection > > > > > > > > > > criteria on the source, it is deleted from the target table. > > Enterprise > > > > > > > > > > Replication translates the update into a delete and sends it to the > > > > > > > > > > target. > > > > > > > > > > - If an update changes a row so that it passes the selection > criteria > > > on > > > > > > > > > > the source, it is inserted into the target table. Enterprise > > > Replication > > > > > > > > > > translates the update into an insert and sends it to the target. > > > > > > > > > > ================== > > > > > > > > > > The above said, if an update changes the row, so the row no longer > > pass > > > > > the selection criteria on the source, it will delete the row of the > > > > remote > > > > > site. If an update changes a row so that it passes the selection > > > > > criteria , it will insert the row in remote site. > > > > > > > > > > My question is: Can we control the above behavior? i.e., No remote > > > > > delete/insert , I want it does the same updates in the remote site > > too. > > > > > > > > > > Thanks > > > > > Frank > > > > > > > > > > --94eb2c192308780921054135f6f4 > > > > > > > > > > > > > > > ************************************************************ > > > > > ******************* > > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > > > > > > > > --94eb2c12cad02339100541379b1d > > > > > > > > > > > > ************************************************************ > > > > ******************* > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > > > > --001a114423266c2257054143bb96 > > > > > > > > > ************************************************************ > > > ******************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > --089e0117610f59f04d0541442275 > > > > > > ************************************************************ > > ******************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --001a114e3b7c6a8f140541445965 > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e0117610f5c78b90541448113
We knew how to use " begin work without replication", but the code change is not possible or favorable , at least currently. But, Deleting or inserting the corresponding row in remote sites should be considered a sort of "wrong" action( at least in our case). It should stay as it is in the source site. On Mon, Nov 14, 2016 at 10:29 AM, Art Kagel <art.kagel@gmail.com> wrote: > Unless the "status" column is the one determining to where the row is > replicated that's not a problem. The final "DONE" state will always > replicate. The way to prevent the intermediate steps from replicating is > not be defining the remote site to only replicate the initial "NEW" and > "DONE' states but rather to code the application so that the intermediate > status changes happen in a transaction wrapped in: > > BEGIN WORK WITHOUT REPLICATION; > UPDATE ... > COMMIT WORK; > > Don't know your development language, but this is available in ESQL/C only > IB, but it wouldn't be hard to code a small app in ESQL/C for just this one > task and call if from anything else if you are using a different > development stack. As a standalone it's probably a 10 line program. > > I have not posted the following in a long time, so it is no only directed > at you but to everyone: > > Do NOT post the solution that you thought you had found to your problem but > that didn't work and ask us how to make it work! Instead post your original > problem and ask us "This is what I need to happen. How can I do that?" It > is a FAR more effective way to solve the initial issue! > > Art > > Art S. Kagel, President and Principal Consultant > ASK Database Management > www.askdbmgt.com > > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on the IIUG, nor any other organization with which I am > associated either explicitly, implicitly, or by inference. Neither do > those opinions reflect those of other individuals affiliated with any > entity with which I am affiliated nor those of the entities themselves. > > On Mon, Nov 14, 2016 at 10:18 AM, FRANK <yunyaoqu@gmail.com> wrote: > > > Hi, Art, > > > > For a row initially inserted in a site, it will be replicated to other > > sites(update-Anywhere) with a condition , e.g., data_status="NEW". > > > > Then the row will be updated numerous times in the remote sites , its > > data_status will be changed from "NEW" to "START", > > "PROCESS","STAGE","READY"......, eventually "DONE". We want the row > stays > > in all sites during these status change, but NO need to replicate these > > changes back to other sites until the last status "DONE" which should be > > replicated to other sites. > > > > Thanks > > Frank > > > > On Mon, Nov 14, 2016 at 10:03 AM, Art Kagel <art.kagel@gmail.com> wrote: > > > > > I got that. But, if you only want a subset of rows that match the WHERE > > > clause on the secondary server, why not delete any rows that are > updated > > > such that they no longer qualify to be on that server? > > > > > > Art > > > > > > Art S. Kagel, President and Principal Consultant > > > ASK Database Management > > > www.askdbmgt.com > > > > > > Blog: http://informix-myview.blogspot.com/ > > > > > > Disclaimer: Please keep in mind that my own opinions are my own > opinions > > > and do not reflect on the IIUG, nor any other organization with which I > > am > > > associated either explicitly, implicitly, or by inference. Neither do > > > those opinions reflect those of other individuals affiliated with any > > > entity with which I am affiliated nor those of the entities themselves. > > > > > > On Mon, Nov 14, 2016 at 9:34 AM, FRANK <yunyaoqu@gmail.com> wrote: > > > > > > > Hi, Art, > > > > > > > > Currently, we are not using WHERE clause. > > > > We want use the WHERE clause to replicate the only needed data to > > > > reduce the overall ER volume. > > > > > > > > Thanks > > > > Frank > > > > > > > > On Sun, Nov 13, 2016 at 7:06 PM, Art Kagel <art.kagel@gmail.com> > > wrote: > > > > > > > > > Yes. Remove the WHERE clause. > > > > > > > > > > Art > > > > > > > > > > On Nov 13, 2016 17:08, "FRANK" <yunyaoqu@gmail.com> wrote: > > > > > > > > > > > Hi, > > > > > > > > > > > > IDS12.10 FC4, Linux > > > > > > > > > > > > > > > > > > http://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/ > > > > > > com.ibm.erep.doc/ids_erp_023.htm > > > > > > > > > > > > ============== > > > > > > WHERE-clause column updates > > > > > > > > > > > > If a replicate includes a WHERE clause in its data selection, the > > > WHERE > > > > > > clause imposes selection criteria for rows in the replicated > table. > > > > > > > > > > > > - If an update changes a row so that it no longer passes the > > > selection > > > > > > > > > > > > criteria on the source, it is deleted from the target table. > > > Enterprise > > > > > > > > > > > > Replication translates the update into a delete and sends it to > the > > > > > > > > > > > > target. > > > > > > > > > > > > - If an update changes a row so that it passes the selection > > criteria > > > > on > > > > > > > > > > > > the source, it is inserted into the target table. Enterprise > > > > Replication > > > > > > > > > > > > translates the update into an insert and sends it to the target. > > > > > > > > > > > > ================== > > > > > > > > > > > > The above said, if an update changes the row, so the row no > longer > > > pass > > > > > > the selection criteria on the source, it will delete the row of > the > > > > > remote > > > > > > site. If an update changes a row so that it passes the selection > > > > > > criteria , it will insert the row in remote site. > > > > > > > > > > > > My question is: Can we control the above behavior? i.e., No > remote > > > > > > delete/insert , I want it does the same updates in the remote > site > > > too. > > > > > > > > > > > > Thanks > > > > > > Frank > > > > > > > > > > > > --94eb2c192308780921054135f6f4 > > > > > > > > > > > > > > > > > > ************************************************************ > > > > > > ******************* > > > > > > Forum Note: Use "Reply" to post a response in the discussion > forum. > > > > > > > > > > > > > > > > > > > > > > --94eb2c12cad02339100541379b1d > > > > > > > > > > > > > > > ************************************************************ > > > > > ******************* > > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > > > > > > > > --001a114423266c2257054143bb96 > > > > > > > > > > > > ************************************************************ > > > > ******************* > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > > > > --089e0117610f59f04d054144
blockquote, div.yahoo_quoted { margin-left: 0 !important; border-left:1px #715FFA solid !important; padding-left:1ex !important; background-color:white !important; } Create two replicates on the table. The first has a where clause of data_status="NEW" and uses ignore-deletes.The second has a where clause of data_status="DONE' and uses always apply. Sent from Yahoo Mail for iPad On Monday, November 14, 2016, 9:18 AM, FRANK <yunyaoqu@gmail.com> wrote: Hi, Art, For a row initially inserted in a site, it will be replicated to other sites(update-Anywhere) with a condition , e.g., data_status="NEW". Then the row will be updated numerous times in the remote sites , its data_status will be changed from "NEW" to "START", "PROCESS","STAGE","READY"......, eventually "DONE". We want the row stays in all sites during these status change, but NO need to replicate these changes back to other sites until the last status "DONE" which should be replicated to other sites. Thanks Frank On Mon, Nov 14, 2016 at 10:03 AM, Art Kagel <art.kagel@gmail.com> wrote: > I got that. But, if you only want a subset of rows that match the WHERE > clause on the secondary server, why not delete any rows that are updated > such that they no longer qualify to be on that server? > > Art > > Art S. Kagel, President and Principal Consultant > ASK Database Management > www.askdbmgt.com > > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on the IIUG, nor any other organization with which I am > associated either explicitly, implicitly, or by inference. Neither do > those opinions reflect those of other individuals affiliated with any > entity with which I am affiliated nor those of the entities themselves. > > On Mon, Nov 14, 2016 at 9:34 AM, FRANK <yunyaoqu@gmail.com> wrote: > > > Hi, Art, > > > > Currently, we are not using WHERE clause. > > We want use the WHERE clause to replicate the only needed data to > > reduce the overall ER volume. > > > > Thanks > > Frank > > > > On Sun, Nov 13, 2016 at 7:06 PM, Art Kagel <art.kagel@gmail.com> wrote: > > > > > Yes. Remove the WHERE clause. > > > > > > Art > > > > > > On Nov 13, 2016 17:08, "FRANK" <yunyaoqu@gmail.com> wrote: > > > > > > > Hi, > > > > > > > > IDS12.10 FC4, Linux > > > > > > > > > > > > http://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/ > > > > com.ibm.erep.doc/ids_erp_023.htm > > > > > > > > ============== > > > > WHERE-clause column updates > > > > > > > > If a replicate includes a WHERE clause in its data selection, the > WHERE > > > > clause imposes selection criteria for rows in the replicated table. > > > > > > > > - If an update changes a row so that it no longer passes the > selection > > > > > > > > criteria on the source, it is deleted from the target table. > Enterprise > > > > > > > > Replication translates the update into a delete and sends it to the > > > > > > > > target. > > > > > > > > - If an update changes a row so that it passes the selection criteria > > on > > > > > > > > the source, it is inserted into the target table. Enterprise > > Replication > > > > > > > > translates the update into an insert and sends it to the target. > > > > > > > > ================== > > > > > > > > The above said, if an update changes the row, so the row no longer > pass > > > > the selection criteria on the source, it will delete the row of the > > > remote > > > > site. If an update changes a row so that it passes the selection > > > > criteria , it will insert the row in remote site. > > > > > > > > My question is: Can we control the above behavior? i.e., No remote > > > > delete/insert , I want it does the same updates in the remote site > too. > > > > > > > > Thanks > > > > Frank > > > > > > > > --94eb2c192308780921054135f6f4 > > > > > > > > > > > > ************************************************************ > > > > ******************* > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > > > > --94eb2c12cad02339100541379b1d > > > > > > > > > ************************************************************ > > > ******************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > --001a114423266c2257054143bb96 > > > > > > ************************************************************ > > ******************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --089e0117610f59f04d0541442275 > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a114e3b7c6a8f140541445965 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Hi, Andreas, I tried --ignoredel y. Nope, it does not do what we need. For a update, it indeed stopped deleting the row remotely, but It confusedly added another extra row remotely. Thanks Frank On Mon, Nov 14, 2016 at 3:26 AM, Andreas Legner <andreas.legner@de.ibm.com> wrote: > Tried the "--ignoredel y" option of 'cdr define replicate'? > > Not sure it applies here but ... it should !? > > HTH, > Andreas > > From: "FRANK" <yunyaoqu@gmail.com> > To: ids@iiug.org > Date: 13.11.2016 23:09 > Subject: ER replication with WHERE clause [38133] > Sent by: ids-bounces@iiug.org > > Hi,=20 > > IDS12.10 FC4, Linux=20 > > http://www.ibm.com/support/knowledgecenter/SSGU8G=5F12.1. > 0/com.ibm.erep.doc= > /ids=5Ferp=5F023.htm=20 > > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=20 > WHERE-clause column updates=20 > > If a replicate includes a WHERE clause in its data selection, the WHERE=20 > clause imposes selection criteria for rows in the replicated table.=20 > > - If an update changes a row so that it no longer passes the selection=20 > > criteria on the source, it is deleted from the target table. Enterprise=20 > > Replication translates the update into a delete and sends it to the=20 > > target.=20 > > - If an update changes a row so that it passes the selection criteria on=20 > > the source, it is inserted into the target table. Enterprise Replication=20 > > translates the update into an insert and sends it to the target.=20 > > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=20 > > The above said, if an update changes the row, so the row no longer pass=20 > the selection criteria on the source, it will delete the row of the remote > = > > site. If an update changes a row so that it passes the selection=20 > criteria , it will insert the row in remote site.=20 > > My question is: Can we control the above behavior? i.e., No remote=20 > delete/insert , I want it does the same updates in the remote site too.=20 > > Thanks=20 > Frank=20 > > --94eb2c192308780921054135f6f4=20 > > ************************************************************ > ***************= > ****=20 > > Forum Note: Use "Reply" to post a response in the discussion forum.=20 > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --f403045e1d2057abd605414774aa