Hello All,

this
> select sum(c.collength) is wrong if you have decimals, datetimes, intervals
or (n)varchars.


i have a version for calculation it all data plus index size with a
given # of rows and asumeing idx size needs to be multiplied 1.25 per
traing manual.
this lot works fine on V7.

anyways it needed to be hacked for 9 (done so however am haven't
checked the
values returned properly yet.
V 9 bitches about not be able to select from sysindexes it pukes with
999 ...)
and before i forget please run on test only!!

anyways if you want it please tell me where to send it to; it is a sql
script of 321 lines.

See you

Superboer.

piece of it:

CREATE PROCEDURE "informix".calc_idx_size(collength SMALLINT, coltype
INT)

-- this procedure gets the encoded column length and type and converts
-- it to the real length for storage on disk
-- the columns with datatype decimal money datetime interval varchar
-- and nvarchar are the ones that have to be converted because
informix
-- stores these values encoded in the system catalog.
-- other datatypes have their length in the system catalog
-- the length in bytes is returned in an integer.

RETURNING INT;
DEFINE ret_idx_size INT;
-- when we have a decimal or money column in an index calculate
-- it's size if the column does not allow nulls the type is the
-- original type plus 256


IF (coltype = 8 or coltype = (8 + 256) OR
coltype = 5 OR coltype = (5 + 256)) THEN
-- corrected by Alan Stanford training manual is wrong
--(1 + (((collength - (MOD(collength , 256))) /256 )/2));
LET ret_idx_size =
(1
+(1+((collength-MOD(collength,256))/256)-MOD(collength,256))/2
+(1+(MOD(collength , 256)))/2
);

-- the formula to get the number of bytes on disk for
-- decimal or money data types

-- we are dealing with varchar or nvarchar calculate the size

ELIF (coltype = 13 OR coltype = (13 + 256) OR
coltype = 16 OR coltype = (16 + 256)) THEN
coltype = 16 OR coltype = (16 + 256)) THEN
IF (collength < 0) THEN
LET ret_idx_size =
(MOD((collength + 65536) , 256));
ELSE
LET ret_idx_size =
(MOD(collength , 256));
END IF
-- formula to get the size for nvarchar or varchar


-- now we are dealing with datetime or intervals
ELIF (coltype = 10 OR coltype = (10 + 256) OR
coltype = 14 OR coltype = (14 + 256)) THEN
LET ret_idx_size =
(1 + ROUND(((collength - (MOD(collength , 256))) /256 )/2));
-- the formula to calculate their sizes

ELSE
-- finally if none of the above is true we can take the length from
the
-- system catalog so a column without a decoded length
LET ret_idx_size = collength;
END IF

-- return the found value
RETURN ret_idx_size;
END PROCEDURE;




"June C. Hunt" <june.c.hunt@gmail.com> wrote in message news:<ybP9d.4571$l07.1742@twister.nyroc.rr.com>...
> On Thu, 07 Oct 2004 13:52:20 -0500, REBELLO, Rulesh Felix
> <rrebello@fsl.org.jm> wrote:
> > Hello Group:
> >
> > Can I find the "index size" for a particular table by querying the
> > sysstem tables .. ..???
> >
> > It is the silimar outout which we get in the dbschema utility fo
> the ifo
> > in the .sql file of a dbexport.
>
> I might only be able to help a small bit. The following was posted to
> c.d.i. by Rajib Sarkar of IBM back on 2002-06-10:
>
> Step 1:
> =========
> select sum(c.collength)
> from syscolumns c, sysindexes i
> where i.tabid = c.tabid
> and i.tabid = <your tabid>
> and (c.colno = ABS(i.part1) or
> c.colno = ABS(i.part2) or
> c.colno = ABS(i.part3) or
> c.colno = ABS(i.part4) or
> c.colno = ABS(i.part5) or
> c.colno = ABS(i.part6) or
> c.colno = ABS(i.part7) or
> c.colno = ABS(i.part8) or
> c.colno = ABS(i.part9) or
> c.colno = ABS(i.part10) or
> c.colno = ABS(i.part11) or
> c.colno = ABS(i.part12) or
> c.colno = ABS(i.part13) or
> c.colno = ABS(i.part14) or
> c.colno = ABS(i.part15) or
> c.colno = ABS(i.part16));
>
> This will just give you the width of the index, now you would require to
> add the overhead to it, and the formula would be:
> Step 2
> =========
> select count(*) from sysindexes where tabid = <tabid>;
>
> index length = (<result of Step 1> + 4 * <result of Step 2> ) * 3/2;
>
> I've tested this formula against IDS 9.20.UC3 and get the index size
> that I expect. I've also tested this formula against IDS 9.40.FC2 and
> it does not come out to be the value I would expect. I'm not sure if
> the difference is due entirely to the version of IDS, in some portion
> to the 32 versus 64-bit difference, a combination of the two, or
> something completely different. I've spent some time looking at the
> Administrator's Guide (Art Kagel referenced that in a post that dealt
> with this same issue) and the Performance Guide (the Admin Guide
> references the Performance Guide), but haven't found an answer yet.
>
> I will continue to look for a formula that will work with IDS 9.4 (I
> can be stubborn that way), but would be happy to end my search if
> anyone else cares to post the correct answer. If not, I'll post what
> I find if and when I get a chance to get back to this....