RE: LOAD FROM error handling

In a situation where you have the possibility of dirty data, LOAD FROM...INSERT INTO may not be the best method. The better recommendation would be to use dbload, because with that tool you can specify how many bad rows are "OK" before forcing a rollback. This tool also allows periodic commits to avoid long transactions, a problem all too common when loading from flat files. Best of luck! > -----Original Message----- > From: frederik@remote.org [SMTP:frederik@remote.org] > Sent: Monday, September 13, 1999 1:19 PM > To: informix-list@iiug.org > Subject: LOAD FROM error handling > > Hi, > > when I do a LOAD FROM filename INTO table and one of the records > to be imported fails (because of a non-unique key value, say) - how > do I tell Informix that it should continue importing the rest of > the file? I'm using dbaccess. > > Thanks, > Frederik > > -- > Frederik Ramm (frederik@remote.org)

> In a situation where you have the possibility of dirty data, LOAD > FROM...INSERT INTO may not be the best method. > > The better recommendation would be to use dbload, because with that tool you > can specify how many bad rows are "OK" before forcing a rollback. This tool > also allows periodic commits to avoid long transactions, a problem all too > common when loading from flat files. Also dbload (yes I am a big fan of it too) identifies the failing row(s), and allows you to restart from any given record in the flat file. When hunting down dupes like this, I've found the most useful technique (if time allows) is to drop the partially written table, load it again but with the index in qusetion defined as non-unique, then hunt down the dupes (I can't code 4gl for beans, so usually capture rowids and the indexed columns to a file, and analyze it using UNIX tools like uniq or an awk script) and take corrective action (whatever the application requires), before dropping the index and reindexing with uniqueness back in place. Anyone know a faster trick? Regards, Paul