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:

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

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.