RE: Please Help w/ SMI Query
I wish to point out two minor errors in Art's SQL statement (as marked
by "^^") and the correct sql should be:
> select tabname, trunc((te_physaddr / 1048576)) chunk, count(*)
> from systabnames tn, systabextents te
> where tn.partnum = te.te_partnum
> group by 1,2
> order by 1,2;>
By using the following (slightly changed) SQL, you should be able to
display dbspace name as well, and for tables of a chosen database only:
select A.tabname table, C.chknum chunk, B.name dbspace, count(*)
num_of_exts
from systables A, sysmaster:sysdbspaces B, sysmaster:syschunks C,
sysmaster:systabnames tn, sysmaster:systabextents te
where A.tabtype='T'
and C.chknum = trunc((te_physaddr / 1048576))
and C.dbsnum=B.dbsnum
and tn.partnum = te.te_partnum
and A.tabname=tn.tabname
group by 1,2,3
order by 1,2
Cheers, Peter
> ----------
> From: Art S. Kagel
> Reply To: kagel@bloomberg.net
> Sent: Wednesday, 23 December 1998 8:50 AM
> To: informix-list@iiug.org
> Subject: Re: Please Help w/ SMI Query
>
> Cory wrote:
> >
> > On Informix 7.24.UC3 on Solaris (2.5 or 2.6), using the SMI tables,
> how do I
> > find out in how many different chunks a table resides? I've looked
> at the
> > schema for the sysmaster database, but all I can find is information
> about
> > extents, nothing to link them to the chunks in which they reside.
>
> Actually there is. In systabextents is the te_physaddr column which
> is
> the physical page address of extent's first page.
> te_physaddr/10487576
> equals the chunk number for the extent. So make a stored procedure
> that joins systabnames to systabextents by partnum and selects the
> chunk numbers then do something like:
>
> select tabname, trunc((te_physaddr / 10487576)) chunk, count(*)
^^
> from systabnames tn, systabextents te
> where tn.partnum = te.te_partnum
> order by 1,2
> group by 1,2; ^^
> Which will give you the count of extents in each chunk by tablename.
>
> Art S. Kagel
>