Informix

 View Only
Expand all | Collapse all

Defragment sbspace

  • 1.  Defragment sbspace

    Posted Tue August 20, 2024 07:51 AM

    Hi,

    IBM Informix Dynamic Server Version 11.70.FC5XE

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



    ------------------------------
    Sincerely,
    Dennis
    ------------------------------


  • 2.  RE: Defragment sbspace

    Posted Wed August 21, 2024 02:37 AM
    Edited by Doug Lawry Wed August 21, 2024 02:40 AM

    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
    ------------------------------

    Original Message


  • 3.  RE: Defragment sbspace

    Posted Wed August 21, 2024 04:31 AM

    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
    ------------------------------

    Original Message


  • 4.  RE: Defragment sbspace

    Posted Wed August 21, 2024 05:20 AM

    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
    ------------------------------

    Original Message


  • 5.  RE: Defragment sbspace

    Posted Wed August 21, 2024 09:08 AM
    Edited by Dennis Melnikov Wed August 21, 2024 09:16 AM

    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
    ------------------------------

    Original Message


  • 6.  RE: Defragment sbspace

    Posted Wed August 21, 2024 10:16 AM

    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
    ------------------------------

    Original Message


  • 7.  RE: Defragment sbspace

    Posted Wed August 21, 2024 11:06 AM

    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
    ------------------------------

    Original Message


  • 8.  RE: Defragment sbspace

    Posted Wed August 21, 2024 01:39 PM
    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


    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.






    Original Message


  • 9.  RE: Defragment sbspace

    Posted Thu August 22, 2024 05:58 AM

    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
    ------------------------------

    Original Message


  • 10.  RE: Defragment sbspace

    Posted Thu August 22, 2024 07:25 AM

    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
    ------------------------------

    Original Message


  • 11.  RE: Defragment sbspace

    Posted Thu August 22, 2024 08:00 AM

    Vicente,

    What is 'object_id'? Does it have a corresponding key in some system table?



    ------------------------------
    Sincerely,
    Dennis
    ------------------------------

    Original Message


  • 12.  RE: Defragment sbspace

    Posted Thu August 22, 2024 08:04 AM

    oncheck -pS or oncheck -cS will give you this information



    ------------------------------
    Vicente Salvador Cubedo
    ------------------------------

    Original Message