DB2 CREATE INDEX abended with S04E 00E70005

There is a couple of news items attached to this post:
1. Today is the first anniversary of this blog
2. This is the 50th post

Well, now into the actual blog item ...

My colleague was trying to drop an existing UNIQUE & CLUSTER index and recreate it after adding 3 new columns at the end of the table, with one of the new columns added to the index. However, the DDL failed with S04E reason code 00E70005.

DB2 messages and codes manual had this info on this reason code: "A relational data system (RDS) subcomponent internal inconsistency was detected". It didn't make any sense. We suspected data issue first, so we REORGed the tablespace, and tried to create the index, but that didn't fix the problem. Next, we tried to repair the DBD using this JCL:

//*===============================================
//* Start the database in UT mode
//*===============================================
//STEP0010   EXEC PGM=IKJEFT01,DYNAMNBR=20
//STEPLIB    DD DSN=SYS2.DB2x.DSNLOAD,DISP=SHR
//SYSTSPRT   DD SYSOUT=*
//SYSPRINT   DD SYSOUT=*
//LISTING    DD SYSOUT=*
//SYSUDUMP   DD DUMMY
//SYSTSIN    DD *
  DSN SYSTEM(DB2x)
  -START DB(database) ACCESS(UT)
  END
/*
//*===============================================
//* Run REPAIR utility on the DBD
//*===============================================
//STEP020 EXEC PGM=DSNUTILB,PARM='DB2x,utilid'
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN    DD  *
  REPAIR DBD REBUILD DATABASE database
/*
//*===============================================
//* Start the database in RW mode
//*===============================================
//STEP0030   EXEC PGM=IKJEFT01,DYNAMNBR=20
//STEPLIB    DD DSN=SYS2.DB2.DB2x.DSNLOAD,DISP=SHR
//SYSTSPRT   DD SYSOUT=*
//SYSPRINT   DD SYSOUT=*
//LISTING    DD SYSOUT=*
//SYSUDUMP   DD DUMMY
//SYSTSIN    DD *
  DSN SYSTEM(DB2x)
  -START DB(database) ACCESS(RW)
  END
/*
 

Even that didn't fix the problem. We were not very keen to open a ticket with IBM, since we wanted to fix the problem fast rather than debugging it. So as a last resort we dropped and recreated the tablespace (off course, we had saved the data). That fixed the problem. We don't know what caused it, but the problem is fixed !! I really hate when I don't know what caused the problem, and the fix is based trial-and-error method.