DB2 FOR LUW / DB2FORLUW / 970 - DB2

Load command fails with SQL0902C with reason code 85, when there 
is an index with key length (which is based on size of index 
columns plus some internal overhead) of around 4000 or around 
8100 bytes.  There is a "Sort reclen limit exceeded" error 
message in db2diag.log like this: 
 
 
2012-12-14-10.37.47.601688-300 I8716054E644        LEVEL: Severe 
 
PID     : 15110                TID  : 46917560035648PROC : 
db2sysc 0 
INSTANCE: db2inst1             NODE : 000          DB   : SAMPLE 
 
APPHDL  : 0-5934               APPID: 
*LOCAL.nytit067.121213230326 
AUTHID  : USER1 
 
EDUID   : 2371                 EDUNAME: db2lfrm0 0 
 
FUNCTION: DB2 UDB, trace services, sqlt_logerr_string (secondary 
 
logging fu, probe:0 
 
MESSAGE : Sort reclen limit exceeded 
 
DATA #1 : String, 156 bytes 
 
Sort.  In function sqlsTableInfoSetup( ) Line 800 
 
Record length exceeds 4005 -- the maximum allowed for this 
temporary 
tablespace. 
 
Sort record length 4013 
 
 
This is caused by mismatched LOAD internal logic -- LOAD first 
determines that we can use 4K / 8K page size (respectively) 
temporary tablespace for index sorts, then later discovers that 
we need larger page size temp tablespace. 
 
A sample scenario: 
 
>CREATE TABLE tab1  ( "ID" INTEGER NOT NULL , "VALUE" 
VARCHAR(4000) NOT NULL ) 
>CREATE INDEX inx1 ON tab1 ("VALUE" ASC)ALLOW REVERSE SCANS 
 
>db2 load from data.del of del replace into tab1 nonrecoverable 
 
SQL0902C  A system error (reason code = "85") occurred. 
Subsequent SQL 
statements cannot be processed.

**************************************************************** 
* USERS AFFECTED:                                              * 
* All DB2 users                                                * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Please upgrade to latest fixpack.                            * 
****************************************************************

Modify the table and/or index definition and increase some 
column size slightly so that the index key length exceeds ~4000 
or ~8100 bytes.  Due to internal overhead that varies with 
different configurations, one may need to do some 
trial-and-error tests to find the specific increments needed to 
avoid the problem.

DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
 

Problem is first fixed in DB2 V9.7 Fixpack 9.

not known / see Local fix

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

08.03.2013
16.12.2013
16.12.2013

9.7.FP9