Abstract
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.
Content
The following SQL lists all objects registered in the sysmaster database as being located in temp dbspaces:
Actual sample output from the Oninit call logging system is:
Utilisation by each object as a percentage of the dbspace
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.
Contact us
If you have any questions or would like to find out more about listing DBspaces and Informix, simply contact us.
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: