Portal | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Forum Index
 
Register
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Need to delete inactive records from 150 existing tables

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Need to delete inactive records from 150 existing tables
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

Senior Member


Joined: 14 Mar 2007
Posts: 10700
Location: italy

PostPosted: Fri May 29, 2020 11:07 pm    Post subject: Reply to: Need to delete inactive records from 150 existing tables
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

Senior Member


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

PostPosted: Sat May 30, 2020 12:16 am    Post subject: Re: Need to delete inactive records from 150 existing tables
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: 2467
Location: NY,USA

PostPosted: Sat May 30, 2020 12:19 am    Post subject:
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    Post subject:
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: 2467
Location: NY,USA

PostPosted: Mon Jun 01, 2020 2:30 am    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Group selective VB records - Trailer ... Premnath_TJ DFSORT/ICETOOL 12 Sun Sep 13, 2020 3:38 am
No new posts insert into session tables with UR jzhardy DB2 0 Fri Aug 28, 2020 3:20 pm
No new posts Sync logic between VSAM files and DB2... murali.andaluri COBOL Programming 9 Tue Aug 25, 2020 1:01 pm
This topic is locked: you cannot edit posts or make replies. SORT the records based on the second ... Ramsee SYNCSORT 1 Wed Aug 19, 2020 3:01 pm
No new posts to Insert Rows from File1 to File 2 ... Arun_Tupili DFSORT/ICETOOL 1 Thu Aug 13, 2020 6:22 pm

Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us