Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Location
    Ortigas, Manila Philippines
    Posts
    10

    Unanswered: My chunk is full.

    Hi,

    I'm administering Informix DB 9.3.

    I run an sql to removed data from table1 and insert it into table2.
    My free chunk was 1.13 GB and it consumed all. so, i just add another 2GB to continue the operations.

    Question:
    When you delete data from the tables does it free up the spaces?
    Are the extents adjust itself when i delete some data?
    Do i have to unload and reload again the tables to free up the dbspaces?

    Thanks for your help!

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

    Re: My chunk is full.

    Originally posted by santi4
    Hi,

    I'm administering Informix DB 9.3.

    I run an sql to removed data from table1 and insert it into table2.
    My free chunk was 1.13 GB and it consumed all. so, i just add another 2GB to continue the operations.

    Question:
    When you delete data from the tables does it free up the spaces?
    Are the extents adjust itself when i delete some data?
    Do i have to unload and reload again the tables to free up the dbspaces?

    Thanks for your help!
    Hi,
    Answer:
    No, delete of rows not free space allocate, You must re-create the table.
    For re-create tyhe table run "alter fragment on table xxxx init in new dbspace" (with database whitout logging), This move the table at new dbspace organized.
    Now, run the follow script for test space ocuped for table.
    __________________________________________________ __________
    [ $# -ne 2 ] && echo "Use mode: `basename $0` <databasename> <tablename>" && exit
    [ `uname -a|grep -c 'AIX'` -eq 1 ] && bk=256 || bk=512
    raya="\n------------------------------------------------------------------------
    ------\n"
    raya2="----------------------------------------------------------------------"
    raya1="\n========================================= ==============================
    =======\n"
    raya3="=========================================== ==========================="
    raya4="=========================================== ===========================\n"
    arch=frgtmp$$

    ## Cheque existencia de base y tabla ingresadas por parametro ##
    base=$1
    tab=$2
    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 '$INFORMIXSERVER'." && exit
    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' en base '$base' no existe" && exit
    zz=`{ echo "select tabid,flags xx from systables \
    where tabname = '$tab';"|dbaccess $base 2>/dev/null; }|sed -e 's/xx//g' -e 's/ta
    bid//'`
    nn=`echo $zz|awk '{print $1}'`
    tip=`echo $zz|awk '{print $2}'`
    [ "$tip" = "8" ] && tipo="RAW" || tipo="STD"
    frg=`{ echo "select count(*) xx from sysfragments \
    where tabid = $nn;"|dbaccess $base 2>/dev/null; }|sed '/xx/d'`
    [ $frg -ne 0 ] && frag="Fragmentada" && tabid=$nn || frag="" || tabid=0

    ### MAIN ###
    if [ $frg -gt 0 ] ; then ## Tablas fragmentadas ##
    dbaccess sysmaster 1>$arch 2>/dev/null <<EOF
    select dbinfo("DBSPACE",ti_partnum) dbspace,
    round(ti_npused/$bk,0) datos,0 indices,
    ti_nrows rows, ti_nextns extents
    from systabinfo
    where ti_partnum in (select partn from $base:sysfragments
    where tabid = $tabid
    and fragtype = 'T')
    into temp tabtemp with no log;
    select dbinfo("DBSPACE",ti_partnum) dbspace,0 datos,
    round(ti_npused/$bk,0) indices,
    ti_nrows rows, ti_nextns extents
    from systabinfo
    where ti_partnum in (select partn from $base:sysfragments
    where tabid = $tabid
    and fragtype = 'I')
    into temp idxtemp with no log;
    select *
    from tabtemp
    order by 1;
    select * from idxtemp
    order by 1
    EOF
    echo $raya3
    echo "Base: $base - Table: $tab - $frag -\t- Data in Mbytes - Type: $tipo"
    echo $raya2
    echo "Dbspace\t\tData\tIndexes\tTotal\tRows\t Extents"
    echo $raya2
    cat $arch|sed -e '/dbs/d' -e '/^$/d' | \
    awk -v r=$raya4 ' BEGIN { {D=0} {I=0} {T=0} {R=0} {E=0} }
    {D+=$2} {I+=$3} {R+=$4} {T+=($2+$3)} {if($2>0) {E+=$5}}
    {printf "%-12s\t%0d\t%0d\t%0d\t%0d\t\t%d\n",$1,$2,$3,$2+$3,$4 ,$5}
    END {printf "%sTotal\t\t%d\t%d\t%d\t%d\t%d\n%s",r,D,I,T,R,E,r} '
    rm -f $arch
    else ## Tablas NO fragmentadas ##
    {
    dbaccess sysmaster 2>/dev/null <<EOF
    select trim(s.name) expres,-- trim(n.dbsname) expres,
    round(sum(ti_nptotal)/$bk,0) expres,
    round(sum(ti_npdata)/$bk,0) expres,
    round((sum(ti_npused)/$bk) - (sum(ti_npdata)/$bk),0) expres,
    i.ti_nrows expres,
    i.ti_nextns expres
    from systabinfo i, sysdbspaces s, $base:systables x
    where x.tabname = '$tab'
    and x.partnum = i.ti_partnum
    and partdbsnum(i.ti_partnum) = s.dbsnum
    group by 1,5,6
    EOF
    } |sed -e 's/expres//' -e '/^$/d'| tr -d "\n" | \
    awk -v m=$tipo -v r1=$raya1 -v r=$raya -v t=$tab -v b=$base \
    '{printf "%sBase: %s - Tabla: %s \t- Datos en Mbytes - Tipo: %s %sDbspace\t\tDat
    os\tIndexes\tTotal\tRows\t\tExtents%s%-12s\t%d\t%d\t%d\t%d\t\t%d%s",r1,b,t,m,r,
    r,$1,$3,$4,$2,$5,$6,r1}'
    fi
    ### END MAIN ###

    Gustavo.

  3. #3
    Join Date
    Nov 2003
    Location
    Ortigas, Manila Philippines
    Posts
    10

    Re: My chunk is full.

    Originally posted by gurey
    Hi,
    Answer:
    No, delete of rows not free space allocate, You must re-create the table.
    For re-create tyhe table run "alter fragment on table xxxx init in new dbspace" (with database whitout logging), This move the table at new dbspace organized.
    Now, run the follow script for test space ocuped for table.
    __________________________________________________ __________
    [ $# -ne 2 ] && echo "Use mode: `basename $0` <databasename> <tablename>" && exit
    [ `uname -a|grep -c 'AIX'` -eq 1 ] && bk=256 || bk=512
    raya="\n------------------------------------------------------------------------
    ------\n"
    raya2="----------------------------------------------------------------------"
    raya1="\n========================================= ==============================
    =======\n"
    raya3="=========================================== ==========================="
    raya4="=========================================== ===========================\n"
    arch=frgtmp$$

    ## Cheque existencia de base y tabla ingresadas por parametro ##
    base=$1
    tab=$2
    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 '$INFORMIXSERVER'." && exit
    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' en base '$base' no existe" && exit
    zz=`{ echo "select tabid,flags xx from systables \
    where tabname = '$tab';"|dbaccess $base 2>/dev/null; }|sed -e 's/xx//g' -e 's/ta
    bid//'`
    nn=`echo $zz|awk '{print $1}'`
    tip=`echo $zz|awk '{print $2}'`
    [ "$tip" = "8" ] && tipo="RAW" || tipo="STD"
    frg=`{ echo "select count(*) xx from sysfragments \
    where tabid = $nn;"|dbaccess $base 2>/dev/null; }|sed '/xx/d'`
    [ $frg -ne 0 ] && frag="Fragmentada" && tabid=$nn || frag="" || tabid=0

    ### MAIN ###
    if [ $frg -gt 0 ] ; then ## Tablas fragmentadas ##
    dbaccess sysmaster 1>$arch 2>/dev/null <<EOF
    select dbinfo("DBSPACE",ti_partnum) dbspace,
    round(ti_npused/$bk,0) datos,0 indices,
    ti_nrows rows, ti_nextns extents
    from systabinfo
    where ti_partnum in (select partn from $base:sysfragments
    where tabid = $tabid
    and fragtype = 'T')
    into temp tabtemp with no log;
    select dbinfo("DBSPACE",ti_partnum) dbspace,0 datos,
    round(ti_npused/$bk,0) indices,
    ti_nrows rows, ti_nextns extents
    from systabinfo
    where ti_partnum in (select partn from $base:sysfragments
    where tabid = $tabid
    and fragtype = 'I')
    into temp idxtemp with no log;
    select *
    from tabtemp
    order by 1;
    select * from idxtemp
    order by 1
    EOF
    echo $raya3
    echo "Base: $base - Table: $tab - $frag -\t- Data in Mbytes - Type: $tipo"
    echo $raya2
    echo "Dbspace\t\tData\tIndexes\tTotal\tRows\t Extents"
    echo $raya2
    cat $arch|sed -e '/dbs/d' -e '/^$/d' | \
    awk -v r=$raya4 ' BEGIN { {D=0} {I=0} {T=0} {R=0} {E=0} }
    {D+=$2} {I+=$3} {R+=$4} {T+=($2+$3)} {if($2>0) {E+=$5}}
    {printf "%-12s\t%0d\t%0d\t%0d\t%0d\t\t%d\n",$1,$2,$3,$2+$3,$4 ,$5}
    END {printf "%sTotal\t\t%d\t%d\t%d\t%d\t%d\n%s",r,D,I,T,R,E,r} '
    rm -f $arch
    else ## Tablas NO fragmentadas ##
    {
    dbaccess sysmaster 2>/dev/null <<EOF
    select trim(s.name) expres,-- trim(n.dbsname) expres,
    round(sum(ti_nptotal)/$bk,0) expres,
    round(sum(ti_npdata)/$bk,0) expres,
    round((sum(ti_npused)/$bk) - (sum(ti_npdata)/$bk),0) expres,
    i.ti_nrows expres,
    i.ti_nextns expres
    from systabinfo i, sysdbspaces s, $base:systables x
    where x.tabname = '$tab'
    and x.partnum = i.ti_partnum
    and partdbsnum(i.ti_partnum) = s.dbsnum
    group by 1,5,6
    EOF
    } |sed -e 's/expres//' -e '/^$/d'| tr -d "\n" | \
    awk -v m=$tipo -v r1=$raya1 -v r=$raya -v t=$tab -v b=$base \
    '{printf "%sBase: %s - Tabla: %s \t- Datos en Mbytes - Tipo: %s %sDbspace\t\tDat
    os\tIndexes\tTotal\tRows\t\tExtents%s%-12s\t%d\t%d\t%d\t%d\t\t%d%s",r1,b,t,m,r,
    r,$1,$3,$4,$2,$5,$6,r1}'
    fi
    ### END MAIN ###

    Gustavo.
    Thank you very much! It will help a lot!

    Cheers!
    Santi4

Posting Permissions

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