Results 1 to 4 of 4

Thread: Extent limits

  1. #1
    Join Date
    Mar 2009
    Posts
    4

    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

    pet

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

  3. #3
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    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
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

  4. #4
    Join Date
    Mar 2009
    Posts
    4
    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.
    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
  •