Informix

 View Only
  • 1.  Unload count discrepancy vs what Informix says is in the database

    Posted Thu May 20, 2021 04:04 PM
    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


  • 2.  RE: Unload count discrepancy vs what Informix says is in the database

    IBM Champion
    Posted Thu May 20, 2021 04:13 PM
    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
    ------------------------------



  • 3.  RE: Unload count discrepancy vs what Informix says is in the database

    Posted Thu May 20, 2021 04:33 PM
    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
    ------------------------------



  • 4.  RE: Unload count discrepancy vs what Informix says is in the database

    IBM Champion
    Posted Thu May 20, 2021 05:17 PM
    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
    ------------------------------



  • 5.  RE: Unload count discrepancy vs what Informix says is in the database

    IBM Champion
    Posted Thu May 20, 2021 04:25 PM
    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
    ------------------------------