11944

DB2/Cursor program working in cobol

Question:

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

FETCH-PARA

EXEC SQL FETCH CUR1 INTO :DCLGEN_CLIENT_ID :DCLGEN_PHONE_NUM :DCLGEN_SEQ_NUM END-EXEC

VALIDATE-PARA

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?

Thanks

Answer1:

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.

Recommend

  • Simulate an array with a struct in C#?
  • WCF Communication though named pipes with non .net App
  • How to Sum array parts within a loop with time-complexity of Big O(N)
  • Google Chart: How to draw the vertical line for bar graph
  • How to change color of Camera LED / Flashlight in Android
  • How to resize a pixmap with XLib?
  • how to remove comments from a bash script
  • Where to save the local DB created for iphone app?
  • R DataTable Join and constrain rows
  • Importing Excel files with a large number of columns header into mysql with c#
  • Table striping rows in CSS Grid
  • How to check if a string contains any string of a column in MySQL and vice versa?
  • How to Add Polymorphic Comments to Feed?
  • Oracle - Second level subquery cannot see field from main query
  • Are there any side effects from calling SQLAlchemy flush() within code?
  • How can i dump blob fields from mysql tables
  • Angular2 - Template reference inside NgSwitch
  • Upload file that is in the cpan database
  • User messaging system
  • Row to Column conversion in Talend
  • Merging rows to columns
  • ASP.NET MVC Application won't update some controllers
  • Low TTL with Leveled Compaction, should I reduce gc_grace_seconds to improve read performance withou
  • Limiting recursion to certain level - Duplicate rows
  • How to rebase a series of branches?
  • Retrieving value from sql ExecuteScalar()
  • Deserializing XML into class C#
  • vba code to select only visible cells in specific column except heading
  • Do I've to free mysql result after storing it?
  • Function pointer “assignment from incompatible pointer type” only when using vararg ellipsis
  • Run Powershell script from inside other Powershell script with dynamic redirection to file
  • How do I rollback to a specific git commit
  • Transpose CSV data with awk (pivot transformation)
  • Load html files in TinyMce
  • How can I get HTML syntax highlighting in my editor for CakePHP?
  • coudnt use logback because of log4j
  • python draw pie shapes with colour filled
  • Programmatically clearing map cache
  • Sorting a 2D array using the second column C++
  • How to Embed XSL into XML