Troubleshooting
Problem
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.
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21393862