Informix

Informix

Connect with Db2, Informix, Netezza, open source, and other data experts to gain value from your data, share insights, and solve problems.

 View Only

  • 1.  Is that SBspace really empty?

    Posted 08/27/26 11:49 AM

    Greetings, Y'all.

    As part of a minot project, I will want to drop an SBspace and later recreate its chunk file in a different raw device.  Of course, as with a DBspace, where the server won't allow me to drop it unless it's empty, the SBspace needs to be empty in order to drop it.  At least so I gather from the help text on onspaces --.

    So how do I determine of an SBspace is empty? And that matter:

    • How d I find out what's in there?
    • How do I delete what's in there (assuming the user is OK with that, of course.)  I did just find onspaces -cl 

    At this time, it is being used for Enterprise Replication. If we cdr stop ER, will that empty the SBspace?

    Or will I still need to run onspaces -cl? The help text says this will "garbage collect smart large objects that are not referenced."  But how do I find what is still being referenced, if anything?

    Thanks for pointers.



    ------------------------------
    +-----------------------------------------------------------+
    | I am pleased to report that I had no problems today. |
    | I had only issues, opportunities, challenges and valuable |
    | learning experiences. |
    +------------------------------------------ Jacob S --------+
    ------------------------------


  • 2.  RE: Is that SBspace really empty?

    Posted 08/28/26 11:25 AM

    If the new raw device is currently available, could you use mirroring to move without worrying about emptying the space?  Use onspaces to add mirroring to the existing chunk, with the mirror being place on the new raw device.  Once the mirrors have synced, use the SQL Admin API command 'execute function task("modify chunk swap_mirror",<chunk number>)' or 'execute function task("modify space swap_mirrors","<space name>")' to swap the primary and mirrored chunks, then disable mirroring to release the current raw device.



    ------------------------------
    mark collins
    ------------------------------



  • 3.  RE: Is that SBspace really empty?

    Posted 08/28/26 01:24 PM

    Interesting, if convoluted, idea there, Scott.  Thanks for offering it.

    Aside that we don't use mirroring in our environment, I don't see why that's better than onspaces -cl.

    I do believe (corrections invited) that the contents of an SBspace are inherently transitory; only while the data is being used (like ER or geographic searches)  and when those functions have completed, whatever is left can be collected by that onspaces command.  If that is correct, I don't see how mirroring 

     Basically, I need to see if there is still something using the SBspace when I want to drop it, before trying and getting an error.



    ------------------------------
    +-----------------------------------------------------------+
    | I am pleased to report that I had no problems today. |
    | I had only issues, opportunities, challenges and valuable |
    | learning experiences. |
    +------------------------------------------ Jacob S --------+
    ------------------------------

    Original Message


  • 4.  RE: Is that SBspace really empty?

    Posted 08/31/26 02:13 AM

    Hi Jacob.

    Although it doesn't answer all your questions, this might be useful:

    https://www.oninitgroup.com/faq-items/mass-updates-and-moving-smart-blobs



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



  • 5.  RE: Is that SBspace really empty?
    Best Answer

    Posted 08/31/26 04:41 AM

    Hi Jake,

    whether an sbspace, or a particular chunk of an sbspace, is empty you best can see from "oncheck -pe <sbspace_name>"
      -> not empty if there are "LO" (or "SBLOBSpace LO") entries with a LO ID in square brackets

    And yes, ER blocks each sbspace it is configured to use, from being dropped, by placing a tiny sblob into them. This "cdr" sblob will only go away when undefining ER or when removing a particular sbspace from the ER configuration (CDR_QDATA_SBSPACE).

    onspaces -cl really only cleans out orphaned (temp) sblobs from your engine's last uptime, so from before the last restart.  It will not clean out any sblobs still referenced by any table or other purposes.

    There's an article somewhere, or two, on how to determine which sbspace is used by a given table's sblobs.  It's not possible to tell who/what references a given sblob in an sbspace.

    HTH,
     Andreas



    ------------------------------
    Andreas Legner
    Informix Dev
    HCL Software
    ------------------------------



  • 6.  RE: Is that SBspace really empty?

    Posted 08/31/26 11:39 AM

    Andreas,

    This has got the be the ideal solution.   At this time we cannot test your recommendation because of a major ER project in progress, one I would not wish to interrupt. However, I can certainly play with your oncheck options to get a feel for what's going in in there.

    And Doug: You are correct; it does not quite answer the question but gave us other information we will be happy to use.  This is all terrific knowledge!

    Thanks MUCH!



    ------------------------------
    +-----------------------------------------------------------+
    | I am pleased to report that I had no problems today. |
    | I had only issues, opportunities, challenges and valuable |
    | learning experiences. |
    +------------------------------------------ Jacob S --------+
    ------------------------------

    Original Message