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.

Thank you very much! It will help a lot!

Cheers!
Santi4