IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Deletion of Duplicate Records from table


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Debasis Misra
Warnings : 1

New User


Joined: 16 Sep 2008
Posts: 72
Location: Bangalore

PostPosted: Mon Sep 14, 2009 4:13 pm
Reply with quote

Hi

Here is the Table Sructure:

Emp-ID Suffix Amt
===== ==== ===
10 1 100
10 2 200
10 3 150
10 4 250

Primary Keys : Emp-ID & Suffix.

Requiremnt: Needs to delete all the duplicate records for the Emp-ID = 10, except any one of the row.

Please Help me.
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Mon Sep 14, 2009 4:16 pm
Reply with quote

a db2 table does not contain records, just rows
already discussed quite a few times,
search the forums with db2 delete duplicate rows
You will find certainly an answer to Your need
Back to top
View user's profile Send private message
Keanehelp

New User


Joined: 27 May 2008
Posts: 71
Location: USA, CA.

PostPosted: Mon Sep 14, 2009 4:27 pm
Reply with quote

Hi,

We in our project had the same problem and we did this by looping Cursors in our Cobol program. I am giving you an example of delete cursor below. Please note that you need to skip the delete query first time and then you can delete every subsequent duplicate row.

Also I tried to do this with a single query but could not succed in DB2, though it is possible in different ORACLE databases.

EXEC SQL
DECLARE THISEMP CURSOR FOR
SELECT EMPNO, LASTNAME,
DEPT, JOB
FROM EMP
WHERE DEPT = 'D11'
FOR UPDATE OF JOB
END-EXEC.


EXEC SQL
OPEN THISEMP
END-EXEC.


EXEC SQL
FETCH THISEMP
INTO :EMP-NUM, :NAME2,
:DEPT, :JOB-NAME
END-EXEC.


EXEC SQL
DELETE FROM EMP
WHERE CURRENT OF THISEMP
END-EXEC.



EXEC SQL
CLOSE THISEMP
END-EXEC.


Thanks

N
Back to top
View user's profile Send private message
Debasis Misra
Warnings : 1

New User


Joined: 16 Sep 2008
Posts: 72
Location: Bangalore

PostPosted: Mon Sep 14, 2009 5:06 pm
Reply with quote

Thanks Keanehelp
Is it possible to do in SPUFI?
Back to top
View user's profile Send private message
Debasis Misra
Warnings : 1

New User


Joined: 16 Sep 2008
Posts: 72
Location: Bangalore

PostPosted: Mon Sep 14, 2009 5:33 pm
Reply with quote

Hi enrico-sorichetti,

"db2 delete duplicate rows" -- I didnt get something useful for my purpose. Can uoy please help me?
Back to top
View user's profile Send private message
Srihari Gonugunta

Active User


Joined: 14 Sep 2007
Posts: 295
Location: Singapore

PostPosted: Mon Sep 14, 2009 6:05 pm
Reply with quote

If you want to use it only for Emp-Id 10. You can use the following

Code:
DELETE FROM TAB A
WHERE A.Suffix <>
(SELECT MAX(B.Suffix)
FROM TAB B
WHERE B.Emp_Id = 10)
and A.Emp_Id = 10;


If you want to use it for all Emp-Ids you can use the following.
Code:
DELETE FROM TAB A
WHERE A.Suffix <>
(SELECT MAX(B.Suffix)
FROM TAB B
WHERE B.Emp_Id = A.Emp_id);


Even though I tested it, I suggest you please try with a ROLLBACK and verify the result before you actually use this SQL.
Back to top
View user's profile Send private message
lkhiger

New User


Joined: 28 Oct 2005
Posts: 89

PostPosted: Tue Sep 15, 2009 8:02 am
Reply with quote

Srihari Gonugunta wrote:
If you want to use it only for Emp-Id 10. You can use the following

Code:
DELETE FROM TAB A
WHERE A.Suffix <>
(SELECT MAX(B.Suffix)
FROM TAB B
WHERE B.Emp_Id = 10)
and A.Emp_Id = 10;


If you want to use it for all Emp-Ids you can use the following.
Code:
DELETE FROM TAB A
WHERE A.Suffix <>
(SELECT MAX(B.Suffix)
FROM TAB B
WHERE B.Emp_Id = A.Emp_id);


Even though I tested it, I suggest you please try with a ROLLBACK and verify the result before you actually use this SQL.


Nobody said primary key is unique.

How it'll work if MAX(Suffix) is repeated for Emp_id ?

The best way known to me:

Quote:
1. insert this single row in the Global Temporary Table
2. delete from TAB all rows with Emp_Id = 10 (or another)
3. insert into TAB selected row from the Global Temporary Table


Lenny
Back to top
View user's profile Send private message
Keanehelp

New User


Joined: 27 May 2008
Posts: 71
Location: USA, CA.

PostPosted: Tue Sep 15, 2009 2:05 pm
Reply with quote

Hi Debasis,

No, you can not execute this in QMF as you can only use cursors in Cobol program.

Thanks

Nitin
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Tue Sep 15, 2009 2:15 pm
Reply with quote

a mild warning, before proceeding with mass updates/deletions

- find out how many records/rows/segments are involved in the process ( (duplicates in this case)
- depending on the number of records/rows/segment involved choose the method...

if the number of <things> involved is more than 30% (*) it might be worth meditate on an alternative approach ...
unload, offline processing and reload

30% is usually the limit between sequential/random processing, unload/reload

the issue is valid for all environments KSDS/DB2/IMS/DB
Back to top
View user's profile Send private message
Debasis Misra
Warnings : 1

New User


Joined: 16 Sep 2008
Posts: 72
Location: Bangalore

PostPosted: Tue Sep 15, 2009 3:12 pm
Reply with quote

Hi enrico,

The no of records will be very less. Maximum 5-6 recods.
Back to top
View user's profile Send private message
Terry Heinze

JCL Moderator


Joined: 14 Jul 2008
Posts: 1249
Location: Richfield, MN, USA

PostPosted: Tue Sep 15, 2009 9:48 pm
Reply with quote

Thanks for that info enrico. I was always curious as to the break-even point of when to update and when to unload/reload.
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Tue Sep 15, 2009 10:45 pm
Reply with quote

Hi Terry,
rule of thumb, the exact figure is not really important might even be lower
the important thing is to realize the need for such evaluation
and proceed being aware of what might be going on

for dbms let db2 or ims/db other factors would be enq and log issues...

a simple sql
delete from some_table where some_condition
might cause abends for enq buffers overrun, delays for log switching, add as many as You want
backout of the delete abend, a real resopurce hog icon_biggrin.gif
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Tue Sep 22, 2009 6:50 pm
Reply with quote

lkhiger wrote:
Nobody said primary key is unique.


icon_eek.gif I thought that was part of the definition
Back to top
View user's profile Send private message
lkhiger

New User


Joined: 28 Oct 2005
Posts: 89

PostPosted: Wed Sep 23, 2009 6:45 am
Reply with quote

GuyC wrote:
lkhiger wrote:
Nobody said primary key is unique.


icon_eek.gif I thought that was part of the definition

In this case I don't understand what does it means:

Quote:
Primary Keys : Emp-ID & Suffix.

Requiremnt: Needs to delete all the duplicate records for the Emp-ID = 10, except any one of the row.

icon_question.gif

Lenny
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Wed Sep 23, 2009 12:31 pm
Reply with quote

well : maybe that the primary key exists of 2 columns : Emp-id , Suffix icon_question.gif
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Wed Sep 23, 2009 12:38 pm
Reply with quote

The TS has simply overinformed us by posting irrelevant info,
giving everybody a chance to add noise to the topic icon_wink.gif

Quote:
Requiremnt: Needs to delete all the duplicate records for the Emp-ID = 10, except any one of the row.


... really this topic was started under the sillyness insignia...
from the columns description there is an amount involved...
so the employee in the next pay slip will be credited or debited a random amount...

and people speak about IT results being determinstic and repeatable icon_rolleyes.gif
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Load new table with Old unload - DB2 DB2 6
No new posts Duplicate transid's declared using CEDA CICS 3
No new posts Compare only first records of the fil... SYNCSORT 7
No new posts Pulling a fixed number of records fro... DB2 2
No new posts Multiple table unload using INZUTILB DB2 2
Search our Forums:

Back to Top