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.
Before you begin
Ensure that you have SYSCTRL or SYSADM authority to create
a system temporary table space if required.
Procedure
To ensure that the maximum page size of your system temporary
table space is large enough for your queries or positioned updates:
- 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.
- 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
- 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.
- 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.