One of the more frequent headaches for Informix DBAs is dealing with Smartblobs (BLOB/CLOB). They are powerful storage pieces to handle binary data. However, they have a special set of ways to handle working with them. Unlike other datatypes you can’t just directly store content in them, you have to handle them from files on disk or through the IO system.
If you have not worked with them before, they offer some advantages over traditional “Dumb” blobs (BYTE/TEXT). These include the ability to have them logged which allows use in replication. They also can be handled in part rather than reading the entire file at once. You also can easily load them from a file on disk. You are able to keep metadata on them to track things like the last accessed date. One other big advantage is they can store up to 4tb of data. You also can round robin store the data between multiple smartblob spaces.
To set up the engine you need to create a smartblob space. To do this use the onspaces command with the -c -S flags. By default the space will be unlogged and automatically set up the space inside of the smartblob space to store the metadata. There are some flags you may want to set when creating the space:
- -Df buffering=ON – Use the normal buffer pools rather than a separate memory pool just for smartblobs
- -Df ACCESSTIME=ON – Track the last time a smartblob is accessed
- -Df LOGGING=ON – Log the smartblob space, this can be changed later on if needed
- -Df AVG_LO_SIZE=<size> – Give a general idea of what size the average object will be, this can help storage efficiency
- -Ms 150 – The size of metadata, in K, the more objects you have the more metadata space you need, if you run out of metadata space it will block writing to that space
onspaces -c -S sbspace -p /cooked_space/sbspaces -o 0 -s 1000000 -Ms 10000 -Df “AVG_LO_SIZE=5K,LOGGING=ON,ACCESSTIME=ON”
Once you have done that, change the $ONCONFIG parameters of SBSPACENAME to define the default smartblob dbs and SBSPACETEMP as the default temp space when dealing with smartblob objects.
At this point the engine is good to go to start working with smartblob fields. You can create them as normal columns of types BLOB or CLOB. One note is that by default they will go to the SBSPACENAME space, if you want them in another space(s) use the PUT flag.
CREATE TABLE test_smartblob ( serial_id serial, blobfield BLOB) PUT blobfield in (sbspace1);
You can list multiple sbspaces in the PUT command, it will use them round robin.
Note: as for all Round Robin fragmentation, if you alter the table to change the PUT location it will not change prior records, it will only use those spaces for new fields
Now, how to access those fields. There is a main set of functions to handle the fields.
To load data use the functions FILETOCLOB and FILETOBLOB. You pass them the same way with the parameters of FILETOBLOB(“file_to_load.txt”, “server or client”);
Where in this case if you define the second field as ‘server’ it will pull the file from the Informix server filesystem and if you set it to ‘client’ it will pull and upload the file from the client system.
insert into table_a (image_data) values (filetoblob(“/tmp/latest_image.jpg”,’client’));
update table_b set latest_log = filetoclob(“/var/log/syslog”, ‘server’) where logname = ‘syslog’;
To copy a smartblob object to another record use the LOCOPY function. Note that this creates a pointer to a file, not a separate file.
INSERT INTO students_2026 (photo) SELECT LOCOPY(photo) FROM students:students_2025;
To retrieve a file you use the LOTOFILE function. This works the same way as FILETOBLOB where you pass LOTOFILE(field_name, “output_file.txt”, “server or client”);
Note however the output file by default will have a suffix of the internal object. So in this case it would write the file “output_file.txt.30cf2”
select lotofile(user_image, ‘user_image.png!’, ‘client’) from users_table where user_id = 3;
select lotofile(latest_tos, ‘/var/www/html/tos.html!’, ‘server’) from web_docs where doc_type = ‘tos’;
If you want to change the naming scheme you can use ‘?’ in the filename to have it add in hex values to help keep the filename unique. While if you use a ‘!’ in the filename it stops at that point. So setting it to “output_file.txt!” will write out just ‘output_file.txt”
One of the common headaches with it is there is not a direct way to get the size of a smartblob object. IBM/HCL has you covered here. There is a datablade called ‘excompat’ that adds additional functionality.
If you need to add it to a database you need to register the datablade:
execute function sysbldprepare(‘excompat.*’, ‘create’);
It will add a set of additional functions. A full list can be found at:
https://help.hcl-software.com/hclinformix/1410/dbxt/ids_dbxt_530.html
The function that I find most useful is the dbms_lob_getlength function, which works just like you think it would. If you run it with dbms_lob_getlength(column) it gives the size in bytes of the record.
One note with excompat. Make sure you are running version 1.2. Up to date Informix versions will have this bundled in $INFORMIXDIR/extend/excompat.1.2, if you are running an older version you may want to verify. There is a bug in version 1.1 and 1.0 where smartblob objects > 2gb will return ‘0’ for the length, it may also impact other functions from the extension with files over that size.
One unofficial trick is if you are running an older version of informix you can get the updated extension from a newer system and it should be backwards compatible, just put it in the $INFORMIXDIR/extend directory.
Note that Informix only stores a pointer to the smartblob object in the BLOB/CLOB field, not the actual file, which is why everything is stored in smartblob spaces and there is not an in-table storage option like there is with TEXT/BYTE.
With all of that, smartblobs provide powerful features inside of Informix that add a great deal of flexibility when dealing with binary data. Sometimes it can be a little tricky to get started with them, however they can be a vital part of an advanced Informix environment.
-Tom Beebe
xDB Systems, Inc
