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