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.
Related Information
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21692357