Delete duplicate rows in fragmented table without

Greetings again. IDS 11.5 I think I saw PaulW address this in a search result yesterday but I can't find it now. I have a fragmented (by expression) table named newrt. Due to an abort and restart of a load, one of the partitions has a large number of repeated rows. (I was waiting to create the PK index until after the load.) If the table had rowids it would be easy: select n2.rowid row_id from newrt n1, newrt n2 where n1.pkcol1 = n2.pkcol1 and n2.pkcol2 = n2.pkcol2 and n1.rowid < n2.rowid into temp drop_these; delete from newrt n2 where n2.rowid in (select row_id from drop_these); This deletes one of the duplicate rows and leaves one copy. (I think) But what do I do for a fragmented table that does not have rowids? I can't add them now - the table has over 75 million huge rows! I could write a 4GL or Perl program for this one purpose but I would not learn anything from doing so. I'm listening! Thanks. -- Jacob S.

First check for triggers which may take action when you delete/insert rows Get downtime to disable if needed Then how about... Create a table with the same columns as the existing table. insert into newtab1 select * from existingtab group by <all cols> having count(*) >1 create procedure killerproc- foreach select * from newtab2 - - begin work - - delete from existingtab where ....all cols match - - uses sqlca.sqlerrd[2] (?) to log how many copies were deleted - - insert into existing tab the row from newtab2 - - commit work - - return the existing row and count of deleted rows - - on exception rollback work - end foreach then select * from table(killerproc()) !! Don't forget to drop the killerproc when done! Regards, David. > On 09 August 2016 at 22:09 JACOB SALOMON <jakesalomon@yahoo.com> wrote: > > > Greetings again. > > IDS 11.5 > > I think I saw PaulW address this in a search result yesterday but I can't find > > it now. > > I have a fragmented (by expression) table named newrt. Due to an abort and > restart of a load, one of the partitions has a large number of repeated rows. > (I was waiting to create the PK index until after the load.) > > If the table had rowids it would be easy: > > select n2.rowid row_id > from newrt n1, newrt n2 > where n1.pkcol1 = n2.pkcol1 > > and n2.pkcol2 = n2.pkcol2 > > and n1.rowid < n2.rowid > into temp drop_these; > delete from newrt n2 > where n2.rowid in (select row_id from drop_these);> > This deletes one of the duplicate rows and leaves one copy. (I think) > > But what do I do for a fragmented table that does not have rowids? I can't add > > them now - the table has over 75 million huge rows! > > I could write a 4GL or Perl program for this one purpose but I would not learn > > anything from doing so. > > I'm listening! > > Thanks. > > -- Jacob S. > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. >

Thanks, Dave. That would indeed be a very tedious route. My fallback is to detach & recreate the fragment with the duplicate rows and reload 15+million rows. UGH! I'm not sure what's better! :-( I'm trying the write a situation-specific Perl script to handle this and I'll post my travails in another thread. -- Jacob S.