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:
SELECT t2.owner [1,8], t2.dbsname [1,18] AS database, t2.tabname [1,22] AS table, t3.name [1,10] AS dbspace, (CURRENT - DBINFO('utc_to_datetime', ti_created)) :: INTERVAL DAY(4) TO SECOND AS life_time, (ti_nptotal * ti_pagesize/1024) :: INT AS size_kb FROM systabinfo AS t1, systabnames AS t2, sysdbspaces AS t3 WHERE t2.partnum = ti_partnum AND t3.dbsnum = TRUNC(t2.partnum/1024/1024) AND TRUNC(MOD(ti_flags,256)/16) > 0 ORDER BY 6 DESC, 5 DESC
Actual sample output from the Oninit call logging system is:
owner database table dbspace life_time size_kb apache calls _temptable temp3dbs 0 00:29:37 16 apache calls _temptable temp1dbs 0 00:29:28 16
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.