Re: Help With Load

In <ian.goddard.49.0013BB63@geo2.poptel.org.uk> ian.goddard@geo2.poptel.org.uk (Ian Goddard) writes: > >In article <46okuv$l5j@shellx.best.com> hsasaki@shellx.best.com (Harold Sasaki) writes: >>From: hsasaki@shellx.best.com (Harold Sasaki) >>Subject: Help With Load >>Date: 26 Oct 1995 18:44:47 GMT > >>We are running a database that interfaces with a Web site. Our client >>occassionally will FTP us a new file which was created by their DOS or >>Windows database. We load their file into our Informix database. The >>problem is that they will be sending us the complete database everytime >>they make changes, even if it's only to one record. How should I >>approach this? > >>1) Delete all records from the database then load the entire database again. > >>2) Check the existing database for changes with the file that was FTPed >> to us. > >>3) ??? Something better? > >>Is there a problem with deleting the old records all of the time? Do the >>deleted records still take up space? > >Something better is, of course, to get them to send you the changes only... > >What's best to recomend depends on what the differences might be. I think >your first step would probably to load the new records in a temporary table. >If it's simply a matter of extra records then it would be a case of something >along the lines of > >INSERT INTO a .... SELECT ... FROM b WHERE ... NOT IN (SELECT ... FROM a)> >or maybe > >SELECT ... FROM b WHERE ... NOT IN (SELECT ... FROM a) INTO TEMP c; >INSERT INTO a ...SELECT ... FROM c> >If you have changes to existing records then you may need to either UPDATE or >DELETE and INSERT. > >Another possibility is that you may need to delete records. > >Other possibilities involve SELECTing ROWIDs into TEMP TABLES on one pass and >processing the rows in a second pass using WHERE ROWID IN (SELECT rowid_alias >FROM your_temp_table) > >It also depends on whether you have to do it in SQL or whether you can use 4GL >or ESQL/C. > >In the latter case it may be easier to load the incoming data and run through >each row checking to see if the live table needs to be updated. This may also >be useful if there have to be related changes to other tables. > >When you load your data remember to create whatever index you will need and >*UPDATE STATISTICS*. > >I've done a bit of this work making adjustments after stocktakes. If the >tables are large it gets tedious - but it gives you time to sit and think how >you could do it faster! > >Deletion or dropping tables means that your data is unavailable for a >while, of course. That itself is a reason not to do it but also deletion is >always a slow operation in Informix - it's been that way since before they >called it Informix. It doesn't shrink the space your table occupies but new >rows can be loaded into the space occupied by the old ones. You could always >drop the table and recreate and reload it. The system increments >systables.tabid. I suppose it's always possible you might run out of tabids >(anybody know what the practical maximum is - practical including it's use in >file names in SE and default index names?)! If you drop and reload don't >forget the indexes and statistics. > >I still think you should get them to send changes only. > Ian: I don't know exactly what you're doing or how big the database is you're dealing with. Have you ever considered getting the whole database export using dbexport and then doing a dbimport? This may be hitting the gnat with a sledge-hammer, but it might be worth considering. ed