extent usage check
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