Re: Oracle vs Informix comparison -Reply

It appears that what Raj is describing is a simple trigger based asynchonous replication that can easily be implemented in Informix, or any other RDBMS that supports triggers, by creating a couple of metadata tables and some triggers. We have been running global replication in a similar manner for over a year in Informix while awaiting the release of CDR (in version 7.22). We have chosen to hold the key of the table rather than the rowid. One of the limitations of this approach (unless Raj has simplified to just get the one point across) is the inability to correctly handle multi-way replication, especially with rowid being used rather than the actual key. (i presume the rowids differ between sites) Central node out to 'anywhere' is fine. But how does it handle the 'anywhere' sites updating the same records simultaneously and replicating them back to the central site and to each other? I.E. Conflict resolution and detection. Informix CDR is alleged to provide this by 4 methods (The last I heard). 1. Latest Timestamp 2. User definable methods 3. Hold for Manual resolution. 4. Ignore (Where you don't care.) That is why we are waiting for CDR. (Why re-invent the wheel?) I think that depending on the volatility of the data there might be some performance differences between log based replication and trigger based. These are the extra activities that happen at OLTP time in a trigger based environment. 1. the trigger is fired. 2. A SP is processed. 3. A record is inserted somewhere in a database table denoting the tablename, record (either key or rowid), and the CRUD event (CUD event I guess). This is on top of the usual logging etc. Whether this is a problem depends I suppose on the activity of the data, and how pushed the system is. Are rowids re-used in Oracle as they are in Informix (pagenum/slotnum)? If so then I wonder how rowid based replication handles an insert, delete, insert which takes place within a single refresh interval. I don't suppose that it would matter unless the first insert had an effect elsewhere else in the system, but that possibility does exist. rgds -- -- Peter Tashkoff <tashkop@kiwi.co.nz> NZ Kiwifruit Marketing Board Usual disclaimers apply. This posting may not be used by any party to vilify another. >>> Rajashekar Sundaramurthy <rajsekar@ix.netcom.com> 10/December/1996 01:41pm >>> You wrote: > >In article <589udr$mgk@svna0001.clipper.ssb.com>, Stephen A Tahmosh ><tahmossx@itd.ssb.com> writes >>Why is replication off of the logs automatically better than that provided via >>stored procedures and triggers? >>All data (almost) is logged - not all data is replicated (usually). >>Triggers/Stored Procedures can be defined only on objects which need >>replication. >> > > With stored procedures replication happens as follows. > > Application updates one table. > Triggers fires > Procedure fires > Procdures sends update to remote site > Remote site performs update > Remote site replys update ok. > ... > Application commits. > Commit send to remote site (2 phase commit protocal followed). > > Application continues. You are describing a synchronous replication scenario wherein the updating transaction waits for the replication to be completed before the commit occurs. In Oracle7 (which uses triggers/stored procedures and deferred transaction queues) to handle replication, the simpler update-anywhere environment works as follows ( for asynchronous replication) Application inserts a row into a table at the master site; The AfterInsert trigger fires which updates a snapshot log with the ROWID of the newly inserted row; Application commits the insert transaction; From the replication site, at pre-defined intervals or on demand, the changes from the master site (since the last refresh) are pulled in through background processes . (Recognize that 2PC is required for this replication txn - to update the remote site and at the master site to mark the records as having been replicated ) > > > I.e. 6 steps per table update (including 2 x network messages). > several steps for the commit (2phase commit). > + transaction logging. > > With logical log replication the 6 steps per update do not happen > and neither does to two phase commit. Instead the transaction logging > is sent across the network. This can contain the whole transaction > in less network messages (since >1 update can be put in one message). > Also the application can update and data replication replicate but > they do not have to happen in sync. Only when a commit/checkpoint > occurs do they have to sync. > > Incorrect; Asynchronous replication implies that the sites would be out of sync for some time (basically your refresh interval). Checkpoint should have nothing to do with replication (sync or async) Raj