Re: Copying tables

Kevan <kevan@motiv.co.uk> wrote: > 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 FROMremote_db@remote_machine:remote_table; > > COMMIT WORK; Try it as follow: CREATE TABLE local_table_temp(column_one,column_two); INSERT INTO local_table_temp SELECT * FROM remote_db@remote_machine:remote_table; DROP TABLE local_table;RENAME TABLE local_table_temp TO local_table; CREATE INDEX ... .. If you use two scripts with different index names one after another the create index step can be performed before the old table will be dropped. (System down time -> 0) Hope this helps Uwe Hope this helps