Since the number of extents is limited it should be checked regularly. The only method (which we know) to get the highest amount of additional extents for a table includes the following steps:

a) get the physical address of a table (oncheck -pt <db>.<table>)
b) get the frcnt value using the physical address (oncheck -pP <physical address>
c) divide the frcnt value by 8

We would like to know if the information which is needed to calculate the additional extent amount is stored (as the output of the oncheck command shows) in the sysmaster tables. If so - where? Maybe somebody knows an easier and/or faster method to get the information we need?

Thanks in advance

pet

I'm using IDS 9.4 on an AIX box so your mileage may vary, this simple SQL is run against the sysmaster DB & it was provided by a PeopleSoft staff DBA.

unload to "count_extents" delimiter "|"

select b.dbsname,
b.tabname,
a.ti_nextns, { total # of extents }
a.ti_nptotal, { total # of 4K pages allocated }
a.ti_npused { total # of 4K pages used }
from systabinfo a,
systabnames b
where a.ti_partnum = b.partnum and

b.dbsname = "your_DB_name" { change database name here }

and a.ti_nrows <> 0 { make sure it's a table, not an index }
and a.ti_nptotal > a.ti_fextsiz {actual size > 1st assigned extent size }
and a.ti_nextns >= 1;

table syspaghdr on sysmaster , but I don't believed this will have good performance

Look for $INFORMIXDIR/etc/sysmaster.sql

Many thanks for the help. We'll check what works best.

pet

Edit:

Thanks to the hint with the syspaghdr and Google I've found the following query which delivers exactly the information we need - the maximal amount of additional extents per table:

select {+ ordered, index(a, syspaghdridx) } -- necessary
c.tabname, -- the table or index
c.dbsname, -- the database
b.name, -- the dbspace
trunc(a.pg_frcnt / 8) frext -- the free extents
from sysmaster:sysdbspaces b,
sysmaster:syspaghdr a,
sysmaster:systabnames c
where a.pg_partnum = sysmaster:partaddr(b.dbsnum, 1)
and sysmaster:bitval(a.pg_flags, 2) = 1
and a.pg_nslots = 5
and c.partnum = sysmaster:partaddr(b.dbsnum, a.pg_pagenum)
order by 4 asc -- show me the problem candidates first

The query is from this dbforum.