Move TSM DB2 tempspace to a separate filesystem

TSM can crash unexpectedly.
The DB2 logs indicated that the tempspace ran out of space due to heavy SQL transaction, suspected at the time to be a custom data collection script for TSM monitoring, although not confirmed.
This can now be reproduced using this SQL query: select node_name,volume_name from contents where node_name='<NODENAME>'

TSM creates the tempspace and the rest of the tablespaces are on the same filesystems.
This means that if any database transactions required more than the fixed space, they would fail because of a filesystem full condition rather than the heavy SQL transaction being automatically killed. Subsequently the TSM server crashes.

The enhancement request is to put the tempspace in a separate filesystem. Then if any database transaction caused the temp space to become full, only that transaction would fail, and the TSM server should not crash, and in addition no other tablespaces would be affected.

This enhancement would, ideally, be available for an existing TSM database (after upgrade, if neccessary) as well as for new installations.

Implicit in this request is a revision to the calculation of the size of a TSM database to separately calculate the amount of space needed for the tempspace. There would also be a requirement for some monitoring of the tempspace (e.g. tempspace exceeds XX%)


In DB2 v9.7 with automatic storage you can create this temp space as an ordinary SMS tablespace specifying a path to a filesystem separate from the other tablespaces. An alternative would be to use an automatic storage DMS tablespace as the tempspace and set a maxsize, but that have a performance hit.

In DB2 v10 you could use a storagegroup for this instead that would simplify the management of this tablespace. Then it would be SMS with automatic storage.

Dev have already captured the requirement, and have discussed it with the other candidate items for a future release.
This RFE is to document what occurred and how it can be reproduced.