IZ80862: ONLINE BACKUP CAN BACKUP AN INDEX IN A PARTIALLY INCONSISTENT STATE

DB2 Version 9.5 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 10 for Linux, UNIX, and Windows

APAR status

• Closed as program error.

Error description

• Space Map Page can be captured before the drop index while other
  data backed up indicates that that index is already dropped. For
  example, consider following record in the log file:
  
  Record 271143    Log Page Offset = 05AA = 1450
                  Log File Offset = 025855AA = 39343530
                       Record LSN = 0000 001C 4ED3 F5AA
                      Record Size = 0033 = 51
                      Record Type = 4E = Normal
                 Log Header Flags = 0000
                       Record TID = 0000007AFBFD
                 Back Pointer LSN = 001C 4ED3 F590
                  Originator Code = 02 = Index Manager
                      Function ID = 6B = 107 : GETPAGE_DP
                          Pool ID = 24 Object ID = 911 Index
  Object Type = x1
                   Parent Pool ID = 8 Parent Object ID = 919
                  Index Root Page = 0057E327 = 5759783
                   Index SMP Page = x0010FAE0 = 1112800
                       Index Page = x005A5ABF = 5921471
       Location of Byte in Bitmap = 20
                 Bit Pair in Byte = 1
                         smlFlags = 41 = SM_LOGF_EXTEND
  SM_LOGF_UPDATE_NLEAF
                       smNewState = 10 = SQLI_SMP2_USED
                  smOriginalState = 00 = SQLI_SMP2_NOTUSED
  
  Here, attempt to redo this GETPAGE_DP log record should change
  the state from 0x00 (SQLI_SMP2_NOTUSED) to 0x10
  (SQLI_SMP2_USED). But since the index is dropped already index
  manager will skip replaying this log record because of a change
  made in 9.5 to log the root page ID with the GETPAGE_DP log
  record as oppose to SQLI_GTPG_LOG_ROOTPG_0 (i.e. 0). As a
  result, when index manager replay this log record it detect that
  this index is already dropped and don't trying to replay it
  (even it should).
  

Local fix

• customer can recover from this problem by using db2dart to mark
  corresponding index as invalid and then connecting to the
  database and running a select (any select) against this table
  that index belongs to.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * DB2 Version 9.5 servers from GA through to Fix Pack 6        *
  * onLinux, Unix and Windows platforms.                         *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * You have restored the database and doing the roll            *
  * forwardwhich ends up with error code SQL1042C and            *
  * SQLSTATE=58004("An unexpected system error occurred."). The  *
  * diagnostic logfile contains the message similar to one       *
  * below:2010-07-08-10.29.27.729622+120 I244997A685 LEVEL:      *
  * EventPID : 393332 TID : 13110 PROC : db2sysc 11INSTANCE:     *
  * db2qbw NODE : 011 DB : QBWAPPHDL : 0-52 APPID:               *
  * *N0.db2qbw.100708082912AUTHID : DB2QBWEDUID : 13110 EDUNAME: *
  * db2redow (QBW) 11FUNCTION: DB2 UDB, index manager, sqliRedo, *
  * probe:20STOP : AUTOMATIC FODC : IndexError : successIMPACT : *
  * NoneDATA #1 : String, 209 bytesThis can happen if a space    *
  * map page has been taken into thebackup image before an index *
  * drop. When doing the rollforward the index manager will skip *
  * the records belonging toan index, thus making an index state *
  * inconsistent.                                                *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to DB2 v9.5 FP7 or use db2dart tool to mark          *
  * affectedindex as invalid to work around. When using work     *
  * around, itis necessary to select from the table to which an  *
  * indexbelongs after running the db2dart tool.                 *
  ****************************************************************
  

Problem conclusion

• The problem has been fixed in DB2 v9.5 FP7.
  

Temporary fix

Comments

APAR Information

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

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

  IC71075

Fix information

• Fixed component name

  DB2 UDB ESE HP-

• Fixed component ID

  5765F4103

Applicable component levels

• R950 PSN

     UP