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.
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:
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:
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.