hi everybody,

i have an challenging query to all,

i have a table with three indexex(one prinmary, and two are composite key indexes) when i try to load the data in to the table by using LOAD utility..
the table gets into REBUILD pending Status, when i try to run the REBUILD utility, RBDP status for 2nd and 3rd indexex get recovered but the RBDP status for Ist index cannot be recovered , when try ance again it says the following error...
Duplicate Key ....

so kindly help to recover the table to normal state..

Please read Must Read before posting

Try recovering to the quiesce point right BEFORE the load.

Does your data have duplicate key values? If so, remove the duplicates.

You could build a non-unique index on the primary key columns, and a mirror table (same cols etc), but an additional col - unique_val char(13) for bit data
insert into mirror select *, generate_unique() from original where key_col in (select key_col from original group by key_col having count(*) > 1)
delete from original where key_col in (select key_col from mirror)
delete from mirror m1 where exists (select 1 from mirror m2 where m1.unique_val > m2.unique_val) << some versions of DB2 don't support this, you may need another copy of mirror to make it work.
insert into original select col1, col2, ... <without unique_val> from mirror

It appears you are running DB2 for MVS/OS390.zOS. Alternatively, you could rerun the load using ENFORCE CONSTRAINTS.

James Campbell

I agree mostly with James. And Srini, you could also be having DEFER YES for those indexes on. Thats why they are not being built during load. You can switch it off using ALTER command and then load. This way, it will discard the duplicates.

Alternatively, you can get the columns from the index and run a query on table to see if any rows are there with those column combinations occuring more than once.

I assume that you are having DB2 in OS/390.

thanks for your response,


i have recovered the index, its due to an Empty records in the load dataset..
i have searched the records and found that it has an empty records .. after deleting it .. i have inserted... it the tables has nearly 65000 records..

later i noticed that ...