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
```

To determine the temporary table space size required, use the following table.

Page size of temporary table space	Row size limit	Column count limit
4K	4 005	500
8K	8 101	1 012
16K	16 293	1 012
32K	32 677	1 012

A temporary table space can support row sizes up to 1048319 bytes, if the temporary table contains at least one varying length string column (VARCHAR, VARBINARY, or VARGRAPHIC) and the extended_row_sz database configuration parameter is set to ENABLE.