IBM FileNet P8, Version 5.2.1            

DB2 Table Overflow Support: Query Impact

DB2® for Linux, UNIX and Windows Version 10.1 and earlier and DB2 for z/OS® have a 32 KB row-size limit for tables. Column-length limitations can be exceeded when you create generated-column indexes for object store databases, which cause an error. To address the 32 KB row-size limit, Content Platform Engine provides a table overflow support feature that can be enabled on an object store, which creates an overflow table if the 32 KB row-size limit of a base DB2 table is exceeded. This feature adds the column for a new class property into an overflow table when the sum of the new column length plus the existing column lengths in a table surpass 32 KB (minus overhead).

Start of changeDB2 for Linux, UNIX and Windows Version 10.5 and later supports an extended row size by default. However, for databases that are upgraded to DB2 for Linux, UNIX and Windows Version 10.5 and later, from a release earlier than Version 10.5, you must run the following command to enable extended row-size support:
UPDATE DATABASE CONFIGURATION FOR dbName USING EXTENDED_ROW_SZ ENABLE
End of change

When table overflow support is enabled, a query against a DB2 database can fail under certain conditions. The following section specifies those conditions, and explains how to avoid the potential failure.

Query Implementation for Overflow Support

During search and object retrieval, all the necessary columns are returned in a database query by joining the base table, for example, DocVersion, to its overflow table. For example, this search, SELECT … FROM Document, is implemented by using a FROM clause that replaces the DocVersion table with an overflow-table join expression like the following expression:

FROM (SELECT … FROM DocVersion LEFT JOIN S1_DocVersion …) TO

S1_DocVersion is the overflow table, and is only included in the FROM clause join-expression when either the SELECT list, WHERE conditions, or ORDER BY properties include a property that is stored in the overflow table. T0 is the alias for the join-expression (also known as a nested table).

DB2 Query Issue

DB2 for Linux, UNIX and Windows Version 10.1 and earlier and DB2 for z/OS have a row length storage limit of 32 KB, which also applies to DB2 system temporary tables used in processing queries. Temporary work tables are needed for complex queries, for example, joins, which need to process a portion of the data first before they return the rows to the client. A work table is stored in memory, but, if it is too large, it is stored in a system temporary table space.

If the sum of the column lengths that are used by the DB2 compiler in creating a work table surpasses 32 KB, the following failure occurs:

SQL1585N A system temporary table space with sufficient page size does not exist. SQLSTATE=54048

This page size error occurs when the work table concatenates columns from the multiple individual tables in the query, each less than 32 KB, into the temporary work table.

Failure Examples

The work table page size error is generated from an ad hoc search when these conditions are present:

These conditions have been tested by IBM®. Note that there might be other, untested, conditions that trigger the DB2 system to use a work table larger than the row length limit, resulting in the work table page size error.

In the following two examples, the select list includes more than 32 KB of columns.

Example 1 - Self join with more than one overflow table

SELECT … FROM Document D INNER JOIN Document E on D.property1 = E.property2

The resulting database SQL is generated as follows:

SELECT … FROM (select … from Docversion d1 left join S1_Docversion d2 on d1.object_id = d2.object_id1 left join S2_Docversion d3 on d1.object_id = d3.object_id2) T0 INNER JOIN (select … from Docversion d1 left join S1_Docversion d2 on d1.object_id = d2.object_id1 left join S2_Docversion d3 on d1.object_id = d3.object_id2) T1 ON T0.property1 = T1.property2

Example 2 - Right join with one overflow table

SELECT … FROM Document D RIGHT OUTER JOIN CustomObject E on D.property1 = E.property2

The resulting database SQL is generated as follows:

SELECT … FROM (select … from Docversion d1 left join S1_Docversion d2 on d1.object_id = d2.object_id1) T0 RIGHT OUTER JOIN Generic T1 on T0.property1 = T1.property2

Avoiding the Work Table Page Size Error

If DB2 table overflow support is enabled for DB2 for Linux, UNIX and Windows Version 10.1 and earlier, you can avoid the potential work table page size error by carefully choosing the SELECT columns in your queries. In example 1 in the previous section, the error can be avoided by not selecting columns from the second self-join table. Similarly, in example 2, not selecting more than 32 KB of columns avoids the error.



Last updated: October 2015
query_sql_syntax_db2.htm

© Copyright IBM Corporation 2015.