Updated November 2021
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.
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:
Actual sample output is:
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:
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:
SQL to use that might be:
Actual sample output follows:
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.
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.
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.