IDS Forum

Re: how to get the table and indexes size

Posted By: Jack Parker
Date: Friday, 1 April 2011, at 8:13 a.m.

In Response To: Re: how to get the table and indexes size (Jack Parker)

That sux. Let's try plain text.

-- Reports size in MB
-- Note: These functions use npused, which is the number of pages used. =
=20
-- Alternatives include npdata (number of pages of data, i.e. w/o bitmap =
pages) and
-- nptotal - which is number of pages allocated
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;

-- Report size of the dbspace in GB
CREATE FUNCTION DBSIZE(dbspace char(128)) RETURNING INTEGER;

DEFINE SIZE INTEGER;

-- Figure out size of database

SELECT (sum(chksize*a.pagesize)/(1024*1024*1024))=20

INTO SIZE

FROM sysmaster:syschunks a, sysmaster:sysdbspaces b

WHERE a.dbsnum=3Db.dbsnum

AND name=3Ddbspace;

RETURN SIZE;
END FUNCTION;

-- Report the size of a table partition (must be a fragmented table)
CREATE FUNCTION partsize(partname 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
WHERE a.partnum=3Db.partn

AND b.partition=3Dpartname

AND b.indexname is null;

RETURN p_size;

END FUNCTION;

On Apr 1, 2011, at 7:54 AM, Jack Parker wrote:

> This is what I use:=20
>=20
> -- Reports size in MB=20
> -- Note: These functions use npused, which is the number of pages =
used. =3D=20
> =3D20=20
> -- Alternatives include npdata (number of pages of data, i.e. w/o =
bitmap =3D=20
> pages) and=20
> -- nptotal - which is number of pages allocated=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
> -- Report size of the dbspace in GB=20
> CREATE FUNCTION DBSIZE(dbspace char(128)) RETURNING INTEGER;=20
>=20
> DEFINE SIZE INTEGER;=20
>=20
> -- Figure out size of database=20
>=20
> SELECT (sum(chksize*a.pagesize)/(1024*1024*1024))=3D20=20
>=20
> INTO SIZE=20
>=20
> FROM sysmaster:syschunks a, sysmaster:sysdbspaces b=20
>=20
> WHERE a.dbsnum=3D3Db.dbsnum=20
>=20
> AND name=3D3Ddbspace;=20
>=20
> RETURN SIZE;=20
> END FUNCTION;=20
>=20
> -- Report the size of a table partition (must be a fragmented table)=20=

> CREATE FUNCTION partsize(partname 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=20
> WHERE a.partnum=3D3Db.partn=20
>=20
> AND b.partition=3D3Dpartname=20
>=20
> AND b.indexname is null;=20
>=20
> RETURN p_size;=20
>=20
> END FUNCTION;=20
> On Apr 1, 2011, at 3:09 AM, JACK PAPA wrote:=20
>=20
>> Hi All,=3D20=20
>> =3D20=20
>> i can get the full database size by selecting sum(chksize) from =3D=20=

> syschunks...I=3D20=20
>> want to know the size of a certain table.. in which table can i get =3D=
=20
> the size=3D20=20
>> on a per table basis or maybe the size of the indexes used.=3D20=20
>> =3D20=20
>> thanks,=3D20=20
>> =3D20=20
>> =3D20=20
>> =3D=20
> =
**************************************************************************=
=3D=20
> *****=3D20=20
>> Forum Note: Use "Reply" to post a response in the discussion =
forum.=3D20=3D=20
>=20
>> =3D20=20
>=20
>=20
> =
**************************************************************************=
*****=20
> Forum Note: Use "Reply" to post a response in the discussion forum.=20=

>=20

Messages In This Thread

• how to get the table and indexes size
  JACK PAPA -- Friday, 1 April 2011, at 3:09 a.m.
  • Re: how to get the table and indexes size
    Jack Parker -- Friday, 1 April 2011, at 7:54 a.m.
    • Re: how to get the table and indexes size
      Jack Parker -- Friday, 1 April 2011, at 8:13 a.m.
      • Re: how to get the table and indexes size
        Jack Parker -- Friday, 1 April 2011, at 8:23 a.m.
  • Re: how to get the table and indexes size
    Jack Parker -- Friday, 1 April 2011, at 7:56 a.m.
  • Re: how to get the table and indexes size
    Alexandre Marini -- Friday, 1 April 2011, at 7:58 a.m.
  • Re: how to get the table and indexes size
    Art Kagel -- Friday, 1 April 2011, at 8:21 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.