Re: question on load vs. count(*) counts

>Date: Mon, 31 Aug 92 16:15:29 EDT >From: uunet!letterkenn-emh1.army.mil!Root >Subject: question on load vs. count(*) counts >X-Informix-List-Id: <list.1412> > >A programmer at our site created a new table (unisys 5000, sql 4.0.UE2, >and SE engine) and then made a flat pipe-separated file with say, 50 >records in it. Using sql load command, try to load it into empty table. > >When the 43rd row had a problem, of course the load halted and a count >of successful rows loaded was displayed, and the reason (error) encountered. > >What we THINK we notice is: > >If the error is 846 Number of values in load file not equal to # columns, the >count displayed is 42, and "select count(*) from table" also yields 42. >No problem, right? The error in my load file is on line 43, and sure enough, >there it is. Neat debugging tool. > >But, when the errors was either 391 cannot insert a null into column, or >239 could not iinsert new row - duplicate value in unique index column >the count displayed is usually 48 (always a multiple of 16). The error >occurs within 16 rows before the displayed count. "select count(*) from >table" displays a count of existing rows which is always less than the >loader said, but not more than 16 less. In this example it said 48 rows >loaded, and the actual select count(*) says 42. Each time we try this we >have run delete from table first. > >Can anyone explain? We also have a call into hotline. Is this a bug in >4.0 UE2? Or are we missing something here? Its easy to work around in an >empty table, but not in the real world. Thanks. This BB is really great >for quick answers to problems; so much expertise in one place! Thanks in >advance. > >Ann Barnes >Letterkenny Army Depot Interesting one. There is a (relatively) simple explanation for the problem and I think the circumvention below will work... Error 846 is detected by dbload when it parses an input line. This means that it can be precise about where the error is detected, as you observed. Error 391 and 239 are detected by the engine, and the problem is that dbload uses an INSERT CURSOR to store the data into the database. This means that a number of rows are submitted to the database for insertion at one time, and from your experiments, on your table, you can fit 16 rows in the buffer. What happens as far a dbload is concerned is that it reads 16 rows of data, each of which has the correct number of fields, so the values are PUT into the insert cursor. When the buffer fills, it has processed line 48 (for sake of example), but the engine rejects row 33 (as an extreme case); dbload only knows that the error occurred because the PUT from row 48 failed with the error and it doesn't know which row actually caused the trouble. We can argue the toss sometime about whether the design of dbload is correct or not; the immediate issue is how to get it to work better for you. I think your best bet is to use the -n option with the value 1; this should mean that each row is individually committed, giving you accurate reporting on when errors occur. This is, however, at the expense of performance: there are potentially many more transactions which use more transaction log; and there are more process switches since each row involves two switches instead of every 16 rows requiring a context switch. The context switches are more important than the log size for performance, though the logs can be a nuisance on an OnLine system. Yours, Jonathan Leffler (johnl@obelix.informix.com) #include <disclaimer.h> PS: dbload syntax picked up from a 4.00 manual -- I don't think it's changed, but I thought I'd warn you anyway.