IBM Support

DB2 receives SQL1585N when performing an sql statement MERGE

Technote (troubleshooting)


Problem(Abstract)

When attempting to perform an sql merge statement a SQL1585N error occurs even though there is a matching sized temporary tablespace.

Symptom

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


Cause

In some scenarios, but not all, this error is caused by the maximum record length being exceeded. Attempts to purposely reproduce this condition may not be possible.

The 32K page size has a maximum record length of 32677 bytes.

For a merge statement, that need to flow 2 sets of columns to temporary space - one set for the UPDATE operation, and the other set for the INSERT operation.

Example:

For a table with DDL of 5 columns of size (3278). 

One set of columns has size of at least 3278 x 5 = 16390 bytes.

Two sets of column will have size of at least 16390 x 2 = ~32780 bytes, which exceeds the limit of 32677.


Resolving the problem

If the column size can be reduced so that the total size is less than the columns width limit of 32677 for 32K page size , there will be enough space for the TEMP operation. This can be done by removing a column from the select statement or reducing the width of one or more of the columns in the select statement.

From the example above the column size is currently 3278 of the VARCHAR columns so reducing the column size to 3267 or below will avoid the problem.

This is a limitation in the max column width of the 32 K page size.  Reducing the column size of the VARCHAR columns is the safest way.

Document information

More support for: DB2 for Linux, UNIX and Windows
OTHER - Uncategorised

Software version: 9.1, 9.5, 9.7

Operating system(s): AIX, HP-UX, Linux, Solaris, Windows

Reference #: 1393862

Modified date: 12 March 2016