Informix stored procedure to list open tables

Abstract

Some Informix Dynamic Server (IDS) operations, such as altering a table schema, are not allowed if its partition is open in another session, which will not necessarily appear as a lock with the “onstat -k” command or in the SQL described in a previous article here. The standard method to list open partitions is the “onstat -g opn” command, which is not user friendly, and requires operating system command line access. This article describes and provides a Stored Procedure Language (SPL) function to obtain a list of sessions holding matching table names open, so you know what needs to be terminated.

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.FC12W5.

The most common reason for a table to be open but not locked is when it is referenced by a cursor or prepared SQL statement in an application. This can be simulated as follows.

Leave the following running in a SQL session, which can be conveniently terminated by killing the “sleep” process:

Copy to Clipboard

At the command line, we can show that there are no locks on “customer”, but it is held open by that session:

Copy to Clipboard

The above “onstat” commands can be run by any user, and do not need configuration parameter UNSECURE_ONSTAT enabled.
Only later IDS versions include database and table names: you need “partn” from IIUG to append them for earlier versions.

Schema changes to the table will fail meanwhile:

Copy to Clipboard

Any change at the database level, such as to the logical logging mode, will also fail if any partition is open:

Copy to Clipboard

At the command line, we would need to find out what sessions were the problem by getting these in turn:

“rstcb” (userthread address) from “onstat -g opn”;
“sessid” (session ID) from “onstat -u”;
details from “onstat -g ses” and “onstat -g sql”.

An easier solution is to install the following SPL function in the database of your choice:

Copy to Clipboard

EXECUTE PROCEDURE ifx_allow_newline('t');

CREATE FUNCTION sp_open_tables
(
    p_dbsname VARCHAR(128) DEFAULT '*',
    p_tabname VARCHAR(128) DEFAULT '*'
)
RETURNING
    INTEGER                     AS session,
    INTEGER                     AS process,
    VARCHAR(32)                 AS username,
    VARCHAR(255)                AS hostname,
    VARCHAR(255)                AS progname,
    INTERVAL HOUR(5) TO SECOND  AS duration,
    VARCHAR(128)                AS dbsname,
    VARCHAR(128)                AS tabname;

DEFINE v_session    INTEGER;
    DEFINE v_process    INTEGER;
    DEFINE v_username   VARCHAR(32);
    DEFINE v_hostname   VARCHAR(255);
    DEFINE v_progname   VARCHAR(255);
    DEFINE v_duration   INTERVAL HOUR(5) TO SECOND;
    DEFINE v_dbsname    VARCHAR(128);
    DEFINE v_tabname    VARCHAR(128);
/*
    List open tables
    www.oninitgroup.com/technical-articles
    Doug Lawry, Oninit Consulting, September 2025

Example usage:

EXECUTE FUNCTION sp_open_tables();
    EXECUTE FUNCTION sp_open_tables('stores_demo');
    EXECUTE FUNCTION sp_open_tables('stores_demo','customer');
    EXECUTE FUNCTION sp_open_tables('stores_demo','systables');

Notes:

1) Requires DBA privileges when first run to create the
       external table.

2) Hex strings are cast to INT8 in SQL - which does work
       if they begin with "0x" - as user thread addresses
       can be very large and old "awk" versions have
       insufficient arithmetic precision.
*/
    ON EXCEPTION IN (-310) -- table already exists
    END EXCEPTION WITH RESUME;

CREATE EXTERNAL TABLE open_tables_ext
    (
        address VARCHAR(24), -- hex user thread
        partnum VARCHAR(24)  -- hex partition number
    )
    USING
    (
        DATAFILES ('DISK:/tmp/open_tables_ext.unl')
    );

SYSTEM '
        umask 0 # create files writable by all
        onstat -g opn | awk ''

/^rstcb/ {
                rstcb = $2
            }

/^[0-9]+ / {

if (rstcb == "") {
                    # IDS <= 11
                    f1 = $2
                    f2 = $6
                }
                else {
                    # IDS >= 12
                    f1 = rstcb
                    f2 = $4
                }

if (f2 !~ "0x")
                    f2 = "0x" f2

printf("%s|%s|\n",f1,f2)
            }

'' | sort | uniq > /tmp/open_tables_ext.unl
    ';

FOREACH

SELECT  x4.sid,
                x4.pid,
                TRIM(x4.username),
                TRIM(x4.hostname),
                TRIM(x4.progname),
                CURRENT - DBINFO('utc_to_datetime', x4.connected),
                TRIM(x1.dbsname),
                TRIM(x1.tabname)
        INTO    v_session,
                v_process,
                v_username,
                v_hostname,
                v_progname,
                v_duration,
                v_dbsname,
                v_tabname
        FROM    open_tables_ext       AS x0, -- open objects
                sysmaster:systabnames AS x1, -- object names
                sysmaster:sysptnhdr   AS x2, -- object details
                sysmaster:sysrstcb    AS x3, -- user threads
                sysmaster:sysscblst   AS x4  -- user sessions
        WHERE   x1.partnum = x0.partnum :: INT8
        AND     x2.partnum = x1.partnum
        AND     x3.address = x0.address :: INT8
        AND     x4.sid = x3.sid
        AND     x1.dbsname MATCHES p_dbsname
        AND     x1.tabname MATCHES p_tabname
        AND     x1.dbsname NOT IN
                (
                    'sysadmin',
                    'syscdr',
                    'sysmaster',
                    'sysuser',
                    'sysutils'
                )
        AND     x2.lockid = x1.partnum        -- primary table partition
        AND     x3.sid != DBINFO('sessionid') -- exclude current session
        ORDER   BY 1, 6, 7

RETURN
            v_session,
            v_process,
            v_username,
            v_hostname,
            v_progname,
            v_duration,
            v_dbsname,
            v_tabname
        WITH RESUME;

END FOREACH;

END FUNCTION

DOCUMENT '

Examples of "onstat -g opn" output (SPL function works for all these versions):

IBM Informix Dynamic Server Version 11.50.FC7GE -- On-Line -- Up 30 days 16:10:14 -- 2288400 Kbytes
    tid  rstcb              isfd  op_mode    op_flags   partnum    ucount ocount lockmode
    6150759 0x07000000510bdf08 0     0x00000400 0x00000317 0x014010c5 2      2      0
    6150759 0x07000000510bdf08 1     0x00000002 0x00000003 0x014010c5 2      2      0
    6150759 0x07000000510bdf08 2     0x00000400 0x00000397 0x001000f2 4      4      0
    6150759 0x07000000510bdf08 3     0x00000002 0x00000003 0x001000f2 4      4      0
    6150759 0x07000000510bdf08 4     0x00000400 0x00000397 0x001000f2 4      4      0
    6150759 0x07000000510bdf08 5     0x00000002 0x00000003 0x001000f2 4      4      0
    6150759 0x07000000510bdf08 6     0x00000400 0x00004407 0x0000001a 1      0      0

IBM Informix Dynamic Server Version 12.10.FC13XJWE -- On-Line -- Up 82 days 15:12:14 -- 6877376 Kbytes
    rstcb 0x461dec68 tid 31
    isfd  op_mode    op_flags   partnum    ucnt ocnt lk ra   partp          ocnt ucnt
    0     0x70000    0x403      0x100004   1    0       0    0x49b1ba60     2    3
    1     0x70000    0x403      0x100005   1    0       0    0x4956c970     0    1
    2     0x70000    0x403      0x100006   1    0       0    0x49576028     0    1
    3     0x70000    0x403      0x100007   1    0       0    0x4956c028     0    1
    4     0x70000    0x403      0x100008   1    0       0    0x49571028     0    1
    5     0x70000    0x403      0x100013   1    0       0    0x49579a98     0    1

IBM Informix Dynamic Server Version 14.10.FC8IE -- On-Line -- Up 10 days 02:58:29 -- 998696 Kbytes
    rstcb 0x4755d328 tid 28
    isfd  op_mode    op_flags   partnum    ucnt ocnt lk ra   partp          ocnt ucnt name
    0     0x70000    0x403      0x8000c2   1    0       0    0x48311028     0    1    oninit:informix.sysindices
    1     0x70000    0x403      0x100168   1    0       0    0x481fbb40     0    1    sysutils:informix.sysindices
    2     0x70000    0x403      0x80006b   1    0       0    0x482d06f0     1    2    sentinel:informix.systables
    3     0x70000    0x403      0x8001c8   1    0       0    0x483ea028     0    1    sysadmin:informix.ix_ph_alert_01
    4     0x70000    0x403      0x80006d   1    0       0    0x482d1ad0     0    1    sentinel:informix.sysindices
    5     0x70000    0x403      0x8001c9   1    0       0    0x483e67f0     0    1    sysadmin:informix.ix_ph_alert_02

IBM Informix Dynamic Server Version 14.10.FC12W5DE -- On-Line -- Up 2 days 01:17:49 -- 327812 Kbytes
    rstcb 0x45bc5118 tid 3686
    isfd  op_mode    op_flags   partnum    ucnt ocnt lk ra   partp          ocnt ucnt name
    0     400        317        500002     2    2       1    0x482c4630     1    1    hq:informix.systables
    1     2          3          500002     2    2       0    0x482c4630     1    1    hq:informix.systables
    2     1000402    403        500057     1    0    X  0    0x483fd028     0    1    hq:informix.s_1_os_memory
    3     1000442    403        500058     1    0    X  0    0x483fd630     0    1    hq:informix.s_1_os_memory_idx
    4     1000402    403        50006d     1    0    X  0    0x48431a60     0    1    hq:informix.s_1_os_networkio
    5     1000442    403        50006e     1    0    X  0    0x48435028     0    1    hq:informix.s_1_os_networkio_idx

';

The above does this:

Runs “onstat -g opn” piped to an embedded “awk” script which converts the output into a pipe-delimited file.
Reads that file via an external table and joins it to “sysmaster” tables to get session details.
Returns the data set.

As it says in the comments at the top, it must be executed first time by a user with DBA privileges, so that the external table can be created.

The documentation page for “onstat -g opn” has this understatement: “The output might change over time and depends on your product version or fix pack.” IDS versions from 11.50 have been tested successfully. See the DOCUMENT section for example outputs from IDS 11.50 to 14.10. Earlier versions might well work but have not been tested. The embedded “awk” code does deal with these differences.

System databases are excluded. Also, only table names are returned, not index names. It is not plausible that an index might be open without the table, and table names are sufficient to determine conflicting sessions.

You can optionally specify a match on database and table name. In our first scenario, specify the table you need to alter, for example:

Copy to Clipboard

session	process	username	hostname	progname	duration	dbsname	tabname
90	2954	informix	dd2cf86eb3ef	/opt/ibm/IDS/14.10.FC12W5/bin/dbaccess	00:00:24	stores_demo	customer

In the second scenario, specify the database that we need to change the logical logging mode, and “systables” as that always seems to be open if anything else is:

Copy to Clipboard

session	process	username	hostname	progname	duration	dbsname	tabname
90	2954	informix	dd2cf86eb3ef	/opt/ibm/IDS/14.10.FC12W5/bin/dbaccess	00:00:24	stores_demo	systables

Caveats

Run time might be several minutes on busier systems with older IDS versions.
The code would need some changes to the SYSTEM statement for Windows.

Conclusion

Determining what is preventing you from performing table or database maintenance is challenging using only standard commands. If you can install and execute this SPL function the first time as a DBA user, others without command line access can thereafter use it to find out what they need using a simple SQL statement.

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.