query to find partition size in fragmented table
User asked how to find the size of fragmented partitioned tables in Informix, as existing queries only work for non-fragmented tables. Jack Parker provided SQL functions that query sysmaster:sysptnhdr and sysfragments to calculate table, index, and partition sizes in MB, handling both fragmented and non-fragmented cases.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
How can we find the size of a partitioned table in term of extents or pages allocated. For non-fragmented tables we can get the results as provided in following link: http://www.informix.com.ua/articles/sysmast/sysmast.htm But these queries do not work for fragmented tables.
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;
On Dec 22, 2014, at 10:28 AM, KAMRAN HAQ <khaq@i2cinc.com> wrote:
> How can we find the size of a partitioned table in term of extents or =
pages=20
> allocated.=20
> For non-fragmented tables we can get the results as provided in =
following=20
> link:=20
> http://www.informix.com.ua/articles/sysmast/sysmast.htm=20
>=20
> But these queries do not work for fragmented tables.=20
>=20
>=20
> =
**************************************************************************=
*****=20
> Forum Note: Use "Reply" to post a response in the discussion forum.=20=
>=20
Here re a couple of other useful ones, because you forgot to ask for =
them.
-- 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 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 Dec 22, 2014, at 10:28 AM, KAMRAN HAQ <khaq@i2cinc.com> wrote:
> How can we find the size of a partitioned table in term of extents or =
pages=20
> allocated.=20
> For non-fragmented tables we can get the results as provided in =
following=20
> link:=20
> http://www.informix.com.ua/articles/sysmast/sysmast.htm=20
>=20
> But these queries do not work for fragmented tables.=20
>=20
>=20
> =
**************************************************************************=
*****=20
> Forum Note: Use "Reply" to post a response in the discussion forum.=20=
>=20
Mind you my mailer decided that an =93=3D=93 sign needed to be followed =
by =933D=94.
j.
On Dec 22, 2014, at 10:47 AM, Jack Parker <jack.parker4@verizon.net> =
wrote:
> 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
> On Dec 22, 2014, at 10:28 AM, KAMRAN HAQ <khaq@i2cinc.com> wrote:=20
>=20
>> How can we find the size of a partitioned table in term of extents or =
=3D=20
> pages=3D20=20
>> allocated.=3D20=20
>> For non-fragmented tables we can get the results as provided in =3D=20=
> following=3D20=20
>> link:=3D20=20
>> http://www.informix.com.ua/articles/sysmast/sysmast.htm=3D20=20
>> =3D20=20
>> But these queries do not work for fragmented tables.=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
Bleh !
On 22 December 2014 at 15:51, Jack Parker <jack.parker4@verizon.net> wrote:
> Mind you my mailer decided that an =93=3D=93 sign needed to be followed =
> by =933D=94.
>
> j.
> On Dec 22, 2014, at 10:47 AM, Jack Parker <jack.parker4@verizon.net> =
> wrote:
>
> > 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
> > On Dec 22, 2014, at 10:28 AM, KAMRAN HAQ <khaq@i2cinc.com> wrote:=20
> >=20
> >> How can we find the size of a partitioned table in term of extents or =
> =3D=20
> > pages=3D20=20
> >> allocated.=3D20=20
> >> For non-fragmented tables we can get the results as provided in =3D=20=
>
> > following=3D20=20
> >> link:=3D20=20
> >> http://www.informix.com.ua/articles/sysmast/sysmast.htm=3D20=20
> >> =3D20=20
> >> But these queries do not work for fragmented tables.=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
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--089e013cbd40a8075a050ad01cf2
Bleh indeed. Attached. j. On Dec 22, 2014, at 10:57 AM, Keith Simmons <smiley73@gmail.com> wrote: > Bleh !=20 >=20 > On 22 December 2014 at 15:51, Jack Parker <jack.parker4@verizon.net> = wrote:=20 >=20