Re: "index size" for a particular table by querying the sysstem tables
--0__=88BBE5BEDFC7D4AD8f9e8a93df938690918c88BBE5BEDFC7D4AD
Content-type: multipart/alternative;
Boundary="1__=88BBE5BEDFC7D4AD8f9e8a93df938690918c88BBE5BEDFC7D4AD"
--1__=88BBE5BEDFC7D4AD8f9e8a93df938690918c88BBE5BEDFC7D4AD
Content-type: text/plain; charset=US-ASCII
Content-transfer-encoding: quoted-printable
Hi June,
I think I can help u with this one ..:-)
Following is the way the index length is calculated in 9.40
Step 1)
select c.coltype, c.collength
from syscolumns c, sysindexes i
where i.tabid =3D <your tabid>
and ( c.colno =3D ABS(i.part1) OR
c.colno =3D ABS(i.part2) or
c.colno =3D ABS(i.part3) or
c.colno =3D ABS(i.part4) or
c.colno =3D ABS(i.part5) or
c.colno =3D ABS(i.part6) or
c.colno =3D ABS(i.part7) or
c.colno =3D ABS(i.part8) or
c.colno =3D ABS(i.part9) or
c.colno =3D ABS(i.part10) or
c.colno =3D ABS(i.part11) or
c.colno =3D ABS(i.part12) or
c.colno =3D ABS(i.part13) or
c.colno =3D ABS(i.part14) or
c.colno =3D ABS(i.part15) or
c.colno =3D ABS(i.part16));
Step 2)
For each row of the above result (pseudo code)
idxlen +=3D
if(decimal type) then
DECLENGTH(collength)
elsif (varchar type) then
VARCHARLENGTH(collength)
else
collength
where:
#define DECLENGTH(length) LENGTH(PRECISONTOT(length),
DECIMALPRECISION(length) )
#define LENGTH(m, n) (( (m) + ((n)&1)+3)/2)
#define PRECISIONTOT(x) (((x)>>8) & 0xff ) -- Total number of digits=
#define DECIMALPRECISION(y) ((y) & 0xff) -- Number of digits after t=
he
decimal
Step 3)
Get the number of indexes for the table:
select count(*) from sysindexes where tabid =3D <your tabid>
Step 4)
select count(*) from sysindexes where idxname in
(select unique indexname from sysfragments where fragtype =3D 'I' andstrategy !=3D 'T'
and tabid =3D (select distinct tabid from sysfragments where frag=
type =3D
'T' and tabid =3D <your tabid> ) )
Step 5)
TOTAL IDXLEN =3D <Results from Step 2> + 5 * <Results from Step 3> + 4 =
*
<Results from Step 4>
I would suggest you to put this in a ESQL/C program to make it useful f=
or
you.
HTH
Thanx much,
Rajib Sarkar
Advisory Software Engineer
DB2/UDB Regional Advanced Support
IBM Data Management Group
If we all did the things we are capable of doing, we would literally
astound ourselves. -- T. Edison
=
"June C. Hunt" =
<june.c.hunt@gmai =
l.com> =
To
Sent by: informix-list@iiug.org =
owner-informix-li =
cc
st@iiug.org =
Subj=
ect
Re: "index size" for a particula=
r
10/09/2004 03:37 table by querying the sysstem =
AM tables =
=
=
Please respond to =
june.c.hunt =
=
=
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 th=
e
> 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:
=3D=3D=3D=3D=3D=3D=3D=3D=3D
select sum(c.collength)
from syscolumns c, sysindexes i
where i.tabid =3D c.tabid
and i.tabid =3D <your tabid>
and (c.colno =3D ABS(i.part1) or
c.colno =3D ABS(i.part2) or
c.colno =3D ABS(i.part3) or
c.colno =3D ABS(i.part4) or
c.colno =3D ABS(i.part5) or
c.colno =3D ABS(i.part6) or
c.colno =3D ABS(i.part7) or
c.colno =3D ABS(i.part8) or
c.colno =3D ABS(i.part9) or
c.colno =3D ABS(i.part10) or
c.colno =3D ABS(i.part11) or
c.colno =3D ABS(i.part12) or
c.colno =3D ABS(i.part13) or
c.colno =3D ABS(i.part14) or
c.colno =3D ABS(i.part15) or
c.colno =3D ABS(i.part16));
This will just give you the width of the index, now you would require t=
o
add the overhead to it, and the formula would be:
Step 2
=3D=3D=3D=3D=3D=3D=3D=3D=3D
select count(*) from sysindexes where tabid =3D <tabid>;
index length =3D (<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....
--
June Hunt
P.S. There appears to have been a minor glitch with the list... Better=
now?
=
--1__=88BBE5BEDFC7D4AD8f9e8a93df938690918c88BBE5BEDFC7D4AD
Content-type: text/html; charset=US-ASCII
Content-Disposition: inline
Content-transfer-encoding: quoted-printable
<html><body>
<p>Hi June,<br>
<br>
I think I can help u with this one ..:-)<br>
<br>
Following is the way the index length is calculated in 9.40<br>
<br>
Step 1)<br>
select c.coltype, c.collength<br>
from syscolumns c, sysindexes i<br>
where i.tabid =3D <your tabid><br>
and ( c.colno =3D ABS(i.part1) OR<br><tt> &nbs