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.

You can actually see individual temporary tables in db2top, on the tables screen:
db2top_temp_tables

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

The result set for that looks something like this:
Queries_by_temp

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.

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *