(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. |
