DB2 FOR LUW / DB2FORLUW / 970 - DB2

While attempting to drop an index from a table you might 
encounter that the database is marked bad (SQL1034C). 
 
In the db2diag.log you will see following messages: 
 
2013-03-25-11.50.50.201855+060 I14650904A538      LEVEL: Severe 
PID     : 17039520             TID  : 7690        PROC : db2sysc 
0 
INSTANCE: db2inst               NODE : 000         DB   : SAMPLE 
APPHDL  : 0-2078               APPID: 
*LOCAL.db2inst.130325044144 
AUTHID  : DB2USR 
EDUID   : 7690                 EDUNAME: db2agent (SAMPLE) 0 
FUNCTION: DB2 UDB, index manager, sqliSortObjPages, probe:2803 
RETCODE : ZRC=0x80040001=-2147221503=SQLD_NOREC "NO MORE RECORDS 
FOUND ON FETCH" 
          DIA8000C An unexpected end of file was reached "". 
 
2013-03-25-11.50.50.209377+060 I14651443A526      LEVEL: Severe 
PID     : 17039520             TID  : 7690        PROC : db2sysc 
0 
INSTANCE: db2inst               NODE : 000         DB   : SAMPLE 
APPHDL  : 0-2078               APPID: 
*LOCAL.db2inst.130325044144 
AUTHID  : DB2USR 
EDUID   : 7690                 EDUNAME: db2agent (SAMPLE) 0 
FUNCTION: DB2 UDB, index manager, sqliSortObjPages, probe:2803 
RETCODE : ZRC=0x87090054=-2029453228=SQLI_PRG_ERR "Program 
error" 
          DIA8575C An index manager programming error occurred. 
 
 
The stack for the failing agent will look like following: 
 
 pthread_kill 
 sqloDumpEDU 
 sqldmdpi 
 sqlrlDropIndex 
 sqlrlDropIndex 
 sqlnq_drop_db_object_end 
 sqlnq_drop_db_object 
 sqlnp_smactn 
 sqlnp_parser 
 sqlnp_main 
 sqlnn_cmpl 
 
The problem will only be hit if all of the following are true: 
- the table contains more than one index when drop index is 
executed 
- the index tablespace is a database managed tablespace that was 
created prior to DB2 version 9.7 
- the index object (i.e. the object containing all the indexes 
on the table) is very large 
 
The size of the index object required to hit the problem is 
exponentially dependant on both the page size and the extent 
size of the index tablespace.  Here are some examples: 
 
PageSize ExtentSize IndexObjectSize 
-------- ---------- --------------- 
    4 KB          2           15 GB 
    4 KB          4           60 GB 
    4 KB          6          135 GB 
     ...        ...             ... 
    8 KB          2          124 GB 
    8 KB          4          497 GB 
    8 KB          6         1119 GB 
     ...        ...             ... 
   16 KB          2         1009 GB 
   16 KB          4         4037 GB 
   16 KB          6         9083 GB 
     ...        ...             ... 
   32 KB          2         8133 GB 
   32 KB          4        32532 GB 
   32 KB          6        73197 GB 
     ...        ...             ...

**************************************************************** 
* USERS AFFECTED:                                              * 
* All                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 version 9.7 Fix Pack 9                        * 
****************************************************************

DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
 

First fixed in DB2 version 9.7 Fix Pack 9

not known / see Local fix

forerunner  : APAR is sysrouted TO one or more of the following: IC91709 IC95262 IC95292 
follow-up : 

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

17.04.2013
23.12.2013
23.12.2013

9.7.FP9