table size
Aix 6.1 IDS 11.70.fc3 Before I develop a query ... wondering if anyone already has this ... Looking for a query that will calculate the amount of space which a table is actually using ... not the allocated space, but what is being used. Needs to take into consideration different page sizes ... fragmentation .. etc. Also like to see it with indexes excluded and included .. If anyone has such a query .. please let me know .. Thanks ... Peter Logan Senior Database Administrator Phone: 616/878-8309
-- Reports size in MB
CREATE FUNCTION tabsize(tabname char(128)) RETURNING integer;
DEFINE p_size INTEGER;
-- Fragmented tables have partnum in sysfragments
SELECT SUM(a.npused * a.pagesize)/(1024*1024)
INTO p_size
FROM sysmaster:sysptnhdr a, sysfragments b, systables c
WHERE c.tabname=3Dtabname
AND b.tabid=3Dc.tabid
AND b.partn=3Da.partnum
AND c.partnum=3D0;
-- Non-fragmented tables have partnum in systables
IF p_size IS NULL THEN
SELECT SUM(a.npused * a.pagesize)/(1024*1024)
INTO p_size
FROM sysmaster:sysptnhdr a, systables c
WHERE c.tabname=3Dtabname
AND c.partnum=3Da.partnum;
END IF;
RETURN p_size;
END FUNCTION;
-- Report size of an index
CREATE FUNCTION idxsize(idxname char(128)) RETURNING integer;
DEFINE p_size INTEGER;
SELECT SUM(a.npused * a.pagesize)/(1024*1024)
INTO p_size
FROM sysmaster:sysptnhdr a, sysfragments b
WHERE indexname=3Didxname
AND b.partn=3Da.partnum;
RETURN p_size;
END FUNCTION;
On Oct 11, 2012, at 9:32 AM, Peter_Logan@spartanstores.com wrote:
> Aix 6.1=20
> IDS 11.70.fc3=20
>=20
> Before I develop a query ... wondering if anyone already has this ...=20=
>=20
> Looking for a query that will calculate the amount of space which a =
table=20
> is actually using ... not the allocated space, but what is being used.=20=
> Needs to take into consideration different page sizes ... =
fragmentation ..=20
> etc. Also like to see it with indexes excluded and included ..=20
>=20
> If anyone has such a query .. please let me know .. Thanks ...=20
>=20
> Peter Logan=20
> Senior Database Administrator=20
> Phone: 616/878-8309=20
>=20
>=20
> =
**************************************************************************=
*****=20
> Forum Note: Use "Reply" to post a response in the discussion forum.=20=
>=20
Not understanding some of the syntax .. what is up with the '3' ?
Peter Logan
Senior Database Administrator
Phone: 616/878-8309
From: "Jack Parker" <jack.parker4@verizon.net>
To: ids@iiug.org,
Date: 10/11/2012 09:45 AM
Subject: Re: table size [28480]
Sent by: ids-bounces@iiug.org
-- Reports size in MB
CREATE FUNCTION tabsize(tabname char(128)) RETURNING integer;
DEFINE p_size INTEGER;
-- Fragmented tables have partnum in sysfragments
SELECT SUM(a.npused * a.pagesize)/(1024*1024)
INTO p_size
FROM sysmaster:sysptnhdr a, sysfragments b, systables c
WHERE c.tabname=3Dtabname
AND b.tabid=3Dc.tabid
AND b.partn=3Da.partnum
AND c.partnum=3D0;
-- Non-fragmented tables have partnum in systables
IF p_size IS NULL THEN
SELECT SUM(a.npused * a.pagesize)/(1024*1024)
INTO p_size
FROM sysmaster:sysptnhdr a, systables c
WHERE c.tabname=3Dtabname
AND c.partnum=3Da.partnum;
END IF;
RETURN p_size;
END FUNCTION;
-- Report size of an index
CREATE FUNCTION idxsize(idxname char(128)) RETURNING integer;
DEFINE p_size INTEGER;
SELECT SUM(a.npused * a.pagesize)/(1024*1024)
INTO p_size
FROM sysmaster:sysptnhdr a, sysfragments b
WHERE indexname=3Didxname
AND b.partn=3Da.partnum;
RETURN p_size;
END FUNCTION;
On Oct 11, 2012, at 9:32 AM, Peter_Logan@spartanstores.com wrote:
> Aix 6.1=20
> IDS 11.70.fc3=20
>=20
> Before I develop a query ... wondering if anyone already has this
...=20=
>=20
> Looking for a query that will calculate the amount of space which a =
table=20
> is actually using ... not the allocated space, but what is being
used.=20=
> Needs to take into consideration different page sizes ... =
fragmentation ..=20
> etc. Also like to see it with indexes excluded and included ..=20
>=20
> If anyone has such a query .. please let me know .. Thanks ...=20
>=20
> Peter Logan=20
> Senior Database Administrator=20
> Phone: 616/878-8309=20
>=20
>=20
> =
**************************************************************************=
*****=20
> Forum Note: Use "Reply" to post a response in the discussion forum.=20=
>=20
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Sorry, my mailer puts that in as an added bonus. It seems to feel that
an "=3D" sign is too one dimensional and wants to turn it into 3D.
So remove the 3D wherever you see it after an equal sign.
Wonder if I can escape it:
"=3D"
\\\\=3D
"\\\\=3D"
=3D
j.
On Oct 11, 2012, at 10:06 AM, Peter_Logan@spartanstores.com wrote:
> Not understanding some of the syntax .. what is up with the '3' ?=20
>=20
> Peter Logan=20
> Senior Database Administrator=20
> Phone: 616/878-8309=20
>=20
> From: "Jack Parker" <jack.parker4@verizon.net>=20
> To: ids@iiug.org,=20
> Date: 10/11/2012 09:45 AM=20
> Subject: Re: table size [28480]=20
> Sent by: ids-bounces@iiug.org=20
>=20
> -- Reports size in MB=20
> CREATE FUNCTION tabsize(tabname char(128)) RETURNING integer;=20>=20
> DEFINE p_size INTEGER;=20
>=20
> -- Fragmented tables have partnum in sysfragments=20
> SELECT SUM(a.npused * a.pagesize)/(1024*1024)=20
> INTO p_size=20
> FROM sysmaster:sysptnhdr a, sysfragments b, systables c=20
> WHERE c.tabname=3D3Dtabname=20
>=20
> AND b.tabid=3D3Dc.tabid=20
>=20
> AND b.partn=3D3Da.partnum=20
>=20
> AND c.partnum=3D3D0;=20
>=20
> -- Non-fragmented tables have partnum in systables=20
> IF p_size IS NULL THEN=20
> SELECT SUM(a.npused * a.pagesize)/(1024*1024)=20
> INTO p_size=20
> FROM sysmaster:sysptnhdr a, systables c=20
> WHERE c.tabname=3D3Dtabname=20
>=20
> AND c.partnum=3D3Da.partnum;=20
> END IF;=20
>=20
> RETURN p_size;=20
>=20
> END FUNCTION;=20
>=20
> -- Report size of an index=20
> CREATE FUNCTION idxsize(idxname char(128)) RETURNING integer;=20>=20
> DEFINE p_size INTEGER;=20
>=20
> SELECT SUM(a.npused * a.pagesize)/(1024*1024)=20
> INTO p_size=20
> FROM sysmaster:sysptnhdr a, sysfragments b=20
> WHERE indexname=3D3Didxname=20
>=20
> AND b.partn=3D3Da.partnum;=20
>=20
> RETURN p_size;=20
>=20
> END FUNCTION;=20
>=20
> On Oct 11, 2012, at 9:32 AM, Peter_Logan@spartanstores.com wrote:=20
>=20
>> Aix 6.1=3D20=20
>> IDS 11.70.fc3=3D20=20
>> =3D20=20
>> Before I develop a query ... wondering if anyone already has this=20
> ....=3D20=3D=20
>=20
>> =3D20=20
>> Looking for a query that will calculate the amount of space which a =3D=
=20
> table=3D20=20
>> is actually using ... not the allocated space, but what is being=20
> used.=3D20=3D=20
>=20
>> Needs to take into consideration different page sizes ... =3D=20
> fragmentation ..=3D20=20
>> etc. Also like to see it with indexes excluded and included ..=3D20=20=
>> =3D20=20
>> If anyone has such a query .. please let me know .. Thanks ...=3D20=20=
>> =3D20=20
>> Peter Logan=3D20=20
>> Senior Database Administrator=3D20=20
>> Phone: 616/878-8309=3D20=20
>> =3D20=20
>> =3D20=20
>> =3D=20
> =
**************************************************************************=
=3D=20
>=20
> *****=3D20=20
>> Forum Note: Use "Reply" to post a response in the discussion =
forum.=3D20=3D=20
>=20
>> =3D20=20
>=20
>=20
> =
**************************************************************************=
*****=20
>=20
> Forum Note: Use "Reply" to post a response in the discussion forum.=20
>=20
>=20
> =
**************************************************************************=
*****=20
> Forum Note: Use "Reply" to post a response in the discussion forum.=20=
>=20
Peter, all of the "3D" and "=20" strings are artifacts of Jacks emailer.
Just clean them out.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Thu, Oct 11, 2012 at 10:06 AM, Peter_Logan@spartanstores.com <
Peter_Logan@spartanstores.com> wrote:
> Not understanding some of the syntax .. what is up with the '3' ?
>
> Peter Logan
> Senior Database Administrator
> Phone: 616/878-8309
>
> From: "Jack Parker" <jack.parker4@verizon.net>
> To: ids@iiug.org,
> Date: 10/11/2012 09:45 AM
> Subject: Re: table size [28480]
> Sent by: ids-bounces@iiug.org
>
> -- Reports size in MB
> CREATE FUNCTION tabsize(tabname char(128)) RETURNING integer;>
> DEFINE p_size INTEGER;
>
> -- Fragmented tables have partnum in sysfragments
> SELECT SUM(a.npused * a.pagesize)/(1024*1024)
> INTO p_size
> FROM sysmaster:sysptnhdr a, sysfragments b, systables c
> WHERE c.tabname=3Dtabname
>
> AND b.tabid=3Dc.tabid
>
> AND b.partn=3Da.partnum
>
> AND c.partnum=3D0;
>
> -- Non-fragmented tables have partnum in systables
> IF p_size IS NULL THEN
> SELECT SUM(a.npused * a.pagesize)/(1024*1024)
> INTO p_size
> FROM sysmaster:sysptnhdr a, systables c
> WHERE c.tabname=3Dtabname
>
> AND c.partnum=3Da.partnum;
> END IF;
>
> RETURN p_size;
>
> END FUNCTION;
>
> -- Report size of an index
> CREATE FUNCTION idxsize(idxname char(128)) RETURNING integer;>
> DEFINE p_size INTEGER;
>
> SELECT SUM(a.npused * a.pagesize)/(1024*1024)
> INTO p_size
> FROM sysmaster:sysptnhdr a, sysfragments b
> WHERE indexname=3Didxname
>
> AND b.partn=3Da.partnum;
>
> RETURN p_size;
>
> END FUNCTION;
>
> On Oct 11, 2012, at 9:32 AM, Peter_Logan@spartanstores.com wrote:
>
> > Aix 6.1=20
> > IDS 11.70.fc3=20
> >=20
> > Before I develop a query ... wondering if anyone already has this
> ....=20=
>
> >=20
> > Looking for a query that will calculate the amount of space which a =
> table=20
> > is actually using ... not the allocated space, but what is being
> used.=20=
>
> > Needs to take into consideration different page sizes ... =
> fragmentation ..=20
> > etc. Also like to see it with indexes excluded and included ..=20
> >=20
> > If anyone has such a query .. please let me know .. Thanks ...=20
> >=20
> > Peter Logan=20
> > Senior Database Administrator=20
> > Phone: 616/878-8309=20
> >=20
> >=20
> > =
> **************************************************************************=
>
> *****=20
> > Forum Note: Use "Reply" to post a response in the discussion forum.=20=
>
> >=20
>
>
>
>
*******************************************************************************
>
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--e89a8f838b35c5b42804cbc96374