IBM Support

IC93971: INDEX / DATA MISMATCH MIGHT OCCUR IN AN MDC TABLE AFTER A DEFERRED ROLLOUT

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • The error might occur when the following criteria are met:
    
    1) The table is an MDC table with at least one RID index.
    
    2) A deferred cleanup rollout delete is performed on that MDC
    table.  The conditions that need to be met for a delete to be
    classified as a rollout delete are:
       - The DELETE statement is a searched DELETE, not a positioned
    DELETE (the statement does not use the WHERE CURRENT OF clause).
       - There is no WHERE clause (all rows are to be deleted), or
    the only conditions in the WHERE clause apply to dimensions.
       - The table is not defined with the DATA CAPTURE CHANGES
    clause.
       - The table is not the parent in a referential integrity
    relationship.
       - The table does not have ON DELETE triggers defined.
       - The table is not used in any MQTs that are refreshed
    immediately.
       - A cascaded delete operation might qualify for rollout if
    its foreign key is a subset of the table's dimension columns.
       - The DELETE statement cannot appear in a SELECT statement
    executing against the temporary table that identifies the set of
    affected rows prior to a triggering SQL operation (specified by
    the OLD TABLE AS clause on the CREATE TRIGGER statement).
    
       The default behavior, immediate cleanup rollout, is to clean
    up RID indexes during the time of deletion. A deferred cleanup
    rollout delete is used when the user sets the DB2_MDC_ROLLOUT
    registry variable to DEFER, or by specifying DEFERRED on the SET
    CURRENT MDC ROLLOUT MODE statement.
    
    Source:
    http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/index.jsp?topic=%
    2Fcom.ibm.db2.luw.admin.perf.doc%2Fdoc%2Fc0007338.html
    
    3) The asynchronous index cleanup operation yields to a LOAD
    command running on the same table.  The load command must use
    the INSERT option and must not specify INDEXING MODE REBUILD or
    INDEXING MODE DEFERRED.
    
    4) After the LOAD command completes, the asynchronous index
    cleanup operation continues.  However, before it completes
    another deferred cleanup rollout delete is performed on the
    table.  Incorrectly, the DB2 software does not consolidate the
    asynchronous index cleanup operations and runs them both
    together because the life LSN of the table is updated by the
    LOAD command.
    
    5) After the first asynchronous index cleanup operation
    completes, it updates the block map incorrectly marking all
    rolled-out blocks as free. Meanwhile, the index cleanup is not
    complete.  This might result in a variety of different index
    errors.  The problem can be detected by running the INSPECT
    command with the INDEXDATA option.
    

Local fix

  • If the problem occurs, rebuild the indexes.  To avoid the
    problem do not use deferred index cleanup.  Make sure the
    following values are NOT set:
    
    SET CURRENT MDC ROLLOUT MODE DEFERRED
    db2set DB2_MDC_ROLLOUT=DEFER
    
    Use the following instead:
    SET CURRENT MDC ROLLOUT MODE IMMEDIATE
    db2set DB2_MDC_ROLLOUT=IMMEDIATE
    OR
    SET CURRENT MDC ROLLOUT MODE NONE
    db2set DB2_MDC_ROLLOUT=
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * All users                                                    *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 version 10.5.0.3.                             *
    ****************************************************************
    

Problem conclusion

  • The problem is first fixed in DB2 version 10.5.0.3.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC93971

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    A50

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2013-07-08

  • Closed date

    2013-08-23

  • Last modified date

    2013-08-23

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

    IC92963

  • 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

  • RA50 PSN

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 10.5

Reference #: IC93971

Modified date: 23 August 2013