DB2 Temporary Table Spaces
What is Temp Space in Db2?
Db2 needs some temporary space on disk to perform some actions. Commonly we think of these actions as primarily sort and join operations. These are operations where Db2 will first use the SORTHEAP memory area, but some data sets are just too big to fit in these locations. Even when an operation spills to temporary space, it may technically still occur in memory – just in the buffer pool for the temporary tablespace. Any type of data manipulation may require these sorts of operations. Db2 uses a system temporary table space for this kind of spill. There is also another type of temporary table space called a user temporary table space
Types of Temp Space
System Temporary Space
At least one temporary table space must exist. This is created by default on any
CREATE DATABASE command, and by default is called TEMPSPACE1. It is created with the default page size for the database. Generally a database should have a temporary table space for each page size in use in a database, and should always have a system temporary table space with a 32 K page size. The Db2 optimizer will usually choose the table space that both has a page size that is large enough to hold at least one row on a single page, and also the one that has a buffer pool that will fit the most rows for the interim result set. This helps to ensure that memory is used as much as possible and whenever possible to avoid the slowness of actually spilling to disk.
Db2 may recognize ahead of time that a data set will not fit in sortheap and intentionally spill to a system temporary table space, or it may realize while executing the query that the data exceeds the memory available and spill to a system temporary table space.
Offline reorgs may also specify a temporary table space to use when rebuilding a table. However, if a temporary table space is used, it may require more overall disk space to accomplish the reorg than it does if the reorg is just allowed to use the existing table space for the reorg.
There are very few situations in which it makes sense to have more than one system temporary table space with the same page size.
System temporary table spaces can make use of storage groups. If you use automatic storage for table spaces like this, a separate storage group is a good idea. Temporary table spaces cannot be moved from one storage group to another. Additionally, if paths are added to a storage group containing system temporary table spaces, the system temporary table spaces will not make use of them until the next database activation.
While SMS table spaces have been deprecated for user data, they are still alive and well for temporary data (and the system catalog). In fact, if you create a system temporary table space on a storage group managed by AST, it will be SMS under the covers. By contrast, for user data, AST uses DMS. SMS makes the most sense because they grow and shrink as needed. System temporary table spaces often spike in utilization and then go back down. I have tended to think of AST as a kind of wrapper for DMS, but in this case it’s a wrapper for SMS.
Here is an example showing the creation of a system temporary table space using AST, and showing Db2 using SMS. This is from Db2 10.5 on AIX:
$ db2 "CREATE USER TEMPORARY TABLESPACE TEMPSPACE_32 IN DATABASE PARTITION GROUP IBMCATGROUP PAGESIZE 32768 MANAGED BY AUTOMATIC STORAGE USING STOGROUP STGTEMP EXTENTSIZE 4 PREFETCHSIZE AUTOMATIC BUFFERPOOL TEMP32_BP OVERHEAD INHERIT TRANSFERRATE INHERIT" DB20000I The SQL command completed successfully. $ db2 list tablespace containers for 17 show detail Tablespace Containers for Tablespace 17 Container ID = 0 Name = /DB2/temp/MYDB/T000/inst/NODE0000/MYDB/T0000017/C0000000.TMP Type = Path Total pages = 1 Useable pages = 1 Accessible = Yes Container ID = 1 Name = /DB2/temp/MYDB/T001/inst/NODE0000/MYDB/T0000017/C0000001.TMP Type = Path Total pages = 1 Useable pages = 1 Accessible = Yes
User Temporary Space
Some temporary tables are not created by Db2 to handle joins or sorts, but are instead created explicitly by users. These may be used to store interim results or data in non-recoverable ways. The definitions for these temporary tables may persist in the database or they may only be there for the session of the user creating them. There are some tools and administrative commands that require a user temporary table space with a 32 K page size.
Too Much Temp Space
Particularly in Analytics/Data Warehouse environments, a large amount of temporary space may be required. In fact, for these types of environments, you may want to use your fastest storage for temp space. It is entirely possible that a Cartesian product or other mistake will fill up any disk you may have available for the system temporary table spaces. This is one reason why it may make sense to have temp space on its own file system. This way when someone fills it up with a bad query, it doesn’t impact database availability. In any case, you want to keep an eye on whatever disk system temporary table spaces reside on to look for upward trends before they become failures. If a query runs out of temp space, it will fail and rollback, and the temp space will be released.
Identifying Statements Using Temp Space
In troubleshooting over-usage of temporary table spaces, you can analyze the package cache to find the statements using the most temporary space. Keep in mind that analysis over time is needed, and you may need to collect this data fairly close to the time when the usage of temporary space is high, so you can catch the statements before they pass out of the package cache. Here’s a statement I’ve used for this:
WITH SUM_TAB (SUM_RR, SUM_CPU, SUM_EXEC, SUM_SORT, SUM_NUM_EXEC, SUM_TMP_READS) AS ( SELECT FLOAT(SUM(ROWS_READ))+1, FLOAT(SUM(TOTAL_CPU_TIME))+1, FLOAT(SUM(STMT_EXEC_TIME))+1, FLOAT(SUM(TOTAL_SECTION_SORT_TIME))+1, FLOAT(SUM(NUM_EXECUTIONS))+1, FLoat(SUM(POOL_TEMP_DATA_L_READS+POOL_TEMP_XDA_L_READS+POOL_TEMP_INDEX_L_READS)) +1 FROM TABLE(MON_GET_PKG_CACHE_STMT ( 'D', NULL, NULL, -2)) AS T ) SELECT INSERT_TIMESTAMP, STMT_TEXT, POOL_TEMP_DATA_L_READS+POOL_TEMP_XDA_L_READS+POOL_TEMP_INDEX_L_READS TMP_READS, DECIMAL(100*(FLOAT(POOL_TEMP_DATA_L_READS+POOL_TEMP_XDA_L_READS+POOL_TEMP_INDEX_L_READS)/SUM_TAB.SUM_TMP_READS),5,2) AS PCT_TOT_TMP, ROWS_READ, DECIMAL(100*(FLOAT(ROWS_READ)/SUM_TAB.SUM_RR),5,2) AS PCT_TOT_RR, TOTAL_CPU_TIME, DECIMAL(100*(FLOAT(TOTAL_CPU_TIME)/SUM_TAB.SUM_CPU),5,2) AS PCT_TOT_CPU, STMT_EXEC_TIME, DECIMAL(100*(FLOAT(STMT_EXEC_TIME)/SUM_TAB.SUM_EXEC),5,2) AS PCT_TOT_EXEC, TOTAL_SECTION_SORT_TIME, DECIMAL(100*(FLOAT(TOTAL_SECTION_SORT_TIME)/SUM_TAB.SUM_SORT),5,2) AS PCT_TOT_SRT, NUM_EXECUTIONS, DECIMAL(100*(FLOAT(NUM_EXECUTIONS)/SUM_TAB.SUM_NUM_EXEC),5,2) AS PCT_TOT_EXEC, DECIMAL(FLOAT(STMT_EXEC_TIME)/FLOAT(NUM_EXECUTIONS+1),10,2) AS AVG_EXEC_TIME FROM TABLE(MON_GET_PKG_CACHE_STMT ( 'D', NULL, NULL, -2)) AS T, SUM_TAB ORDER BY TMP_READS DESC FETCH FIRST 20 ROWS ONLY WITH UR
Ah, the irony – the first statement in this particular result set is my query to find statements using temp space! This is because I’m using a structure called a Common Table Expression (CTE), which is actually a temporary table that exists only for the duration of this query.
By looking at these results over time, you may be able to help identify problem queries when it comes to system temporary space utilization.