How do I recover the disk space when I have deleted the data in a table?
I have too many tables to drop individually - so I tried to drop the chunks. I couldn't drop the chunk because it was the first chunk in the dbspace, so I dropped the dbspace. This did not work, so I dropped the database. This also did not work because when I recreated the database, a GB of space was taken up with just the table definitions.

What am I missing??

Thanks...

I suggest, "don't worry about it." A database will recycle the disk-space and that's what you want it to do.

In many operating systems, shrinking and extending the physical size of a file is an expensive operation. It is much less expensive to recycle it. If disk-space is at a premium, a far more satisfactory solution is to .. buy more disks.

It doesn't appear to be 'recycling' the space.

I reloaded the table, and I have 2x more disk space allocated (without the indexes) then I should because it did not release it or recycle it!

I am on Linux RH 2.4 kernel, IDS 9.40.UC2 engine with raw disk for storage. Isn't there anyway to release it?

Hi Jenifer,

Please run this sripts:
-----------------------------------------------------------------------------------
This look for spaces:

[ `uname -a|grep -c 'AIX'` -eq 1 ] && bk=256 || bk=512
raya="--------------------------------------------------------------------------
--"
echo "Space used in On-Line `echo $INFORMIXSERVER` al `date +%d/%m/%Y-%H:
%M`"
echo $raya
{
dbaccess sysmaster <<!
select dbsnum,
(
select name[1,18]
from sysdbspaces
where syschunks.dbsnum=sysdbspaces.dbsnum
) dbspace,
trunc((sum(chksize))/$bk,0) total,
trunc(((sum(chksize)-sum(nfree)))/$bk,0) ocupado,
trunc(sum(nfree)/$bk,0) libre
from syschunks
group by 1,2
order by 1
!
} 2>/dev/null | sed '/^$/d'|pg -p "(%d) <Enter>=Continue - <q>uit:"
echo $raya
-----------------------------------------------------------------------------------

And this for look amount extents for table:

usage()
{
echo "Modo de uso: $0 <base> [<tabla>]" ; exit
}

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

traeinfo2()
{
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
if [ $frg -ne 0 ] ; then
echo "\nTabla: $tab - $frag - Tipo: $tipo -"
sql="select sum(nextns) nextns \
from sysmaster:sysptnhdr \
where partnum in (select partn from $base:sysfragments where tabid=$nn \
and fragtype = 'T')"
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
}
else
echo "\nTabla: $tab - No Fragmentada - Tipo: $tipo -"
sql="select sum(nextns) nextns \
from sysmaster:sysptnhdr \
where partnum in (select partnum from $base:systables where tabid=$nn )"
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
}
fi
nn=`echo $ne|sed 's/ //g'`
echo "La tabla $tab de la base $base posee: $nn extent(s)"
exit
}


## MAIN ##
case $# in
1) base=$1; info $base |more ;;
2) base=$1; tab=$2; traeinfo2 $base $tab ;;
*) usage ;;
esac
## END MAIN ##
Gustavo.

I don't know how you went about "reloading the table" but all sorts of things -- like transactions -- can figure into your results in unexpected ways. Try reloading a couple of more times. If it still isn't going down then you must have a problem indeed.