Table extentsize query

[thomasgsmith]

Does anybody have a query that returns tablename and extentsizes? I
haven't quite gotten one to work vs. sysmaster -- it will return 2
rows per table:

select tabname[1,24], hex(syspt.partnum), fextsiz, nextns
from sysptnhdr syspt, systabnames systab
where
dbsname = "<target_database>" and
tabname in
(.
.
.)
and
hex(syspt.partnum) = hex(systab.partnum)
order by 1;

My functional knowledge of sysmaster is obviously deficient --
predicate correction would be appreciated. Want to avoid parsing slow
oncheck output.

[Art Kagel]

If you are getting multiple rows from this query it is because the table is fragmented into that many partitions.

BTW, using HEX() in the filter is slow BTW so your should removed it and just compare the partnums directly.  The key is to select only fields that will be identical between the fragments, GROUP the query and select the MIN(), MAX(), or AVG() of the extent sizes (since all extents have the same size) it doesn't matter:

select tabname[1,24], MIN( fextsiz ), MAX( nextns )

from sysptnhdr syspt, systabnames systab
where
dbsname = "<target_database>" and
tabname in
(.
.
.)
and
hex(syspt.partnum) = hex(systab.partnum)

group by 1
order by 1;

Art

Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (a...@iiug.org)

See you at the 2010 IIUG Informix Conference
April 25-28, 2010
Overland Park (Kansas City), KS
www.iiug.org/conf

Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly.  Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.

_______________________________________________
Informix-list mailing list
Inform...@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list

[red_v...@yahoo.com]

> On Thu, Dec 17, 2009 at 4:12 PM, thomasgsmith <thomasgsm...@verizon.net>wrote:
>
> > Does anybody have a query that returns tablename and extentsizes?  I
> > haven't quite gotten one to work vs. sysmaster -- it will return 2
> > rows per table:
>
> > select tabname[1,24], hex(syspt.partnum), fextsiz, nextns
> > from sysptnhdr syspt, systabnames systab
> > where
> > dbsname = "<target_database>" and
> > tabname in
> > (.
> > .
> > .)
> > and
> > hex(syspt.partnum) = hex(systab.partnum)
> > order by 1;
>
> > My functional knowledge of sysmaster is obviously deficient --
> > predicate correction would be appreciated.  Want to avoid parsing slow
> > oncheck output.
>
> > _______________________________________________
> > Informix-list mailing list

> > Informix-l...@iiug.org
> >http://www.iiug.org/mailman/listinfo/informix-list

Thanks, Art.

Some of the tables return two rows, some a single row. None have been
purposely fragmented by using "fragment by . . ." at table create.
Still scratching my head over that. I suspect there's an additional
condition that needs to be specified.

Meanwhile, the query that returned a match to dbschema output for each
table is (including first and next extent sizes and number of
extents):

select tabname[1,24], max( fextsiz ), max( nextns ), max(nextsiz)

> from sysptnhdr syspt, systabnames systab
> where
> dbsname = "<target_database>" and
> tabname in
> (.
> .
> .)
> and

> syspt.partnum = systab.partnum

[red_v...@yahoo.com]

On Dec 18, 9:13 am, "red_val...@yahoo.com" <red_val...@yahoo.com>
wrote:

Oops -- here's the query:

select tabname[1,24], max( fextsiz ), max( nextns ), max(nextsiz)
from sysptnhdr syspt, systabnames systab
where
dbsname = "<target_database" and
tabname in
(.
.
.)
and
syspt.partnum = systab.partnum
group by 1
order by 1;

Sizes will be in pages. Dbschema output converts to k.

[Art Kagel]

Ahh, old technology, so I didn't think of it.  Those entries you are seeing are what were known as semi-detached indexes.  The indexes didn't get their own separate partition but were allocated attached partitions as if they were fragments of the table itself.  This was implemented in 7.30 & 7.31 IB as the default to replace attached indexes (index pages interleaved with data pages in the same partition) and deprecated as the default in 9.30 and later engines (though still supported for tables that were upgraded from 7.3x).  If you have semi-attached indexes, then use the MAX() function to see the table's own extent sizing rather than the index's (may be the same, I don't remember).

Art

Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (a...@iiug.org)

See you at the 2010 IIUG Informix Conference
April 25-28, 2010
Overland Park (Kansas City), KS
www.iiug.org/conf

Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly.  Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.

[Habichtsberg, Reinhard]

Sorry, for me the query isn't much meaningful, though. The problem is that
you will see the first extent size only, the next extent size, which is set
dynamically is - in some cases - much more important. Unfortunately you
cannot see how much extents of which size exist. If you build aggregate sums
the result may or may not be accurate.

In short: You can see, how many extents exist but you cannot see how large
they are in particular.

Reinhard.

Art

Art S. Kagel
Oninit ( www.oninit.com <http://www.oninit.com> )
IIUG Board of Directors ( a...@iiug.org <mailto:a...@iiug.org> )

See you at the 2010 IIUG Informix Conference
April 25-28, 2010
Overland Park (Kansas City), KS

www.iiug.org/conf <http://www.iiug.org/conf>

Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. 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, Dec 17, 2009 at 4:12 PM, thomasgsmith < thomas...@verizon.net
<mailto:thomas...@verizon.net> > wrote:

Does anybody have a query that returns tablename and extentsizes? I
haven't quite gotten one to work vs. sysmaster -- it will return 2
rows per table:

select tabname[1,24], hex(syspt.partnum), fextsiz, nextns
from sysptnhdr syspt, systabnames systab
where
dbsname = "<target_database>" and
tabname in
(.
.
.)
and
hex(syspt.partnum) = hex(systab.partnum)
order by 1;

My functional knowledge of sysmaster is obviously deficient --
predicate correction would be appreciated. Want to avoid parsing slow
oncheck output.

_______________________________________________
Informix-list mailing list
Inform...@iiug.org <mailto:Inform...@iiug.org>
http://www.iiug.org/mailman/listinfo/informix-list
<http://www.iiug.org/mailman/listinfo/informix-list>

[Art Kagel]

But that's a different question, with a different answer.  The queries to see the size of each extent and the number and other stats of the extents are:

select dbsname, tabname, size
from systabnames st, sysextents se
where st. partnum = se.partnum
;

select dbsname, tabname, count(*) as num_extents, min(size) as smallest_extent, max(size) as largest_extent
from systabnames st, sysextents se
where st. partnum = se.partnum
;

Art

Art S. Kagel

Oninit (www.oninit.com)
IIUG Board of Directors (a...@iiug.org)

See you at the 2010 IIUG Informix Conference
April 25-28, 2010
Overland Park (Kansas City), KS
www.iiug.org/conf

Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly.  Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.

http://www.iiug.org/mailman/listinfo/informix-list