IBM Support

How to move a table's sblobs?

Question & Answer


Question

How can I move all or selected sblobs from where they reside to a specific designated sbspace?

Cause

You might want to move certain sblobs in your tables from where they currently reside to a different sbspace. For instance they, for some reason, didn't follow the sblob column's PUT clause or you need to free up space in the sbspace they currently reside in.

Altering a table's PUT clause will not move any data, it will only affect sblobs inserted or updated in the future.

And e.g. an 'UPDATE <target_table> SET <target_sblob_col> = (SELECT <source_sblob_col> FROM <source_table>)' will not move or copy any sblobs either, instead it will copy the sblob reference and increment the sblob's reference counter.

Answer

You can use the built-in LOCOPY function for a real sblob copy, creating a new sblob reference.

The LOCOPY function has two forms:

  • LOCOPY(source_sblob)
  • LOCOPY(source_sblob, 'characteristics_table', 'characteristics_column')


In its first form it will use the configured default sbspace (SBSPACENAME onconfig parameter) and default sblob characteristics for the new sblob.
The second form will follow the PUT clause of specified column in specified table.

You can use this to update an existing sblob onto itself and thereby move (recreate) the sblob following current PUT clause.

E.g., after altering a table's PUT clause, the following update will move existing sblob data to newly specified sbspace(s):

    UPDATE my_table

      SET my_sblob_col = LOCOPY(my_sblob_col, 'my_table', 'my_sblob_col')

      WHERE ...;


All that's been said would apply to sblobs of type BLOB or CLOB.

[{"Product":{"code":"SSGU8G","label":"Informix Servers"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"--","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF022","label":"OS X"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"11.5;11.7;12.1","Edition":"","Line of Business":{"code":"","label":""}}]

Document Information

Modified date:
16 June 2018

UID

swg21692357