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

Subscribe

You can track all active APARs for this component.

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 number

    IC88424

  • 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

    2012-11-20

  • Closed date

    2013-04-08

  • Last modified date

    2013-04-08

  • 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



Rate this page:

(0 users)Average rating

Document information


More support for:

DB2 for Linux, UNIX and Windows

Software version:

9.7

Reference #:

IC88424

Modified date:

2013-04-08

Translate my page

Machine Translation

Content navigation