Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Location
    US
    Posts
    4

    Unanswered: running out of extents @ids 731 engine

    We are having a query with order by clause in i4gl. While running it on a database with 50k rows, no more extents error is coming.

    Do I need to change my onconfig or something to set proper extent size.
    ne pointers..

    TIA,
    Sippy

  2. #2
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    780

    Re: running out of extents @ids 731 engine

    Originally posted by Sippy
    We are having a query with order by clause in i4gl. While running it on a database with 50k rows, no more extents error is coming.

    Do I need to change my onconfig or something to set proper extent size.
    ne pointers..

    TIA,
    Sippy
    Hi,

    You problem perhaps so or the table reach the 32 GB or no more free space on the dbspace.

  3. #3
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    780

    Re: running out of extents @ids 731 engine

    Originally posted by gurey
    Hi,

    You problem perhaps so or the table reach the 32 GB or no more free space on the dbspace.
    Sorry !!
    surely I reach the maxima quantity of extents by table.
    The following scripts
    the following one script permits it to see until quantity of extent can grow each table.


    #!/bin/ksh
    [ $# -eq 0 -o $# -gt 2 ] && echo "Modo de uso: $0 <base> [<tabla>]" && exit

    base=$1
    tab=$2
    dbaccess $base 1>/dev/null 2>&1 <<!
    close database;
    !
    [ $? -ne 0 ] && echo "La base '$base' no existe" && exit
    dbaccess $base 1>/dev/null 2>&1 <<!
    select partnum from systables where tabname = '$tab'
    !
    [ $? -ne 0 ] && echo "La Tabla '$tab' no existe" && exit

    [ $tab ] && {
    sql="select nextns from sysptnhdr where partnum in (select partnum from $base:sy
    stables where tabname = '$tab')"
    ne=`echo $sql | dbaccess sysmaster 2>/dev/null| sed -e '/^$/d' -e '/nextns/d' `
    [ `echo $ne|grep -c "Error"` -gt 0 ] && {
    echo "Hubo errores al acceder a la base."
    exit
    }
    nn=`echo $ne|sed 's/ //g'`
    [ "X$nn" = "X" ] && echo "No pude acceder a la tabla $tab" && exit
    echo "La tabla $tab de la base $base posee: $nn extent(s)"
    exit
    }

    {
    dbaccess $base 2>/dev/null << EOF
    set isolation to dirty read;
    select partnum,tabname[1,18],fextsize
    from systables
    -- where tabid > 99
    where tabtype = 'T'
    into temp tmpbase with no log;
    --select t.tabname[1,18] Tablas, s.rowsize rowsize, s.nrows filas,
    select t.tabname[1,18] Tablas, s.nrows filas,
    s.nextns Extents, t.fextsize First_Ext, (s.nptotal*4) Extent_Ini
    from sysmaster:sysptnhdr s, tmpbase t
    where s.partnum = t.partnum
    order by 3 desc
    EOF
    } | more

  4. #4
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    780

    Re: running out of extents @ids 731 engine

    Originally posted by gurey
    Sorry !!
    surely I reach the maxima quantity of extents by table.
    The following scripts
    the following one script permits it to see until quantity of extent can grow each table.


    #!/bin/ksh
    [ $# -eq 0 -o $# -gt 2 ] && echo "Modo de uso: $0 <base> [<tabla>]" && exit

    base=$1
    tab=$2
    dbaccess $base 1>/dev/null 2>&1 <<!
    close database;
    !
    [ $? -ne 0 ] && echo "La base '$base' no existe" && exit
    dbaccess $base 1>/dev/null 2>&1 <<!
    select partnum from systables where tabname = '$tab'
    !
    [ $? -ne 0 ] && echo "La Tabla '$tab' no existe" && exit

    [ $tab ] && {
    sql="select nextns from sysptnhdr where partnum in (select partnum from $base:sy
    stables where tabname = '$tab')"
    ne=`echo $sql | dbaccess sysmaster 2>/dev/null| sed -e '/^$/d' -e '/nextns/d' `
    [ `echo $ne|grep -c "Error"` -gt 0 ] && {
    echo "Hubo errores al acceder a la base."
    exit
    }
    nn=`echo $ne|sed 's/ //g'`
    [ "X$nn" = "X" ] && echo "No pude acceder a la tabla $tab" && exit
    echo "La tabla $tab de la base $base posee: $nn extent(s)"
    exit
    }

    {
    dbaccess $base 2>/dev/null << EOF
    set isolation to dirty read;
    select partnum,tabname[1,18],fextsize
    from systables
    -- where tabid > 99
    where tabtype = 'T'
    into temp tmpbase with no log;
    --select t.tabname[1,18] Tablas, s.rowsize rowsize, s.nrows filas,
    select t.tabname[1,18] Tablas, s.nrows filas,
    s.nextns Extents, t.fextsize First_Ext, (s.nptotal*4) Extent_Ini
    from sysmaster:sysptnhdr s, tmpbase t
    where s.partnum = t.partnum
    order by 3 desc
    EOF
    } | more
    Forgive me me confundi of script. This is the correct.

    #!/bin/ksh
    ## Devuelve la cantidad de extent y el maximo al que puede llegar una tabla.
    seebase()
    {
    nn=`{ echo "select count(*) xx from sysdatabases \
    where name = '$base';"|dbaccess sysmaster 2>/dev/null; }|sed 's/xx//g'`
    [ $nn -eq 0 ] && echo "La Base '$base' no existe en on_line: '$INFORMIXSERVER'."
    && exit
    }

    seetable()
    {
    nn=`{ echo "select count(*) xx from systables \
    where tabname = '$tab';"|dbaccess $base 2>/dev/null; }|sed 's/xx//g'`
    [ $nn -eq 0 ] && echo "La tabla '$tab' no existe en base: '$base'." && exit
    }

    msg()
    {
    echo "Modo de uso: $0 <basename> [<tabname>]"
    exit
    }

    case $# in
    0) msg ;;
    1) base=$1 ; seebase $base
    echo "Se tomaran todas las tablas de la base $base, continua (s/n)?: \c"
    read g
    case $g in
    s|S) break ;;
    *) exit ;;
    esac ;;
    2) base=$1; seebase ; tab=$2 ; seetable ; break ;;
    *) msg ; exit ;;
    esac
    if [ $# -eq 2 ] ; then
    dbaccess $base 2>/dev/null <<!
    select x.partnum,dbinfo("DBSPACE",x.partnum) dbs,tabname,n.nextns
    from systables x, sysmaster:sysptnhdr n
    where x.tabid > 99
    and x.tabtype = 'T'
    and n.partnum = x.partnum
    union
    select z.partn,dbinfo("DBSPACE",z.partn) dbs,tabname,n.nextns
    from systables x, sysmaster:sysptnhdr n, sysfragments z
    where z.tabid = x.tabid
    and z.partn = n.partnum
    and fragtype = 'T'
    order by 1
    into temp temptab with no log;
    select z.dbs,z.tabname, z.nextns can_ext ,z.nextns+
    (select trunc (pg_frcnt/8) max_ext
    from sysmaster:syspaghdr
    where pg_partnum = 0
    and pg_pagenum = ( select physaddr from sysmaster:sysptntab
    where partnum=z.partnum)) max_ext
    from temptab z
    where z.tabname = '$tab'
    order by dbs
    !
    else
    {
    dbaccess $base 2>/dev/null <<!
    select x.partnum,dbinfo("DBSPACE",x.partnum) dbs,tabname,n.nextns
    from systables x, sysmaster:sysptnhdr n
    where x.tabid > 99
    and x.tabtype = 'T'
    and n.partnum = x.partnum
    union
    select z.partn,dbinfo("DBSPACE",z.partn) dbs,tabname,n.nextns
    from systables x, sysmaster:sysptnhdr n, sysfragments z
    where z.tabid = x.tabid
    and z.partn = n.partnum
    and fragtype = 'T'
    order by 1
    into temp temptab with no log;
    select z.dbs,z.tabname, z.nextns can_ext ,z.nextns+
    (select trunc (pg_frcnt/8) max_ext
    from sysmaster:syspaghdr
    where pg_partnum = 0
    and pg_pagenum = ( select physaddr from sysmaster:sysptntab
    where partnum=z.partnum)) max_ext
    from temptab z
    order by 3 desc,1,2
    !
    } | more
    fi

  5. #5
    Join Date
    Mar 2004
    Location
    US
    Posts
    4

    Thumbs up

    Thanx fo rthe info

Posting Permissions

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