Table Replication

hi, I need some advice or sample to do table replication on ids 11.70FC1. i already create 2 informix instance, dev1 and dev2. Some table on dev1 and dev2 like "customer" table already create at dev1 and dev2 with same column.What we need to do now, customer table must be have same data at same time, means this table must be replication, what i need to do? Please advice ... tq

Hello.If you just want to replicate one table, you should go for Enterprise Replication feature.Here is the official documentation link:http://publib.boulder.ibm.com/infocenter/idshelp/v117/topic/com.ibm.erep.do c/erep.htm (you should make a bidirectional ER setup, I think, like a root-root node, for example, and then you could easily create a template for your whole table, and start it in the two nodes). That should be very nice if you implement some conflict resolution node (to help the engines decide what register could be prioritary, for example, if two updates come at the same time, from each node). But if you intend to do inserts, updates, deletes in only one side (the other could be just a read-only node), you should do a primary-target ER setup, and then start a simple replicate from primary to target nodes. Tip: you could implement both cases from OAT, in a graphical environment, instead of typing long lines of commands, with several sintaxes, like the manual says, but... it is just a matter of vision. If you have any doubt on ER, just ask us, and we could help you further ok? Regards. Alexandre Marini IBM Informix Certified Professional v10 / v11.50 / v11.70 IBM Information Management Informix Technical Professional IBM Infosphere DataStage Technical Professional Informix Senior DBA - Orizon Brasil BRIUG website administrator Informix independent consultant > To: ids@iiug.org > From: fadzil@isianpadu.com > Subject: Table Replication [29922] > Date: Sat, 30 Mar 2013 08:57:11 -0400 > > hi, > > I need some advice or sample to do table replication on ids 11.70FC1. > > i already create 2 informix instance, dev1 and dev2. Some table on dev1 and > dev2 like "customer" table already create at dev1 and dev2 with same > column.What we need to do now, customer table must be have same data at same > time, means this table must be replication, what i need to do? > > Please advice ... > > tq > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

You might consider using Informix Flexible Grid (which is based on ER/Enterprise Replication), but simplifies many of the actions for an update anywhere solution and Informix Flexible Grid replicates both DML= and DDL statements, while ER only replicates the data. Here is a link to a red book which talks about many of the Informix replication technologies http://www.redbooks.ibm.com/redbooks/pdfs/sg247937.pdf John F. Miller III STSM, Lead Architect miller3@us.ibm.com 503-747-1366 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 03/30/2013 05:57:11 AM: > From: "MOHD FADZIL JUSOH" <fadzil@isianpadu.com> > To: ids@iiug.org, > Date: 03/30/2013 05:58 AM > Subject: Table Replication [29922] > Sent by: ids-bounces@iiug.org > > hi, > > I need some advice or sample to do table replication on ids 11.70FC1.= > > i already create 2 informix instance, dev1 and dev2. Some table on de= v1 and > dev2 like "customer" table already create at dev1 and dev2 with same > column.What we need to do now, customer table must be have same data = at same > time, means this table must be replication, what i need to do? > > Please advice ... > > tq > > > ***********************************************************************= ******** > Forum Note: Use "Reply" to post a response in the discussion forum.= >=

As other folks have mentioned, Enterprise replication is probably the b= est solution. Also the flexible grid makes it easier to perform much of enterprise replication Including the replication of the DDL Operations.= Sent from my iPad On Mar 30, 2013, at 7:58 AM, "MOHD FADZIL JUSOH" <fadzil@isianpadu.com>= wrote: > hi, > > I need some advice or sample to do table replication on ids 11.70FC1.= > > i already create 2 informix instance, dev1 and dev2. Some table on de= v1 and > dev2 like "customer" table already create at dev1 and dev2 with same > column.What we need to do now, customer table must be have same data = at same > time, means this table must be replication, what i need to do? > > Please advice ... > > tq > > > ***********************************************************************= ******** > Forum Note: Use "Reply" to post a response in the discussion forum.= >=

hi, i new on informix, can u give sample how to do replication table between 2 informix instance or step to do table replication.. please help.. tq

hi, thank for your advice. most sample replication i find, the replication between 2 server. what i need to do is replicate table between 2 intance on same server, dev1 and dev2. host: masid 1. informixserver= dev1_tcp onconfig = onconfig.dev1 database = demo1 table = stores ( table need to duplicate) 2. informixserver= dev2_tcp onconfig = onconfig.dev2 database = demo2 table = stores ( table need to duplicate) Can informix have features to do replication like this? if can, please give me step by step how to do it, or sample please advice, tq

Yes, indeed the only requirement for using ER replication, is that you can only do it in different database instances. You should proceed in the same way as if your 2 instances are in separated machines. Start creating a connection group for your instances, in your sqlhosts file (supposing you have only one file, for both instances): grp_er1 group - - i=1 dev1_tcp onsoctcp masid [port_or_servicename] g=grp_er1 grp_er2 group - - i=2 dev2_tcp onsoctcp masid [port_or_servicename2] g=grp_er2 That should enable you to connect from one instance to another, through dbaccess -> connection -> connect menu. From that on, you should proceed to the regular kind of ER setup you want to, as follows: 1) cdr define server ....... (in both instances) 2) a) cdr define replicate ..... (or) b) cdr define template ..... 3) from 2)a) setup: cdr start replicate ..... from 2)b) setup: cdr realize template ..... (in both ER nodes) 4) you must do an initial sync (replicate sync, or template sync, depending on your previous choice of setup). This is a basic cook receipt, but there are much more things you could enable to get thins easier to administer, and monitor... begin in this path, and if you have further questions, just let us know. Regards. Alexandre Marini IBM Informix Certified Professional v10 / v11.50 / v11.70 IBM Information Management Informix Technical Professional IBM Infosphere DataStage Technical Professional Informix Senior DBA - Orizon Brasil BRIUG website administrator Informix independent consultant > To: ids@iiug.org > From: fadzil@isianpadu.com > Subject: Re: RE: Table Replication [29940] > Date: Mon, 1 Apr 2013 05:18:14 -0400 > > hi, > > thank for your advice. > most sample replication i find, the replication between 2 server. > > what i need to do is replicate table between 2 intance on same server, dev1 > and dev2. > > host: masid > > 1. informixserver= dev1_tcp > > onconfig = onconfig.dev1 > > database = demo1 > > table = stores ( table need to duplicate) > > 2. informixserver= dev2_tcp > > onconfig = onconfig.dev2 > > database = demo2 > > table = stores ( table need to duplicate) > > Can informix have features to do replication like this? if can, please give me > step by step how to do it, or sample > > please advice, tq > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

hi, thank for your advice, i already done to create replication. i still have a question. 1. Most existing table don't have primary key, so i need to alter table to use erkey, automatic add 3 column to table, right? this 3 column will be effect to my 4gl code or not,because some code use select .*, insert .* , i worry we need to change my exiting 4gl code if return error "column not match" because when uses erkey, 3 column already add to table. i need more explanation for this? 2. This replication just one to one or when can use to one to many replication. I already replication table store between maindb@dev1 and javadb@dev2, on dev2 to we have another database call java2@dev2 that also have table state, table state at java2@dev2 can do replication with table state at maindb@dev1 or cannot? tq

What version? I knew of one customer who created a primary key by usi= ng two columns having with a unique index on one column and another column= . That way the unique index behavior remained in effect. Sent from my iPad On Apr 2, 2013, at 8:21 PM, "MOHD FADZIL JUSOH" <fadzil@isianpadu.com> wrote: > hi, > > thank for your advice, i already done to create replication. > > i still have a question. > > 1. Most existing table don't have primary key, so i need to alter tab= le to use > erkey, automatic add 3 column to table, right? this 3 column will be effect to > my 4gl code or not,because some code use select .*, insert .* , i wor= ry we > need to change my exiting 4gl code if return error "column not match"= because > when uses erkey, 3 column already add to table. i need more explanati= on for > this? > > 2. This replication just one to one or when can use to one to many > replication. > > I already replication table store between maindb@dev1 and javadb@dev2= , > > on dev2 to we have another database call java2@dev2 that also have ta= ble > > state, table state at java2@dev2 can do replication with table state > > at maindb@dev1 or cannot? > > tq > > > ***********************************************************************= ******** > Forum Note: Use "Reply" to post a response in the discussion forum.= >=

Well, that´s nice to hear you´ve already done the unidirectional ER, and it worked. About ERKEYS As I see, your trouble is more ER architectural than ever. 1) Please watch out for what kind did you create your servers (root, leaf nodes, etc) first 2) Read the manual (link suggestion: http://publib.boulder.ibm.com/infocenter/idshelp/v117/topic/com.ibm.erep.doc/ids _erp_083.htm) 3) If your ER architecture is not according to what you really need, you should recreate it according to the specifications (I think you must go for Update-Anywhere ER system, not Primary-Target, if that´s what you´ve done). 4) As I said before, you might go a little further, and implement some kind of conflict resolution, here is a good link: http://publib.boulder.ibm.com/infocenter/idshelp/v117/topic/com.ibm.erep.doc/ids _erp_093.htm Hope it helps, as usual. Regards. Alexandre Marini IBM Informix Certified Professional v10 / v11.50 / v11.70 IBM Information Management Informix Technical Professional IBM Infosphere DataStage Technical Professional Informix Senior DBA - Orizon Brasil BRIUG website administrator Informix independent consultant > To: ids@iiug.org > From: fadzil@isianpadu.com > Subject: Re: RE: Table Replication [29946] > Date: Tue, 2 Apr 2013 21:18:57 -0400 > > hi, > > thank for your advice, i already done to create replication. > > i still have a question. > > 1. Most existing table don't have primary key, so i need to alter table to use > erkey, automatic add 3 column to table, right? this 3 column will be effect to > my 4gl code or not,because some code use select .*, insert .* , i worry we > need to change my exiting 4gl code if return error "column not match" because > when uses erkey, 3 column already add to table. i need more explanation for > this? > > 2. This replication just one to one or when can use to one to many > replication. > > I already replication table store between maindb@dev1 and javadb@dev2, > > on dev2 to we have another database call java2@dev2 that also have table > > state, table state at java2@dev2 can do replication with table state > > at maindb@dev1 or cannot? > > tq > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Hi, I use ids v11.70fc1 at hpux itanium Tq.

hi, your right, i need ER for (Update-Anywhere ER system) because both side need to do insert, update, delete. $ cdr define repl -c grp_er1 -C timestamp -S tran -A rep_puparms --erkey \\\\ > "maindb@grp_er1:root.puparms" "select * from puparms" \\\\ > "epdb_java@grp_er2:connids.fais__puparms" "select * from fais__puparms" as i already mention before this, a few existing table don't have primary key, so i use ERKEY to do replication,my question is erkey take effect to existing 4gl code or not when this table add another 3 column (ifx_erkey_1,ifx_erkey_2, ifx_erkey_3 ), default value for erkey ? if have effected, how to solve this problem ? we need to avoid change existing 4gl code. I already test a few program, when this table have erkey, this error appear "number of column in insert does not match number of value" on insert .* syntax. Please advice... tq

Your existing tables must have some set of columns that comprise a unique key! Just declare a primary key constraint on those columns and ER will use that key. and you won't need to add ERKEYS. You do know that havine SELECT * and INSERT with no column list is VERY BAD practice and causes the kinds of problems that you are seeing here. You REALLY SHOULD modify those misbehaving 4GL apps so that they comply with best practices! Just thought I'd mention that. If you don't know what best practices are, download my presentation on Best Practices for Informix from the IIUG website's members' pages. Art Art S. Kagel Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Wed, Apr 3, 2013 at 9:04 PM, MOHD FADZIL JUSOH <fadzil@isianpadu.com>wrote: > hi, > > your right, i need ER for (Update-Anywhere ER system) because both side > need > to do > insert, update, delete. > > $ cdr define repl -c grp_er1 -C timestamp -S tran -A rep_puparms --erkey \\\\> > > "maindb@grp_er1:root.puparms" "select * from puparms" \\\\ > > > "epdb_java@grp_er2:connids.fais__puparms" "select * from fais__puparms" > > as i already mention before this, a few existing table don't have primary > key, > so i use ERKEY to do replication,my question is erkey take effect to > existing > 4gl code or not when this table add another 3 column > (ifx_erkey_1,ifx_erkey_2, > ifx_erkey_3 ), default value for erkey ? if have effected, how to solve > this > problem ? we need to avoid change existing 4gl code. > > I already test a few program, when this table have erkey, this error appear > "number of column in insert does not match number of value" on insert .* > syntax. > > Please advice... > > tq > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --bcaec554d2320de5c204d97ea2b7

hi, ok, thank you for your advice. IF your don't mind, can send to my email your presentation on Best Practices for Informix, it's can be guideline for our developer to improve their skill on informix sql syntax. tq