Re: count(*) <> "wc -l" of unload file
(comp.databases.informix: pls forgive me if this posts twice. Problems
posting...)
Peter-
Thanks from across the world. The results of your suggestion of
count(*) where 1=1 removes my worry that I'm facing something more
serious than just a wrong number in the statistics.
select count(*)
from wfgtransd;
(count(*))
10765402
select count(*)
from wfgtransd
where 1 = 1;
(count(*))
10767789
And the latter is the number that I wanted to see.
This morn, I received a vmail from IFMX tech support confirming a bug
related to partition pages (and it's not a serious problem), so I guess
the mystery is solved.
Anyway, since we're shifting data over to a different platform, the
dbimport on the SUN will make this issue irrelevant
(at least at creation time because it's creating a new count).
Thanks again for your response. You deserve a Zespri Kiwifruit (the
world's finest).
John
Peter Tashkoff wrote:
> I believe you statement as constructed just reads the count from the
statistics info.
> Does select count... where 1=1; give a different result? The latter
will actually do a sequential count of the file and will therefore take
longer of course.
> If the two forms of the statement differ in results try;
> 1. Updating statistics.
> 2. If the above doesn't fix it , try rebuilding your indexing on the
table.
> I dont have TFM in front of me but I seem to remember both update
stats and oncheck being recommended before doing any exports, anyway, it
would seem like a good idea :-)
> HTH
> rgds
>
> Peter Tashkoff <tashkop@iname.com>
> Zespri International Limited Std Disclaimers Apply
> All rights reserved. No party may use this document to vilify
another.
>
> Zespri New Zealand Kiwifruit, The World's Finest
>
> >>> John Bitsche <john.bitsche@wholefoods.com> 10/04/98 08:13:31 >>>
> 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