Re: load a file in sequence

dday@rheem.com wrote: > > We have a need to load a work table with data from a flat file. > The table has no index and because of several record types within > the table, it needs to loaded in the exact order it was written > to the flat file. The dbload works great 99% of the time. When > we get a larger flat file (4600 rows or greater), the load goes > haywire and starts loading rows haphazardly, sometimes beginning > at the top of the file again. Is there a setting that we're missing > or something that could cause this? Thanks for any advice that you > might give. Dbload always loads sequentially from the beginning of the input file to the end. Just try on a table with a serial column and a zero value in the serial field in the load file. So if this is a given then what do you mean? Are you expecting that a select from the table without an ORDER BY clause and no indexes selected, or even existing, that the rows will be return in any kind of sequential order that matches the order in which they were loaded? Sorry. It isn't going to happen except with a small number of rows (duh!) or a vast galactic accident! The RDBMS and SQL standards do not require any physical ordering of the selected data without the inclusion of an ORDER BY clause. Informix therefore not only makes no effort to return the rows in any particular order it makes no effort to store them in any particular order. If a partial page is in the process of being flushed to disk, and therefore locked, the engine will just put the current row onto another new or partial page and fill the first page later so rows are not stored in order on disk. If your rows do not have a natural key on which you can sort or index them then add a serial field and either edit/sed/awk/c-process the input file to include a zero valued field to be loaded or use a more complex dbload script to insert a literal zero into the serial column. Then you can ORDER BY the the serial column and be guaranteed that the rows will return in the order that they were loaded. Art S. Kagel