Re: Getting table extents from sysmaster
The core of it needs to be sysptnext in order to get the breakdown of
allocated table-extents within a dbspace.
This is what I ended up with:
select substr(DBINFO("DBSPACE",partnum),1,10) DBSpace,
dbsname[1,10] Database,
tabname[1,15] Table,
sum(pe_size) tot_space,
count(*) no_of_exts
from sysptnext, systabnames
where pe_partnum = partnum
and tabname != "TBLSpace"
group by 1,2,3
order by 1,4 desc
Thanks to all who replied.
Andy
"malcolm.iiug" <malcolm.iiug@btopenworld.com> wrote in message news:<bqki25$3ql$1@terabinaries.xmission.com>...
> Murray's answer highlights one of the challenges of using sysmaster. In
> places they refer to dbsname to mean the name of the dbspace and in others
> it is the name of the database.
>
> regards
>
> Malcolm
> ----- Original Message -----
> From: "Murray Wood (IList)" <ifxmaillist@quanta.co.nz>
> To: <informix-list@iiug.org>
> Sent: Tuesday, December 02, 2003 8:44 PM
> Subject: RE: Getting table extents from sysmaster
>
>
> > Here is part of what you want...
> >
> > select tabname[1,15], ti_nextns xt, ti_nptotal, ti_npused,
> > ti_nrows, ti_nextsiz
> > from systabnames, systabinfo
> > where dbsname = "your_database"
> > and partnum = ti_partnum
> > and tabname not matches "_temp*"
> > order by 2 desc, 1
> >
> > Join it to systabextents with te_partnum=partnum to get the chunk number
> of
> > the table. Then link to syschktab to the dbspace number.
> >
> > MW
> >
> > > -----Original Message-----
> > > From: owner-informix-list@iiug.org
> > > [mailto:owner-informix-list@iiug.org]On Behalf Of Andy Kent
> > > Sent: Wednesday, 3 December 2003 5:09 a.m.
> > > To: informix-list@iiug.org
> > > Subject: Getting table extents from sysmaster
> > >
> > >
> > > How can I write queries showing which table has extents in which
> > > dbspace through sysmaster? - a bit like an oncheck -pe except I want
> > > to condense the output with aggregate functions.
> > >
> > > I can see a table called systabextents but I can't fathom out what I
> > > should join it to.
> > >
> > > Thanks
> > >
> > > Andy
> >
> > sending to informix-list
>
> sending to informix-list