Smart large object space usage per table

Abstract

Informix Dynamic Server (IDS) stores smart large objects in sbspaces separate to dbspaces containing tables. Typically, all will be stored in the default sbspace configured in parameter SBSPACENAME. It is therefore challenging to determine which of your columns of type BLOB (binary large object) or CLOB (character large object) are taking up the most room.

This article provides and describes a stored procedure to list the total size for each smart large object column.

Content

Firstly, did you know that smart large objects are not logged by default? See Storage characteristics of sbspaces. Unless you have a good reason otherwise, always create sbspaces as in the following example:

The alternate create sbspace with log SQL API method returns “ERROR” with no further explanation on my test system (see DT463888 in Conclusions):

Use this two-stage process instead:

You can check existing sbspaces with:

It would otherwise show “LO_NOLOG”.

You can “Turn logging on or off for an sbspace“, though that only changes the default for new objects. See also our article on “Mass updates and moving smart blobs“.

There is no built-in way to get the size of a smart object:

A solution was given in the IBM Informix Community forum here:

Is there a way to check the sbspace usage by table?

As pointed out by Art Kagel, the SQL Packages Extension DataBlade provided with IDS contains a suitable function “dbms_lob_getlength”. Install it in your preferred database with this SQL:

Note that the documentation on the DBMS_LOB package wrongly lists the function name as “dbms_lob.get_length” (see Conclusions).

We can then use that in our procedure which you should create in the same database as the DataBlade:

Copy to Clipboard

DROP FUNCTION IF EXISTS f_sbspace_usage;

CREATE PROCEDURE f_sbspace_usage
(
    p_dbsname VARCHAR(128) DEFAULT '*',
    p_debug   BOOLEAN      DEFAULT 'f'
)
    RETURNING
        VARCHAR(128) AS dbsname,
        VARCHAR(128) AS tabname,
        VARCHAR(128) AS colname,
        VARCHAR(4)   AS coltype,
        VARCHAR(128) AS sbspace,
        BIGINT       AS nrows,
        BIGINT       AS blobs,
        BIGINT       AS bytes;
/*
    Show smart BLOB space usage per column
    Doug Lawry, Oninit Consulting, February 2026

Prerequisite:

EXECUTE FUNCTION sysbldprepare('excompat.*, 'create');

Example usage:

EXECUTE FUNCTION f_sbspace_usage();        -- no debug
        EXECUTE FUNCTION f_sbspace_usage('*','t'); -- with debug

DROP VIEW IF EXISTS v_sbspace_usage;

CREATE VIEW v_sbspace_usage
        (
            dbsname,
            tabname,
            colname,
            coltype,
            sbspace,
            nrows,
            blobs,
            bytes
        )
        AS
        SELECT * FROM TABLE
        (
            FUNCTION f_sbspace_usage()
        );

DROP TABLE IF EXISTS temp_sbspace_usage;

SELECT * FROM v_sbspace_usage
        INTO TEMP temp_sbspace_usage;

SELECT *
        FROM temp_sbspace_usage
        ORDER BY 1, 2;

SELECT
            sbspace,
            SUM(blobs) AS blobs,
            SUM(bytes) AS bytes
        FROM temp_sbspace_usage
        GROUP BY 1
        ORDER BY 1;
*/
    DEFINE l_dbsname VARCHAR(128);
    DEFINE l_tabname VARCHAR(128);
    DEFINE l_colname VARCHAR(128);
    DEFINE l_coltype VARCHAR(4);
    DEFINE l_sbspace VARCHAR(128);
    DEFINE l_nrows   BIGINT;
    DEFINE l_blobs   BIGINT;
    DEFINE l_bytes   BIGINT;
    DEFINE l_default VARCHAR(128);

IF p_debug THEN
        SET DEBUG FILE TO '/tmp/f_sbspace_usage.debug';
        TRACE ON;
    END IF

SET LOCK MODE TO WAIT 60;
    SET ISOLATION TO DIRTY READ;

LET l_default = NULL;

SELECT cf_effective
    INTO l_default
    FROM sysmaster:sysconfig
    WHERE cf_name = 'SBSPACENAME';

FOREACH

SELECT name
        INTO l_dbsname
        FROM sysmaster:sysdatabases
        WHERE name MATCHES p_dbsname
        AND is_logging IN
        (
            -- Can only access databases with same logging
            SELECT is_logging
            FROM sysmaster:sysdatabases
            WHERE name = DBINFO('dbname')
        )
        AND name IN
        (
            -- Can only access databases with same locale
            SELECT dbs_dbsname
            FROM sysmaster:sysdbslocale
            WHERE dbs_collate IN
            (
                SELECT dbs_collate
                FROM sysmaster:sysdbslocale
                WHERE dbs_dbsname = DBINFO('dbname')
            )
        )
        ORDER BY 1

PREPARE e_columns FROM
                'SELECT '
            ||      't.tabname, '
            ||      'c.colname, '
            ||      'x.name, '
            ||      'a.sbspace '
            ||  'FROM '
            ||      l_dbsname || ':sysxtdtypes'   || ' AS x, '
            ||      l_dbsname || ':syscolumns'    || ' AS c, '
            ||      l_dbsname || ':systables'     || ' AS t, OUTER '
            ||      l_dbsname || ':syscolattribs' || ' AS a '
            ||  'WHERE x.name IN (''blob'',''clob'') '
            ||      'AND c.extended_id = x.extended_id '
            ||      'AND c.tabid > 99 '
            ||      'AND t.tabid = c.tabid '
            ||      'AND t.tabtype = ''T'' '
            ||      'AND a.tabid = c.tabid '
            ||      'AND a.colno = c.colno '
            ||   'ORDER BY 1, 2';

DECLARE c_columns CURSOR FOR e_columns;
        OPEN    c_columns;

WHILE 1=1

LET l_tabname = NULL;

FETCH c_columns INTO
                l_tabname,
                l_colname,
                l_coltype,
                l_sbspace;

IF l_tabname IS NULL THEN
                EXIT WHILE;
            END IF

PREPARE e_blobs FROM
                    'SELECT '
                ||      'COUNT(*), '
                ||      'COUNT(' || l_colname || '), '
                ||      'SUM(dbms_lob_getlength(' || l_colname || ')) '
                ||  'FROM '
                ||      l_dbsname || ':' || l_tabname;

DECLARE c_blobs CURSOR FOR e_blobs;
            OPEN    c_blobs;
            FETCH   c_blobs INTO l_nrows, l_blobs, l_bytes;

CLOSE   c_blobs;
            FREE    c_blobs;
            FREE    e_blobs;

RETURN
                l_dbsname,
                l_tabname,
                l_colname,
                l_coltype,
                NVL(l_sbspace,l_default),
                l_nrows,
                l_blobs,
                NVL(l_bytes,0)
            WITH RESUME;

END WHILE

CLOSE   c_columns;
        FREE    c_columns;
        FREE    e_columns;

END FOREACH

END PROCEDURE;

You can then run the report across all databases in your instance simply with:

On my test system, that produces:

dbsname	tabname	colname	coltype	sbspace	nrows	blobs	bytes
test	clobs	contents	clob	sbspace3	4	4	1834
test	smart_blob_test	data	blob	sbspace	2	2	16313
test	smart_blob_test_copy	data	blob	sbspace	2	2	16313
test	smart_clob_test	data	clob	sbspace	2	2	16313
test	smart_clob_test_copy	data	clob	sbspace	2	2	16313

You can wrap that in a view which might be easier to use:

Copy to Clipboard

You can then select sub-totals, etc. The above and example usage are in comments at the top of the procedure.

Caveats

Note that “bytes” in the result is the total size of smart large objects, not the amount of space allocated in the sbspace: that is very complicated to work out exactly due to overheads with metadata, extents, etc. Also, the minimum size allocated for a BLOB or CLOB is 512 bytes.

Conclusion

The results of the procedure provided in this article will be a good indicator of the relative usage per smart large object column.

TS021500062 has been raised with IBM for the create sbspace with log defect and SQL Packages Extension documentation fault:

DT463888: execute function task(“create unencrypted sbspace” or “create sbspace with log) is failing, sometimes with an assertion.
Documentation: Function names are not correct in the “SQL Package Extension” page of Informix extensions and Datablade Modules

RFE INFX-I-699 has been raised for a new configuration parameter SPSPACELOGGING to make sbspaces logged by default.

Disclaimer

Suggestions above are provided “as is” without warranty of any kind, either express or implied, including without limitation any implied warranties of condition, uninterrupted use, merchantability, fitness for a particular purpose, or non-infringement.