How to list temp dbspace contents

Updated November 2021

Abstract

If an Informix instance warns that one of your temp dbspaces is full, it is useful to have SQL quickly to list what it contains, or to be run automatically if dbspace usage goes over a maximum acceptable percentage. Such a “sysmaster” query is provided in this article.

Content

The following SQL, which works with all Informix Dynamic Server (IDS) versions, lists objects registered in the “sysmaster” database as being located in temp dbspaces:

Copy to Clipboard

Actual sample output is:

owner	database	table	dbspace	life_time	size_kb
doug	stores_demo	doug_temp_1	tempdbs	0 00:00:24	2012
doug	stores_demo	doug_temp_2	tempdbs	0 00:00:24	768

Utilisation by each object as a percentage of the dbspace

We had an interesting question from Pravin Bankar who asked how to extend the query to present utilization by each object as a percentage of the dbspace. This was the proposed approach:

Copy to Clipboard

The above solutions primarily use view “systabinfo” with underlying pseudo-table “sysptnhdr”. From IDS 12 onwards, that table – but not the view – has an additional column “sid” populated for temporary tables with the ID of the creating session. That was included in standard view “syssessiontempspaceusage” from IDS 14.10.FC6 which exposed the existence of the new column. We have used that in the following improved solution. We also now believe that a view is more practical:

Copy to Clipboard

CREATE VIEW view_temp_tables
(
    owner,
    database,
    table,
    dbspace,
    life_time,
    size_kb,
    sid,
    pid,
    hostname,
    percent
)
AS
SELECT
    q1.*,
    (100 * size_kb / dbs_size)
        :: DECIMAL(5,2) AS percent
FROM
(
    SELECT
        t2.owner [1,8],
        t2.dbsname [1,18] AS database,
        t2.tabname [1,22] AS table,
        t3.name [1,10] AS dbspace,
        (CURRENT - DBINFO('utc_to_datetime', t1.created))
            :: INTERVAL DAY(4) TO SECOND AS life_time,
        (t1.nptotal * t1.pagesize/1024)
            :: INT AS size_kb,
        t4.sid,
        t4.pid,
        t4.hostname [1,20]
    FROM sysmaster:sysptnhdr AS t1
    JOIN sysmaster:systabnames AS t2 ON t2.partnum = t1.partnum
    JOIN sysmaster:sysdbspaces AS t3 ON t3.dbsnum = TRUNC(t1.partnum/POWER(2,20))
    JOIN sysmaster:syssessions AS t4 ON t4.sid = t1.sid
    WHERE t1.sid != DBINFO('SESSIONID') -- exclude current session
) AS q1,
(
    SELECT
        d2.name AS dbspace,
        SUM(d1.chksize * d1.pagesize/1024) AS dbs_size
    FROM sysmaster:syschunks AS d1
    JOIN sysmaster:sysdbspaces AS d2 ON d1.dbsnum = d2.dbsnum
    GROUP BY 1
) AS q2
WHERE q1.dbspace = q2.dbspace;

SQL to use that might be:

Copy to Clipboard

Actual sample output follows:

owner	database	table	dbspace	life_time	size_kb	sid	pid	hostname	percent
doug	stores_demo	doug_temp_1	tempdbs	0 00:02:04	27572	280	-1	172.17.0.1	27.57
doug	stores_demo	doug_temp_2	tempdbs	0 00:02:04	8334	280	-1	172.17.0.1	8.33

Note that JDBC clients present “-1” instead of an actual process ID.

We will be requesting that AGS include session details where possible in the Server Studio “Temp Table Usage” report.

Conclusion

Rapid access to this information is possible via SQL, and can help identify which applications are filling temp dbspaces.

If you have any questions or would like to find out more about listing DBspaces and Informix, please contact us.

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.

Updated November 2021

Abstract

Content

Conclusion

Disclaimer

About the Author:

Related Technical Articles:

Share this story: