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
 

 

SQL CODE -536

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

New User


Joined: 04 Jan 2008
Posts: 42
Location: india

PostPosted: Wed Jul 27, 2011 5:21 pm    Post subject: SQL CODE -536
Reply with quote

Hi Friends,

I have 3 tables Supplier, Parts and Shipment table as below.

Supplier Table - S

S# SNAME STATUS CITY
S1 SMITH 20 LONDON
S2 JONES 10 PARIS
S3 BLAKE 30 PARIS
S4 CLARK 20 LONDON
S5 ADAMS 30 ATHENS

Parts Table - P

P# PNAME COLOR WEIGHT CITY
P1 NUT RED 12 LONDON
P2 BOLT GREEN 17 PARIS
P3 SCREW BLUE 17 ROME
P4 SCREW RED 14 LONDON
P5 CAM BLUE 12 PARIS
P6 COG RED 19 LONDON

Shipment Table - SP

S# P# QTY
S1 P1 300
S1 P2 200
S1 P3 400
S1 P4 200
S1 P5 100
S1 P6 100
S2 P1 300
S2 P2 400
S3 P2 200
S4 P2 200
S4 P4 300
S4 P5 400

1. The Shipment table (SP) is child table for the Supplier (S) and Parts table (P)
2. such that column SP.S# REFERENCES Supplier table S (column S#) ON DELETE CASCADE
3. Column SP.P# REFERENCES Parts table P (Column P#) ON DELETE RESTRICT

When I executed the below query I am getting -536 error (THE DELETE STATEMENT IS INVALID BECAUSE TABLE table-name CAN BE AFFECTED BY THE OPERATION)

DELETE
FROM S
WHERE NOT EXISTS
( SELECT *
FROM SP
WHERE SP.S# = S.S#) --> ended with -536 error

However, when I execute the same query with WHERE EXISTS clause it works fine.

DELETE
FROM S
WHERE EXISTS
( SELECT *
FROM SP
WHERE SP.S# = S.S#) --> works fine.

Could anyone please explain why the same query executed fine with WHERE EXISTS clause and does not work fine with WHERE NOT EXISTS Clause.

Thanks & Regards
Prasannaa S
Back to top
View user's profile Send private message

gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Wed Jul 27, 2011 5:27 pm    Post subject:
Reply with quote

Programmer Response: Do not attempt to reference a table in a subquery of
a DELETE statement when the table can be affected by the DELETE statement.
Back to top
View user's profile Send private message
sprasannapathy

New User


Joined: 04 Jan 2008
Posts: 42
Location: india

PostPosted: Wed Jul 27, 2011 6:25 pm    Post subject:
Reply with quote

Hi gylbharat,

Thank you for response. I would like you to go through my posted subject and Query completely. I am aware about delete-connect rule. I am not requesting for the programmer response when -536 error happens. My question is

1. The same query executes fine with WHERE EXISTS clause but ends with -536 error with WHERE NOT EXISTS clause. Why this happens? If DB2 issues -536 error for the dependent table (relationship with a delete rule of CASCADE or SET NULL – for last delete rule in the parent & child path ) in subquery, it should be for both WHERE EXISTS and WHERE NOT EXISTS clause.

2. What can go wrong if this restriction is violated?

Thanks & regards
Prasannaa S
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Jul 27, 2011 8:16 pm    Post subject:
Reply with quote

Hello,

Quote:
I am not requesting for the programmer response when -536 error happens.
No, you're not - but you also do not appear understand the -526. The answer given is exactly what caused the condition.

Quote:
Why this happens?
Because of the internal processing rule. . .

Quote:
What can go wrong if this restriction is violated?
I believe db2 will prevent this every time it is attempted so other than the -526 nothing else should "go wrong". It will not be permitted. . .
Back to top
View user's profile Send private message
sprasannapathy

New User


Joined: 04 Jan 2008
Posts: 42
Location: india

PostPosted: Fri Jul 29, 2011 5:48 pm    Post subject:
Reply with quote

Quote:
No, you're not - but you also do not appear understand the -526. The answer given is exactly what caused the condition.


I error code -526 is related to temporary table (I am not using temporary table, I think it is a typo). I have read the explanation for error -536 and understood (I have cut pasted the explanation in my initial posted subject).

Quote:
Why this happens? - Because of the internal processing rule. . .


Yes, this is because of internal processing rule. However, the rule should work for both WHERE EXISTS clause and WHERE NOT EXISTS clause. Whereas same query executes fine with WHERE EXISTS clause but ends with -536 error with WHERE NOT EXISTS clause.

Thanks. Prasannaa S
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Fri Jul 29, 2011 6:35 pm    Post subject:
Reply with quote

you are the one that has access to the ddl,
you are the one that has access to the constrainsts
in fact, you are the only one who has access to anything
that could help in resolving YOUR PROBLEM.

we have to desire to guess.

you want guesses, create a random comment generator.
Back to top
View user's profile Send private message
daveporcelan

Active Member


Joined: 01 Dec 2006
Posts: 646
Location: Pennsylvania

PostPosted: Fri Jul 29, 2011 7:01 pm    Post subject:
Reply with quote

Quote:
However, the rule should work for both WHERE EXISTS clause and WHERE NOT EXISTS clause.


On what basis do you believe this to be true?

This is purely an assumption on your part, nothing more.

In fact, the evidence shows otherwise.

Either open a ticket with IBM, or shut the whole line of inquiry down.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Jul 29, 2011 8:34 pm    Post subject:
Reply with quote

Hello,

Quote:
However, the rule should work for both WHERE EXISTS clause and WHERE NOT EXISTS clause.
Please post anything from ibm/db2 (not someone else's thoughts) that confirms this. . .

Why should "just anything" work? In one of your cases, there is no problem - in the other there is. You will need to work with your data and your support people to determine the specifics, but it is rarely appropriate to believe that if a thing works one way it will work some other way.
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 How to Capture the Max return code of... anilkumar922 All Other Mainframe Topics 0 Thu Jan 12, 2017 12:02 pm
This topic is locked: you cannot edit posts or make replies. Calling REXX code from ISPF panel sanchita dey TSO/ISPF 4 Mon Jan 02, 2017 5:13 pm
No new posts PL/I code tuning/Performance improvement Virendra Shambharkar PL/I & Assembler 4 Mon Dec 05, 2016 11:57 am
No new posts What is the code in CLIST to enable t... jackzhang75 CLIST & REXX 1 Fri Dec 02, 2016 3:02 am
No new posts IDEAL - Code Retrieval yugendran CA Products 0 Fri Nov 25, 2016 3:27 pm


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