Identify Temporary Tables

Temporary tables are created in the OnLine system either explicitly or implicitly. Explicit temporary tables are created with the CREATE TEMP TABLE or the SELECT … INTO TEMP … command variants in SQL. Implicit temporary tables are created by the engine in the process of executing certain queries. None of these temporary tables ever receives an entry in any of the system tables, and thus it is difficult to see exactly what is going on with temporary tables.

These temporary tables are stored in the rootdbs dbspace unless they have been explicitly created in another dbspace. The rootdbs is always the first dbspace in an OnLine system, and its dbspace number is 1. Since the high-order bit of the tblnum out put in tbstat -t represents the number of the dbspace, all default temporary tables will have a tblnum beginning with 1.

For versions of OnLine greater than 3.3, sorting is no longer done in the rootdbs. In these later versions, sorting is done in the directory identified by the $DBTEMP variable, in /tmp if $dbtemp is not set, or in the directories specified in $PSORT_DBTEMP if you are using the parallel sorting option, PSORT. The only table that should normally reside in the rootdbs is the tablespace tablespace, which is identified with a final 1 in the tblnum. Thus 1000001 identifies the only non-temp table in the rootdbs in the default installation. Any other tables beginning with 1 should be temporary tables.

This is assuming that you are not placing ordinary databases and tables in the rootdbs. For maximum flexibility you should not be placing data in the rootdbs. The rootdbs is the only dbspace that cannot be dropped. If you have your databases located other than in rootdbs, you can always rebuild the dbspace on a larger device should your needs change. If you have tables in rootdbs, you lose this flexibility. As long as you have your initial three logfiles in rootdbs and as long as your physical logfile is in rootdbs, you probably don't want to add to the I/O load there anyway.

IDS allows you to designate the location for your temporary files. In many cases, it will make sense to place your temporary files in a separate dbspace custom designed to hold the temporary tables. Again, for flexibility, having them in anywhere but rootdbs will give you more options. If you are not yet on 6.0, you may want to go ahead and set up a dbspace for temporary files and build your explicit temporary files with the IN DBSPACE option to place them in the correct dbspace. This will allow you to have a somewhat easier transition to 6.0.

Temporary tables are deleted when the SQL statement that has created them is finished or when the database initializes. When a system crash occurs, the SQL statements do not complete and temporary tables can remain. It is possible to initialize OnLine with a tbinit -r command that tells the engine not to bother removing temporary tables. This may make the database initialization somewhat faster, but it runs the risk of leaving temporary tables lying around when they are not in use. I know of no way to actually use the data that's in these zombie tables.