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

Need to delete inactive records from 150 existing tables


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Sanchayita Sinha

New User


Joined: 29 May 2020
Posts: 2
Location: India

PostPosted: Fri May 29, 2020 10:47 pm
Reply with quote

Hi, I need to create a high level design document which will include deletion of all inactive accounts and it's related records from 150 existing tables. The active account should not be impacted.

Here's 2-3 approaches with small variations that I thought of.
1) We can unload all the tables with imagecopy for backup, remove the unwanted records from the child tables and independent tables first, then from the parent tables and load it back using JCL.
2) We can unload all the tables with imagecopy for backup, extract the inactive accounts from the child tables and independent tables first, then from the parent tables usin JCL. Then delete all the inactive records from the tables using COBOL DB2 program.
3) Write a COBOL DB2 program and declare a cursor to retrieve the inactive accounts and delete those records first from the child and independent tables, then from the parent tables.

Can you please suggest any other approaches which will be more efficient and effective?

Thanks in Advance!
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10801
Location: italy

PostPosted: Fri May 29, 2020 11:07 pm
Reply with quote

what is the expected elapsed time ?
how large/small is the window to do it ?

are there any relations/dependencies between the accounts that will be kept and the accounts that will deleted
Back to top
View user's profile Send private message
prino
Warnings : 1

Senior Member


Joined: 07 Feb 2009
Posts: 1263
Location: Vilnius, Lithuania

PostPosted: Sat May 30, 2020 12:16 am
Reply with quote

Sanchayita Sinha wrote:
3) Write a COBOL DB2 program and declare a cursor to retrieve the inactive accounts and delete those records first from the child and independent tables, then from the parent tables.

Don't you have referential integrity constraints? A delete from the parent should be able to automagically delete the children!

Sheesh...
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2789
Location: NYC,USA

PostPosted: Sat May 30, 2020 12:19 am
Reply with quote

Welcome to this forum!!
Quote:
Can you please suggest any other approaches which will be more efficient and effective?
Did you talk to DBA yet? They might come up with better choices.
The first approach looks effective as long as you figured out all the relationships between these tables.

How about also thinking of ON DELETE CASCADE?
Back to top
View user's profile Send private message
Sanchayita Sinha

New User


Joined: 29 May 2020
Posts: 2
Location: India

PostPosted: Sun May 31, 2020 6:48 pm
Reply with quote

There is no relation between the deleting accounts and the remaining accounts.

And, yes I didn't think of referential integrity.


Can you please elaborate how to use delete on cascade in this case?

We have to find out few feasible approaches and then we will talk to the DBA.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2789
Location: NYC,USA

PostPosted: Mon Jun 01, 2020 2:30 am
Reply with quote

You have all the above approaches and I guess that’s good enough to engage DBA’s. It’s not good to wait until the last moment rather they should be the first place to go and get suggestions.

Google ON DELETE CASCADE and that goes with referential integrity.
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 How to: PK does not exist in several ... DB2 5
This topic is locked: you cannot edit posts or make replies. Sort to include records of file 2 int... Java & MQSeries 1
No new posts Cobol program to determine the sequen... COBOL Programming 2
No new posts Merge a variable number of records fr... DFSORT/ICETOOL 5
This topic is locked: you cannot edit posts or make replies. copy file 2 data into file1 for match... DFSORT/ICETOOL 4
Search our Forums:

Back to Top