RE: Copying tables
Kevan 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.
How about this:
DROP TABLE local_table;
CREATE SYNONYM local_table FOR remote_host:remote_table;
CREATE TABLE local_temp (...); { like local_table.* }
INSERT INTO local_temp
SELECT * FROM remote_host:remote_table;
DROP SYNONYM local_table;RENAME TABLE local_temp TO local_table;
This way your users are pointing at the remote host whilst you rebuild the
table. If this is not possible (due to say different user-ids), ignore the
first two lines of this code, and change DROP SYNONYM to DROP TABLE. This
method effectively creates two copies of the table for the duration of the
rebuild.
cheers
RET
+------------------------------------------------------------------------------+
| Richard Thomas (DBA) richard_thomas@yes.optus.com.au +61 2 9342
7188 |
+------------------------------------------------------------------------------+