Listing temp dbspace contents

Abstract

If an Informix instance sometimes warns that a temp dbspace 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 lists all objects registered in the sysmaster database as being located in temp dbspaces:

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', ti_created))
            :: INTERVAL DAY(4) TO SECOND AS life_time,
        (ti_nptotal * ti_pagesize/1024)
            :: INT AS size_kb
FROM   systabinfo   AS t1,
       systabnames  AS t2,
       sysdbspaces  AS t3
WHERE  t2.partnum = ti_partnum
AND    t3.dbsnum = TRUNC(t2.partnum/1024/1024)
AND    TRUNC(MOD(ti_flags,256)/16) > 0
ORDER  BY 6 DESC, 5 DESC

Actual sample output from the Oninit call logging system is:

owner    database   table        dbspace    life_time    size_kb
apache   calls      _temptable   temp3dbs   0 00:29:37        16
apache   calls      _temptable   temp1dbs   0 00:29:28        16

Conclusion

Rapid access to this information can help identify which applications are filling temp dbspaces.


Disclaimer

The code fix suggested above is 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.

Comments

We’ve had an interesting question from Pravin Bankar. Pravin has asked how to extend the query to present utilization by each object as a percentage of the dbspace. Here’s a proposed approach:

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', ti_created))
           :: INTERVAL DAY(4) TO SECOND AS life_time,
        (ti_nptotal * ti_pagesize/1024)
            :: INT AS size_kb
    FROM
        systabinfo AS t1,
        systabnames AS t2,
        sysdbspaces AS t3
    WHERE t2.partnum = ti_partnum
    AND t3.dbsnum = TRUNC(t2.partnum/1024/1024)
    AND TRUNC(MOD(ti_flags,256)/16) > 0
) AS q1,
(
    SELECT
        name AS dbspace,
        SUM(chksize * d1.pagesize/1024) AS dbs_size
    FROM
        syschunks AS d1,
        sysdbspaces AS d2
    WHERE d1.dbsnum = d2.dbsnum
    GROUP BY 1
) AS q2
WHERE q1.dbspace = q2.dbspace
ORDER BY 6 DESC, 5 DESC;