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
 

 

Cannot use on delete cascade

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

New User


Joined: 17 Feb 2009
Posts: 32
Location: Bangalore

PostPosted: Fri Mar 20, 2009 10:13 am    Post subject: Deleting rows from a table
Reply with quote

I used the below query with ON DELETE CASCADE option to delete rows from all the tables ( Parent & child ).

DELETE FROM ALSR5. table1
WHERE
BOR_SSN = ********* AND
BOR_ACT_SFX = '1' AND
BOR_SEQ_NUM = 01
ON DELETE CASCADE

Here table1 is the parent table and we have got many child tables. The problem here is the table is defined with DELETE RESTRICT option because of which i couldn't able to use ON DELETE CASCADE option. So i got -199 error on spufi.

Let know if there are any other keyword with which we can achieve this.
Back to top
View user's profile Send private message

Debasis Misra
Warnings : 1

New User


Joined: 16 Sep 2008
Posts: 72
Location: Bangalore

PostPosted: Fri Mar 20, 2009 10:20 am    Post subject:
Reply with quote

Sriram,

i think you atr trying to post a new topic
Back to top
View user's profile Send private message
ovreddy

Active User


Joined: 06 Dec 2004
Posts: 211
Location: Keane Inc., Minneapolis USA.

PostPosted: Thu Mar 26, 2009 2:42 am    Post subject:
Reply with quote

Hi,

DELETE RESTRICT restricts deletion of child table rows whenever a parent row is deleted. In turn you cannot delete any thing. I hope this will not work even in reverse direction of Deleting child table then deleting parent if it is a foreign key with DELETE RESTRICT in both parent and child tables.

The only way would be to disable this for some time and delete needed rows. Then reenable it. Contact your DBA to do this.

Thanks,
Reddy
Back to top
View user's profile Send private message
Raghu navaikulam

Active User


Joined: 27 Sep 2008
Posts: 191
Location: chennai

PostPosted: Fri Mar 27, 2009 12:39 am    Post subject: Reply to: Cannot use on delete cascade
Reply with quote

Hi Sriram

Could you please post the entire error message generated by DB2. The SQLCODE -199 says it is purely a syntax error. Can you please check at your shop whether the ON DELETE CASCADE can be used with the DELETE statement. To my knowledge, ON DELETE CASCADE is a RI rule, that can be mentioned with DDL, not DML. Check your DB2 version you are using and also check whether this statement is a valid one for your DB2.

Regards
Raghu
Back to top
View user's profile Send private message
ruodeer

New User


Joined: 06 Jul 2007
Posts: 59
Location: home

PostPosted: Fri Mar 27, 2009 7:06 am    Post subject:
Reply with quote

Hi, I believe
Code:
ON DELETE CASCADE
is kind of syntax of referential constraint, you can not use it in Delete statement, please check DB2 SQL manual.
Back to top
View user's profile Send private message
senthilnathanj

New User


Joined: 31 Jul 2007
Posts: 47
Location: chennai

PostPosted: Fri Mar 27, 2009 3:45 pm    Post subject:
Reply with quote

hi,
SQLCODE -199
Explanation: A syntax error was detected in the statement at the point
where the keyword keyword appears.
For Example,
Create a table COLOR with the Parent table.
Code:
CREATE TABLE COLOR
(
    c_id INTEGER NOT NULL PRIMARY KEY,
    c_desc  CHAR(20) );

Create a Child table OBJECT as mentioned below:
Code:
CREATE TABLE OBJECT
(
  o_id CHAR(2),
  o_desc CHAR(20),
  c_id   INTEGER  REFERENCES  COLOR(c_id) ON DELETE CASCADE);

Code:
DELETE  FROM COLOR  WHERE c_id =2

you can create like this and execute.
Please let me know if any concerns..
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 JCL to delete component in a package sundaram.naveen Compuware & Other Tools 14 Tue Nov 29, 2016 6:21 pm
No new posts How to delete second instance from Fl... Gunapala CN DFSORT/ICETOOL 6 Tue Oct 18, 2016 11:42 pm
No new posts Delete record or Reset RC code using ... mpawan Compuware & Other Tools 4 Tue Jul 26, 2016 3:52 pm
No new posts Delete VSAM File Records amitc23 CICS 6 Sun Apr 10, 2016 10:10 am
No new posts DB2 Stored Procedure - Dynamic SQL - ... GuyC DB2 2 Wed Feb 17, 2016 5:10 pm


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