View Only
  • 1.  Moving Smart Large Objects

    Posted Fri May 13, 2022 08:30 PM
    Informix 12.10.FC14
    Solaris 10  1/13

    I want to move smart large objects from their current sbspace to a new sbspace.  Working with BLOBS isn't always done the way one might, at first, think.

    I found this document: How to move a table's sblobs?

    Is it current and accurate?

    Will it create an actual new instance (copy) of the blob in the target sbspace, and then decrement the reference count for the old space (removing the blob entirely if the count becomes 0)?

    Thank you.

    David Grove

    David Grove

  • 2.  RE: Moving Smart Large Objects

    Posted Mon May 16, 2022 02:28 AM
    Hi David,

    An alter table statement which modifies the location of sblob columns (put in <sdbspace>)
    affects only new records, not existing records.
    If the goal from this operation is the full replacement of an sblob space instead of a new sblobspace
    to be added in order to overcome a very full sdbspace and to spread the data over several sblob spaces
    for any reason, the update operation using locopy needs to be done in order to really move 
    the sblob contents to the new sdbspace.
    That kind of operation might be necessary if you want to replace a set of hard disks with a new one
    without having to restore a full backup, but doing it in-place while keeping the database up.
    This might take a while depending on the amount of data to be moved. 
    In case you are using replication of any kind, make sure the target sblob dbspace
    is logged. (onspaces -S ... -Df LOGGING=ON, or for an pre-existing sblob space onspaces -ch -Df)
    So this article is still valid (also with newer releases 14.x).
    The original sblob should have no references at the end, resulting in an empty sblob space,
    which can be dropped after this operation (unless there are other tables with referencing content).
    There is an additional operation which might be necessary to mark the sblob objects as freed,
    when there is no reference any more (onspaces -cl).
    Best, Marcus