Defragment sbspace
Dennis Melnikov asked how to defragment an sbspace on IDS 11.70.FC5XE after many LOB deletes. Doug Lawry said smart blobs cannot be defragmented and only dropping trailing chunks shrinks the space; Vicente Salvador Cubedo suggested rewriting with LOCOPY. They decoded the LVARCHAR cast (sbspace and chunk, byte order matters) and oncheck -pS or -cS gives object details. No real repack method.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Hi, IBM Informix Dynamic Server Version 11.70.FC5XE Please, how to defragment (repack) a sbspace after plenty LOBs been deleted? ------------------------------ Sincerely, Dennis ------------------------------
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.
------------------------------
Vicente Salvador Cubedo
------------------------------
Vicente, The note explains how to determine a sbspace that holds the sblob: https://www.ibm.com/support/pages/how-determine-which-sbspace-tables-sblobs-reside Is it possible to extract an offset too? ------------------------------ Sincerely, Dennis ------------------------------
Ok, at least, sbspace num and chunk num are easy to extract: sbsnum = ("0x" || SUBSTR(<sblob_col>::LVARCHAR,17,8))::INT chunknum = ("0x" || SUBSTR(<sblob_col>::LVARCHAR,25,8))::INT That gives a good starting point for repacking a sbspace. ------------------------------ Sincerely, Dennis ------------------------------
It's not exactly like this: Check this method: select ("0x" || SUBSTR(c1::LVARCHAR,23,2) || SUBSTR(c1::LVARCHAR,21,2) || SUBSTR(c1::LVARCHAR,19,2) || SUBSTR(c1::LVARCHAR,17,2))::INT dbsnum, ("0x" || SUBSTR(c1::LVARCHAR,31,2) || SUBSTR(c1::LVARCHAR,29,2) || SUBSTR(c1::LVARCHAR,27,2) || SUBSTR(c1::LVARCHAR,25,2))::INT chknum from test ------------------------------ Vicente Salvador Cubedo ------------------------------
Vicente, According to the note your method is for little-endian platforms like Linux (some) and Windows. The 1st method works fine on AIX. BTW, do you know how to extract offset (and size) from the LVARCHAR? Server Studio displays length of blob in SELECT, but I have no idea how it gets it. ------------------------------ Sincerely, Dennis ------------------------------
Dennis:
I do not know how to extract the offset and length from the lvarchar stored in-row, but there is a function dbms_lob_getlength() in the excompat datablade that you can install that will return the length of a BLOB of CLOB type column.
I typically install that blade then use create function to map dbms_lob_getlength() to length(). Witness:
CREATE FUNCTION "art".length ( input blob ) returning bigint;
return dbms_lob_getlength( input );
end function;
REVOKE EXECUTE ON FUNCTION "art".length( blob ) FROM "public" AS "art";
GRANT EXECUTE ON FUNCTION "art".length( blob ) TO "public" AS "art";CREATE FUNCTION "art".length ( input clob ) returning bigint;
return dbms_lob_getlength( input );
end function;
REVOKE EXECUTE ON FUNCTION "art".length( clob ) FROM "public" AS "art";
GRANT EXECUTE ON FUNCTION "art".length( clob ) TO "public" AS "art";Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.com
Blog:
http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.
Art, Yes, I use DBMS_LOB_GETLENGTH(). My point is that Server Studio shows BLOB's size without the datablade. Apparently it decodes LVARCHAR the same way as the function does. ------------------------------ Sincerely, Dennis ------------------------------
Indeed, my test server is little indian. Unfortunately, smartblob to lvarchar casting don't show all the required metadata info. This is all I know about data showed in cast conversión: select ("0x" || SUBSTR(c1::LVARCHAR,23,2) || SUBSTR(c1::LVARCHAR,21,2) || SUBSTR(c1::LVARCHAR,19,2) || SUBSTR(c1::LVARCHAR,17,2))::INT dbsnum, ("0x" || SUBSTR(c1::LVARCHAR,31,2) || SUBSTR(c1::LVARCHAR,29,2) || SUBSTR(c1::LVARCHAR,27,2) || SUBSTR(c1::LVARCHAR,25,2))::INT chknum, ("0x" || SUBSTR(c1::LVARCHAR,39,2) || SUBSTR(c1::LVARCHAR,37,2) || SUBSTR(c1::LVARCHAR,35,2) || SUBSTR(c1::LVARCHAR,33,2))::INT chkpage, ("0x" || SUBSTR(c1::LVARCHAR,47,2) || SUBSTR(c1::LVARCHAR,45,2) || SUBSTR(c1::LVARCHAR,43,2) || SUBSTR(c1::LVARCHAR,41,2))::INT object_id from test; Just convert this command from LE to your BE platform Vicente ------------------------------ Vicente Salvador Cubedo ------------------------------
Vicente, What is 'object_id'? Does it have a corresponding key in some system table? ------------------------------ Sincerely, Dennis ------------------------------
oncheck -pS or oncheck -cS will give you this information
------------------------------Vicente Salvador Cubedo
------------------------------