Results 1 to 4 of 4

Thread: Extent limits

  1. #1
    Join Date
    Mar 2009

    Question Unanswered: Extent limits

    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


  2. #2
    Join Date
    Dec 2003
    North America
    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,
    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;

  3. #3
    Join Date
    Apr 2007
    Jundiai / SP - Brasil
    table syspaghdr on sysmaster , but I don't believed this will have good performance

    Look for $INFORMIXDIR/etc/sysmaster.sql
    César Inacio Martins
    Jundiai / SP - Brasil - em Português - English (translated by Google).

  4. #4
    Join Date
    Mar 2009
    Many thanks for the help. We'll check what works best.



    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, -- 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.
    Last edited by peturba; 08-28-09 at 07:35. Reason: Final solution found

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts