IBM Support

IC81299: FAILED REORG INDEXES WITH ALLOW NO ACCESS CAN LEAD TO TABLE-INDEX MISMATCH

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • There is problem in the REORG INDEXES error handling code that
    could result in a mismatch between the table and index if REORG
    INDEXES fails in a small window due to limited resources at the
    beginning of its processing.  To hit this problem you need to be
    on DB2 9.5 FixPack 5 to 8, run REORG INDEXES with the ALLOW NO
    ACCESS option, without the CLEANUP ONLY option and you need to
    have it fail when allocating memory for the REORG INDEXES
    progress monitor.
    
    You will see db2diag.log messages similar to the following:
    
    2012-02-08-11.23.43.398565-300 I32056E526          LEVEL:
    Warning
    PID     : 9121                 TID  : 46912979855680PROC :
    db2sysc
    INSTANCE: ztoth                NODE : 000          DB   : WSDB
    APPHDL  : 0-15                 APPID: *LOCAL.ztoth.120208162340
    AUTHID  : ZTOTH
    EDUID   : 25                   EDUNAME: db2agent (WSDB)
    FUNCTION: DB2 UDB, relation data serv, sqlrreorg_indexes,
    probe:300
    DATA #1 : String, 96 bytes
    Starting Offline Reorg Indexes on:
    Table T1 (poolID 2 : objectID 4)
    with reorg flags 0x04000010
    
    2012-02-08-11.23.43.400329-300 E32583E532          LEVEL:
    Warning
    PID     : 9121                 TID  : 46912979855680PROC :
    db2sysc
    INSTANCE: ztoth                NODE : 000          DB   : WSDB
    APPHDL  : 0-15                 APPID: *LOCAL.ztoth.120208162340
    AUTHID  : ZTOTH
    EDUID   : 25                   EDUNAME: db2agent (WSDB)
    FUNCTION: DB2 UDB, relation data serv, sqlrreorg_indexes,
    probe:400
    MESSAGE : ADM9501W  BEGIN index reorganization on table "ZTOTH
    .T1" (ID "4")
              and table space "USERSPACE1" (ID "2").
    
    2012-02-08-11.30.30.006153-300 I35204E500          LEVEL: Severe
    PID     : 9121                 TID  : 46912979855680PROC :
    db2sysc
    INSTANCE: ztoth                NODE : 000          DB   : WSDB
    APPHDL  : 0-15                 APPID: *LOCAL.ztoth.120208162340
    AUTHID  : ZTOTH
    EDUID   : 25                   EDUNAME: db2agent (WSDB)
    FUNCTION: DB2 UDB, relation data serv, sqlrreorg_index_obj,
    probe:800
    DATA #2 : Hexdump, 4 bytes
    0x00002AAAC77F0560 : 0200 0F8B
    ....
    
    2012-02-08-11.30.30.008525-300 E35705E690          LEVEL:
    Warning
    PID     : 9121                 TID  : 46912979855680PROC :
    db2sysc
    INSTANCE: ztoth                NODE : 000          DB   : WSDB
    APPHDL  : 0-15                 APPID: *LOCAL.ztoth.120208162340
    AUTHID  : ZTOTH
    EDUID   : 25                   EDUNAME: db2agent (WSDB)
    FUNCTION: DB2 UDB, relation data serv, sqlrreorg_indexes,
    probe:1000
    MESSAGE : ADM9504W  Index reorganization on table "ZTOTH   .T1"
    (ID "4") and
              table space "USERSPACE1" (ID "2") failed on this node
    with SQLCODE
              "-956" reason code "".  To resolve this problem,
    re-submit the REORG
              INDEXES command on the failing node(s).
    
    Please note that these probe points can be reported even if we
    failed outside of the problematic timing window.  If the problem
    is hit, then at this point the index object descriptor will
    indicate an error while page zero of the index object will not.
    As a result, the index is not maintained on undo, and the index
    can become out-of-sync with the table.  This could result in a
    number of different symptoms.  The following are two examples:
    
    1)  Row not found in table during data fetch from index.  A
    message with the following text may appear in the db2diag.log:
    
    FUNCTION: DB2 UDB, data management, sqldDataFetch, probe:4623
    MESSAGE : Row not found on data fetch from index!
    
    2)  Row not found in the index during the update or delete of a
    row in the table.  A message with the following text may appear
    in the db2diag.log:
    
    FUNCTION: DB2 UDB, index manager, procT2Leaf2Del, probe:7
    RETCODE : ZRC=0x8709002C=-2029453268=SQLI_NOKEY "Key not found
    within node"
              DIA8541C The index key could not be found, the value
    was "".
    

Local fix

  • Recreate the affected indexes.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * All uers                                                     *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See the Error Description field.                             *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 version 9.5.0.9.                              *
    ****************************************************************
    

Problem conclusion

  • The problem is first fixed in DB2 version 9.5.0.9.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC81299

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    950

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2012-02-08

  • Closed date

    2012-03-19

  • Last modified date

    2012-03-19

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

    IC81289

  • 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

  • R950 PSN

       UP

[{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSEPGG","label":"DB2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"9.5","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
19 March 2012