How to find out the temporary tables that are currently created

Problem(Abstract)

This document explains how you can use a SQL statement to find out the temporary tables that are currently created

Resolving the problem

INTRODUCTION

You can use a SQL statement to obtain information regarding each of the temporary tables that are currently created.


STEPS

You can run the following SELECT statement from any application:

SELECT t.tabname ,t.dbsname,t.owner,
       DECODE(d.is_logging,1,"Y","N") AS db_with_log,
       s.name AS dbspace,
       DBINFO("UTC_TO_DATETIME",ti_created) AS created,
       DECODE(hex(mod(ti_flags,256)/16),6,"Y","N") AS
       table_using_log,ti_npused AS num_usedpages,
       ti_nptotal AS num_pages
FROM   sysmaster:systabnames t, sysmaster:systabinfo i,
       sysmaster:sysdbspaces s, sysmaster:sysdatabases d
WHERE  t.partnum=ti_partnum AND
       d.name=t.dbsname AND
       s.dbsnum=TRUNC(t.partnum/1048576) AND
       hex(mod(ti_flags,256)/16) IN ( 6,2 )

Column​​	Description​​
tabname​​	The name of the temporary table​​
dbsname​​	The name of the database where the temporary table was created​​
owner​​	The name of the user that created the temporary table​​
db_with_log​​	If the database is not currently using logging a ​​N​​ will be added to this column. And a ​​Y​​ will be added if the database is using logging.​​
dbspace​​	Name of the dbspace where the temporary table is created​​
created​​	The date and time when the temporary table was created​​
table_using_log​​	If the temporary table is using logging a ​​Y​​ will be added to this column. And a ​​N​​ will be added if the temporary table is not using logging.​​
num_usedpages​​	Number of pages current in use by the temporary table​​
num_pages​​	Total number of pages allocated for the temporary table​​

Example:

This example shows two temporary tables created on May 23rd, 2005. The temp1 table was created without the WITH NO LOG option. The temp2 table was created using the WITH NO LOG option. 

tabname          temp1
dbsname          stores_demo
owner            informix
db_with_log      Y
dbspace          rootdbs
created          2005-05-23 16:44:24
table_using_log  Y
num_usedpages    1
num_pages        8

tabname          temp2
dbsname          stores_demo
owner            informix
db_with_log      Y
dbspace          rootdbs
created          2005-05-23 16:44:24
table_using_log  N
num_usedpages    1
num_pages        8

COMMON PROBLEMS

Symptom: After you execute the SELECT previously mentioned, the following message is returned:

No rows found.

Resolution: It means that no temporary table has been created.

Symptom: After you execute the SELECT previously mentioned, all the temporary tables have thetable_using_log column with a N value, and the db_with_log column has a N value.

Resolution: The symptom described above is expected since all temporary tables created in a database with no logging, are also created as no logging tables.

https://www-304.ibm.com/support/docview.wss?uid=swg21174524