Re: count(*) <> "wc -l" of unload file

John Bitsche wrote: > > Greetings from the Heart of Texas (Austin): > > Outside the sky is azure, the sun is golden, the temperature is in the > eighties, and...I'm sitting inside at work puzzling over a problem. > Perhaps somebody out there possesses the knowledge that will set me free > in time to carpe some diem? > > The short version of our problem (in compact jargonese): > count(*) of table <> "wc -l" of unload file > Subsequently, if we dbexport and dbimport, the dbimport croaks with > "Import data is corrupted!" > I know we can just change the number of rows in the .exp/.sql file to > agree with the number of rows in the unload file, but I am loathe to do > this without understanding the cause of the problem. > > More verbosely: > > INFORMIX-OnLine Version 7.24.UC1 > INFORMIX-SQL Version 6.04.UC1 > dgux wfb R4.11MU04 generic AViiON > > select count(*) from wfgtransd> = 10765402 > > after dbexporting wfm_glint, wfm_glint.exp/wfm_glint.sql > contains: > > {TABLE wfgtransd row size = 20 number of columns = 5 index > size = 37} > { unload file name = wfgtr00652.unl number of rows = 10765402 } > > create table wfgtransd > ( > transaction_id integer, > transaction_source char(1), > tender_id integer, > transaction_amount money(16,2), > machine_id smallint > ) extent size 204800 next size 20480 lock mode row; > revoke all on wfgtransd from "public";> > create index i1_wfgtransd on wfgtransd > (transaction_id, tender_id); > create index i2_wfgtransd on wfgtransd > (transaction_source, > tender_id,transaction_id);> > However, 'wc -l wfgtr00652.unl' yields 10767789. > > Which causes an error during dbimport on fgdev, as follows (excerpted > from dbimport.out): > > { TABLE wfgtransd row size = 20 number of columns = 5 index > size = 37} > { unload file name = wfgtr00652.unl number of rows = 10765402 } > > create table wfgtransd > ( > transaction_id integer, > transaction_source char(1), > tender_id integer, > transaction_amount money(16,2), > machine_id smallint > ) extent size 204800 next size 20480 lock mode row;> Import data is corrupted! > > Somehow, dbexport wrote more rows than it counted. Either something is > funky about these additional rows, or the count(*) is inaccurate for > that table. > > Weird, huh? > > Now, we realize that we can manually change the number of rows in the > .exp/.sql file so that the import will sail through successfully, but > I'd kind of like to know the nature of this beast. > > Also, I have indeed opened a case with Informix technical support, but > given my timeline, I'm anxious to get an answer as soon as possible > (hence my plea). > > -- > John Bitsche (a.k.a. Beach) > Whole Foods Market > john.bitsche@wholefoods.com Although I see you may have a solution from other contributors, there is another possible cause of this problem: embedded return chars in the database. If you have fields that users enter with WORDWRAP (in 4GL) they can enter linefeeds (with Ctrl+N) which will give you linefeeds escaped with backslash in the unload file which wc will count. A sed or awk script will filter these out if you want to. -- Peter Lancashire Information Systems Specialist, Bayer plc Eastern Way, Bury St Edmunds, Suffolk, IP32 7AH, UK Tel: +44-1635-562258, Fax: +44-1635-562281 Mail: Peter.Lancashire.PL1@bayer.co.uk --- My Internet plumbing does not allow me to mail and post news together. Sorry. All opinions are my own and not those of Bayer plc. --- Join Infuse, the UK Informix User Group at http://www.infuse.co.uk/