Hi, everybody,



We've run into a funny problem with our DataWarehouse database:

update statistics HIGH and MEDIUM doesn't work for tables with more

then 2^31 = 2147483648 records (no need to say, that table is
fragmented)



Operation finishes immediately without any error, and doesn't create any
SYSDISTRIB record.



Also, SYSTABLES always gives 2^31-1 records for big tables: NROWS is of
type INT4...

One can conclude, that 'update statistics' was never supposed to work
for bigger tables.



I've filed a PMR to IBM 'passport advantage' support



-------------------------

Alexey Sonkin

http://www-1.ibm.com/support/search....Go.x=49&Go.y=8

On Jun 22, 2:29 pm, "Alexey Sonkin" <alex... (AT) cidc (DOT) com> wrote:
Yes, but the nrows in systables is not used for anything on a
fragmented table, it would be the nrows in sysfragments for each
fragment instead. In any event, since these values are UPDATED by
update statistics, it's actually the NROWS values in sysptnhdr, which
is also by fragment, that is used to calculate bucket sizes, etc.
This is just to note that the fact that these fields are INT4 is
irrelevant. There seems, as ScottishPoet pointed out with his link,
there's a bug in UPDATE STATISTICS MEDIUM in certain releases for
larger tables. Try using HIGH for now, and definitely follow up with
your Tech support call and give them the bug number noted in the link.

Art S. Kagel

Thank you,

IBM support engineer has just told me exactly same thing -
problem is addressed in 10.0FC7 and in Cheetah.

BTW, SYSTABLES.NROWS has FLOAT type in Cheetah...

-Alexey


U5D%2B

Various issues with update statistics on large tables have been fixed in
versions 9.40.xC9, 10.00xC6 and Cheetah. See following APAR's

For 9.40.xC9:
http://www-1.ibm.com/support/search....-search=Search
For 10.00xC6:
http://www-1.ibm.com/support/search....-search=Search

In 9.40xC9 and 10.00xC6, if an overflow occurs, the following columns have
been rounded off to their maximum value (2^31). In Cheetah, a complete fix
has been introduced and these columns have been changed from integer type
to double type.

systables: nrows, npused
sysindices: nleaves, nunique, clust
sysfragments: nrows, npused

Regards,
Nita.




"Art S. Kagel" <art.kagel (AT) gmail (DOT) com>
Sent by: informix-list-bounces (AT) iiug (DOT) org
06/25/2007 09:18 AM

To
informix-list (AT) iiug (DOT) org
cc

Subject
Re: Update statistics for table with more then 2^31 records






On Jun 22, 2:29 pm, "Alexey Sonkin" <alex... (AT) cidc (DOT) com> wrote:
Yes, but the nrows in systables is not used for anything on a
fragmented table, it would be the nrows in sysfragments for each
fragment instead. In any event, since these values are UPDATED by
update statistics, it's actually the NROWS values in sysptnhdr, which
is also by fragment, that is used to calculate bucket sizes, etc.
This is just to note that the fact that these fields are INT4 is
irrelevant. There seems, as ScottishPoet pointed out with his link,
there's a bug in UPDATE STATISTICS MEDIUM in certain releases for
larger tables. Try using HIGH for now, and definitely follow up with
your Tech support call and give them the bug number noted in the link.

Art S. Kagel

_______________________________________________
Informix-list mailing list
Informix-list (AT) iiug (DOT) org
http://www.iiug.org/mailman/listinfo/informix-list