how to get the table and indexes size
Asked how to find the size of an individual table or index (not just total dbspace size from syschunks). Several working answers were given: Jack Parker posted SPL functions (tabsize, idxsize, partsize, dbsize) that compute MB from sysmaster:sysptnhdr npused * pagesize, joining systables/sysfragments to handle both fragmented and non-fragmented tables; Alexandre Marini offered a query summing pe_size from sysmaster:sysptnext joined to systabnames (result in pages); Art Kagel suggested simply summing size from sysextents grouped by dbsname/tabname. Also noted that a database per se has no size — chksize gives the dbspace size.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi All, i can get the full database size by selecting sum(chksize) from syschunks...I want to know the size of a certain table.. in which table can i get the size on a per table basis or maybe the size of the indexes used. thanks,
This is what I use:
-- 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 3:09 AM, JACK PAPA wrote:
> Hi All,=20
>=20
> i can get the full database size by selecting sum(chksize) from =
syschunks...I=20
> want to know the size of a certain table.. in which table can i get =
the size=20
> on a per table basis or maybe the size of the indexes used.=20
>=20
> thanks,=20
>=20
>=20
> =
**************************************************************************=
*****=20
> Forum Note: Use "Reply" to post a response in the discussion forum.=20=
>=20
Also note, you are getting the dbspace size, not the database size. A = database does not really have a size, it's tables do. cheers j. =20 On Apr 1, 2011, at 3:09 AM, JACK PAPA wrote: > Hi All,=20 >=20 > i can get the full database size by selecting sum(chksize) from = syschunks...I=20 > want to know the size of a certain table.. in which table can i get = the size=20 > on a per table basis or maybe the size of the indexes used.=20 >=20 > thanks,=20 >=20 >=20 > = **************************************************************************= *****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20= >=20
Hello:
I have an old query, that I use since version 10. It was obtained from
the IIUG site (Mr Art Kagel package, maybe, I don´t remember).
If someone has another, or better one, I am sorry ok?
-----------------------------------------------
select dbsname,
tabname,
count(*) num_of_extents,
sum( pe_size ) total_size
from sysmaster:systabnames, sysmaster:sysptnext
where partnum = pe_partnum
and dbsname = [your_db_name]
and tabname = [your_table_name]
--------------------------
As you see, the results of sizing are in pages, so you must convert it
to KB, using your architecture specific page size.
Best regards.
Em 01/04/2011 03:09, JACK PAPA escreveu:
> Hi All,
>
> i can get the full database size by selecting sum(chksize) from syschunks...I
> want to know the size of a certain table.. in which table can i get the size
> on a per table basis or maybe the size of the indexes used.
>
> thanks,
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Alexandre Marini
Tecnologia da Informação - DBA
msn: alexandre_marini@hotmail.com
SEFAZ-MS / SGI-UGSR / Sistemas IBM-Informix
Cert-Info-Mgmt_color <Cert-Info-Mgmt_color.jpg>
IBM Certified System Administrator - Informix Dynamic Server V10 / V11
<http://www.iiug.org/conf/2011/iiug/>;
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
select dbsname, tabname, sum(size) from sysextents where ....
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 Fri, Apr 1, 2011 at 3:09 AM, JACK PAPA <informix2009@gmail.com> wrote:
> Hi All,
>
> i can get the full database size by selecting sum(chksize) from
> syschunks...I
> want to know the size of a certain table.. in which table can i get the
> size
> on a per table basis or maybe the size of the indexes used.
>
> thanks,
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--20cf307f32928c2141049fda73da
Sorry, still figuring out this mail reader.
Plain text ON!
Hmmm, how about Open Sesame?
cheers
j.
-- 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;