IC88424: CRASH RECOVERY/ROLLFORWARD CAN FAIL WITH SQLD_PRGERR IN SQLUPROC ESSLOADSTARTLOGRCD, OR TABLESPACE STAY IN LOAD IN PROGRESS STATE

DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows

APAR status

• Closed as program error.

Error description

• (Note: There are 2 possible problem symptoms associated with a
  LOAD operation described below.)
  
  Having a table definition with the following & using LOAD:
  
  - Partitioned Table
  - Partition section also has INDEX IN clause & UNIQUE Tablespace
  for this clause.
  - No Partitioned Indexes
  - LOAD command issue on the table
  
  Having the following Table Definition & performing a load will
  cause incorrect Transaction Log Records for the Load
  
  CREATE TABLE DB2INST1.PART_TAB  (
      "ORDER_NUM" BIGINT NOT NULL ,
      "ORDER_TYPE" CHAR(1) ,
      "ORDER_QTY" DECIMAL(10,2) ,
      "BATCH_ID" INTEGER NOT NULL ,
      "LOAD_DATE" DATE NOT NULL )
     COMPRESS YES
       INDEX IN "PART_TAB_IDX" PARTITION BY RANGE("LOAD_DATE")
     (
     PART "PART_0" STARTING('2011-10-01') ENDING('2011-11-01')
  EXCLUSIVE IN "PART_TAB_D_00" INDEX IN "PART_TAB_I_00",
     PART "PART_1" STARTING('2011-11-01') ENDING('2011-12-01')
  EXCLUSIVE IN "PART_TAB_D_01" INDEX IN "PART_TAB_I_01",
     PART "PART_2" STARTING('2011-12-01') ENDING('2012-01-01')
  EXCLUSIVE IN "PART_TAB_D_02" INDEX IN "PART_TAB_I_02",
  ...
     PART "PART_10" STARTING('2012-08-01') ENDING('2012-09-01')
  EXCLUSIVE IN "PART_TAB_D_10" INDEX IN "PART_TAB_I_10",
     );
  ALTER TABLE DB2INST1."PART_TAB"
   ADD CONSTRAINT "PART_TAB_PK" PRIMARY KEY
    ("ORDER_NUM");
  
  LOAD Command:
  
  db2 LOAD FROM data.del OF DEL INSERT INTO DB2INST1.PART_TAB
  
  When DB2 is performing Crash Recovery or Rollforward you would
  see the following error in the db2diag.log:
  
  2012-11-13-14.25.42.466776+000 I33153A558         LEVEL: Severe
  PID     : 21954586             TID  : 16450       PROC : db2sysc
  6
  INSTANCE: db2inst1              NODE : 000         DB   : MYDB
  APPHDL  : 0-52                 APPID: *N0.db2inst1.121113142516
  AUTHID  : db2inst1
  EDUID   : 16450                EDUNAME: db2redom (MYDB) 6
  FUNCTION: DB2 UDB, database utilities,
  sqluProcessLoadStartLogRcd, probe:2372
  RETCODE : ZRC=0x87040055=-2029780907=SQLD_PRGERR "Unknown
  PROGRAM ERROR"
            DIA8576C A data management services programming error
  occurred.
  
  2012-11-13-14.25.42.467423+000 I33712A543         LEVEL: Error
  PID     : 21954586             TID  : 16450       PROC : db2sysc
  6
  INSTANCE: db2inst1              NODE : 000         DB   : MYDB
  APPHDL  : 0-52                 APPID: *N0.db2inst1.121113142516
  AUTHID  : db2inst1
  EDUID   : 16450                EDUNAME: db2redom (MYDB) 6
  FUNCTION: DB2 UDB, recovery manager, sqlprProcDPSrec, probe:680
  MESSAGE : ZRC=0x87040055=-2029780907=SQLD_PRGERR "Unknown
  PROGRAM ERROR"
            DIA8576C A data management services programming error
  occurred.
  
  2012-11-13-14.25.42.467648+000 I34256A544         LEVEL: Error
  PID     : 21954586             TID  : 16450       PROC : db2sysc
  6
  INSTANCE: db2inst1              NODE : 000         DB   : MYDB
  APPHDL  : 0-52                 APPID: *N0.db2inst1.121113142516
  AUTHID  : db2inst1
  EDUID   : 16450                EDUNAME: db2redom (MYDB) 6
  FUNCTION: DB2 UDB, recovery manager, sqlpPRecReadLog, probe:3720
  RETCODE : ZRC=0x87040055=-2029780907=SQLD_PRGERR "Unknown
  PROGRAM ERROR"
            DIA8576C A data management services programming error
  occurred.
  
  
  In addition, if the database is recoverable and LOAD is using
  COPY NO option, then the unique tablespace in the INDEX IN
  clause of the partition section of the table will incorrectly
  remain in Load In Progress state, after the LOAD completes
  successfully.
  

Local fix

• Creating a partitioned index can work around the problem, e.g.
  the following partitioned index defined:
  
  db2 "create index hdw.idx1 on hdw.picked_order(load_date)
  partitioned"
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All Platform                                                 *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error Description                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to v9.7 Fixpack 8 or Higher                          *
  ****************************************************************
  

Problem conclusion

• First Fixed in v9.7 Fixpack 8
  

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:

Fix information

• Fixed component name

  DB2 FOR LUW

• Fixed component ID

  DB2FORLUW

Applicable component levels

• R970 PSN

     UP