Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Deleting rows from parent and dependent tables at one shot

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
anju subrahmanian

New User


Joined: 11 Jul 2010
Posts: 21
Location: Bangalore

PostPosted: Wed Jul 21, 2010 3:38 pm    Post subject: Deleting rows from parent and dependent tables at one shot
Reply with quote

Hi,

I am very new to DB2.

I want to delete one record from a particular table say 'A'. Before this obviously I have to delete rows from tables dependent on 'A'.

My question is can we do this in one shot? i.e when i say to delete from table 'A' corresponding rows from dependent tables should also get deleted. Also while defining the constraints between tables 'CASCADE' option is not used.
Back to top
View user's profile Send private message

sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Wed Jul 21, 2010 3:56 pm    Post subject:
Reply with quote

Anju,

Its time to use the CASCADE option.

Thanks,
Sushanth
Back to top
View user's profile Send private message
anju subrahmanian

New User


Joined: 11 Jul 2010
Posts: 21
Location: Bangalore

PostPosted: Wed Jul 21, 2010 4:17 pm    Post subject: Reply to: Deleting rows from parent and dependent tables at
Reply with quote

In fact, I cannot touch the way table is defined.

What I am supposed to do is prepare a jcl to delete a particular row from a table. But I am finding a chain of dependencies for this table which makes my life miserable.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Wed Jul 21, 2010 5:13 pm    Post subject:
Reply with quote

Anju,

Simple, tell your manager this is the easiest & efficient way and it will a lot of programming time. Ask him to raise a DBA request for this.

You don't have to ALTER anything, DBA will take care of it.

Sushanth
Back to top
View user's profile Send private message
anju subrahmanian

New User


Joined: 11 Jul 2010
Posts: 21
Location: Bangalore

PostPosted: Thu Jul 22, 2010 2:47 pm    Post subject: Reply to: Deleting rows from parent and dependent tables at
Reply with quote

I could not find any useful information so far regarding this. I have to delete rows from each and every depenedent table explicitly.

Thanks for replying.........
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Thu Jul 22, 2010 4:10 pm    Post subject:
Reply with quote

Anju,

You should be looking at DB2 V9 - SQL REFERENCE manual and for definations click here

Code:
ALTER TABLE DSN8910.DEPT
FOREIGN KEY(ADMRDEPT) REFERENCES DSN8910.DEPT ON DELETE CASCADE;


Thanks,
Sushanth
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2276
Location: @my desk

PostPosted: Thu Jul 22, 2010 4:27 pm    Post subject: Re: Reply to: Deleting rows from parent and dependent tables
Reply with quote

anju subrahmanian wrote:
But I am finding a chain of dependencies for this table which makes my life miserable.
May be only once. After finding the dependent tables, you can prepare standard SQLs to do it next time without having to find out the relations again.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Thu Jul 22, 2010 5:13 pm    Post subject:
Reply with quote

Arun,

Quote:
May be only once

If this is done by SQL, whenever an dependent table is created, now or later.
Developer has to remember to add delete statements for this new table also, when deleting from parent table. Otherwise you will be finding a lot of orphan rows.

If you are going to use DELETE statements, you can also have an option of using TRIGGERs.


Thanks,
Sushanth
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2276
Location: @my desk

PostPosted: Thu Jul 22, 2010 6:09 pm    Post subject:
Reply with quote

sushanth bobby wrote:
Developer has to remember to add delete statements for this new table also, when deleting from parent table.
Not if properly documented.
Back to top
View user's profile Send private message
anju subrahmanian

New User


Joined: 11 Jul 2010
Posts: 21
Location: Bangalore

PostPosted: Thu Jul 22, 2010 7:04 pm    Post subject: Reply to: Deleting rows from parent and dependent tables at
Reply with quote

Sushanth, I understood what you suggested, but I was not in a position to do such things..........

Thanks for the support
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 Data replication from multiple Db2 ta... kishpra DB2 9 Mon Mar 27, 2017 9:58 pm
No new posts To display values of multiple rows in... vinu78 DB2 2 Thu Mar 09, 2017 1:20 pm
No new posts SPUFI -- Joining 3 tables – data in... Sysaron DB2 2 Wed Mar 08, 2017 4:18 am
No new posts To know activities on db2 tables ashek15 DB2 4 Fri Mar 03, 2017 11:23 pm
No new posts how to send just 10 rows in a CICS sc... Megha Gupta CICS 5 Thu Feb 23, 2017 6:57 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us