IBM Mainframe Forum

Yes, you can use the DB2 DSNUTILB program and perform an UNLOAD of the table. This will create the unload file and the PUNCH, or Statement file, that you use to reload the data back into the table, again using DSNUTILB. So this can be achieved by using utility programs DSNUTILB and DFSORT.

I'd speak to your DBA about doing the UNLOAD and LOAD REPLACE as you may need to experiment with this a bit if you have not used the DB2 unload and load before.

This is midbolwing Steve. I will use this utility to delete the duplicate records.... I am soo greatful to receive this information from you Steve, Thanks soo much.

You're welcome.

I must just say though that this would not be a recommended way of doing this. Perhaps consider adding a DB2 TIMESTAMP field. You could then delete the duplicate rows using the TIMESTAMP field.

Just a thought.

Thanks Steve... Will consider the TIMESTAMP too...

create temp_table like orig_table
;
insert into temp_table (select col1,col2,col3,col4 from orig_table group by col1,col2,col3,col4)
;
delete from orig_table
;
insert into orig_table (select * from temp_table)
;
drop table temp_table
;

Hello,

If this is a high-volume table, this would not be a good alternative. . .

True, but high volume table wasn't the question

RajendraPrabhuS wrote:Actually it a small table and you are correct i have to do it from SPUFI...

So the answer does exactly what was asked.

Hello,

So the answer does exactly what was asked.

Yup, it does. IMHO another of the things that can be done that usually should not be done. . .

On well-managed/administered systems, data is not deleted (or otherwise modified) without proper footprints.

I've been some places where doing something like this in Production was grounds for termination. . .

If this is some little set of test data that has not been properly controlled to prevent duplicates, i've no problem with a quick spufi fix. I'd strongly suggest that the production system be made "duplicate-proof". Wth the current situation, how would the proper duplicate be kept and the other(s) discarded? If this is just test data, it may not matter. If there is different content in the duplicates it may be less than trivial for "real" data. . .

Try this :

DELETE FROM TABLE ABC
WHERE COLUMN =
(SELECT COLUMN FROM TABLE ABC
GROUP BY COLUMN
HAVING COUNT(*) > 1);

Raghu.

raghucheepinapi wrote:Try this :

DELETE FROM TABLE ABC
WHERE COLUMN =
(SELECT COLUMN FROM TABLE ABC
GROUP BY COLUMN
HAVING COUNT(*) > 1);

Raghu.

This will delete both rows. so if 1001 is duplicate after this you will have 0 rows with 1001 instead of the desired 1.