Question about replication

Informix Dynamic Server Version 7.3x I'm working on a system which synchronises a large number of tables from one server to another via a series of elablorate stored procedures and triggers. The idea is that once a day the tables on the target server are an exact copy of the same tables on the source server. It is possible for Informix replication to achieve the same result and eliminate all this code? They wish to do it asynchronously once a day. Thanks in advance :) Sent via Deja.com http://www.deja.com/ Before you buy.

take the enterprise replication class offered by informix. in your case it sounds like a simple primary->target replication, without the need for worrying about update collisions (although you did not say if it was a busy updating oltp system or not, so i could be ( am often am) wrong). basically you will alter slightly the INFORMIXSQLHOSTS file on both servers, alter you ONCONFIG file slightly, and create a replication server then assign your tables to be replicated. You can do this from the command line or use an interface provided by informix to be used on a client side, in an NT box. strangiato@my-deja.com wrote: > Informix Dynamic Server Version 7.3x > > I'm working on a system which synchronises a large number of tables from > one server to another via a series of elablorate stored procedures and > triggers. > > The idea is that once a day the tables on the target server are an exact > copy of the same tables on the source server. > > It is possible for Informix replication to achieve the same result and > eliminate all this code? > > They wish to do it asynchronously once a day. > > Thanks in advance :) > > Sent via Deja.com http://www.deja.com/ > Before you buy.

Hi, My reading of Informix replication is that it is log based, and this type of replication architecture has a number of deficiencies. Informix replication will probably meet your requirements in a two server scenario, but you might want to have a look at our PeerDirect Replication Engine; this is a more robust product which supports hub and spoke, multi-hub and spoke, remote/mobile users, and was designed to require very little administration to operate large networks. For example, in a log based replication system like Informix's, if you happen to become unable to replicate with the other site, say because the network is disconnected or because the other server has crashed, you need to have enough disk space on hand to allow your log devices to continue growing until you can bring the other machine back online. This means that unless you've planned for seriously long outages, you could either run out of disk space because the logs are growing continuously, or you must abandon replication to the unavailable site. If you abandon replication, when the other site comes back online, you will have to do a complete re-extraction and a full resynch of that site. This can be very time consuming if you're doing it over a slow network, or can require you to ship tapes or some other medium for very large databases. In contrast, PDRE (because it uses our own control table technology instead of being log based) handles interruptions and outages automatically and without administrator intervention. If PDRE is interrupted even partway through a replication session, the next time it starts up (according to the schedule you've set up, which can even be as often as once a minute in 'trickle mode'), it simply picks up from where it left off. This is true whether your outage lasts a few minutes or days or even weeks, and you don't need to worry about potentially unlimited growth in the size of your logs. With PDRE you can truncate logs at will, and since our control tables stay predictably sized and do not grow over time, your local users can keep accessing and updating the local database and when replication is reestablished the other site is brought up to date automatically... no re-extraction or tape restores required, etc. There is a lot more information on PDRE at our web site, www.peerdirect.com. We support all major database platforms as well as Informix (MS, IBM, Oracle, Sybase, etc.) Kim -- T. Kim Nguyen, MASc mailto:knguyen@peerdirect.com PeerDirect Inc. http://www.peerdirect.com Multi-Platform Secure Database Replication Systems +1 (416) 805-9088 ext. 227 Fax: +1 (905) 822-3824 > From: strangiato@my-deja.com > Subject: Question about replication > Date: 25 Apr 2000 00:00:00 GMT > Message-ID: <8e49dq$7eg$1@nnrp1.deja.com> > X-Http-Proxy: 1.0 x33.deja.com:80 (Squid/1.1.22) for client 128.253.114.37 > Organization: Deja.com - Before you buy. > X-Article-Creation-Date: Tue Apr 25 14:17:39 2000 GMT > X-MyDeja-Info: XMYDJUIDstrangiato > Newsgroups: comp.databases.informix > X-Http-User-Agent: Mozilla/4.08 [en] (WinNT; U ;Nav) > > Informix Dynamic Server Version 7.3x > > I'm working on a system which synchronises a large number of tables from > one server to another via a series of elablorate stored procedures and > triggers. > > The idea is that once a day the tables on the target server are an exact > copy of the same tables on the source server. > > It is possible for Informix replication to achieve the same result and > eliminate all this code? > > They wish to do it asynchronously once a day. > > Thanks in advance :) Sent via Deja.com http://www.deja.com/ Before you buy.

I was going to remain silent on this thread because I thought that some of the user members had answered the original question rather nicely. However, there were some misconceptions about Informix and Enterprise Replication in this email, so I felt that I needed to respond. See below for a few comments.... I suspect that the best solution for the original problem would be to use HPL or to simply restore from the backup since the object was to gain a snapshot of the database once a day. But since this has become a topic for replication ---- well.... One quick comment first. From the description given, I suspect that PeerDirect is doing what might be called 'time based re-synchronization', not 'time based replication'. While there is a definite place for re-synchronization, it is really not the same as replication. For instance with re-synchronization, it is impossible to fire update triggers for each of the changes that may have happened to a row. Only the net change/last trigger will file. While in many cases that's probably not a big deal, in others it could be significant. For instance suppose you wanted to have a trigger to create an audit record any time that the price in a replicated stock item changed by more than 5%. Re-synchronization would not be able to capture all of those changes. Replication would. Again, this is not meant to be a 'slam dunk' against PeerDirect. I checked out their web site and was intrested in the product. However, I wanted to correct some of the misconceptions in the prior email in this thread... tkimnguyen@my-deja.com wrote: > Hi, > > My reading of Informix replication is that it is log based, and this > type of replication architecture has a number of deficiencies. Informix > replication will probably meet your requirements in a two server > scenario, but you might want to have a look at our PeerDirect > Replication Engine; this is a more robust product which supports hub and > spoke, multi-hub and spoke, remote/mobile users, and was designed to > require very little administration to operate large networks. While the older HDR would only support a twoserver, Primary/Target environment, this is not true with Enterprise Replication. The current releases of ER supports peer to peer, multi-level hierarchies, hub and spoke, multi-hub and spoke. We currently have quite a few customers replicating with some rather complex topologies. > > > For example, in a log based replication system like Informix's, if you > happen to become unable to replicate with the other site, say because > the network is disconnected or because the other server has crashed, you > need to have enough disk space on hand to allow your log devices to > continue growing until you can bring the other machine back online. Not quite true. While HDR directly applied the logs from the source to the target, that is not how ER works. With ER we process the log buffers as they are being written to disk. We then package only the information from those log buffers that are necessary for the replication of that transaction. It is true that we will spool the transaction into a stable queue if the target is unavailable, but that is in a much more compact format than the original log files. Once that is done, then we are able to release the log file space. While the stable queue does require disk space, well ---- in today's Fry's ads, they had 20 Gig drives on sell for $200.... ;-) We chose log capture because we wanted to avoid user impact. Trigger based data capture caused significant overhead to the user and forced a doubling of the log resources needed by a transaction. In normal mode in the current releases, log snooping is done from the log buffers as they are written to disk so the snooping process does not require additional IO overhead. > > This means that unless you've planned for seriously long outages, you > could either run out of disk space because the logs are growing > continuously, or you must abandon replication to the unavailable site. > If you abandon replication, when the other site comes back online, you > will have to do a complete re-extraction and a full resynch of that > site. This can be very time consuming if you're doing it over a slow > network, or can require you to ship tapes or some other medium for very > large databases. Again the 'logs growing continuously' is not true. Also again - Fry's has 20 Gig drives for sell for $200.00. Additionally the bit about a full resync is not totally true. If it is anticipated that the failure time is going to be relatively short, it might be worth it to simply let the stable queue get big. Yep - you might need to take a quick trip for one of those 20 Gig drives that Fry's has on sell, but that might be the choice that you want to do. You can enlarge the space used by the stable queue by adding a chunk to it's dbspace. If you chose to break the replicates, then there is the issue of how to resync the data. But it doesn't necessarly require a complete re-extraction and full resync. For instance the easiest is to simply do a dummy update on the source for tables using timestamp conflict resolution. If you want to be selective, you can simply 'grab' the rows whose 'cdrtime' is more recent than when the failure occured. > > > In contrast, PDRE (because it uses our own control table technology > instead of being log based) handles interruptions and outages > automatically and without administrator intervention. If PDRE is > interrupted even partway through a replication session, the next time it > starts up (according to the schedule you've set up, which can even be as > often as once a minute in 'trickle mode'), it simply picks up from where > it left off. This is also true with Enterprise Replication. We can configure the replication to be time based. Also whenever the replication connection is broken, it is automatically resumed where it left off when the connection is resumed. > This is true whether your outage lasts a few minutes or > days or even weeks, and you don't need to worry about potentially > unlimited growth in the size of your logs. Again ER does not cause the log files to grow. > With PDRE you can truncate > logs at will, and since our control tables stay predictably sized and do > not grow over time, your local users can keep accessing and updating the > local database and when replication is reestablished the other site is > brought up to date automatically... no re-extraction or tape restores > required, etc. > There is a lot more information on PDRE at our web site, > www.peerdirect.com. We support all major database platforms as well as > Informix (MS, IBM, Oracle, Sybase, etc.) > > Kim > > -- > T. Kim Nguyen, MASc mailto:knguyen@peerdirect.com > PeerDirect Inc. http://www.peerdirect.com > Multi-Platform Secure Database Replication Systems > +1 (416) 805-9088 ext. 227 Fax: +1 (905) 822-3824 > > > From: strangiato@my-deja.com > > Subject: Question about replication > > Date: 25 Apr 2000 00:00:00 GMT > > Message-ID: <8e49dq$7eg$1@nnrp1.deja.com> > > X-Http-Proxy: 1.0 x33.deja.com:80 (Squid/1.1.22) for client >

In article <8ecol4$ieq$1@nnrp1.deja.com>, tkimnguyen@my-deja.com writes >Hi, > >My reading of Informix replication is that it is log based, and this Correct. >type of replication architecture has a number of deficiencies. Informix >replication will probably meet your requirements in a two server >scenario, but you might want to have a look at our PeerDirect >Replication Engine; this is a more robust product which supports hub and >spoke, multi-hub and spoke, remote/mobile users, and was designed to >require very little administration to operate large networks. > >For example, in a log based replication system like Informix's, if you >happen to become unable to replicate with the other site, say because >the network is disconnected or because the other server has crashed, you >need to have enough disk space on hand to allow your log devices to >continue growing until you can bring the other machine back online. Correct. This applies to any replication system. OK, I have a table with 1,000,000 rows in it and delete all but 1 of them. Where does your system store the 'missing' data? >This means that unless you've planned for seriously long outages, you >could either run out of disk space because the logs are growing >continuously, or you must abandon replication to the unavailable site. >If you abandon replication, when the other site comes back online, you >will have to do a complete re-extraction and a full resynch of that >site. This can be very time consuming if you're doing it over a slow >network, or can require you to ship tapes or some other medium for very >large databases. > >In contrast, PDRE (because it uses our own control table technology >instead of being log based) handles interruptions and outages Your 'control table technology must still store a list of the rows deleted above to be able to delete them from the target site when it reconnects. How do you handle this? Sounds like smoke and mirrors to me.... >automatically and without administrator intervention. If PDRE is >interrupted even partway through a replication session, the next time it >starts up (according to the schedule you've set up, which can even be as >often as once a minute in 'trickle mode'), it simply picks up from where >it left off. This is true whether your outage lasts a few minutes or >days or even weeks, and you don't need to worry about potentially >unlimited growth in the size of your logs. With PDRE you can truncate >logs at will, and since our control tables stay predictably sized and do ^^^^^^^^^^^^^^^^^^^^^^ Does this mean a) a maximum fixed size or b) the size can be calculated based on what needs to be replicated? b) means you need the same space as log-based system since you are still storing the data to be replicated. What happens if this runs out of space? >not grow over time, your local users can keep accessing and updating the >local database and when replication is reestablished the other site is >brought up to date automatically... no re-extraction or tape restores >required, etc. > >There is a lot more information on PDRE at our web site, >www.peerdirect.com. We support all major database platforms as well as >Informix (MS, IBM, Oracle, Sybase, etc.) > > Kim > >-- >T. Kim Nguyen, MASc mailto:knguyen@peerdirect.com >PeerDirect Inc. http://www.peerdirect.com >Multi-Platform Secure Database Replication Systems >+1 (416) 805-9088 ext. 227 Fax: +1 (905) 822-3824 > >> From: strangiato@my-deja.com >> Subject: Question about replication >> Date: 25 Apr 2000 00:00:00 GMT >> Message-ID: <8e49dq$7eg$1@nnrp1.deja.com> >> X-Http-Proxy: 1.0 x33.deja.com:80 (Squid/1.1.22) for client >128.253.114.37 >> Organization: Deja.com - Before you buy. >> X-Article-Creation-Date: Tue Apr 25 14:17:39 2000 GMT >> X-MyDeja-Info: XMYDJUIDstrangiato >> Newsgroups: comp.databases.informix >> X-Http-User-Agent: Mozilla/4.08 [en] (WinNT; U ;Nav) >> >> Informix Dynamic Server Version 7.3x >> >> I'm working on a system which synchronises a large number of tables >from >> one server to another via a series of elablorate stored procedures and >> triggers. >> >> The idea is that once a day the tables on the target server are an >exact >> copy of the same tables on the source server. >> >> It is possible for Informix replication to achieve the same result and >> eliminate all this code? >> >> They wish to do it asynchronously once a day. >> >> Thanks in advance :) > > >Sent via Deja.com http://www.deja.com/ >Before you buy. -- David Williams