Hello,
I have a serious problem on my server.
Since a few days the informix database is 100% full (userdbs1).
I deleted a huge amount of data (pm_cdrdatatbl from 170 million records to 5 million records), but the database keeps 100% full.
Is it possible that the pm_cdrdatatbl for example is fragmented?
Or is my db maybe filled up with log files or something else?

Can anyone give me some tips & tricks, I'm an unexperienced user.

Many Thanks in advance!

-----------------------------

output of onstat -d :

# onstat -d

Informix Dynamic Server 2000 Version 9.21.UC4X4 -- On-Line -- Up 11:01:56 -- 591240 Kbytes

Dbspaces
address number flags fchunk nchunks flags owner name
2ca6e7d0 1 0x1 1 1 N informix rootdbs
2ca9e830 2 0x1 2 1 N informix hsddbs
2ca9e978 3 0x1 3 1 N informix logmdbs
2ca9eac0 4 0x2001 4 2 N T informix tmpdbs1
2ca9ec08 5 0x2001 5 1 N T informix tmpdbs2
2ca9ed50 6 0x2001 6 1 N T informix tmpdbs3
2ca9ee98 7 0x1 7 5 N informix userdbs1
2d0fd018 8 0x1 13 1 N informix backupdbs
8 active, 2047 maximum

Chunks
address chk/dbs offset size free bpages flags pathname
2ca6e918 1 1 0 200000 85220 PO- /dev/rdsk/c0b0t0d1se
2caad2a8 2 2 200000 5500 5447 PO- /dev/rdsk/c0b0t0d1se
2caad418 3 3 205500 160000 103229 PO- /dev/rdsk/c0b0t0d1se
2caad588 4 4 365500 25000 24947 PO- /dev/rdsk/c0b0t0d1se
2caad6f8 5 5 390500 25000 24947 PO- /dev/rdsk/c0b0t0d1se
2caad868 6 6 415500 25000 24947 PO- /dev/rdsk/c0b0t0d1se
2caad9d8 7 7 440500 608075 72 PO- /dev/rdsk/c0b0t0d1se
2caadb48 8 7 0 1048575 5 PO- /dev/rdsk/c0b0t0d1sf
2caadcb8 9 4 0 1048575 1048572 PO- /dev/rdsk/c0b0t0d1s10
2caade28 10 7 0 568248 0 PO- /dev/rdsk/c0b0t0d1s11
2ca6ea88 11 7 0 1048575 10 PO- /dev/rdsk/c0b0t0d0se
2ca6ebf8 12 7 0 456076 0 PO- /dev/rdsk/c0b0t0d0sf
2ca6ed68 13 8 0 262144 257741 PO- /.AS/BACKUP/tmp_cdb/backup_tmp_dbspace
13 active, 2047 maximum

#

Hi,

In order to release space you must recreate the table.
A suggestion, uses links simbolicos instead of aiming direct at raw-devices, this facilitates the task if a disc is broken or you recover the on-line one in another servant.

Gustavo.

Thanx!
I think I understand it: if a table has got a size of x Gig, it always keeps this size although you delete his records.
This problem might never have happened because we've set an autodelete on 1 month. This apparently failed and we now had 4.5 months of data in the table.

We've escalated our problem to Vienna, they should know how to recreate the table.

One small question: how do I know if I've got space to extend the database (add chunks). I've just seen that another (bigger) customer of us has a database of only 3 Gig...

greetz,
B

I assume your question is how do I tell if I've got any more physical space on the disk to add a chunk?

That depends upon the operating system, but must likely your going to have to look at each physical disk and determine how/if each logical partition is allocated.

From the pathname in your onstat -d output it would appear that you have two physical drives in play: c0b0t0d0 and c0b0t0d1. Most of the space is allocated across c0b0t0d1 using partition "e" with offsets. I'm not sure what someone did with the chunks allocated to s10 and s11.

At any rate, I'd start with disk c0b0t0d0.

Point of note: You are running three temp dbspaces of dissimilar sizes. tempdbs1 has a large second chuck. I'm assuming that this was done because of occasional sort or temporary table requirements?

If this extra space really isn't needed, and since three temp spaces are a waste (considering they are all on the same device) you might rethink the temp space and use most or all of chunk 9 for additional space for userdbs1.

Thanks to everyone for the useful hints!

Friend,

I'm brazilian and I've finished my english course a long time ago, so, sorry if I write something wrong, ok?! :-)
Well, I have a doubt. I'd like to know if it's possible to increase the number of pages in chunk for a specific temporary dbspace. In other words, I'd like to know if I can change value of chksize.
If not, the one solution will be add a new chunk?

Thanks in advance,
Sílvia Soares.

Hi,

Please execute this script, for look free space.

#!/usr/bin/ksh
[ `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

Gustavo.