Informix Stored Procedure for Mass Delete

Updated June 2020

Informix stored procedure for mass delete: Parameter “p_select” altered to LVARCHAR (default length 2048) as VARCHAR(255) not long enough in real situations.

Updated January 2024

Informix stored procedure for mass delete resolved: 305: Subscripted column (p_select) is not of type CHAR, VARCHAR, TEXT nor BYTES.

Abstract

IBM Informix Dynamic Server (IDS) needs careful handling of data manipulation language (DML) operations affecting millions of rows. If performed in a single SQL statement on a logged database, the database engine must keep each affected row locked until it completes: even if no transaction has been started with BEGIN WORK, the statement still runs inside an implicit transaction.

Assuming the table has LOCK MODE set to ROW (the norm for OLTP) rather than PAGE (usually avoided due to concurrency problems), the result is millions of locks, particularly as they are required on each index as well as the table. The documentation for the LOCKS configuration parameter, which defines the initial size of the lock structure in the resident shared memory segment, states that each requires 100-200 bytes depending on the platform. Should it run out and need to allocate an extension lock structure in virtual shared memory, it may have to add new segments dynamically totalling gigabytes. Unless the Enterprise Edition is in use, this may cause user sessions to be starved of memory or even rejected.

Following a feature request by the author, configuration parameter SESSION_LIMIT_LOCKS is available from IDS 12.10.xC4 that you can use to prevent an excessive number of locks by any one session, but that will obviously result in the statement being aborted if the limit is reached.

Locks can be avoided entirely by placing an EXCLUSIVE lock on the table or by temporarily disabling logging on the database or altering the type to RAW, but this will most likely interfere with other users, break applications, and would invalidate any replication.

There is a second major problem with very large transactions: if so much data is affected that most of the logical log space has been consumed, depending on configuration parameter LTXHWM, the statement will encounter “Long Transaction Aborted”. It can then take at least as long for the transaction to be rolled back, during which time the instance may be unusable if LTXEHWM has also been reached.

The right way to avoid this entirely is to split DML statements into smaller transaction affecting only a few thousand rows each at most. The two most common scenarios are when loading fresh data from a file or deleting a large number of rows. These can be safely achieved committing a few thousand rows per transaction with the dbload tool provided with IDS (see wrapper script at the end of this article) and the dbdelete open source ESQL-C program by Art Kagel (see also a Python version by Andrew Ford). Other scenarios may need to be specifically coded.

Informix stored procedure for mass delete allows millions of rows to be safely deleted in one operation inside any SQL session. This article describes a stored procedure alternative to dbdelete based on it.

Content

A complex archiving program may need to delete rows depending on the contents of a temporary table which is only visible to the current session. Compiling and deploying a C program may be inconvenient or prohibited. The SQL session may be running on a different machine where such a tool is not available or for which such a tool cannot easily be compiled, such as on Windows.

Whatever the reason, the Informix stored procedure for mass delete sp_dbdelete listed after this article might be an attractive alternative. The available parameters are:

Copy to Clipboard

You must supply the table name first. If it’s not in the current database, specify with “database:table”. If it’s in a different instance, use “database@instance:table”.

The set of rows to be deleted must also be defined as either:

conditions within the table affected, starting with keyword WHERE (unlike dbdelete);
a complete SELECT statement returning integer key values to be deleted.

Either can be upper or lower case. If the second form is used, the key column is assumed to be ROWID unless a column name is provided as a third parameter. Only columns of type INT, INT8, BIGINT, or their SERIAL equivalents are supported at this time. ROWID is always available unless the table has multiple partitions (aka fragments) as was created without.

The last fourth parameter will normally never need specifying. You can perform your own experiments, but 100 seems to be the smallest number of rows per DELETE that doesn’t experience slower performance. Thousands are not possible due to limits on the maximum statement size possible with EXECUTE IMMEDIATE in Stored Procedure Language (SPL) which varies with version and is not well documented.

Return values are:

INT AS selected, INT AS deleted, INT AS seconds;

The first two will be the same unless some of the rows selected were no longer there at time of deletion, in which case “deleted” will be slightly less. This would be a concern if using ROWID, as the wrong rows could be deleted as a result on a volatile table.

The run time in seconds is also returned, calculated accurately by fetching the time from a system table before and after. Beware of using CURRENT which is a constant in SPL containing when the procedure was started.

Within SPL, when a SQL statement references an object without specifying the database, it is assumed to be in the same one as the procedure, not the database to which the calling session is connected. This is potentially inconvenient if you want just one copy of the procedure defined, but will be calling it from many application databases in that instance. This is solved in sp_dbdelete as follows:

If the table name has been specified without a database prefix, the top-level database for the session is determined and applied.
When supplying an entire SQL statement in the second parameter to fetch key values, prefix object names with “$dbname” if you want that replaced in the same manner.

Do not execute this procedure inside a transaction as that will negate the purpose!

See the DOCUMENT section at the end of the procedure listing for examples of usage.

Should the need arise, a very similar procedure for mass UPDATE could be coded with one extra parameter containing the SET clause.

sp_dbdelete.sql

Copy to Clipboard

EXECUTE PROCEDURE ifx_allow_newline('t');
DROP FUNCTION IF EXISTS sp_dbdelete;

CREATE FUNCTION sp_dbdelete
(
    p_table  VARCHAR(128), -- name of table containing rows to delete
    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 delete statement
)
    RETURNING
        INT AS selected,
        INT AS deleted,
        INT AS seconds;

-- SPL alternative to "dbdelete" (see utils_ak by Art Kagel at www.iiug.org)
    -- Safe mass delete avoiding "long transaction aborted" and excessive locks
    -- Doug Lawry, Oninit Consulting Ltd, October 2017

DEFINE l_dbname   VARCHAR(128);
    DEFINE l_sql      LVARCHAR(4000);
    DEFINE l_rowid    INT8;
    DEFINE l_selected INT;
    DEFINE l_deleted  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_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_deleted  = 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_deleted =
                    l_deleted + 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 =
                'DELETE ' ||
                ' FROM '  || p_table  ||
                ' 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_deleted,
        l_seconds;

END FUNCTION

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

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

INSERT INTO delete_test (tabname)
    SELECT tabname FROM systables;

EXECUTE FUNCTION oninit:sp_dbdelete
    (
        'delete_test',
        'WHERE serial_no <= 50'
    );

EXECUTE FUNCTION oninit:sp_dbdelete
    (
        'delete_test',
        'SELECT serial_no FROM $dbname:delete_test',
        'serial_no'
    );

DROP TABLE delete_test;
";

dbload.sh

Copy to Clipboard

Conclusion

Excessively large transactions are a menace. The Informix stored procedure for mass delete tool can help to avoid them and stored procedure sp_dbdelete is a useful addition to the arsenal so that millions of rows can be safely deleted in one operation inside any SQL session.

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.