Question & Answer
How can I know which sbspace a specifc sblob in my table resides in?
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.
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)
Example in superstores_demo database:
select ("0x" || substr(advert.picture::lvarchar,17,8))::INT sbspace
where advert.picture is not null;
16 June 2018