IBM Support

IC77761: FUNCTION: DB2 UDB, INDEX MANAGER, SQLICLEANUPEMPTYLEAF, PROBE:12 13 RETCODE : ZRC=0X87090054=-2029453228=SQLI_PRG_ERR "PROGRAM ER

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • A DB2 instance can encounter a programming error message during
    an index update operation.
    
    Possible stack:
    
    pthread_kill
    sqloDumpEDU
    sqldDumpContext
    sqlidelk
    sqlidelk
    sqldUpdateIndexes
    sqlriupd
    
    db2diag.log:
    
    2012-06-15-11.44.37.192742-240 I41733E537  LEVEL: Severe
    PID     : 20122        TID  : 469129463012 PROC : db2sysc
    INSTANCE: dbuser1      NODE : 000          DB   : SAMPLEDB
    APPHDL  : 0-7          APPID: *LOCAL.dbuser1.120615154434
    AUTHID  : DBUSER1
    EDUID   : 16           EDUNAME: db2agent (SAMPLEDB)
    FUNCTION: DB2 UDB, index manager, sqliCleanupEmptyLeaf,
    probe:1213
    RETCODE : ZRC=0x87090054=-2029453228=SQLI_PRG_ERR "Program
    error" DIA8575C An index manager programming error occurred.
    
    2012-06-15-11.44.37.212833-240 I42271E741  LEVEL: Severe
    PID     : 20122        TID  : 469129463012 PROC : db2sysc
    INSTANCE: dbuser1      NODE : 000          DB   : SAMPLEDB
    APPHDL  : 0-7          APPID: *LOCAL.dbuser1.120615154434
    AUTHID  : DBUSER1
    EDUID   : 16           EDUNAME: db2agent (SAMPLEDB)
    FUNCTION: DB2 UDB, trace services, sqlt_logerr_string (secondary
    logging fu, probe:0
    DATA #1 : String, 173 bytes
    Running the following command before and after restart database
    may provide useful problem determination information.  Replace
    DBNAME with the real DB name if not provided.
    DATA #2 : String, 87 bytes
    db2dart SAMPLEDB /di /tsi 7 /oi 4 /ps 114  /np 1 /v y /scr n
    /rptn iTsi7oi4pg114o+0.rpt
    
    ...
    
    2012-06-15-12.03.54.627008-240 E160694E966 LEVEL: Critical
    PID     : 24172        TID  : 469129463012 PROC : db2sysc
    INSTANCE: dbuser1      NODE : 000          DB   : SAMPLEDB
    APPHDL  : 0-7          APPID: *LOCAL.dbuser1.120615160352
    AUTHID  : DBUSER1
    EDUID   : 16           EDUNAME: db2agent (INDPDDB)
    FUNCTION: DB2 UDB, base sys utilities,
    sqeLocalDatabase::MarkDBBad, probe:10
    MESSAGE : ADM14001C  An unexpected and critical error has
    occurred:  "DBMarkedBad". The instance may have been shutdown as
    a result. "Automatic" FODC (First Occurrence Data Capture) has
    been invoked and diagnostic information has been recorded in
    directory
    "/home/dbuser1/sqllib/db2dump/FODC_DBMarkedBad_2012-06-15-12.03.
    54.626917_0000/". Please look in this directory for detailed
    evidence about what happened and contact IBM support if
    necessary to diagnose the problem.
    
    The database will get marked as bad, and will require Crash
    recovery.  The databsae should complete crash recovery without
    any problems, however the index object is damaged and needs to
    be recreated (see the Local Fix section).
    
    To confirm that this problem has been hit, you need to check the
    transaction logs.  Look for the "sqliCleanupEmptyLeaf,
    probe:1213" error in the db2diag.log, then search down for a
    db2dart command.  Shown above.
    
    Look for this rpt file in one of the FODC_IndexError*
    directories, open it and note the Page LSN:
    Page LSN = 0000000002B72B11
    
    Then find this LSN in the transaction logs and examine the
    transaction it is a part of.  If this APAR was hit you will see
    the following in that transaction:
    - CRINDEX_DP log record for the object reference in the db2dart
    command
    - A SQLI_LRT_MARK_INX_BAD and SQLD_LRT_CHANGELIFELSN log record
    indicating that CREATE INDEX has completed followed by
    - A GETPAGE_DP log record with the same root page that appears
    in the CRINDEX_DP log record
    - The transaction rolls back and aborts
    

Local fix

  • To avoid hitting problem, the customer should COMMIT any CREATE
    INDEX statements immediately after the CREATE INDEX statement
    has completed, instead of including the CREATE INDEX as a part
    of a larger transaction.
    
    To recover from the probelm if hit, the user should recreate the
    index object by either:
    - running REORG INDEXES ALL on the table with either the ALLOW
    NO ACCESS or the ALLOW READ ACCESS option OR
    - marking the index object invalid using db2dart and rebuild it
    OR
    - dropping all indexes on the table (including ones created
    implicitly for primary keys and unique constraints) and then
    recreating them
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * All Platforms                                                *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * a DB2 instance can encounter a programming error message     *
    * during                                                       *
    * an index update operation.                                   *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 V9.7 Fix Pack 5.                              *
    ****************************************************************
    

Problem conclusion

  • This problem was first fixed in DB2 V9.7 Fix Pack 5.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC77761

  • 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

    2011-07-27

  • Closed date

    2011-12-27

  • Last modified date

    2012-06-15

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

    IC77187

  • 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

[{"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.7","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
15 June 2012