|
Problem IC95424
|
Status: Closed |
LOAD MAY FAIL WITH SQL0902C RC85 IF THERE IS AN INDEX WITH KEY LENGTH AROUND 4000 OR 8100 BYTES |
| product: |
DB2 FOR LUW / DB2FORLUW / A50 - DB2 |
| Problem description: |
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. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* All user. *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Please upgrade to latest fixpack. *
**************************************************************** |
| Local Fix: |
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. |
| available fix packs: |
DB2 Version 10.5 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 10.5 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Cancun Release 10.5.0.4 (also known as Fix Pack 4) for Linux, UNIX, and Windows
DB2 Version 10.5 Fix Pack 9 for Linux, UNIX, and Windows
|
| Solution |
Problem first fixed in DB2 V 10.5 fixpack 3. |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 27.08.2013
27.02.2014
27.02.2014 |
| Problem solved at the following versions (IBM BugInfos) |
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 10.5.0.3
|
 |
| 10.5.0.3
|
|
