Table extentsize query
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.
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 (art@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.
On Thu, Dec 17, 2009 at 4:12 PM, thomasgsmith <thomasgsmith@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-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>
On Dec 17, 4:38 pm, Art Kagel <art.ka...@gmail.com> wrote:
> 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), KSwww.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 <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
> group by 1
> order by 1;
On Dec 18, 9:13 am, "red_val...@yahoo.com" <red_val...@yahoo.com>
wrote:
> On Dec 17, 4:38 pm, Art Kagel <art.ka...@gmail.com> wrote:
>
>
>
> > 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), KSwww.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 <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
> > group by 1
> > order by 1;
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.
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 (art@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.
On Fri, Dec 18, 2009 at 9:13 AM, red_valsen@yahoo.com
<red_valsen@yahoo.com>wrote:
> On Dec 17, 4:38 pm, Art Kagel <art.ka...@gmail.com> wrote:
> > 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), KSwww.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 <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
> > group by 1
> > order by 1;
>
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>