Strange behaviour deleteing blobs

Hello, IDS 9.40FC5 HP-UX11.11 We've got a table with a byte field stored all in a single dbspaces, I mean no blobspace. oncheck -pt shows all allocated pages as used. When we delete the old rows, we should expect the number of used pages decrease, but that doesn't happen. New rows are been inserted and no new extents are been claimed, so I must assume that Informix is managing it somehow. I've read in an old post that after a level 0 copy it will show correctly, but it was talking about blobspaces, wich is not my case, and anyhow it didn't work for me. Probably an alter table ... init, or a unload/load will work, but I need to do that in a downtime and for the moment I prefer not. Any clues on what's happening? TIA

ifxuser@gmail.com wrote: > IDS 9.40FC5 HP-UX11.11 > > We've got a table with a byte field stored all in a single dbspaces, I > mean no blobspace. oncheck -pt shows all allocated pages as used. When > we delete the old rows, we should expect the number of used pages > decrease, but that doesn't happen. > New rows are been inserted and no new extents are been claimed, so I > must assume that Informix is managing it somehow. > I've read in an old post that after a level 0 copy it will show > correctly, but it was talking about blobspaces, wich is not my case, > and anyhow it didn't work for me. > Probably an alter table ... init, or a unload/load will work, but I > need to do that in a downtime and for the moment I prefer not. > > Any clues on what's happening? This is standard; once IDS allocates tablespace space to a table, it doesn't release it on the grounds that if the table used to need it, it probably will again -- and you said your blobs were stored in the table. To actually release the space, you'd have to do something more dramatic, such as causing the table to be rebuilt. Since you're on 9.40, you can't use TRUNCATE TABLE, so you could consider using ALTER INDEX pk_table TO CLUSTER - which will rebuild the table in a new partition and thereby release the space (assuming it is not fragmented; the verbiage changes but the concept doesn't if it is fragmented). You might need to do ALTER INDEX pk_table TO NOT CLUSTER first - that is a very cheap operation. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/