Re: how to copy large data fast

Ian Michael Gumby wrote: > > > >> From: Suppository Admin <suppositoryadmin@the-suppository.com> >> If the new database is not being used by anyone, you could certainly >> turn logging off on the receiver database, run the load, then turn >> logging back on. >> >> Same for the database with the data, if no one is in it, turn the >> logging off, run the unload, then turn the logging back on. >> > > Err yes. Thought about that. But since I'm not a DBA, but an architect > and app designer, I wasn't sure how Informix would cope. Thought there > was a new type of table that didn't have logging introduced in 10. But > then again, I was falling asleep during that presentation. ;-) > > But it goes back to my point. > Going directly from one table to another is easier. You could use my dbcopy utility from the utils2_ak package. It's nearly as fast as insert into ... select ... from... and often faster when run with the -F option, the -f<commit level> option prevents long transaction problems as long as the logical logs are being actively backed up on the target, and the -a option avoids lock table problems. If you can break up the data and formulate a -s<SELECT ....> option for each you can run many copies of dbcopy, especially if dbcopy is running on a separate machine from the source and target servers, to take advantage of the extra CPU power. Finally, since it uses separate connections to the source and target server instances you can put the target database into NOLOG mode to increase the insert speed even more (can't do that with insert into... select ... from... without also dropping logging on the source). I've copied a large table with 40 copies of dbcopy running in parallel with -p 2 set (PDQPRIORITY=2). WHOOSH! 8^) Art S. Kagel >> >> Ian Michael Gumby wrote: <SNIP>