load from is slow

Hi, All; I have Informix SE 7.2 running on HP-UNIX 10.2. I used 'unload' to move a table (16 million records in it) from another database into this new database. However, 'load' is very slow. It only loads about 1 million records in 5 hours. So the question is what is the best way to move data from one database to another. TIA Randy

On Mon, 4 Sep 2000 10:27:24 -0700, "r_hao" <r_hao@hotmail.com> wrote: >I have Informix SE 7.2 running on HP-UNIX 10.2. I used 'unload' to move a >table (16 million records in it) from another database into this new >database. However, 'load' is very slow. It only loads about 1 million >records in 5 hours. So the question is what is the best way to move data >from one database to another. For a large, bulk load, drop any existing indexes (except any UNIQUE indexes that you need to remian enforced, perhaps), then rebuild the indexes after loading is complete -- it saves the overhead of maintaining the indexes on the fly. -- Alan Denney yosemite at accesscom.com "My childhood was typical... when I was insolent, I was placed in a burlap bag and beaten with reeds. Pretty standard, really." -- Dr. Evil

If you must use unload/load, try to build the indexes after importing the data: The load itself will be faster, but building indexes can be slow (read verrry ...) Is it just one table - or a few of them? And is the new database brand new (no other data, or easily rebuilt) And, I presume that under SE you can see the data and index files (*.dat, *.idx). Two approaches - essentially similar. The second approach (backup/restore) should be quicker. The variant below that is sweeter. Try dbexport and dbimport - there are other current discussions in the group Unload data from the new_db that you do not want to loose (also backup) dbschema -d new_db new_db Edit new_db.sql just produced so it will recreate bit you want to keep (you may want to add load from ... for your saved data) dbexport -d old_db Edit the script produced, so it creates new_db, and only imports information you want. drop new_db Then do your dbimport Run your new_db script to recover structure and data you dropped earlier. dbimport relies on loading data, so may not be much quicker. Try backup/restore Unload data from the new db that you do not want to loose (also backup) dbschema as above DO NOT drop new_db cd ${DBPATH}/old_db.dbs check your current directory backup current directory (old_db) - basically, we want the filenames, and data but not the path to those files cd ${DBPATH}/new_db.dbs check your current directory - the next bit could be embarrassing delete everything in the current directory (new_db) restore old_db in new_db Now, dbaccess (or isql) to drop unnecessary tables, triggers, procedures, users &c from the restore Rebuild new_db data that you just deleted. Variant on the above ... OK if you are working on different servers Unload essential data from new_db (backup new_db) Back-up old_db (normal full backup - level 0 in online parlance) Restore old_db as old_db on new server (normal full restore). Drop unnecessary tables, procedures &c Drop new_db Rename old_db as new_db Reload essential data How desperate are you? I can think of a back-passage approach (you know what comes out of those!) based on the backup/restore model above. You would need to understand the data dictionary, to think laterally, and as I say, be desperate. You *could* reduce the backup to two files. "r_hao" <r_hao@hotmail.com> wrote in message news:8p0bhh$2es$1@slb6.atl.mindspring.net... > Hi, All; > I have Informix SE 7.2 running on HP-UNIX 10.2. I used 'unload' to move a > table (16 million records in it) from another database into this new > database. However, 'load' is very slow. It only loads about 1 million > records in 5 hours. So the question is what is the best way to move data > from one database to another. > > TIA > > Randy > >

> On Mon, 4 Sep 2000 10:27:24 -0700, "r_hao" <r_hao@hotmail.com> wrote: > > >I have Informix SE 7.2 running on HP-UNIX 10.2. I used 'unload' to move a > >table (16 million records in it) from another database into this new > >database. However, 'load' is very slow. It only loads about 1 million > >records in 5 hours. So the question is what is the best way to move data > >from one database to another. To the other good advice, particularly about the High Performance Loader and loading without indexes, you will get better performance if the indexes and tables are on different disks, and if possible even different controllers. Best wishes, David Dahl Portland, Oregon