Unload count discrepancy vs what Informix says is in the database
Jared Heath found a 265 million row table unloaded on 11.70.FC8 gave a file with 140 more lines than the reported row count. Andreas Legner and Mike Walker suspected newlines inside character fields, escaped with a backslash, and suggested grepping for lines ending in a backslash. The grep count matched the discrepancy. Art Kagel said such pasted newlines are common in real-world data.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
I have a very large table with over 265 million rows. When I unload the table, it reports a number unloaded. When I run a wc against the unload file, I get 140 more rows in the file than the database says it unloaded and reports as in the table from multiple sources. This is 11.70.FC8 Anyone have any ideas? Is this just a function of the super big dataset, or has the DB really lost track of 140 rows? ------------------------------ Jared Heath ------------------------------ #Informix
Newlines in character fields? Not uncommon ... Just go through the unload file using 'more' (or 'less'), only until you find the first occurrence. ------------------------------ Andreas Legner ------------------------------
As Andreas suggested - most probably newline characters in a character field. The newline character will be escaped so that it is not interpreted as an end-of-line character when loading the file. Because it's such a large file, use grep or egrep to look for a newline preceded by a "\\" character (assuming the default escape character), but will still take a while. Maybe something like this: grep '\\\\$' <filename> ------------------------------ Mike Walker ------------------------------
I didn't even think about this. The application should never let this data into the fields in this table, so I completely discounted the possibility....but the grep count matches the discrepancy. Now for a little discussion with the dev team....I doubt this data is valid for the app. ------------------------------ Jared Heath ------------------------------
Welcome to real-world data! Probably some user cut and pasted lines from another app into one or more fields on the form. That's the most common way newlines get into fields where a hard carriage return would simply move to the next field or save the form. Very common in older apps developed before GUI's and mice! ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------