Mass updates and moving smart blobs

Abstract

It is sometimes necessary to run a simple UPDATE statement through most or all of the rows in a large table. This cannot be done as a single statement in a logged database without adversely impacting the system or affecting users. The table cannot be exclusively locked while it is in use, so an excessive number of row locks would likely result, causing shared memory growth. It might also fail with “Long Transaction Aborted” due to logical log consumption over the allowed threshold.

This article provides an Informix Stored Procedure Language (SPL) function to commit a sensible number of rows per transaction, and describes a particular use case when you need to move smart blobs to another sbspace.

See also these previous articles that provide functions “sp_dbload” and “sp_dbdelete” to achieve the same objective with INSERT and DELETE:

LOAD and UNLOAD functions

Stored Procedure for Mass Delete

Content

Examples in this article were produced using the provided “stores_demo” database in a Docker container from informix-dockerhub with Informix Dynamic Server (IDS) updated to 14.10.FC11.

Two functions will be described:

sp_dbupdate – Safe mass update avoiding “long transaction aborted” and excessive locks
sp_sbrewrite – Rewrite all smart blobs for a specified column after redefining their location

It’s best to see how they are used for context before we look at the code. In an actual scenario, we needed to move all smart blobs to a new dbspace after deleting obsolete rows. There is no “repack shrink” for blob spaces, and we wanted to release space, so the only solution was to create a new smaller smart blob space and move them all across.

For demonstration in this article, this was set up:

Copy to Clipboard

EXECUTE FUNCTION sysadmin:task
(
    'create sbspace', 'sbspace2', '/opt/ibm/data/spaces/sbspaces2.000', '10 M'
);

EXECUTE FUNCTION sysadmin:task
(
    'create sbspace', 'sbspace3', '/opt/ibm/data/spaces/sbspaces3.000', '10 M'
);

EXECUTE FUNCTION sysadmin:task
(
    'set sbspace logging on', 'sbspace2'
);

EXECUTE FUNCTION sysadmin:task
(
    'set sbspace logging on', 'sbspace3'
);

CREATE TABLE clobs
(
     unique_id   SERIAL      NOT NULL,
     description VARCHAR(20) NOT NULL,
     contents    CLOB        NOT NULL
)
     PUT contents IN (sbspace2) (LOG);

INSERT INTO clobs VALUES (0, 'fstab',  FILETOCLOB('/etc/fstab',  'server'));
INSERT INTO clobs VALUES (0, 'hosts',  FILETOCLOB('/etc/hosts',  'server'));
INSERT INTO clobs VALUES (0, 'motd',   FILETOCLOB('/etc/motd',   'server'));
INSERT INTO clobs VALUES (0, 'passwd', FILETOCLOB('/etc/passwd', 'server'));

​x
 
EXECUTE FUNCTION sysadmin:task(    'create sbspace', 'sbspace2', '/opt/ibm/data/spaces/sbspaces2.000', '10 M');​EXECUTE FUNCTION sysadmin:task(    'create sbspace', 'sbspace3', '/opt/ibm/data/spaces/sbspaces3.000', '10 M');​EXECUTE FUNCTION sysadmin:task(    'set sbspace logging on', 'sbspace2');​EXECUTE FUNCTION sysadmin:task(    'set sbspace logging on', 'sbspace3');​CREATE TABLE clobs(     unique_id   SERIAL      NOT NULL,     description VARCHAR(20) NOT NULL,     contents    CLOB        NOT NULL)     PUT contents IN (sbspace2) (LOG);​INSERT INTO clobs VALUES (0, 'fstab',  FILETOCLOB('/etc/fstab',  'server'));INSERT INTO clobs VALUES (0, 'hosts',  FILETOCLOB('/etc/hosts',  'server'));INSERT INTO clobs VALUES (0, 'motd',   FILETOCLOB('/etc/motd',   'server'));INSERT INTO clobs VALUES (0, 'passwd', FILETOCLOB('/etc/passwd', 'server'));

Smart blobs are not logged by default, meaning they would not be replicated, etc., so either specify that at the sbspace or table level. We have done both above, whereas you would normally do one or the other.

You do not need a PUT clause for BLOB or CLOB columns that will use the default sbspace held in configuration parameter SBSPACENAME, as could have been configured in our example with:

Copy to Clipboard

EXECUTE FUNCTION sysadmin:task
(
    'set onconfig permanent', 'SBSPACENAME', 'sbspace2'
);

 
EXECUTE FUNCTION sysadmin:task(    'set onconfig permanent', 'SBSPACENAME', 'sbspace2');

Before moving blobs, we first need to redefine where they should in future should be located:

Copy to Clipboard

ALTER TABLE clobs
    PUT contents IN (sbspace3) (LOG);

 
ALTER TABLE clobs    PUT contents IN (sbspace3) (LOG);

If the database were not logged, we could have done this to move blobs to the new dbspace:

Copy to Clipboard

UPDATE clobs
    SET contents = LOCOPY(contents, 'clobs', 'contents');

 
UPDATE clobs    SET contents = LOCOPY(contents, 'clobs', 'contents');

“LOCOPY(contents)” on its own would use system default storage characteristics.
Informix removes the old copy of the blob automatically once it is no longer referenced.

See the documentation on LOCOPY here.

However, a single UPDATE statement is not practical for a large logged table, so a function has been written to do any such update safely, committing a sensible number of rows per transaction. For example, any outstanding in-place alter would be eliminated by this:

Copy to Clipboard

EXECUTE FUNCTION oninit:sp_dbupdate
(
    'clobs',
    'description=description',
    'WHERE 1=1'
);

 
EXECUTE FUNCTION oninit:sp_dbupdate(    'clobs',    'description=description',    'WHERE 1=1');

That returns:

Copy to Clipboard

   selected     updated     seconds

          4           4           0

 
   selected     updated     seconds​          4           4           0

For our smart blob copy, we could do this:

Copy to Clipboard

EXECUTE FUNCTION oninit:sp_dbupdate
(
    'clobs',
    'contents = LOCOPY(contents, ''$dbname:clobs'', ''contents'')',
    'WHERE contents IS NOT NULL'
);

 
EXECUTE FUNCTION oninit:sp_dbupdate(    'clobs',    'contents = LOCOPY(contents, ''$dbname:clobs'', ''contents'')',    'WHERE contents IS NOT NULL');

Note that two quotes represent a literal quote.
The current database needs specifying as “$dbname” in that context.
We can and should skip rows where the smart blob is null anyway.

To make it easier, the following wrapper function performs a smart blob column rewrite, returning the same output:

Copy to Clipboard

EXECUTE FUNCTION oninit:sp_sbrewrite
(
    'clobs',
    'contents'
);

 
EXECUTE FUNCTION oninit:sp_sbrewrite(    'clobs',    'contents');

Whichever of those methods was used, we could then drop the original smart blob space without loss of data:

Copy to Clipboard

EXECUTE FUNCTION sysadmin:task
(
    'drop sbspace',
    'sbspace2'
);

 
EXECUTE FUNCTION sysadmin:task(    'drop sbspace',    'sbspace2');

Both functions were installed in separate database “oninit”, where we place general purpose functions. It does not need to be in every database where it might be used, but can be called from a central store of your choice. Listings and explanations follow.

Copy to Clipboard

EXECUTE PROCEDURE ifx_allow_newline('t');

DROP FUNCTION IF EXISTS sp_dbupdate;

CREATE FUNCTION sp_dbupdate
(
    p_table  VARCHAR(128), -- name of table containing rows to update
    p_set    LVARCHAR,     -- SET clause of UPDATE statement
    p_select LVARCHAR,     -- WHERE clause or key value SELECT statement
    p_column VARCHAR(128) DEFAULT 'ROWID', -- integer key column name
    p_rows   SMALLINT     DEFAULT 100      -- rows per update statement
)
    RETURNING
        INT AS selected,
        INT AS updated,
        INT AS seconds;

    -- Safe mass update avoiding "long transaction aborted" and excessive locks
    -- Doug Lawry, Oninit Consulting Ltd, August 2024

    DEFINE l_dbname   VARCHAR(128);
    DEFINE l_sql      LVARCHAR(4000);
    DEFINE l_rowid    INT8;
    DEFINE l_selected INT;
    DEFINE l_updated  INT;
    DEFINE l_seconds  INT;

    SELECT TRIM(odb_dbname) -- main session database
    INTO l_dbname
    FROM sysmaster:sysopendb
    WHERE odb_sessionid = DBINFO('sessionid')
    AND odb_odbno = 0;

    IF l_dbname != DBINFO('dbname') -- function database
    AND p_table NOT MATCHES '*:*' -- not already specified
    THEN
        LET p_table = l_dbname || ':' || p_table;
    END IF

    IF UPPER(SUBSTR(p_select,1,5)) = 'WHERE' THEN
        LET p_select =
            'SELECT ' || p_column ||
            ' FROM '  || p_table  ||
            ' '       || p_select;
    END IF

    LET p_set    = REPLACE(p_set,    '$dbname', l_dbname);
    LET p_select = REPLACE(p_select, '$dbname', l_dbname);

    PREPARE e_select FROM p_select;
    DECLARE c_select CURSOR FOR e_select;
    OPEN    c_select;

    LET l_sql      = NULL;
    LET l_selected = 0;
    LET l_updated  = 0;

    SELECT  sh_curtime
    INTO    l_seconds
    FROM    sysmaster:sysshmvals;

    WHILE 1 = 1

        LET l_rowid = NULL;

        FETCH c_select INTO l_rowid;

        IF l_rowid IS NULL
        OR MOD(l_selected, p_rows) = 0
        THEN

            IF l_sql IS NOT NULL THEN

                LET l_sql = l_sql || ')';

                EXECUTE IMMEDIATE l_sql;

                LET l_updated =
                    l_updated + DBINFO('sqlca.sqlerrd2');

            END IF

            LET l_sql = NULL;

        END IF

        IF l_rowid IS NULL THEN
            EXIT WHILE;
        END IF

        LET l_selected = l_selected + 1;

        IF l_sql IS NULL THEN
            LET l_sql =
                'UPDATE ' || p_table  ||
                ' SET '   || p_set    ||
                ' WHERE ' || p_column || ' IN (';
        ELSE
            LET l_sql = l_sql || ',';
        END IF

        LET l_sql = l_sql || l_rowid;

    END WHILE

    CLOSE   c_select;
    FREE    c_select;
    FREE    e_select;

    SELECT  sh_curtime - l_seconds
    INTO    l_seconds
    FROM    sysmaster:sysshmvals;

    RETURN
        l_selected,
        l_updated,
        l_seconds;

END FUNCTION

DOCUMENT
"
    -- Basic tests with function in different database:

    CREATE TABLE update_test
    (
        serial_no SERIAL,
        tabname VARCHAR(128)
    );

    INSERT INTO update_test (tabname)
    SELECT tabname FROM systables;

    EXECUTE FUNCTION oninit:sp_dbupdate
    (
        'update_test',
        'tabname = tabname',
        'WHERE serial_no <= 50'
    );

    EXECUTE FUNCTION oninit:sp_dbupdate
    (
        'update_test',
        'tabname = tabname',
        'SELECT serial_no FROM $dbname:update_test',
        'serial_no'
    );

    DROP TABLE update_test;
";

 
EXECUTE PROCEDURE ifx_allow_newline('t');​DROP FUNCTION IF EXISTS sp_dbupdate;​CREATE FUNCTION sp_dbupdate(    p_table  VARCHAR(128), -- name of table containing rows to update    p_set    LVARCHAR,     -- SET clause of UPDATE statement    p_select LVARCHAR,     -- WHERE clause or key value SELECT statement    p_column VARCHAR(128) DEFAULT 'ROWID', -- integer key column name    p_rows   SMALLINT     DEFAULT 100      -- rows per update statement)    RETURNING        INT AS selected,        INT AS updated,        INT AS seconds;​    -- Safe mass update avoiding "long transaction aborted" and excessive locks    -- Doug Lawry, Oninit Consulting Ltd, August 2024​    DEFINE l_dbname   VARCHAR(128);    DEFINE l_sql      LVARCHAR(4000);    DEFINE l_rowid    INT8;    DEFINE l_selected INT;    DEFINE l_updated  INT;    DEFINE l_seconds  INT;​    SELECT TRIM(odb_dbname) -- main session database    INTO l_dbname    FROM sysmaster:sysopendb    WHERE odb_sessionid = DBINFO('sessionid')    AND odb_odbno = 0;​    IF l_dbname != DBINFO('dbname') -- function database    AND p_table NOT MATCHES '*:*' -- not already specified    THEN        LET p_table = l_dbname || ':' || p_table;    END IF​    IF UPPER(SUBSTR(p_select,1,5)) = 'WHERE' THEN        LET p_select =            'SELECT ' || p_column ||            ' FROM '  || p_table  ||            ' '       || p_select;    END IF​    LET p_set    = REPLACE(p_set,    '$dbname', l_dbname);    LET p_select = REPLACE(p_select, '$dbname', l_dbname);​    PREPARE e_select FROM p_select;    DECLARE c_select CURSOR FOR e_select;    OPEN    c_select;​    LET l_sql      = NULL;    LET l_selected = 0;    LET l_updated  = 0;​    SELECT  sh_curtime    INTO    l_seconds    FROM    sysmaster:sysshmvals;​    WHILE 1 = 1​        LET l_rowid = NULL;​        FETCH c_select INTO l_rowid;​        IF l_rowid IS NULL        OR MOD(l_selected, p_rows) = 0        THEN​            IF l_sql IS NOT NULL THEN​                LET l_sql = l_sql || ')';​                EXECUTE IMMEDIATE l_sql;​                LET l_updated =                    l_updated + DBINFO('sqlca.sqlerrd2');​            END IF​            LET l_sql = NULL;​        END IF​        IF l_rowid IS NULL THEN            EXIT WHILE;        END IF​        LET l_selected = l_selected + 1;​        IF l_sql IS NULL THEN            LET l_sql =                'UPDATE ' || p_table  ||                ' SET '   || p_set    ||                ' WHERE ' || p_column || ' IN (';        ELSE            LET l_sql = l_sql || ',';        END IF​        LET l_sql = l_sql || l_rowid;​    END WHILE​    CLOSE   c_select;    FREE    c_select;    FREE    e_select;​    SELECT  sh_curtime - l_seconds    INTO    l_seconds    FROM    sysmaster:sysshmvals;​    RETURN        l_selected,        l_updated,        l_seconds;​END FUNCTION​DOCUMENT"    -- Basic tests with function in different database:​    CREATE TABLE update_test    (        serial_no SERIAL,        tabname VARCHAR(128)    );​    INSERT INTO update_test (tabname)    SELECT tabname FROM systables;​    EXECUTE FUNCTION oninit:sp_dbupdate    (        'update_test',        'tabname = tabname',        'WHERE serial_no <= 50'    );​    EXECUTE FUNCTION oninit:sp_dbupdate    (        'update_test',        'tabname = tabname',        'SELECT serial_no FROM $dbname:update_test',        'serial_no'    );​    DROP TABLE update_test;";

See comments in the article on the similar sp_dbdelete function. Also note:

Tables names containing special characters or mixed case should be handled if contained in double quotes with DELIMIDENT set in the environment.
If a user supplies a whole SQL statement to specify key values, only a single SELECT works with DECLARE, so this is secure against SQL injection exploits.

Copy to Clipboard

EXECUTE PROCEDURE ifx_allow_newline('t');

DROP FUNCTION IF EXISTS sp_sbrewrite;

CREATE FUNCTION sp_sbrewrite
(
    p_table VARCHAR(128), -- name of table with smart blobs column
    p_sblob VARCHAR(128), -- name of smart blob column to repack
    p_key   VARCHAR(128) DEFAULT 'ROWID', -- integer key column name
    p_rows  SMALLINT     DEFAULT 100      -- rows per delete statement
)
    RETURNING
        INT AS selected,
        INT AS updated,
        INT AS seconds;

    -- Rewrite all smart blobs for a specified column
    -- Use after changing PUT clause or SBSPACENAME
    -- Doug Lawry, Oninit Consulting Ltd, August 2024

    DEFINE l_dbname   VARCHAR(128);
    DEFINE l_set      LVARCHAR;
    DEFINE l_where    LVARCHAR;
    DEFINE l_selected INT;
    DEFINE l_updated  INT;
    DEFINE l_seconds  INT;

    -- SET DEBUG FILE TO '/tmp/sp_sbrewrite.debug';
    -- TRACE ON;

    SELECT TRIM(odb_dbname) -- main session database
    INTO l_dbname
    FROM sysmaster:sysopendb
    WHERE odb_sessionid = DBINFO('sessionid')
    AND odb_odbno = 0;

    IF l_dbname != DBINFO('dbname') -- function database
    AND p_table NOT MATCHES '*:*' -- not already specified
    THEN
        LET p_table = l_dbname || ':' || p_table;
    END IF

    LET l_set =
        p_sblob || ' = LOCOPY(' ||
        p_sblob || ', '''       ||
        p_table || ''', '''     ||
        p_sblob || ''')';

    LET l_where = 'WHERE ' || p_sblob || ' IS NOT NULL';

    CALL sp_dbupdate
    (
        p_table,
        l_set,
        l_where,
        p_key,
        p_rows
    )
    RETURNING
        l_selected,
        l_updated,
        l_seconds;

    RETURN
        l_selected,
        l_updated,
        l_seconds;

END FUNCTION

DOCUMENT
"
    -- Basic tests with function in different database:

    CREATE TABLE update_test
    (
        serial_no SERIAL,
        contents CLOB
    );

    INSERT INTO update_test (contents)
    SELECT tabname FROM systables;

    SELECT * FROM update_test;

    EXECUTE FUNCTION oninit:sp_sbrewrite
    (
        'update_test',
        'contents'
    );

    SELECT * FROM update_test;

    EXECUTE FUNCTION oninit:sp_sbrewrite
    (
        'update_test',
        'contents',
        'serial_no'
    );

    SELECT * FROM update_test;

    DROP TABLE update_test;
";

 
EXECUTE PROCEDURE ifx_allow_newline('t');​DROP FUNCTION IF EXISTS sp_sbrewrite;​CREATE FUNCTION sp_sbrewrite(    p_table VARCHAR(128), -- name of table with smart blobs column    p_sblob VARCHAR(128), -- name of smart blob column to repack    p_key   VARCHAR(128) DEFAULT 'ROWID', -- integer key column name    p_rows  SMALLINT     DEFAULT 100      -- rows per delete statement)    RETURNING        INT AS selected,        INT AS updated,        INT AS seconds;​    -- Rewrite all smart blobs for a specified column    -- Use after changing PUT clause or SBSPACENAME    -- Doug Lawry, Oninit Consulting Ltd, August 2024​    DEFINE l_dbname   VARCHAR(128);    DEFINE l_set      LVARCHAR;    DEFINE l_where    LVARCHAR;    DEFINE l_selected INT;    DEFINE l_updated  INT;    DEFINE l_seconds  INT;​    -- SET DEBUG FILE TO '/tmp/sp_sbrewrite.debug';    -- TRACE ON;​    SELECT TRIM(odb_dbname) -- main session database    INTO l_dbname    FROM sysmaster:sysopendb    WHERE odb_sessionid = DBINFO('sessionid')    AND odb_odbno = 0;​    IF l_dbname != DBINFO('dbname') -- function database    AND p_table NOT MATCHES '*:*' -- not already specified    THEN        LET p_table = l_dbname || ':' || p_table;    END IF​    LET l_set =        p_sblob || ' = LOCOPY(' ||        p_sblob || ', '''       ||        p_table || ''', '''     ||        p_sblob || ''')';​    LET l_where = 'WHERE ' || p_sblob || ' IS NOT NULL';​    CALL sp_dbupdate    (        p_table,        l_set,        l_where,        p_key,        p_rows    )    RETURNING        l_selected,        l_updated,        l_seconds;​    RETURN        l_selected,        l_updated,        l_seconds;​END FUNCTION​DOCUMENT"    -- Basic tests with function in different database:​    CREATE TABLE update_test    (        serial_no SERIAL,        contents CLOB    );​    INSERT INTO update_test (contents)    SELECT tabname FROM systables;​    SELECT * FROM update_test;​    EXECUTE FUNCTION oninit:sp_sbrewrite    (        'update_test',        'contents'    );​    SELECT * FROM update_test;​    EXECUTE FUNCTION oninit:sp_sbrewrite    (        'update_test',        'contents',        'serial_no'    );​    SELECT * FROM update_test;​    DROP TABLE update_test;";

Conclusion

The functions in this article provide a safer way to update a large number of rows in a table instead of using a single UPDATE statement, and a specific convenient way to move smart blobs to a different sbspace.

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.