My requirement is to delete data from db2 table which contains duplicate phone num(invalid phone num, set of 16 phone numbers which is hardcoded).And one phone num should be retained , that depends on sequence number(minimum value).
consider main table has following data : PHONE_TAB
Client_Id Phone_num Seq_num 1234 45678 15 --- delete 4444 55555 45 1234 45678 10 ---should be retained 5555 22222 25 1234 45678 20 --- delete 1234 45678 11 --- delete
Note : also i have to move the rows which will be deleted to output tape file before deleting to take backup.
Have come up with the below logic, Declaring cursor :
EXEC SQL DECLARE CUR1 CURSOR WITH HOLD FOR SELECT * FROM PHONE_TAB WHERE PHONE_NUM = 45678 END-EXEC
EXEC SQL FETCH CUR1 INTO :DCLGEN_CLIENT_ID :DCLGEN_PHONE_NUM :DCLGEN_SEQ_NUM END-EXEC
EXEC SQL SELECT MIN(SEQ_NUM) FROM PHONE_TAB WHERE CLIENT_ID = :DCLGEN_CLIENT_ID PHONE_NUM = :DCLGEN_PHONE_NUM GROUP BY CLIENT_ID AND PHONE_NUM INTO WS_MIN END-EXEC EVALUATE TRUE WHEN SEQ_NUM > WS_MIN PERFORM BACKUP-PARA WHEN OTHER PERFORM FETCH-PARA
Finally , DELETE-PARA
EXEC SQL DELETE FROM PHONE_TAB WHERE CURRENT CURSOR END-EXEC
Can someone tell me whether this logic is right ? My doubt is whether it will do mass deletion without taking backup to tape file?
You can delete them all in one go with the following SQL
DELETE FROM PHONE_TAB A WHERE EXISTS (SELECT 1 FROM PHONE_TAB B WHERE A.PHONE_NUM = B.PHONE_NUM AND A.SEQ_NUM > B.SEQ_NUM)
It works by deleting all rows where another row exists that has the same phone number and a lower sequence number.
You can check that it deletes the right rows by first running it with a select, like this
SELECT * FROM PHONE_TAB A WHERE EXISTS (SELECT 1 FROM PHONE_TAB B WHERE A.PHONE_NUM = B.PHONE_NUM AND A.SEQ_NUM > B.SEQ_NUM)
If you need to backup the rows, you can run the select and then delete the rows.