Question & Answer
Question
How can I know which sbspace a specifc sblob in my table resides in?
Cause
On occasion you might want to know which sbspace exactly a certain table's or row's smart blobs reside in.
For instance your table might have an sblob column with a PUT clause specifying multiple sbspaces, and you need to know which rows have their sblob in which sbspace. Or you suspect certain sblobs of your table didn't follow the specified PUT clause.
Answer
The following expression on an sblob value returns the sbspace number this sblob resides in:
("0x" || substr(<sblob_col>::lvarchar,17,8))::INT
This simple expression needs to be replaced by following slightly more complex expression on 'little endian' platforms like Linux and Windows:
("0x" || substr(<sblob_col>::lvarchar,23,2)
|| substr(<sblob_col>::lvarchar,21,2)
|| substr(<sblob_col>::lvarchar,19,2)
|| substr(<sblob_col>::lvarchar,17,2))::INT
Example in superstores_demo database:
select ("0x" || substr(advert.picture::lvarchar,17,8))::INT sbspace
from catalog
where advert.picture is not null;
Related Information
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21692353