Re: Copying tables

Kevan wrote: > > 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? An ESQL-C or 4GL program with an INSERT CURSOR declared WITH HOLD and committed every N rows will free the locks. You could then partition the rows that you need to copy and run multiple copies to speed the load. I have found that a typical single CPU 5.0X instance can handle about 10 copies before significant degredation to other processes and deminishing gains take over. (7.xx -> 7.xx you can double that). You want to SET ISOLATION DIRTY READ to keep the SELECT cursor from holding locks on the source machine also. Art S. Kagel