extent usage check
Stephen F. Cawley wanted a script to show how full a table's extents were. John Carlson (WHSmith) gave sysmaster queries on sysptprof and sysptnhdr (7.2 or later) for nextns, npused, nptotal and npdata, and one joining sysextents for individual extents. Art S. Kagel recommended oncheck -pT database:table or his printfreeB.ec utility.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
I am trying to write a script to check not only total extents allocated to a table, but how full they are. A table might be allocated 20K, but only have 3K of data. Is there a way to do this? SC
If you're using version >= 7.2 then try this:
database sysmaster;
select tabname, nextns, npused, nptotal, npdata
from sysmaster:sysptprof prof, sysmaster:sysptnhdr hdr
where dbsname = '<database name>'
and prof.partnum = hdr.partnum
tabname - table name within dbsname
nextns - number of extents for tabname
npused - total number of pages actually used for this table
nptotal - total number of pages allocated for this table
npdata - total number of data pages for this table (no indices incl.)
John Carlson
Informix DBA
WHSmith USA
Stephen F. Cawley wrote:
>
> I am trying to write a script to check not only total extents allocated to a
>
> table, but how full they are. A table might be allocated 20K, but only have
>
> 3K of data. Is there a way to do this?
>
> SC
Use:
oncheck -pT <database>:<table>
or get my printfreeB.ec utility. But the oncheck -pT works fine.
Art S. Kagel
"Stephen F. Cawley" wrote:
>
> I am trying to write a script to check not only total extents allocated to a
>
> table, but how full they are. A table might be allocated 20K, but only have
>
> 3K of data. Is there a way to do this?
>
> SC
Oops, forgot to show the individual extents for a table . . .
select prof.dbsname, prof.tabname, start, size
from sysptprof prof, sysextents ext
where prof.dbsname = ext.dbsname
and prof.tabname = ext.tabname
and prof.dbsname = '<database name>'
and prof.tabname = '<table name>'
Of course, oncheck -pT or oncheck -pe could probably do the trick also
John Carlson
Informix DBA
WHSmith USA
Carlson@WHSmith wrote:
>
> If you're using version >= 7.2 then try this:
>
> database sysmaster;>
> select tabname, nextns, npused, nptotal, npdata
> from sysmaster:sysptprof prof, sysmaster:sysptnhdr hdr
> where dbsname = '<database name>'
> and prof.partnum = hdr.partnum>
> tabname - table name within dbsname
> nextns - number of extents for tabname
> npused - total number of pages actually used for this table
> nptotal - total number of pages allocated for this table
> npdata - total number of data pages for this table (no indices incl.)
>
> John Carlson
> Informix DBA
> WHSmith USA
>
> Stephen F. Cawley wrote:
> >
> > I am trying to write a script to check not only total extents allocated to a
> >
> > table, but how full they are. A table might be allocated 20K, but only have
> >
> > 3K of data. Is there a way to do this?
> >
> > SC