Re: Help With Load

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