Temporary tablespaces

The system temporary tablespace and user temporary tablespace were created for you. These tablespaces were created by using the sample statements that are provided in this topic.

System temporary tablespace

When the core warehouse database is created, the DB2® software creates one system temporary tablespace called TEMPSPACE1 by default. In an IBM® PureData® System for Operational Analytics environment, this tablespace is dropped because it is an SMS (system managed space) tablespace and it uses the default buffer pool.

The system temporary space is created as a DMS (database managed space) tablespace using containers on the /db2fs/bcuaix/NODE000N file systems on the flash storage nodes.

The following sample statement created a new DMS system temporary tablespace with containers on the flash storage nodes, and then dropped the SMS tablespace TEMPSPACE1:

CREATE TEMPORARY TABLESPACE temp16k 
 IN DATABASE PARTITION GROUP ibmtempgroup
 PAGESIZE 16384 
 MANAGED BY DATABASE 
 USING (FILE '/db2fs/bcuaix/NODE000 $N/BCUDB/temp16k' $dmsspace) 
 ON DBPARTITIONNUMS (0 to 9) 
 USING (FILE '/db2fs/bcuaix/NODE00 $N/BCUDB/temp16k' $dmsspace) 
 ON DBPARTITIONNUMS (10 to 99) 
 USING (FILE '/db2fs/bcuaix/NODE0 $N/BCUDB/temp16k' $dmsspace) 
 ON DBPARTITIONNUMS (100 to $num_of_data_partitions) 
 EXTENTSIZE 16
 BUFFERPOOL bp_16k
 NO FILE SYSTEM CACHING
 OVERHEAD 4.0
 TRANSFERRATE 0.04;

 DROP TABLESPACE TEMPSPACE1;

where $dmsspace represents the size of each temporary tablespace container on each file system, and $num_of_data_partitions represents the number of database partitions on your system.

The size needed for the system temporary tablespace depends on the database workload. As a starting point, specify the system temporary tablespace to be as large as the largest table. You can limit the growth of the system temporary tablespace to a maximum size by specifying the MAXSIZE option.

User temporary tablespace

Use the following statement as a model when creating a user temporary tablespace:

CREATE USER TEMPORARY TABLESPACE usertemp16k IN DATABASE PARTITION GROUP pdpg
		BUFFERPOOL bp_16k
		OVERHEAD 4.0 
    TRANSFERRATE 0.04;

Guidance on system and user temporary tablespaces IBM PureData System for Operational Analytics V1.1 environments

Temporary tablespaces are subject to heavy usage. To improve the performance of your temporary tablespaces there is guidance to use 4 containers per partition for all tablespaces; this is especially true for temporary tablespaces. It is therefore recommended that all customers use the following DDL for system temporary tablespaces.

CREATE TEMPORARY TABLESPACE temp16k IN DATABASE PARTITION GROUP IBMTEMPGROUP
		PAGESIZE 16384
		MANAGED BY DATABASE
		USING (FILE   '/db2fs/bcuaix/NODE $4N /bcudb/temp16k1' 68 G,
			FILE   '/db2fs/bcuaix/NODE $4N /bcudb/temp16k2' 68 G,
			FILE   '/db2fs/bcuaix/NODE $4N /bcudb/temp16k3' 68 G,
			FILE   '/db2fs/bcuaix/NODE $4N /bcudb/temp16k4' 68 G)
		EXTENTSIZE 16
		BUFFERPOOL BP_16K
		OVERHEAD 4.0
		NO FILE SYSTEM CACHING
		TRANSFERRATE 0.04