DB2 Version 10.1 for Linux, UNIX, and Windows

Creating temporary table spaces

Temporary table spaces hold temporary data required by the database manager when performing operations such as sorts or joins, since these activities require extra space to process the results set. You create temporary table spaces using a variation of the CREATE TABLESPACE statement.

About this task

A system temporary table space is used to store system temporary tables. A database must always have at least one system temporary table space since system temporary tables can only be stored in such a table space. When a database is created, one of the three default table spaces defined is a system temporary table space called "TEMPSPACE1". You should have at least one system temporary table space of each page size for the user table spaces that exist in your database, otherwise some queries might fail. See Table spaces for system, user and temporary data for more information.

User temporary table spaces are not created by default when a database is created. If your application programs need to use temporary tables, you must create a user temporary table space where the temporary tables will reside. Like regular table spaces, user temporary table spaces can be created in any database partition group other than IBMTEMPGROUP. IBMDEFAULTGROUP is the default database partition group that is used when creating a user temporary table.

Restrictions

For system temporary table spaces in a partitioned environment, the only database partition group that can be specified when creating a system temporary table space is IBMTEMPGROUP.

Procedure

To create a system temporary table space in addition to the default TEMPSPACE1, use a CREATE TABLESPACE statement that includes the keywords SYSTEM TEMPORARY. For example:
```
   CREATE SYSTEM TEMPORARY TABLESPACE tmp_tbsp       
     MANAGED BY SYSTEM       
     USING ('d:\tmp_tbsp','e:\tmp_tbsp') 
```
To create a user temporary table space, use the CREATE TABLESPACE statement with the keywords USER TEMPORARY. For example:
```
   CREATE USER TEMPORARY TABLESPACE usr_tbsp       
     MANAGED BY AUTOMATIC STORAGE
```