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.

Ember Crooks
Ember Crooks

Ember is always curious and thrives on change. She has built internationally recognized expertise in IBM Db2, spent a year working with high-volume MySQL, and is now learning Snowflake. Ember shares both posts about her core skill sets and her journey learning Snowflake.

Ember lives in Denver and work from home

Articles: 544

19 Comments

  1. Hi Ember,

    I guess we can get the same info directly by running db2pd -tcbstats which will give the application handle and then get the SQL running by that handle using db2pd -apinfo .. The benefit here is you don’t need to connect to the database.

  2. Hello Ember,
    In our prod environment, we’re currently having an ongoing issue where single application is taking more than 100GB(which is filling up the entire FS).
    Can you suggest some solution for this. And how about implementing this registry variable-> DB2_SMS_TRUNC _TMPTABLE_THRESH to zero. Can this be done online.
    —
    Many Thanks

  3. Hi Ember,
    Thanks for sharing!
    I have a very strange issue: I have the same query running on primary and secondary boxes, statistics are updated on both, but the query uses a 16K temp tablespace while in the other is using a 4K for CTE’s (confirmed with the explained output of the query) and in the case of 4K is taking almost 4x more to return the rows. Any suggestion what should I check? And why is this happening?
    Thank you!
    Jorge

    • Is the hardware on the two boxes the same? You say primary and secondary – what keeps the data on the secondary up to date? Is it HADR, Replication, or something else?

      • Hi Ember!
        Exactly the same hardware and actually we don’t have any strategy to replicate data, we process the same data in parallel on both sides.
        And exactly the same objects definition, but I noticed that Buffer Pools are set as automatic and in the case of Primary (where it runs faster) the size of the 16K BP assigned to 16K SysTemp tablespace is much greater than Secondary, I think we should set to a pre-defined size our BP’s and see if this way the Db2 optimizer changes the execution plan.
        Thanks for your reply!

  4. Hi Ember

    I am basically an Oracle DBA, but I have very less knowledge about DB2 and trying to learn & understand, and support. BTW since I am supporting as a secondary DB2 DBA, one of the developer posed a problem as below :
    “”I get this error when try to run this query..
    >>>SQL1585N A system temporary table space with sufficient page size does not exist.”
    I can reduce the number of columns and It works, but can the size be increased to 32K so that it can handle all the columns?””
    NAME TYPE CONTAINERS TOTAL_PGS USED_PGS FREE_PGS MAX_SZ PG_SZ
    ————— ———- —————— —————- ————— ——— —————– ———-
    TEMPSPACE32 SMS 1 1 1 0 – 32768
    TEMPSPACE1 SMS 2 2 2 0 – 4096

    How to go about? Do I have the option to allocate 32768 BYTES for TEMPSPACE1 or drop & recreate or create a new TEMPORARY TBS with 32768 BYTES and allocate it to use the new TEMP TBS to the query in question? I really appreciate if you can help me in resolving this issue.

    Thanks
    Datta

    • You likely need to create a temporary tablespace with a 32K page size. Usually it makes sense to have one temporary tablespace for each page size. (4,8,16,32). Db2 only creates one for the default page size for the database. There are all kinds of options for where this tablespace goes, but the simplest format of the create command is :
      db2 “CREATE BUFFERPOOL BUFF32K IMMEDIATE SIZE 2500 AUTOMATIC PAGESIZE 32 K”
      db2 “CREATE SYSTEM TEMPORARY TABLESPACE TEMPSYS32K PAGESIZE 32 K BUFFERPOOL BUFF32K”

  5. Hi Ember

    Thanks for your immediate response which is much appreciated!

    I tried creating one recommended by you, but I got the below error :
    aixdb3:/home/db2inst1>db2 “CREATE BUFFERPOOL BUFF32K IMMEDIATE SIZE 2500 AUTOMATIC PAGESIZE 32 K”
    DB20000I The SQL command completed successfully.
    aixdb3:/home/db2inst1>db2 “CREATE SYSTEM TEMPORARY TABLESPACE TEMPSYS32K PAGESIZE 32 K BUFFERPOOL BUFF32K”
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL20317N Automatic storage has not been defined for the database.
    SQLSTATE=55060

    I appreciate if you can let me know, what could have gone wrong. BTW, I tried executing the command on a SAMPLE database without any issues!

    Thanks

  6. Hi Ember

    I was able to figure out based on the error and I was able to create TBS “db2 alter database add storage on ‘storage_path'”.

    I am have asked the developer to test the query in question.

    Again, thanks for your help and in case if I happen to stumble, will reach out to you.

    Thanks again for your kind and immediate response.

    Regards
    Datta

  7. Hi Ember

    While waiting for the developers to test, I have a question, is it possible to make the new TEMPSYS32K (32K) default and get rid of the TEMPSPACE1 (4K ), so that the query can avail 32K size temporary tablespace?

    NAME PG_SZ
    ————— ———–
    TEMPSPACE32 32768
    TEMPSYS32K 32768
    TEMPSPACE1 4096

    I just gave a try, but could not!

    aixdb3:/home/db2inst1>db2 ‘drop tablespace TEMPSPACE1’
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0283N System temporary table space “TEMPSPACE1” cannot be dropped because
    it is the only system temporary table space with a “4K” page size in the
    database. SQLSTATE=55026

    Thanks
    Datta

    • At a minimum, you must have a temporary table space with the same page size as the default page size for the database. In this case, your default page size is probably 4K.

      I prefer to have a temporary table space for each page size. Db2 will automatically pick the page size of temp table space that it needs based on the row width of the query result set. Since temporary table spaces grow and shrink as needed, all of them can share storage, and when there is no activity, they won’t take up much space.

  8. Hi Ember

    Any update!! Is it possible to change the default tablespace 4K to 32K? If yes, please let me know as to how to go about. My only concern is that in spite of creating with a larger size, how the query in question will pick 32K? I really appreciate you expert advise on this.

    Thanks
    Datta

    • It is not possible to change the page size of a table space. Db2 will select the appropriate size based on the query result set, and this is not an area that is questionable or that DBAs spend time worrying about – it just works. Queries that use inappropriate amounts of temp space (of any page size) are a much bigger problem, but are often a result of poorly written SQL, poorly indexed tables, or Cartesian products.

      • Hi Ember

        Thanks for taking time in giving a detailed explanation in spite of your busy schedule and it’s much appreciated!

        Thanks for all your help!

        Regards
        Datta

  9. Hello everyone,
    I am new with Db2 LUW and would like to find out which table/view I may query the “USING” value for the CREATE TEMPORARY TABLESPACE statement.
    Thanks in advance for your help.

  10. Hi Ember,

    We have two mounts 800GB each for temporary table space. End users creating Cognos reports with Cartesian joins is filling up the entire temporary table space. Is it normal to consume 1.6TB of space? Btw the tables are column organised. We never had this issue when the tables are row based and the same Cognos report is running fine.

    Cheers
    RB

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.