Updated November 2021

Abstract

If an Informix instance warns that one of your temp 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, which works with all Informix Dynamic Server (IDS) versions, lists objects registered in the “sysmaster” database as being located in temp dbspaces:

Copy to Clipboard

Actual sample output is:

ownerdatabasetabledbspacelife_timesize_kb
dougstores_demodoug_temp_1tempdbs0 00:00:242012
dougstores_demodoug_temp_2tempdbs0 00:00:24768

Utilisation by each object as a percentage of the dbspace

We had an interesting question from Pravin Bankar who asked how to extend the query to present utilization by each object as a percentage of the dbspace. This was the proposed approach:

Copy to Clipboard

The above solutions primarily use view “systabinfo” with underlying pseudo-table “sysptnhdr”. From IDS 12 onwards, that table – but not the view – has an additional column “sid” populated for temporary tables with the ID of the creating session. That was included in standard view “syssessiontempspaceusage” from IDS 14.10.FC6 which exposed the existence of the new column. We have used that in the following improved solution. We also now believe that a view is more practical:

Copy to Clipboard

SQL to use that might be:

Copy to Clipboard

Actual sample output follows:

ownerdatabasetabledbspacelife_timesize_kbsidpidhostnamepercent
dougstores_demodoug_temp_1tempdbs0 00:02:0427572280-1172.17.0.127.57
dougstores_demodoug_temp_2tempdbs0 00:02:048334280-1172.17.0.18.33

Note that JDBC clients present “-1” instead of an actual process ID.

We will be requesting that AGS include session details where possible in the Server Studio “Temp Table Usage” report.

Conclusion

Rapid access to this information is possible via SQL, and can help identify which applications are filling temp dbspaces.

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

Disclaimer

Suggestions above are 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.

About the Author: