|
View previous topic :: View next topic
|
| Author |
Message |
Sanchayita Sinha
New User
Joined: 29 May 2020
Posts: 2
Location: India
|
|
|
|
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 |
|
 |
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
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 |
|
|
prino
Senior Member
Joined: 07 Feb 2009
Posts: 1306
Location: Vilnius, Lithuania
|
|
|
|
| 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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
|
|
|
|
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 |
|
|
Sanchayita Sinha
New User
Joined: 29 May 2020
Posts: 2
Location: India
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
|
|
|
|
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 |
|
|
|
|
