IBM Support

How to determine which sbspace a table's sblobs reside in

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;

[{"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

swg21692353