RE: load from <tablename>.txt insert into <tablename>

Hi, I have a secondary backup system set up where I backup the tables in our main database to pipe delimited files in a single directory. Part of the reason why I did this was to load it into a different instance of the database. This is to avoid using export which would tie up the production database. What I have also done as part of the process is to write a script to load the export files as one batch job (sql script) which contain a whole lot of commands of the following format... Load from <tablename>.txt insert into <tablename>; This however takes ages to reload a database (size is 14gb) and I have never waited long enough to wait until the process has finished. I was wondering if there was a faster way to do it? Lee Aholima, Database Administrator Thames-Coromandel District Council Ph 07 868 6025 Email lee.aholima@tcdc.govt.nz Mobile 0274444969 The contents of this e-mail maybe CONFIDENTIAL OR LEGALLY PRIVILEGED, & is intended only for the persons named above. If this e-mail is not addressed to you, you must not use, read, distribute or copy this document. If you have received this document by mistake, please call us and destroy the original. Thank you.

Lee Aholima said > I have a secondary backup system set up where I backup the > tables in our main database to pipe delimited files in a > single directory. Part of the reason why I did this was to > load it into a different instance of the database. > > This is to avoid using export which would tie up the > production database. > > What I have also done as part of the process is to write a > script to load the export files as one batch job (sql script) > which contain a whole lot of commands of the following format... > > Load from <tablename>.txt insert into <tablename>;> > This however takes ages to reload a database (size is 14gb) > and I have never waited long enough to wait until the process > has finished. I was wondering if there was a faster way to do it? > Dbload would probably be a lot better than an insert command, but High Performance Loader will be even faster. Dbload has the advantage of being able to specify a commit quantity to avoid long transactions. I find 10k or 100k records at a time works well. It also gives you a log file. HPL gives you all that but takes a bit more setting up. What version are you on ? 9.4 has added utils for setting up HPL. Colin Bull C.bull@videonetworks.com ________________________________________________________________________ This email has been scanned for all known viruses by the MessageLabs Email Security System. ________________________________________________________________________

Yes, take a look at both dbload and HPL (High Performance Loader). Both should be much faster. Also, in your existing scripts, if you build all indexes after loading a table rather than at the same time, then they should run much faster. "Lee Aholima" <lee.aholima@tcdc.govt.nz> Sent by: forum.subscriber@iiug.org 07/08/2004 06:06 PM To: ids@iiug.org cc: Subject: RE: load from <tablename>.txt insert into <tablename> [3212] Hi, I have a secondary backup system set up where I backup the tables in our main database to pipe delimited files in a single directory. Part of the reason why I did this was to load it into a different instance of the database. This is to avoid using export which would tie up the production database. What I have also done as part of the process is to write a script to load the export files as one batch job (sql script) which contain a whole lot of commands of the following format... Load from <tablename>.txt insert into <tablename>; This however takes ages to reload a database (size is 14gb) and I have never waited long enough to wait until the process has finished. I was wondering if there was a faster way to do it? Lee Aholima, Database Administrator Thames-Coromandel District Council Ph 07 868 6025 Email lee.aholima@tcdc.govt.nz Mobile 0274444969 The contents of this e-mail maybe CONFIDENTIAL OR LEGALLY PRIVILEGED, & is intended only for the persons named above. If this e-mail is not addressed to you, you must not use, read, distribute or copy this document. If you have received this document by mistake, please call us and destroy the original. Thank you.

check the load faq for more detail. www.artentech.com/downloads.htm j. ----- Original Message ----- From: <JHAYS2@sears.com> To: <ids@iiug.org> Sent: Friday, July 09, 2004 9:51 AM Subject: RE: load from <tablename>.txt insert into <tablename> [3217] > Yes, take a look at both dbload and HPL (High Performance Loader). Both > should be much faster. > Also, in your existing scripts, if you build all indexes after loading a > table rather than at the same time, then they should run much faster. > > > > > "Lee Aholima" <lee.aholima@tcdc.govt.nz> > Sent by: forum.subscriber@iiug.org > 07/08/2004 06:06 PM > > > To: ids@iiug.org > cc: > Subject: RE: load from <tablename>.txt insert into <tablename> [3212] > > > Hi, > > I have a secondary backup system set up where I backup the tables in our > main database to pipe delimited files in a single directory. Part of the > reason why I did this was to load it into a different instance of the > database. > > This is to avoid using export which would tie up the production > database. > > What I have also done as part of the process is to write a script to > load the export files as one batch job (sql script) which contain a > whole lot of commands of the following format... > > Load from <tablename>.txt insert into <tablename>;> > This however takes ages to reload a database (size is 14gb) and I have > never waited long enough to wait until the process has finished. I was > wondering if there was a faster way to do it? > > Lee Aholima, > Database Administrator > Thames-Coromandel District Council > Ph 07 868 6025 > Email lee.aholima@tcdc.govt.nz > Mobile 0274444969 > > The contents of this e-mail maybe CONFIDENTIAL OR LEGALLY PRIVILEGED, & > is intended only for the persons named above. If this e-mail is not > addressed to you, you must not use, read, distribute or copy this > document. If you have received this document by mistake, please call us > and destroy the original. Thank you. > > > > > > > >