Informix

Hi,

IBM Informix Dynamic Server Version 11.70.FC5XE

Please, how to defragment (repack) a sbspace after plenty LOBs been deleted?

Hi Dennis.

I assume we are talking about smart blobs (BLOB/CLOB) and not legacy blobs (BYTE/TEXT).

Continual insert/delete of smart blobs is well behaved. We managed stable apps that turnover sbspace contents every week. The only purpose therefore would be if you intend to reduce the size of the sbspace. That is only possible if you have multiple chunks, and you intend to drop the last chunk(s).

If you need to repack the sbspace, you would need to use this technique to recreate each smart blob:

https://www.ibm.com/support/pages/how-move-tables-sblobs

That might result in "long transaction aborted", in which case perhaps you could use this as the framework for SPL to update a few hundred at a time:

https://www.oninitgroup.com/faq-items/informix-stored-procedure-for-mass-delete

Others on this forum may have views on whether using LOCOPY with the same sbspace will reliably move them all to the front. If not, or if you have one big chunk which is too large, you will need to create a new smaller sbspace, use the above technique to move contents across, then drop the original sbspace.

When creating a new sbspace, the main consideration is whether contents should be logged, without which replication won't work, etc., and that's not the default. See:

https://www.ibm.com/docs/en/informix-servers/14.10?topic=logging-turn-off-sbspace

------------------------------
Doug Lawry
Oninit Consulting
------------------------------

SmartBlob spaces cannot be "defragmented" as they contains chunks of data and there is at the row itself that stores the offset and length of this chunk of data.

Only think you can do is to "rewrite" the blobs and pray for enough free space to really reposition the data in "free space". But there are nothing that can assure you, that data will be countinously writen.

E.g.

UPDATE mytable SET locolumn = LOCOPY(locolumn, 'mytable', 'locolumn')

You need to test this. Other method is to create a new smartblob, change the put clause for the column to this new smartblob and copy all data:

UPDATE mytable SET locolumn = LOCOPY(locolumn, 'mytable', 'locolumn')

This will move smartblobs to new smartblobspace defined in the put clause for the column.

Please, test all this before executing in production.