Ensuring system temporary table spaces page sizes meet requirements

The use of larger record identifiers (RID) increases the row size in your result sets from queries or positioned updates. If the row size in your result sets is close to the maximum row length limit for your existing system temporary table spaces, you might have to create a system temporary table space with a larger page size.

To ensure that the maximum page size of your system temporary table space is large enough for your queries or positioned updates:

1. Determine the maximum row size in your result sets from queries or positioned updates. Monitor your queries or calculate the maximum row size using the DDL statement that you used to create your tables.
2. Determine the page size for each of your system temporary table spaces and the page size of the table spaces where the tables referenced in the queries or updates were created by issuing the following query:

   db2 "SELECT CHAR(TBSP_NAME,20) TBSP_NAME, TBSP_CONTENT_TYPE, TBSP_PAGE_SIZE 
        FROM SYSIBMADM.SNAPTBSP"
   
   TBSP_NAME            TBSP_CONTENT_TYPE TBSP_PAGE_SIZE
   -------------------- ----------------- --------------------
   SYSCATSPACE          ANY                               8192
   TEMPSPACE1           SYSTEMP                           8192
   USERSPACE1           LARGE                             8192
   IBMDB2SAMPLEREL      LARGE                             8192
   SYSTOOLSPACE         LARGE                             8192
   SYSTOOLSTMPSPACE     USRTEMP                           8192
   
     6 record(s) selected.

   You can identify the system temporary table spaces in the output by looking for table spaces that have the TBSP_CONTENT_TYPE column with a value of SYSTEMP.
   If you are upgrading from Version 8.1, use the following command:

   db2 LIST TABLESPACES SHOW DETAIL

3. Check whether the largest row size in your result sets fits into your system temporary table space page size:

      maximum_row_size > maximum_row_length - 8 bytes (structure overhead in 
                                                       single partition)
      maximum_row_size > maximum_row_length - 16 bytes (structure overhead in DPF)

   where maximum_row_size is the maximum row size for your result sets, and maximum_row_length is the maximum length allowed based on the largest page size of all of your system temporary table spaces. Review the database manager page size-specific limits to determine the maximum row length per table space page size.

   If the maximum row size is less than the calculated value then your queries will run in the same manner that they did in DB2® UDB Version 8, and you do not have to continue with this task.

4. Create a system temporary table space that is at least one page size larger than the table space page size where the tables were created if you do not already have a system temporary table with that page size. For example, on the Windows operating systems, if you created your table in a table space with 8 KB page size , create the additional system temporary table space using an 16 KB page size:

      db2 CREATE SYSTEM TEMPORARY TABLESPACE tmp_tbsp
            PAGESIZE 16K
            MANAGED BY SYSTEM
            USING ('d:\tmp_tbsp','e:\tmp_tbsp')

   If your table space page size is 32 KB, you can reduce the information that you are selecting in your queries or split the queries to fit in the system temporary table space page. For example, if you select all columns from a table, you can instead select only the columns that you really required or a substring of certain columns to avoid exceeding the page size limitation.