IBM Support

Tablespace TDA files not being deleted and are filling up the filesystem

Technote (troubleshooting)


Problem(Abstract)

The temporary tablespace is creating TDA files in the tablespace directory and they are filling up the filesystem.

Symptom

TDA files being created by the system temp tablespaces and filling up the file system/disk


Cause

Some query statements require temporary tables for processing such as a work file for sorting operations that cannot be done in memory (sortheap). These temporary tables require disk space and the amount of space required is dependent upon the size, number, and nature of the queries, as well as the size of the returned tables. Normally, a process which creates a .TDA file will truncate the file to 0 length once it is done using it., however sometimes the process may not have the chance to do so due to some uncommon reasons.

The information within the .TDA file, though, is of no real use once the process using it is terminated, they are only useful during the lifetime in which they would be used.
Also, these files are not allocated in fixed sizes. They are normally generated during sort time, since we normally do the sorting inside the sortheap, if the sort heap is not big enough then we will create a temp table in the bufferpool to hold the sorted/unsorted data. If during the sorting some of these pages in the bufferpool are victimized, then they will reside in the TDA files. These files are not removed during runtime (while the database is active) once the usage is complete to avoid the overhead of deleting and recreating them..

Resolving the problem

The file size can be forced to be reduced to 0 bytes using these DB2 Registry Variables:
DB2_SMS_TRUNC_TMPTABLE_THRESH=0
and DB2_OPT_MAX_TEMP_SIZE to limit the amount of temporary table space used by queries

A database deactivate/activate will also clear these files.

Related information

Temporary Tablespaces
Space requirements for tables

Document information

More support for: DB2 for Linux, UNIX and Windows
Database Objects/Config - Tablespaces

Software version: 9.1, 9.5, 9.7, 9.8, 10.1, 10.5

Operating system(s): AIX, HP-UX, Linux, Solaris, Windows

Reference #: 1654258

Modified date: 07 December 2013


Translate this page: