If an Informix instance warns that one of your temp your 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.


The following SQL lists all objects registered in the sysmaster database as being located in temp dbspaces:

Copy to Clipboard

Actual sample output from the Oninit call logging system is:

Copy to Clipboard

Utilisation by each object as a percentage of the dbspace

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:

Copy to Clipboard


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


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.

Contact us

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

Contact us

About the Author: