Re: Copying tables

If your systems can handle it, why not replicate? Because you are going V5 to V7, you'll have to do it yourself. Here's how I do trigger-based replication: Requirement: I-Star Steps: 1. Create store & forward table "copies" in the source dbserver. Add two columns to each s&f table: action code char(1), timestamp datetime. 2. Create ins/upd/del triggers in the source database. The triggers should insert a row into the s&f table with appropriate action codes (I, U, D). 3. Write a replication daemon that wakes up periodically to check for new rows in the s&f tables. Foreach row, do an insert/update/delete to the table in the remote target database, then delete the s&f row. If the remote DM fails, don't do the s&f delete and try again later. For updates to tables with no unique index, it gets a little trickier - you have to have the trigger save the old column values and the new ones. Your daemon then uses the old values to find the one row in the target that needs to be updated. I would recommend 4GL for the replicator daemon (there is no better language for Data Manipulation that I'm aware of).. Add a command line argument to tell the replicator how long to sleep, then you can vary replication cycles (once a week to every second). > Hi, > > We currently have a system that involves two Informix databases, a > local Online 7 database and a remote Online 5 database. Because of > this remote connection we want to periodically take copies of certain > highly accessed tables on the remote system so that we 'cache' them > locally. While the duplication is taking place we cannot stop local > read access to the cached table. > > The simple way seems to be something along the lines of... > > DELETE FROM local_table;> > INSERT INTO local_table (column_one,column_two) > SELECT column_one,column_two FROM> remote_db@remote_machine:remote_table; > > COMMIT WORK; > > ...but for some of the larger tables this is a slow process due to the > > vast amount of locks that are created. > > What is the best (fastest & secure) way of doing this? > > Many Thanks In Advance > > -- > Kevan Heydon > > Motiv Systems Ltd. | Email: k.heydon@motiv.co.uk > Orwell House | Phone: +44 (0)1223 576318 > Cowley Road | Fax : +44 (0)1223 576319 > Cambridge, CB4 4WY | WWW : http://www.motiv.co.uk/