IC90743: LOAD MAY FAIL WITH SQL0902C RC85 IF THERE IS AN INDEX WITH KEY LENGTH AROUND 4000 OR 8100 BYTES

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error 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.
    

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.
    

Problem summary

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

Problem conclusion

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

Temporary fix

Comments

APAR Information

  • APAR number

    IC90743

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2013-03-08

  • Closed date

    2013-12-16

  • Last modified date

    2013-12-16

  • APAR is sysrouted FROM one or more of the following:

    IC89990

  • APAR is sysrouted TO one or more of the following:

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSN

       UP



Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

DB2 for Linux, UNIX and Windows

Software version:

9.7

Reference #:

IC90743

Modified date:

2013-12-16

Translate my page

Machine Translation

Content navigation