[DB2 z/OS 12]

Ruediger Kurtz

[DB2 z/OS 12]
Folks,

we’re using quite a bit of RI in our shop. Due to legal requirements we are forced to change some delete-rules from restrict to cascade.
All changes but one have successfully been implemented so far, but this one drives us nuts.

We are receiving an -634 and have spent the last two days looking for an explanation but haven’t come up with any so far.
For those who are not familiar with this code:

<snip>

-634

THE DELETE RULE MUST NOT BE CASCADE

Explanation
~~~~~~~~~~~

The code is used to report that the CASCADE delete rule specified in the
FOREIGN KEY clause of an ALTER TABLE statement is invalid because:

The relationship would form a cycle that would cause a table to be
delete-connected to itself.

The relationship would cause another table to be delete-connected to the
same table through multiple paths with different delete rules or with a
delete rule equal to SET NULL.

</snip>

We can rule out the SET NULL rule.
We changed delete-rules forth and back, set them to ENFORCE NO, but the -634 won’t go away.
Now the question to you SQL gurus out there: Do any of you have an sql handy that detects any such circle?

Thanks in advance

Rüdiger

Rüdiger Kurtz
Abteilung Informatik – Betrieb

HUK-COBURG
Bahnhofsplatz
96444 Coburg
Telefon: 09561 96-44148
Telefax: 09561 96-44104
E-Mail: [login to unmask email]
Internet: www.huk.de
________________________________
HUK-COBURG Haftpflicht-Unterstützungs-Kasse kraftfahrender Beamter Deutschlands a. G. in Coburg
Reg.-Gericht Coburg HRB 100; St.-Nr. 9212/101/00021
Sitz der Gesellschaft: Bahnhofsplatz, 96444 Coburg
Vorsitzender des Aufsichtsrats: Prof. Dr. Heinrich R. Schradin.
Vorstand: Klaus-Jürgen Heitmann (Sprecher), Stefan Gronbach, Dr. Hans Olav Herøy, Dr. Jörg Rheinländer (stv.), Sarah Rössler, Daniel Thomas (stv.).
________________________________
Diese Nachricht enthält vertrauliche und/oder rechtlich geschützte Informationen.
Wenn Sie nicht der richtige Adressat sind oder diese Nachricht irrtümlich erhalten haben,
informieren Sie bitte sofort den Absender und vernichten Sie diese Nachricht.
Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Nachricht ist nicht gestattet.

This information may contain confidential and/or privileged information.
If you are not the intended recipient (or have received this information in error) please notify the
sender immediately and destroy this information.
Any unauthorized copying, disclosure or distribution of the material in this information is strictly forbidden.
________________________________

Philip Sevetson

[DB2 z/OS 12]
(in response to Ruediger Kurtz)
Rudiger,
Do you have a catalog navigation tool? Also… do you suspect the RI loop of being so long (or complex) that walking the SYSRELS table won’t be enough to find it? You’d need a recursion processor to concatenate table names, I think, and set it to loop at least as many times as your worst-case imagination about number of levels.

From: Kurtz, Rüdiger [mailto:[login to unmask email]
Sent: Tuesday, January 09, 2018 9:35 AM
To: '[login to unmask email]'
Subject: [DB2-L] - [DB2 z/OS 12]

Folks,

we’re using quite a bit of RI in our shop. Due to legal requirements we are forced to change some delete-rules from restrict to cascade.
All changes but one have successfully been implemented so far, but this one drives us nuts.

We are receiving an -634 and have spent the last two days looking for an explanation but haven’t come up with any so far.
For those who are not familiar with this code:

<snip>

-634

THE DELETE RULE MUST NOT BE CASCADE

Explanation
~~~~~~~~~~~

The code is used to report that the CASCADE delete rule specified in the
FOREIGN KEY clause of an ALTER TABLE statement is invalid because:

The relationship would form a cycle that would cause a table to be
delete-connected to itself.

The relationship would cause another table to be delete-connected to the
same table through multiple paths with different delete rules or with a
delete rule equal to SET NULL.

</snip>

We can rule out the SET NULL rule.
We changed delete-rules forth and back, set them to ENFORCE NO, but the -634 won’t go away.
Now the question to you SQL gurus out there: Do any of you have an sql handy that detects any such circle?

Thanks in advance

Rüdiger

Rüdiger Kurtz
Abteilung Informatik – Betrieb

HUK-COBURG
Bahnhofsplatz
96444 Coburg
Telefon:

09561 96-44148

Telefax:

09561 96-44104

E-Mail:

[login to unmask email]<mailto:[login to unmask email]>

Internet:

www.huk.de http://www.huk.de

________________________________
HUK-COBURG Haftpflicht-Unterstützungs-Kasse kraftfahrender Beamter Deutschlands a. G. in Coburg
Reg.-Gericht Coburg HRB 100; St.-Nr. 9212/101/00021
Sitz der Gesellschaft: Bahnhofsplatz, 96444 Coburg
Vorsitzender des Aufsichtsrats: Prof. Dr. Heinrich R. Schradin.
Vorstand: Klaus-Jürgen Heitmann (Sprecher), Stefan Gronbach, Dr. Hans Olav Herøy, Dr. Jörg Rheinländer (stv.), Sarah Rössler, Daniel Thomas (stv.).
________________________________
Diese Nachricht enthält vertrauliche und/oder rechtlich geschützte Informationen.
Wenn Sie nicht der richtige Adressat sind oder diese Nachricht irrtümlich erhalten haben,
informieren Sie bitte sofort den Absender und vernichten Sie diese Nachricht.
Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Nachricht ist nicht gestattet.

This information may contain confidential and/or privileged information.
If you are not the intended recipient (or have received this information in error) please notify the
sender immediately and destroy this information.
Any unauthorized copying, disclosure or distribution of the material in this information is strictly forbidden.
________________________________

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

Ruediger Kurtz

AW: [DB2 z/OS 12]
(in response to Philip Sevetson)
Phil,

we use Catalog Manager and we have come up with an sql to guide us through RI’s mist:

WITH PUMP (LEVEL, STARTTAB, ENDTAB, PATH, histdelrule,
relname,deleterule,ENFORCED
,cols
) AS
(SELECT 1, REFTBNAME, TBNAME ,
STRIP(REFTBNAME)!!'_'!!deleterule!!'_'!!STRIP(TBNAME)
,deleterule
,relname ,deleterule,ENFORCED
,colcount COLS
FROM SYSIBM.SYSRELS
where
REFTBNAME='<parent-table>'
and REFTBCREATOR = '<creator>'
UNION ALL
SELECT PUMP.LEVEL+1, PUMP.STARTTAB,
CHILD.TBNAME, PUMP.PATH!!'_'!!child.deleterule!!'_'!!CHILD.TBNAME,
pump.deleterule!!'_'!!child.deleterule,
child.relname ,child.deleterule
,child.ENFORCED
,colcount cols
FROM PUMP, SYSIBM.SYSRELS CHILD
WHERE PUMP.ENDTAB = CHILD.REFTBNAME
AND CHILD.TBNAME <> CHILD.REFTBNAME
AND PUMP.STARTTAB <> PUMP.ENDTAB
and child.REFTBCREATOR = '<creator>'
-- and pump.DELETERULE != 'C'
AND PUMP.LEVEL < 30
)
SELECT distinct
LEVEL, STARTTAB, ENDTAB, PATH,histdelrule,
relname,deleterule,ENFORCED
,cols
from pump

When I said we are using quite a bit of RI I really mean quite a bit ... just to give you some hint of what I’m talking about, we do have tables with more than 200 relations.
Call us crazy, but this is what we have. Neither Catalog Manager or our sql could provide an explanation so far.

Regards

Ruediger




Rüdiger Kurtz
Abteilung Informatik – Betrieb

HUK-COBURG
Bahnhofsplatz
96444 Coburg
Telefon: 09561 96-44148
Telefax: 09561 96-44104
E-Mail: [login to unmask email]
Internet: www.huk.de
________________________________
HUK-COBURG Haftpflicht-Unterstützungs-Kasse kraftfahrender Beamter Deutschlands a. G. in Coburg
Reg.-Gericht Coburg HRB 100; St.-Nr. 9212/101/00021
Sitz der Gesellschaft: Bahnhofsplatz, 96444 Coburg
Vorsitzender des Aufsichtsrats: Prof. Dr. Heinrich R. Schradin.
Vorstand: Klaus-Jürgen Heitmann (Sprecher), Stefan Gronbach, Dr. Hans Olav Herøy, Dr. Jörg Rheinländer (stv.), Sarah Rössler, Daniel Thomas (stv.).
________________________________
Diese Nachricht enthält vertrauliche und/oder rechtlich geschützte Informationen.
Wenn Sie nicht der richtige Adressat sind oder diese Nachricht irrtümlich erhalten haben,
informieren Sie bitte sofort den Absender und vernichten Sie diese Nachricht.
Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Nachricht ist nicht gestattet.

This information may contain confidential and/or privileged information.
If you are not the intended recipient (or have received this information in error) please notify the
sender immediately and destroy this information.
Any unauthorized copying, disclosure or distribution of the material in this information is strictly forbidden.
________________________________
Von: Sevetson, Phil [mailto:[login to unmask email]
Gesendet: Dienstag, 9. Januar 2018 15:40
An: '[login to unmask email]' <[login to unmask email]>
Betreff: [DB2-L] - RE: [DB2 z/OS 12]

Rudiger,
Do you have a catalog navigation tool? Also… do you suspect the RI loop of being so long (or complex) that walking the SYSRELS table won’t be enough to find it? You’d need a recursion processor to concatenate table names, I think, and set it to loop at least as many times as your worst-case imagination about number of levels.

From: Kurtz, Rüdiger [mailto:[login to unmask email]
Sent: Tuesday, January 09, 2018 9:35 AM
To: '[login to unmask email]'
Subject: [DB2-L] - [DB2 z/OS 12]

Folks,

we’re using quite a bit of RI in our shop. Due to legal requirements we are forced to change some delete-rules from restrict to cascade.
All changes but one have successfully been implemented so far, but this one drives us nuts.

We are receiving an -634 and have spent the last two days looking for an explanation but haven’t come up with any so far.
For those who are not familiar with this code:

<snip>

-634

THE DELETE RULE MUST NOT BE CASCADE

Explanation
~~~~~~~~~~~

The code is used to report that the CASCADE delete rule specified in the
FOREIGN KEY clause of an ALTER TABLE statement is invalid because:

The relationship would form a cycle that would cause a table to be
delete-connected to itself.

The relationship would cause another table to be delete-connected to the
same table through multiple paths with different delete rules or with a
delete rule equal to SET NULL.

</snip>

We can rule out the SET NULL rule.
We changed delete-rules forth and back, set them to ENFORCE NO, but the -634 won’t go away.
Now the question to you SQL gurus out there: Do any of you have an sql handy that detects any such circle?

Thanks in advance

Rüdiger

Rüdiger Kurtz
Abteilung Informatik – Betrieb

HUK-COBURG
Bahnhofsplatz
96444 Coburg
Telefon:

09561 96-44148

Telefax:

09561 96-44104

E-Mail:

[login to unmask email]<mailto:[login to unmask email]>

Internet:

www.huk.de http://www.huk.de

________________________________
HUK-COBURG Haftpflicht-Unterstützungs-Kasse kraftfahrender Beamter Deutschlands a. G. in Coburg
Reg.-Gericht Coburg HRB 100; St.-Nr. 9212/101/00021
Sitz der Gesellschaft: Bahnhofsplatz, 96444 Coburg
Vorsitzender des Aufsichtsrats: Prof. Dr. Heinrich R. Schradin.
Vorstand: Klaus-Jürgen Heitmann (Sprecher), Stefan Gronbach, Dr. Hans Olav Herøy, Dr. Jörg Rheinländer (stv.), Sarah Rössler, Daniel Thomas (stv.).
________________________________
Diese Nachricht enthält vertrauliche und/oder rechtlich geschützte Informationen.
Wenn Sie nicht der richtige Adressat sind oder diese Nachricht irrtümlich erhalten haben,
informieren Sie bitte sofort den Absender und vernichten Sie diese Nachricht.
Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Nachricht ist nicht gestattet.

This information may contain confidential and/or privileged information.
If you are not the intended recipient (or have received this information in error) please notify the
sender immediately and destroy this information.
Any unauthorized copying, disclosure or distribution of the material in this information is strictly forbidden.
________________________________

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
-----End Original Message-----

Philip Sevetson

[DB2 z/OS 12]
(in response to Ruediger Kurtz)
Rudiger,

I don’t have a way to test this for an actual loop, because we don’t have a real RI loop in our catalog. If you have one, I _think_ this will find it within a single schema. If you have RI crossing schema boundaries, let me know and I’ll fix this a bit for that.

Can you, or someone, please either test this for me, or get me the ALTER statements to create an RI loop which doesn’t throw me out on syntax? The example below works reasonably well (and produces all of the intermediate states as well as the final, fully expressed levels) in my test. (I ran my test with LEVEL < 10.) I’d ideally like to have someone report that it found a loop; if so, I’ll commit it to the Code Place.

--Phil Sevetson

P.S. Hat tip to Suresh Sane for the original looping code which I built up from, and to David Bretz (http://www.idug.org/p/do/sd/sid=3780), who created the first recursive query against SYSRELS in the Codeplace. And to whoever wrote Rudiger’s example code.

WITH RAW_HIERARCHY (LEVEL, PARENT, LAST_LEV_FOUND, LOOP_FOUND, CHILDCHAIN)
AS
(SELECT 1, REFTBNAME, TBNAME, ' '
, CHAR(RTRIM(TBNAME)
CONCAT ','''
CONCAT DELETERULE
CONCAT ''' '
,255
)
FROM SYSIBM.SYSRELS WHERE REFTBCREATOR = '[your schema]'
UNION ALL
SELECT LEVEL + 1, C.PARENT, R.TBNAME
, CASE
WHEN C.PARENT = R.TBNAME THEN 'LOOP'
ELSE 'OK'
END AS LOOP_FOUND
, CHAR(RTRIM(C.CHILDCHAIN)
CONCAT ', '
CONCAT R.TBNAME
CONCAT ','''
CONCAT R.DELETERULE
CONCAT ''''
,255
)
AS CHILDCHAIN
FROM RAW_HIERARCHY C
,SYSIBM.SYSRELS R
WHERE LEVEL < 100
AND R.REFTBNAME = C.LAST_LEV_FOUND
AND R.REFTBCREATOR = '[your schema]'
)
SELECT *
FROM RAW_HIERARCHY
;


From: Kurtz, Rüdiger [mailto:[login to unmask email]
Sent: Tuesday, January 09, 2018 9:57 AM
To: '[login to unmask email]'
Subject: [DB2-L] - AW: [DB2 z/OS 12]

Phil,

we use Catalog Manager and we have come up with an sql to guide us through RI’s mist:

WITH PUMP (LEVEL, STARTTAB, ENDTAB, PATH, histdelrule,
relname,deleterule,ENFORCED
,cols
) AS
(SELECT 1, REFTBNAME, TBNAME ,
STRIP(REFTBNAME)!!'_'!!deleterule!!'_'!!STRIP(TBNAME)
,deleterule
,relname ,deleterule,ENFORCED
,colcount COLS
FROM SYSIBM.SYSRELS
where
REFTBNAME='<parent-table>'
and REFTBCREATOR = '<creator>'
UNION ALL
SELECT PUMP.LEVEL+1, PUMP.STARTTAB,
CHILD.TBNAME, PUMP.PATH!!'_'!!child.deleterule!!'_'!!CHILD.TBNAME,
pump.deleterule!!'_'!!child.deleterule,
child.relname ,child.deleterule
,child.ENFORCED
,colcount cols
FROM PUMP, SYSIBM.SYSRELS CHILD
WHERE PUMP.ENDTAB = CHILD.REFTBNAME
AND CHILD.TBNAME <> CHILD.REFTBNAME
AND PUMP.STARTTAB <> PUMP.ENDTAB
and child.REFTBCREATOR = '<creator>'
-- and pump.DELETERULE != 'C'
AND PUMP.LEVEL < 30
)
SELECT distinct
LEVEL, STARTTAB, ENDTAB, PATH,histdelrule,
relname,deleterule,ENFORCED
,cols
from pump

When I said we are using quite a bit of RI I really mean quite a bit ... just to give you some hint of what I’m talking about, we do have tables with more than 200 relations.
Call us crazy, but this is what we have. Neither Catalog Manager or our sql could provide an explanation so far.

Regards

Ruediger




Rüdiger Kurtz
Abteilung Informatik – Betrieb

HUK-COBURG
Bahnhofsplatz
96444 Coburg
Telefon:

09561 96-44148

Telefax:

09561 96-44104

E-Mail:

[login to unmask email]<mailto:[login to unmask email]>

Internet:

www.huk.de http://www.huk.de

________________________________
HUK-COBURG Haftpflicht-Unterstützungs-Kasse kraftfahrender Beamter Deutschlands a. G. in Coburg
Reg.-Gericht Coburg HRB 100; St.-Nr. 9212/101/00021
Sitz der Gesellschaft: Bahnhofsplatz, 96444 Coburg
Vorsitzender des Aufsichtsrats: Prof. Dr. Heinrich R. Schradin.
Vorstand: Klaus-Jürgen Heitmann (Sprecher), Stefan Gronbach, Dr. Hans Olav Herøy, Dr. Jörg Rheinländer (stv.), Sarah Rössler, Daniel Thomas (stv.).
________________________________
Diese Nachricht enthält vertrauliche und/oder rechtlich geschützte Informationen.
Wenn Sie nicht der richtige Adressat sind oder diese Nachricht irrtümlich erhalten haben,
informieren Sie bitte sofort den Absender und vernichten Sie diese Nachricht.
Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Nachricht ist nicht gestattet.

This information may contain confidential and/or privileged information.
If you are not the intended recipient (or have received this information in error) please notify the
sender immediately and destroy this information.
Any unauthorized copying, disclosure or distribution of the material in this information is strictly forbidden.
________________________________
Von: Sevetson, Phil [mailto:[login to unmask email]
Gesendet: Dienstag, 9. Januar 2018 15:40
An: '[login to unmask email]' <[login to unmask email]<mailto:[login to unmask email]>>
Betreff: [DB2-L] - RE: [DB2 z/OS 12]

Rudiger,
Do you have a catalog navigation tool? Also… do you suspect the RI loop of being so long (or complex) that walking the SYSRELS table won’t be enough to find it? You’d need a recursion processor to concatenate table names, I think, and set it to loop at least as many times as your worst-case imagination about number of levels.

From: Kurtz, Rüdiger [mailto:[login to unmask email]
Sent: Tuesday, January 09, 2018 9:35 AM
To: '[login to unmask email]'
Subject: [DB2-L] - [DB2 z/OS 12]

Folks,

we’re using quite a bit of RI in our shop. Due to legal requirements we are forced to change some delete-rules from restrict to cascade.
All changes but one have successfully been implemented so far, but this one drives us nuts.

We are receiving an -634 and have spent the last two days looking for an explanation but haven’t come up with any so far.
For those who are not familiar with this code:

<snip>

-634

THE DELETE RULE MUST NOT BE CASCADE

Explanation
~~~~~~~~~~~

The code is used to report that the CASCADE delete rule specified in the
FOREIGN KEY clause of an ALTER TABLE statement is invalid because:

The relationship would form a cycle that would cause a table to be
delete-connected to itself.

The relationship would cause another table to be delete-connected to the
same table through multiple paths with different delete rules or with a
delete rule equal to SET NULL.

</snip>

We can rule out the SET NULL rule.
We changed delete-rules forth and back, set them to ENFORCE NO, but the -634 won’t go away.
Now the question to you SQL gurus out there: Do any of you have an sql handy that detects any such circle?

Thanks in advance

Rüdiger

Rüdiger Kurtz
Abteilung Informatik – Betrieb

HUK-COBURG
Bahnhofsplatz
96444 Coburg
Telefon:

09561 96-44148

Telefax:

09561 96-44104

E-Mail:

[login to unmask email]<mailto:[login to unmask email]>

Internet:

www.huk.de http://www.huk.de

________________________________
HUK-COBURG Haftpflicht-Unterstützungs-Kasse kraftfahrender Beamter Deutschlands a. G. in Coburg
Reg.-Gericht Coburg HRB 100; St.-Nr. 9212/101/00021
Sitz der Gesellschaft: Bahnhofsplatz, 96444 Coburg
Vorsitzender des Aufsichtsrats: Prof. Dr. Heinrich R. Schradin.
Vorstand: Klaus-Jürgen Heitmann (Sprecher), Stefan Gronbach, Dr. Hans Olav Herøy, Dr. Jörg Rheinländer (stv.), Sarah Rössler, Daniel Thomas (stv.).
________________________________
Diese Nachricht enthält vertrauliche und/oder rechtlich geschützte Informationen.
Wenn Sie nicht der richtige Adressat sind oder diese Nachricht irrtümlich erhalten haben,
informieren Sie bitte sofort den Absender und vernichten Sie diese Nachricht.
Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Nachricht ist nicht gestattet.

This information may contain confidential and/or privileged information.
If you are not the intended recipient (or have received this information in error) please notify the
sender immediately and destroy this information.
Any unauthorized copying, disclosure or distribution of the material in this information is strictly forbidden.
________________________________

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
-----End Original Message-----

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

Suresh Sane

[DB2 z/OS 12]
(in response to Philip Sevetson)
Phil, Rüdiger,


I think the issue of true loops in the hierarchy is a complex one and I don't think recursive SQL can handle the general case. This is a tree traversal algorithm where you want to detect that you have visited a node before and eliminate it. This requires IMHO some form of NOT EXISTS which is not permitted in the syntax...the limit may be exhausted without visiting all nodes.


The condition below:


WHEN C.PARENT = R.TBNAME THEN 'LOOP'


is a good start and should detect recursive RI (table having a Foreign key to itself).


However, I don't think it can detect a chains like A is the parent of B, B is the parent of C and C is the parent of A.


I admit the business value of such looped RI is questionable, but I have seen a legitimate RI where A and B are parents of each other (no kidding!).


I take pride in the "I can do this in 1 SQL" mantra Sheryl advocated, but as I get older, feel more inclined to write some procedural code. This may be one of them.


Thx

Suresh






________________________________
From: Sevetson, Phil <[login to unmask email]>
Sent: Tuesday, January 9, 2018 4:36 PM
To: '[login to unmask email]'
Subject: [DB2-L] - RE: [DB2 z/OS 12]


Rudiger,



I don’t have a way to test this for an actual loop, because we don’t have a real RI loop in our catalog. If you have one, I _think_ this will find it within a single schema. If you have RI crossing schema boundaries, let me know and I’ll fix this a bit for that.



Can you, or someone, please either test this for me, or get me the ALTER statements to create an RI loop which doesn’t throw me out on syntax? The example below works reasonably well (and produces all of the intermediate states as well as the final, fully expressed levels) in my test. (I ran my test with LEVEL < 10.) I’d ideally like to have someone report that it found a loop; if so, I’ll commit it to the Code Place.



--Phil Sevetson



P.S. Hat tip to Suresh Sane for the original looping code which I built up from, and to David Bretz (http://www.idug.org/p/do/sd/sid=3780), who created the first recursive query against SYSRELS in the Codeplace. And to whoever wrote Rudiger’s example code.



WITH RAW_HIERARCHY (LEVEL, PARENT, LAST_LEV_FOUND, LOOP_FOUND, CHILDCHAIN)

AS

(SELECT 1, REFTBNAME, TBNAME, ' '

, CHAR(RTRIM(TBNAME)

CONCAT ','''

CONCAT DELETERULE

CONCAT ''' '

,255

)

FROM SYSIBM.SYSRELS WHERE REFTBCREATOR = '[your schema]'

UNION ALL

SELECT LEVEL + 1, C.PARENT, R.TBNAME

, CASE

WHEN C.PARENT = R.TBNAME THEN 'LOOP'

ELSE 'OK'

END AS LOOP_FOUND

, CHAR(RTRIM(C.CHILDCHAIN)

CONCAT ', '

CONCAT R.TBNAME

CONCAT ','''

CONCAT R.DELETERULE

CONCAT ''''

,255

)

AS CHILDCHAIN

FROM RAW_HIERARCHY C

,SYSIBM.SYSRELS R

WHERE LEVEL < 100

AND R.REFTBNAME = C.LAST_LEV_FOUND

AND R.REFTBCREATOR = '[your schema]'

)

SELECT *

FROM RAW_HIERARCHY

;





From: Kurtz, Rüdiger [mailto:[login to unmask email]
Sent: Tuesday, January 09, 2018 9:57 AM
To: '[login to unmask email]'
Subject: [DB2-L] - AW: [DB2 z/OS 12]



Phil,

we use Catalog Manager and we have come up with an sql to guide us through RI’s mist:



WITH PUMP (LEVEL, STARTTAB, ENDTAB, PATH, histdelrule,

relname,deleterule,ENFORCED

,cols

) AS

(SELECT 1, REFTBNAME, TBNAME ,

STRIP(REFTBNAME)!!'_'!!deleterule!!'_'!!STRIP(TBNAME)

,deleterule

,relname ,deleterule,ENFORCED

,colcount COLS

FROM SYSIBM.SYSRELS

where

REFTBNAME='<parent-table>'

and REFTBCREATOR = '<creator>'

UNION ALL

SELECT PUMP.LEVEL+1, PUMP.STARTTAB,

CHILD.TBNAME, PUMP.PATH!!'_'!!child.deleterule!!'_'!!CHILD.TBNAME,

pump.deleterule!!'_'!!child.deleterule,

child.relname ,child.deleterule

,child.ENFORCED

,colcount cols

FROM PUMP, SYSIBM.SYSRELS CHILD

WHERE PUMP.ENDTAB = CHILD.REFTBNAME

AND CHILD.TBNAME <> CHILD.REFTBNAME

AND PUMP.STARTTAB <> PUMP.ENDTAB

and child.REFTBCREATOR = '<creator>'

-- and pump.DELETERULE != 'C'

AND PUMP.LEVEL < 30

)

SELECT distinct

LEVEL, STARTTAB, ENDTAB, PATH,histdelrule,

relname,deleterule,ENFORCED

,cols

from pump



When I said we are using quite a bit of RI I really mean quite a bit ... just to give you some hint of what I’m talking about, we do have tables with more than 200 relations.
Call us crazy, but this is what we have. Neither Catalog Manager or our sql could provide an explanation so far.



Regards

Ruediger







Rüdiger Kurtz
Abteilung Informatik – Betrieb

HUK-COBURG
Bahnhofsplatz
96444 Coburg

Telefon:


09561 96-44148


Telefax:


09561 96-44104


E-Mail:


[login to unmask email]<mailto:[login to unmask email]>


Internet:


www.huk.de http://www.huk.de


________________________________

HUK-COBURG Haftpflicht-Unterstützungs-Kasse kraftfahrender Beamter Deutschlands a. G. in Coburg
Reg.-Gericht Coburg HRB 100; St.-Nr. 9212/101/00021
Sitz der Gesellschaft: Bahnhofsplatz, 96444 Coburg
Vorsitzender des Aufsichtsrats: Prof. Dr. Heinrich R. Schradin.
Vorstand: Klaus-Jürgen Heitmann (Sprecher), Stefan Gronbach, Dr. Hans Olav Herøy, Dr. Jörg Rheinländer (stv.), Sarah Rössler, Daniel Thomas (stv.).

________________________________

Diese Nachricht enthält vertrauliche und/oder rechtlich geschützte Informationen.
Wenn Sie nicht der richtige Adressat sind oder diese Nachricht irrtümlich erhalten haben,
informieren Sie bitte sofort den Absender und vernichten Sie diese Nachricht.
Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Nachricht ist nicht gestattet.

This information may contain confidential and/or privileged information.
If you are not the intended recipient (or have received this information in error) please notify the
sender immediately and destroy this information.
Any unauthorized copying, disclosure or distribution of the material in this information is strictly forbidden.

________________________________

Von: Sevetson, Phil [mailto:[login to unmask email]
Gesendet: Dienstag, 9. Januar 2018 15:40
An: '[login to unmask email]' <[login to unmask email]<mailto:[login to unmask email]>>
Betreff: [DB2-L] - RE: [DB2 z/OS 12]



Rudiger,

Do you have a catalog navigation tool? Also… do you suspect the RI loop of being so long (or complex) that walking the SYSRELS table won’t be enough to find it? You’d need a recursion processor to concatenate table names, I think, and set it to loop at least as many times as your worst-case imagination about number of levels.



From: Kurtz, Rüdiger [mailto:[login to unmask email]
Sent: Tuesday, January 09, 2018 9:35 AM
To: '[login to unmask email]'
Subject: [DB2-L] - [DB2 z/OS 12]



Folks,

we’re using quite a bit of RI in our shop. Due to legal requirements we are forced to change some delete-rules from restrict to cascade.

All changes but one have successfully been implemented so far, but this one drives us nuts.

We are receiving an -634 and have spent the last two days looking for an explanation but haven’t come up with any so far.
For those who are not familiar with this code:



<snip>



-634



THE DELETE RULE MUST NOT BE CASCADE



Explanation

~~~~~~~~~~~



The code is used to report that the CASCADE delete rule specified in the

FOREIGN KEY clause of an ALTER TABLE statement is invalid because:



The relationship would form a cycle that would cause a table to be

delete-connected to itself.



The relationship would cause another table to be delete-connected to the

same table through multiple paths with different delete rules or with a

delete rule equal to SET NULL.



</snip>

We can rule out the SET NULL rule.
We changed delete-rules forth and back, set them to ENFORCE NO, but the -634 won’t go away.

Now the question to you SQL gurus out there: Do any of you have an sql handy that detects any such circle?



Thanks in advance

Rüdiger

Rüdiger Kurtz
Abteilung Informatik – Betrieb

HUK-COBURG
Bahnhofsplatz
96444 Coburg

Telefon:


09561 96-44148


Telefax:


09561 96-44104


E-Mail:


[login to unmask email]<mailto:[login to unmask email]>


Internet:


www.huk.de http://www.huk.de


________________________________

HUK-COBURG Haftpflicht-Unterstützungs-Kasse kraftfahrender Beamter Deutschlands a. G. in Coburg
Reg.-Gericht Coburg HRB 100; St.-Nr. 9212/101/00021
Sitz der Gesellschaft: Bahnhofsplatz, 96444 Coburg
Vorsitzender des Aufsichtsrats: Prof. Dr. Heinrich R. Schradin.
Vorstand: Klaus-Jürgen Heitmann (Sprecher), Stefan Gronbach, Dr. Hans Olav Herøy, Dr. Jörg Rheinländer (stv.), Sarah Rössler, Daniel Thomas (stv.).

________________________________

Diese Nachricht enthält vertrauliche und/oder rechtlich geschützte Informationen.
Wenn Sie nicht der richtige Adressat sind oder diese Nachricht irrtümlich erhalten haben,
informieren Sie bitte sofort den Absender und vernichten Sie diese Nachricht.
Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Nachricht ist nicht gestattet.

This information may contain confidential and/or privileged information.
If you are not the intended recipient (or have received this information in error) please notify the
sender immediately and destroy this information.
Any unauthorized copying, disclosure or distribution of the material in this information is strictly forbidden.

________________________________



-----End Original Message-----

**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

-----End Original Message-----



-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
-----End Original Message-----

Philip Sevetson

[DB2 z/OS 12]
(in response to Suresh Sane)
Suresh,

That (C.PARENT) may be an unfortunate column name on my part. I should perhaps have named it ULTIMATE_PARENT or FIRST_PARENT. What I did with this was to start a chain/row for _every_ parent, because I couldn't figure out how to (easily) weed out the parents which were also children.

Anyway: the C.PARENT in that predicate is the *first* parent and implicitly at the top of the constructed chain, so any time that C.PARENT is matched, that's a closing of the loop. (I think, and that's what I was coding to achieve.)

However. You have clearly elucidated the glaring weakness of that query; that while it can probably find loops (subject to confirmation by testing), it certainly will construct all chains and parts of chains in the system. It lacks the intelligence to note when the rows (CHILDCHAIN column) are subsets of the values found in other rows.

--Phil

From: suresh sane [mailto:[login to unmask email]
Sent: Tuesday, January 09, 2018 1:31 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: [DB2 z/OS 12]


Phil, Rüdiger,



I think the issue of true loops in the hierarchy is a complex one and I don't think recursive SQL can handle the general case. This is a tree traversal algorithm where you want to detect that you have visited a node before and eliminate it. This requires IMHO some form of NOT EXISTS which is not permitted in the syntax...the limit may be exhausted without visiting all nodes.



The condition below:



WHEN C.PARENT = R.TBNAME THEN 'LOOP'



is a good start and should detect recursive RI (table having a Foreign key to itself).



However, I don't think it can detect a chains like A is the parent of B, B is the parent of C and C is the parent of A.



I admit the business value of such looped RI is questionable, but I have seen a legitimate RI where A and B are parents of each other (no kidding!).



I take pride in the "I can do this in 1 SQL" mantra Sheryl advocated, but as I get older, feel more inclined to write some procedural code. This may be one of them.



Thx

Suresh









________________________________
From: Sevetson, Phil <[login to unmask email]<mailto:[login to unmask email]>>
Sent: Tuesday, January 9, 2018 4:36 PM
To: '[login to unmask email]'
Subject: [DB2-L] - RE: [DB2 z/OS 12]


Rudiger,



I don't have a way to test this for an actual loop, because we don't have a real RI loop in our catalog. If you have one, I _think_ this will find it within a single schema. If you have RI crossing schema boundaries, let me know and I'll fix this a bit for that.



Can you, or someone, please either test this for me, or get me the ALTER statements to create an RI loop which doesn't throw me out on syntax? The example below works reasonably well (and produces all of the intermediate states as well as the final, fully expressed levels) in my test. (I ran my test with LEVEL < 10.) I'd ideally like to have someone report that it found a loop; if so, I'll commit it to the Code Place.



--Phil Sevetson



P.S. Hat tip to Suresh Sane for the original looping code which I built up from, and to David Bretz (http://www.idug.org/p/do/sd/sid=3780), who created the first recursive query against SYSRELS in the Codeplace. And to whoever wrote Rudiger's example code.



WITH RAW_HIERARCHY (LEVEL, PARENT, LAST_LEV_FOUND, LOOP_FOUND, CHILDCHAIN)

AS

(SELECT 1, REFTBNAME, TBNAME, ' '

, CHAR(RTRIM(TBNAME)

CONCAT ','''

CONCAT DELETERULE

CONCAT ''' '

,255

)

FROM SYSIBM.SYSRELS WHERE REFTBCREATOR = '[your schema]'

UNION ALL

SELECT LEVEL + 1, C.PARENT, R.TBNAME

, CASE

WHEN C.PARENT = R.TBNAME THEN 'LOOP'

ELSE 'OK'

END AS LOOP_FOUND

, CHAR(RTRIM(C.CHILDCHAIN)

CONCAT ', '

CONCAT R.TBNAME

CONCAT ','''

CONCAT R.DELETERULE

CONCAT ''''

,255

)

AS CHILDCHAIN

FROM RAW_HIERARCHY C

,SYSIBM.SYSRELS R

WHERE LEVEL < 100

AND R.REFTBNAME = C.LAST_LEV_FOUND

AND R.REFTBCREATOR = '[your schema]'

)

SELECT *

FROM RAW_HIERARCHY

;





From: Kurtz, Rüdiger [mailto:[login to unmask email]
Sent: Tuesday, January 09, 2018 9:57 AM
To: '[login to unmask email]'
Subject: [DB2-L] - AW: [DB2 z/OS 12]



Phil,

we use Catalog Manager and we have come up with an sql to guide us through RI's mist:



WITH PUMP (LEVEL, STARTTAB, ENDTAB, PATH, histdelrule,

relname,deleterule,ENFORCED

,cols

) AS

(SELECT 1, REFTBNAME, TBNAME ,

STRIP(REFTBNAME)!!'_'!!deleterule!!'_'!!STRIP(TBNAME)

,deleterule

,relname ,deleterule,ENFORCED

,colcount COLS

FROM SYSIBM.SYSRELS

where

REFTBNAME='<parent-table>'

and REFTBCREATOR = '<creator>'

UNION ALL

SELECT PUMP.LEVEL+1, PUMP.STARTTAB,

CHILD.TBNAME, PUMP.PATH!!'_'!!child.deleterule!!'_'!!CHILD.TBNAME,

pump.deleterule!!'_'!!child.deleterule,

child.relname ,child.deleterule

,child.ENFORCED

,colcount cols

FROM PUMP, SYSIBM.SYSRELS CHILD

WHERE PUMP.ENDTAB = CHILD.REFTBNAME

AND CHILD.TBNAME <> CHILD.REFTBNAME

AND PUMP.STARTTAB <> PUMP.ENDTAB

and child.REFTBCREATOR = '<creator>'

-- and pump.DELETERULE != 'C'

AND PUMP.LEVEL < 30

)

SELECT distinct

LEVEL, STARTTAB, ENDTAB, PATH,histdelrule,

relname,deleterule,ENFORCED

,cols

from pump



When I said we are using quite a bit of RI I really mean quite a bit ... just to give you some hint of what I'm talking about, we do have tables with more than 200 relations.
Call us crazy, but this is what we have. Neither Catalog Manager or our sql could provide an explanation so far.



Regards

Ruediger







Rüdiger Kurtz
Abteilung Informatik - Betrieb

HUK-COBURG
Bahnhofsplatz
96444 Coburg

Telefon:


09561 96-44148


Telefax:


09561 96-44104


E-Mail:


[login to unmask email]<mailto:[login to unmask email]>


Internet:


www.huk.de http://www.huk.de


________________________________

HUK-COBURG Haftpflicht-Unterstützungs-Kasse kraftfahrender Beamter Deutschlands a. G. in Coburg
Reg.-Gericht Coburg HRB 100; St.-Nr. 9212/101/00021
Sitz der Gesellschaft: Bahnhofsplatz, 96444 Coburg
Vorsitzender des Aufsichtsrats: Prof. Dr. Heinrich R. Schradin.
Vorstand: Klaus-Jürgen Heitmann (Sprecher), Stefan Gronbach, Dr. Hans Olav Herøy, Dr. Jörg Rheinländer (stv.), Sarah Rössler, Daniel Thomas (stv.).

________________________________

Diese Nachricht enthält vertrauliche und/oder rechtlich geschützte Informationen.
Wenn Sie nicht der richtige Adressat sind oder diese Nachricht irrtümlich erhalten haben,
informieren Sie bitte sofort den Absender und vernichten Sie diese Nachricht.
Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Nachricht ist nicht gestattet.

This information may contain confidential and/or privileged information.
If you are not the intended recipient (or have received this information in error) please notify the
sender immediately and destroy this information.
Any unauthorized copying, disclosure or distribution of the material in this information is strictly forbidden.

________________________________

Von: Sevetson, Phil [mailto:[login to unmask email]
Gesendet: Dienstag, 9. Januar 2018 15:40
An: '[login to unmask email]' <[login to unmask email]<mailto:[login to unmask email]>>
Betreff: [DB2-L] - RE: [DB2 z/OS 12]



Rudiger,

Do you have a catalog navigation tool? Also... do you suspect the RI loop of being so long (or complex) that walking the SYSRELS table won't be enough to find it? You'd need a recursion processor to concatenate table names, I think, and set it to loop at least as many times as your worst-case imagination about number of levels.



From: Kurtz, Rüdiger [mailto:[login to unmask email]
Sent: Tuesday, January 09, 2018 9:35 AM
To: '[login to unmask email]'
Subject: [DB2-L] - [DB2 z/OS 12]



Folks,

we're using quite a bit of RI in our shop. Due to legal requirements we are forced to change some delete-rules from restrict to cascade.

All changes but one have successfully been implemented so far, but this one drives us nuts.

We are receiving an -634 and have spent the last two days looking for an explanation but haven't come up with any so far.
For those who are not familiar with this code:



<snip>



-634



THE DELETE RULE MUST NOT BE CASCADE



Explanation

~~~~~~~~~~~



The code is used to report that the CASCADE delete rule specified in the

FOREIGN KEY clause of an ALTER TABLE statement is invalid because:



The relationship would form a cycle that would cause a table to be

delete-connected to itself.



The relationship would cause another table to be delete-connected to the

same table through multiple paths with different delete rules or with a

delete rule equal to SET NULL.



</snip>

We can rule out the SET NULL rule.
We changed delete-rules forth and back, set them to ENFORCE NO, but the -634 won't go away.

Now the question to you SQL gurus out there: Do any of you have an sql handy that detects any such circle?



Thanks in advance

Rüdiger

Rüdiger Kurtz
Abteilung Informatik - Betrieb

HUK-COBURG
Bahnhofsplatz
96444 Coburg

Telefon:


09561 96-44148


Telefax:


09561 96-44104


E-Mail:


[login to unmask email]<mailto:[login to unmask email]>


Internet:


www.huk.de http://www.huk.de


________________________________

HUK-COBURG Haftpflicht-Unterstützungs-Kasse kraftfahrender Beamter Deutschlands a. G. in Coburg
Reg.-Gericht Coburg HRB 100; St.-Nr. 9212/101/00021
Sitz der Gesellschaft: Bahnhofsplatz, 96444 Coburg
Vorsitzender des Aufsichtsrats: Prof. Dr. Heinrich R. Schradin.
Vorstand: Klaus-Jürgen Heitmann (Sprecher), Stefan Gronbach, Dr. Hans Olav Herøy, Dr. Jörg Rheinländer (stv.), Sarah Rössler, Daniel Thomas (stv.).

________________________________

Diese Nachricht enthält vertrauliche und/oder rechtlich geschützte Informationen.
Wenn Sie nicht der richtige Adressat sind oder diese Nachricht irrtümlich erhalten haben,
informieren Sie bitte sofort den Absender und vernichten Sie diese Nachricht.
Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Nachricht ist nicht gestattet.

This information may contain confidential and/or privileged information.
If you are not the intended recipient (or have received this information in error) please notify the
sender immediately and destroy this information.
Any unauthorized copying, disclosure or distribution of the material in this information is strictly forbidden.

________________________________



-----End Original Message-----

**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

-----End Original Message-----


-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

Sam Baugh

[DB2 z/OS 12]
(in response to Philip Sevetson)
I just happen to be working on a recursive SQL statement to detect infinite
loops in data that has a parent-child type relationship within a single
table. It concatenates the "nodes" and sets a flag when it detects the
node is already in the string. The "X" prevents the infinite loop from
getting out of hand.

For this test case, the full node strings are 0-1-2-3-4, 5-7-9,
6-8-10-5-7-9, and infinite loop 6-8-10-6-8-10 etc.

with task_list(task_id,dependent_task)
as (
select 1, 0 from sysibm.sysdummy1 union all
select 2, 1 from sysibm.sysdummy1 union all
select 3, 2 from sysibm.sysdummy1 union all
select 4, 3 from sysibm.sysdummy1 union all
select 5, 10 from sysibm.sysdummy1 union all
select 6, 10 from sysibm.sysdummy1 union all
select 7, 5 from sysibm.sysdummy1 union all
select 8, 6 from sysibm.sysdummy1 union all
select 9, 7 from sysibm.sysdummy1 union all
select 10, 8 from sysibm.sysdummy1
)
, task_chain(x,start_id,task_id,task_string,circular)
as (
select 1
, task_id
, task_id
, cast(rtrim(dependent_task)||'-'||rtrim(task_id) as
varchar(100)) as task_string
, 'N'
from task_list
union all
select x + 1
, a.start_id
, b.task_id
, a.task_string||'-'||rtrim(b.task_id) as task_string
, case when b.task_id = a.start_id then 'Y' else 'N' end
from task_chain a
join task_list b
on b.dependent_task = a.task_id
where x <= 20 and circular = 'N'
)
select *
from task_chain
where circular = 'Y'
with ur

On Tue, Jan 9, 2018 at 1:48 PM, Sevetson, Phil <[login to unmask email]> wrote:

> Suresh,
>
>
>
> That (C.PARENT) may be an unfortunate column name on my part. I should
> perhaps have named it ULTIMATE_PARENT or FIRST_PARENT. What I did with this
> was to start a chain/row for _*every*_ parent, because I couldn’t figure
> out how to (easily) weed out the parents which were also children.
>
>
>
> Anyway: the C.PARENT in that predicate is the **first** parent and
> implicitly at the top of the constructed chain, so any time that C.PARENT
> is matched, that’s a closing of the loop. (I think, and that’s what I was
> coding to achieve.)
>
>
>
> However. You have clearly elucidated the glaring weakness of that query;
> that while it can probably find loops (subject to confirmation by testing),
> it certainly will construct all chains and parts of chains in the system.
> It lacks the intelligence to note when the rows (CHILDCHAIN column) are
> subsets of the values found in other rows.
>
>
>
> --Phil
>
>
>
> *From:* suresh sane [mailto:[login to unmask email]
> *Sent:* Tuesday, January 09, 2018 1:31 PM
> *To:* [login to unmask email]
> *Subject:* [DB2-L] - RE: [DB2 z/OS 12]
>
>
>
> Phil, Rüdiger,
>
>
>
> I think the issue of true loops in the hierarchy is a complex one and I
> don't think recursive SQL can handle the general case. This is a tree
> traversal algorithm where you want to detect that you have visited a node
> before and eliminate it. This requires IMHO some form of NOT EXISTS which
> is not permitted in the syntax...the limit may be exhausted without
> visiting all nodes.
>
>
>
> The condition below:
>
>
>
> WHEN C.PARENT = R.TBNAME THEN 'LOOP'
>
>
>
> is a good start and should detect recursive RI (table having a Foreign key
> to itself).
>
>
>
> However, I don't think it can detect a chains like A is the parent of B, B
> is the parent of C and C is the parent of A.
>
>
>
> I admit the business value of such looped RI is questionable, but I have
> seen a legitimate RI where A and B are parents of each other (no kidding!).
>
>
>
> I take pride in the "I can do this in 1 SQL" mantra Sheryl advocated, but
> as I get older, feel more inclined to write some procedural code. This may
> be one of them.
>
>
>
> Thx
>
> Suresh
>
>
>
>
>
>
>
>
>
>
> ------------------------------
>
> *From:* Sevetson, Phil <[login to unmask email]>
> *Sent:* Tuesday, January 9, 2018 4:36 PM
> *To:* '[login to unmask email]'
> *Subject:* [DB2-L] - RE: [DB2 z/OS 12]
>
>
>
> Rudiger,
>
>
>
> I don’t have a way to test this for an actual loop, because we don’t have
> a real RI loop in our catalog. If you have one, I _*think*_ this will
> find it within a single schema. If you have RI crossing schema boundaries,
> let me know and I’ll fix this a bit for that.
>
>
>
> *Can you, or someone, please either test this for me, or get me the ALTER
> statements to create an RI loop which doesn’t throw me out on syntax?*
> The example below works reasonably well (and produces all of the
> intermediate states as well as the final, fully expressed levels) in my
> test. (I ran my test with LEVEL < 10.) I’d ideally like to have someone
> report that it found a loop; if so, I’ll commit it to the Code Place.
>
>
>
> --Phil Sevetson
>
>
>
> P.S. Hat tip to Suresh Sane for the original looping code which I built up
> from, and to David Bretz (http://www.idug.org/p/do/sd/sid=3780), who
> created the first recursive query against SYSRELS in the Codeplace. And to
> whoever wrote Rudiger’s example code.
>
>
>
> WITH RAW_HIERARCHY (LEVEL, PARENT, LAST_LEV_FOUND, LOOP_FOUND,
> CHILDCHAIN)
>
> AS
>
> (SELECT 1, REFTBNAME, TBNAME, ' '
>
> , CHAR(RTRIM(TBNAME)
>
> CONCAT ','''
>
> CONCAT DELETERULE
>
> CONCAT ''' '
>
> ,255
>
> )
>
> FROM SYSIBM.SYSRELS WHERE REFTBCREATOR = '[your schema]'
>
> UNION ALL
>
> SELECT LEVEL + 1, C.PARENT, R.TBNAME
>
> , CASE
>
> WHEN C.PARENT = R.TBNAME THEN 'LOOP'
>
> ELSE 'OK'
>
> END AS LOOP_FOUND
>
> , CHAR(RTRIM(C.CHILDCHAIN)
>
> CONCAT ', '
>
> CONCAT R.TBNAME
>
> CONCAT ','''
>
> CONCAT R.DELETERULE
>
> CONCAT ''''
>
> ,255
>
> )
>
> AS CHILDCHAIN
>
> FROM RAW_HIERARCHY C
>
> ,SYSIBM.SYSRELS R
>
> WHERE LEVEL < 100
>
> AND R.REFTBNAME = C.LAST_LEV_FOUND
>
> AND R.REFTBCREATOR = '[your schema]'
>
> )
>
> SELECT *
>
> FROM RAW_HIERARCHY
>
> ;
>
>
>
>
>
> *From:* Kurtz, Rüdiger [mailto:[login to unmask email] <[login to unmask email]>]
>
> *Sent:* Tuesday, January 09, 2018 9:57 AM
> *To:* '[login to unmask email]'
> *Subject:* [DB2-L] - AW: [DB2 z/OS 12]
>
>
>
> Phil,
>
> we use Catalog Manager and we have come up with an sql to guide us through
> RI’s mist:
>
>
>
> WITH PUMP (LEVEL, STARTTAB, ENDTAB, PATH, histdelrule,
>
> relname,deleterule,ENFORCED
>
> ,cols
>
> ) AS
>
> (SELECT 1, REFTBNAME, TBNAME ,
>
> STRIP(REFTBNAME)!!'_'!!deleterule!!'_'!!STRIP(TBNAME)
>
> ,deleterule
>
> ,relname ,deleterule,ENFORCED
>
> ,colcount COLS
>
> FROM SYSIBM.SYSRELS
>
> where
>
> REFTBNAME='<parent-table>'
>
> and REFTBCREATOR = '<creator>'
>
> UNION ALL
>
> SELECT PUMP.LEVEL+1, PUMP.STARTTAB,
>
> CHILD.TBNAME, PUMP.PATH!!'_'!!child.deleterule!!'_'!!CHILD.TBNAME,
>
> pump.deleterule!!'_'!!child.deleterule,
>
> child.relname ,child.deleterule
>
> ,child.ENFORCED
>
> ,colcount cols
>
> FROM PUMP, SYSIBM.SYSRELS CHILD
>
> WHERE PUMP.ENDTAB = CHILD.REFTBNAME
>
> AND CHILD.TBNAME <> CHILD.REFTBNAME
>
> AND PUMP.STARTTAB <> PUMP.ENDTAB
>
> and child.REFTBCREATOR = '<creator>'
>
> -- and pump.DELETERULE != 'C'
>
> AND PUMP.LEVEL < 30
>
> )
>
> SELECT distinct
>
> LEVEL, STARTTAB, ENDTAB, PATH,histdelrule,
>
> relname,deleterule,ENFORCED
>
> ,cols
>
> from pump
>
>
>
> When I said we are using quite a bit of RI I really mean quite a bit ...
> just to give you some hint of what I’m talking about, we do have tables
> with more than 200 relations.
> Call us crazy, but this is what we have. Neither Catalog Manager or our
> sql could provide an explanation so far.
>
>
>
> Regards
>
> Ruediger
>
>
>
>
>
>
>
> Rüdiger Kurtz
> Abteilung Informatik – Betrieb
>
> HUK-COBURG
> Bahnhofsplatz
> 96444 Coburg
>
> Telefon:
>
> 09561 96-44148
>
> Telefax:
>
> 09561 96-44104
>
> E-Mail:
>
> [login to unmask email]
>
> Internet:
>
> www.huk.de
> ------------------------------
>
> HUK-COBURG Haftpflicht-Unterstützungs-Kasse kraftfahrender Beamter
> Deutschlands a. G. in Coburg
> Reg.-Gericht Coburg HRB 100; St.-Nr. 9212/101/00021
> Sitz der Gesellschaft: Bahnhofsplatz, 96444 Coburg
> Vorsitzender des Aufsichtsrats: Prof. Dr. Heinrich R. Schradin.
> Vorstand: Klaus-Jürgen Heitmann (Sprecher), Stefan Gronbach, Dr. Hans Olav
> Herøy, Dr. Jörg Rheinländer (stv.), Sarah Rössler, Daniel Thomas (stv.).
> ------------------------------
>
> Diese Nachricht enthält vertrauliche und/oder rechtlich geschützte
> Informationen.
> Wenn Sie nicht der richtige Adressat sind oder diese Nachricht irrtümlich
> erhalten haben,
> informieren Sie bitte sofort den Absender und vernichten Sie diese
> Nachricht.
> Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Nachricht
> ist nicht gestattet.
>
> This information may contain confidential and/or privileged information.
> If you are not the intended recipient (or have received this information
> in error) please notify the
> sender immediately and destroy this information.
> Any unauthorized copying, disclosure or distribution of the material in
> this information is strictly forbidden.
> ------------------------------
>
> *Von:* Sevetson, Phil [mailto:[login to unmask email] <[login to unmask email]>]
>
> *Gesendet:* Dienstag, 9. Januar 2018 15:40
> *An:* '[login to unmask email]' <[login to unmask email]>
> *Betreff:* [DB2-L] - RE: [DB2 z/OS 12]
>
>
>
> Rudiger,
>
> Do you have a catalog navigation tool? Also… do you suspect the RI loop of
> being so long (or complex) that walking the SYSRELS table won’t be enough
> to find it? You’d need a recursion processor to concatenate table names, I
> think, and set it to loop at least as many times as your worst-case
> imagination about number of levels.
>
>
>
> *From:* Kurtz, Rüdiger [mailto:[login to unmask email] <[login to unmask email]>]
>
> *Sent:* Tuesday, January 09, 2018 9:35 AM
> *To:* '[login to unmask email]'
> *Subject:* [DB2-L] - [DB2 z/OS 12]
>
>
>
> Folks,
>
> we’re using quite a bit of RI in our shop. Due to legal requirements we
> are forced to change some delete-rules from restrict to cascade.
>
> All changes but one have successfully been implemented so far, but this
> one drives us nuts.
>
> We are receiving an -634 and have spent the last two days looking for an
> explanation but haven’t come up with any so far.
> For those who are not familiar with this code:
>
>
>
> <snip>
>
>
>
> -634
>
>
>
>
>
> THE DELETE RULE MUST NOT BE CASCADE
>
>
>
>
>
> Explanation
>
>
> ~~~~~~~~~~~
>
>
>
>
>
> The code is used to report that the CASCADE delete rule specified in
> the
>
> FOREIGN KEY clause of an ALTER TABLE statement is invalid
> because:
>
>
>
>
> The relationship would form a cycle that would cause a table to
> be
>
> delete-connected to itself.
>
>
>
>
>
> The relationship would cause another table to be delete-connected to
> the
>
> same table through multiple paths with different delete rules or
> with a
>
> delete rule equal to SET NULL.
>
>
>
>
> </snip>
>
> We can rule out the SET NULL rule.
> We changed delete-rules forth and back, set them to ENFORCE NO, but the
> -634 won’t go away.
>
> Now the question to you SQL gurus out there: Do any of you have an sql
> handy that detects any such circle?
>
>
>
> Thanks in advance
>
> Rüdiger
>
> Rüdiger Kurtz
> Abteilung Informatik – Betrieb
>
> HUK-COBURG
> Bahnhofsplatz
> 96444 Coburg
>
> Telefon:
>
> 09561 96-44148
>
> Telefax:
>
> 09561 96-44104
>
> E-Mail:
>
> [login to unmask email]
>
> Internet:
>
> www.huk.de
> ------------------------------
>
> HUK-COBURG Haftpflicht-Unterstützungs-Kasse kraftfahrender Beamter
> Deutschlands a. G. in Coburg
> Reg.-Gericht Coburg HRB 100; St.-Nr. 9212/101/00021
> Sitz der Gesellschaft: Bahnhofsplatz, 96444 Coburg
> Vorsitzender des Aufsichtsrats: Prof. Dr. Heinrich R. Schradin.
> Vorstand: Klaus-Jürgen Heitmann (Sprecher), Stefan Gronbach, Dr. Hans Olav
> Herøy, Dr. Jörg Rheinländer (stv.), Sarah Rössler, Daniel Thomas (stv.).
> ------------------------------
>
> Diese Nachricht enthält vertrauliche und/oder rechtlich geschützte
> Informationen.
> Wenn Sie nicht der richtige Adressat sind oder diese Nachricht irrtümlich
> erhalten haben,
> informieren Sie bitte sofort den Absender und vernichten Sie diese
> Nachricht.
> Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Nachricht
> ist nicht gestattet.
>
> This information may contain confidential and/or privileged information.
> If you are not the intended recipient (or have received this information
> in error) please notify the
> sender immediately and destroy this information.
> Any unauthorized copying, disclosure or distribution of the material in
> this information is strictly forbidden.
> ------------------------------
>
>
>
> -----End Original Message-----
>
> **This e-mail, including any attachments, may be confidential, privileged,
> or otherwise legally protected. It is intended only for the addressee. If
> you received this e-mail in error or from someone who was not authorized to
> send it to you, do not disseminate, copy, or otherwise use this e-mail or
> its attachments. Please notify the sender immediately by reply e-mail and
> delete the e-mail from your system.**
>
> -----End Original Message-----
>
>
>
> -----End Original Message-----
>
> **This e-mail, including any attachments, may be confidential, privileged,
> or otherwise legally protected. It is intended only for the addressee. If
> you received this e-mail in error or from someone who was not authorized to
> send it to you, do not disseminate, copy, or otherwise use this e-mail or
> its attachments. Please notify the sender immediately by reply e-mail and
> delete the e-mail from your system.**
> -----End Original Message-----
> **This e-mail, including any attachments, may be confidential, privileged,
> or otherwise legally protected. It is intended only for the addressee. If
> you received this e-mail in error or from someone who was not authorized to
> send it to you, do not disseminate, copy, or otherwise use this e-mail or
> its attachments. Please notify the sender immediately by reply e-mail and
> delete the e-mail from your system.**
> -----End Original Message-----
>

Peter Vanroose

Re: [DB2 z/OS 12]
(in response to Sam Baugh)

Nice recursive SQL!
It's indeed sufficient to keep track of the 'start node' (and compare with a new one) to detect circularity.

Just a minor point: I believe the initiator for start_id should be "dependent_task", not "task_id":

WITH ... 
task_chain(x,start_id,task_id,circular,task_string)
as (
select 1
, dependent_task
, task_id
, 'N'
, cast(rtrim(dependent_task)||'-'||rtrim(task_id) as varchar(100))
from task_list
UNION ALL
select x + 1
, tc.start_id
, tl.task_id
, case when tl.task_id = tc.start_id then 'Y' else 'N' end
, tc.task_string||'-'||rtrim(tl.task_id) as task_string
from task_chain tc join task_list tl on tl.dependent_task = tc.task_id
where x <= 20 and circular = 'N'
)
In Reply to Sam Baugh:
I just happen to be working on a recursive SQL statement [...]
For this test case, the full node strings are 0-1-2-3-4, 5-7-9, 6-8-10-5-7-9, and infinite loop 6-8-10-6-8-10 etc.
with task_list(task_id,dependent_task)
as (select 1, 0 from sysibm.sysdummy1 union all
select 2, 1 from sysibm.sysdummy1 union all
select 3, 2 from sysibm.sysdummy1 union all
select 4, 3 from sysibm.sysdummy1 union all
select 5, 10 from sysibm.sysdummy1 union all
select 6, 10 from sysibm.sysdummy1 union all
select 7, 5 from sysibm.sysdummy1 union all
select 8, 6 from sysibm.sysdummy1 union all
select 9, 7 from sysibm.sysdummy1 union all
select 10, 8 from sysibm.sysdummy1
)
, task_chain(x,start_id,task_id,task_string,circular)
as (
select 1
, task_id
, task_id
, cast(rtrim(dependent_task)||'-'||rtrim(task_id) as
varchar(100)) as task_string
, 'N'
from task_list
union all
select x + 1
, a.start_id
, b.task_id
, a.task_string||'-'||rtrim(b.task_id) as task_string
, case when b.task_id = a.start_id then 'Y' else 'N' end
from task_chain a
join task_list b
on b.dependent_task = a.task_id
where x <= 20 and circular = 'N'
)
select *
from task_chain
where circular = 'Y'
with ur

--      Peter Vanroose
        ABIS Training & Consulting,
        Leuven, Belgium.
        http://www.abis.be/

Ruediger Kurtz

AW: [DB2 z/OS 12]
(in response to Suresh Sane)
Phil, Sam, Suresh, Michael (who responded off-list),

Thanks for your combined efforts.
This morning we succeeded in identifying the culprit.
Here’s what we basically did: We dropped *all* foreign keys pointing to either the parent and the child table but the one we wanted to change, changed the delete-rule on that particular one from restrict to cascade and then rebuilt the afore-dropped foreign keys one by one until we finally hit the one that caused all the trouble.

In this particular case, we found that we do not encounter an infinite loop, but we have restrictions on tables that prevent us from altering the foreign key; thus we had to alter two different foreign keys’ deleterule and all’s well.

One minor thought, though: we haven’t got the thumbs-up from our application developers since these two foreign keys are not on the list of the ones we had to change, thus they have to check the underlying business-rules. But this an SOP (Someone Else’s Problem).

Thanks for your invaluable help all the same.

@Phil: I ran your sql, it works but doesn’t detect any loops; I had to limit the search, though, as the original version blew up our work-db. I also had to change the “CHAR(<whatever), 255” syntax to “VARCHAR(<whatever>) , 4096), but this is just a minor alteration.

@Sam: I haven’t applied your sql to our needs yet, hope I’ll get round to it this week.

@Michael: I wonder if you hadn’t recognized the sql I used in my original mail ☺

best regards

Ruediger



Rüdiger Kurtz
Abteilung Informatik – Betrieb

HUK-COBURG
Bahnhofsplatz
96444 Coburg
Telefon: 09561 96-44148
Telefax: 09561 96-44104
E-Mail: [login to unmask email]
Internet: www.huk.de
________________________________
HUK-COBURG Haftpflicht-Unterstützungs-Kasse kraftfahrender Beamter Deutschlands a. G. in Coburg
Reg.-Gericht Coburg HRB 100; St.-Nr. 9212/101/00021
Sitz der Gesellschaft: Bahnhofsplatz, 96444 Coburg
Vorsitzender des Aufsichtsrats: Prof. Dr. Heinrich R. Schradin.
Vorstand: Klaus-Jürgen Heitmann (Sprecher), Stefan Gronbach, Dr. Hans Olav Herøy, Dr. Jörg Rheinländer (stv.), Sarah Rössler, Daniel Thomas (stv.).
________________________________
Diese Nachricht enthält vertrauliche und/oder rechtlich geschützte Informationen.
Wenn Sie nicht der richtige Adressat sind oder diese Nachricht irrtümlich erhalten haben,
informieren Sie bitte sofort den Absender und vernichten Sie diese Nachricht.
Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Nachricht ist nicht gestattet.

This information may contain confidential and/or privileged information.
If you are not the intended recipient (or have received this information in error) please notify the
sender immediately and destroy this information.
Any unauthorized copying, disclosure or distribution of the material in this information is strictly forbidden.
________________________________
Von: suresh sane [mailto:[login to unmask email]
Gesendet: Dienstag, 9. Januar 2018 19:31
An: [login to unmask email]
Betreff: [DB2-L] - RE: [DB2 z/OS 12]


Phil, Rüdiger,



I think the issue of true loops in the hierarchy is a complex one and I don't think recursive SQL can handle the general case. This is a tree traversal algorithm where you want to detect that you have visited a node before and eliminate it. This requires IMHO some form of NOT EXISTS which is not permitted in the syntax...the limit may be exhausted without visiting all nodes.



The condition below:



WHEN C.PARENT = R.TBNAME THEN 'LOOP'



is a good start and should detect recursive RI (table having a Foreign key to itself).



However, I don't think it can detect a chains like A is the parent of B, B is the parent of C and C is the parent of A.



I admit the business value of such looped RI is questionable, but I have seen a legitimate RI where A and B are parents of each other (no kidding!).



I take pride in the "I can do this in 1 SQL" mantra Sheryl advocated, but as I get older, feel more inclined to write some procedural code. This may be one of them.



Thx

Suresh









________________________________
From: Sevetson, Phil <[login to unmask email]<mailto:[login to unmask email]>>
Sent: Tuesday, January 9, 2018 4:36 PM
To: '[login to unmask email]'
Subject: [DB2-L] - RE: [DB2 z/OS 12]


Rudiger,



I don’t have a way to test this for an actual loop, because we don’t have a real RI loop in our catalog. If you have one, I _think_ this will find it within a single schema. If you have RI crossing schema boundaries, let me know and I’ll fix this a bit for that.



Can you, or someone, please either test this for me, or get me the ALTER statements to create an RI loop which doesn’t throw me out on syntax? The example below works reasonably well (and produces all of the intermediate states as well as the final, fully expressed levels) in my test. (I ran my test with LEVEL < 10.) I’d ideally like to have someone report that it found a loop; if so, I’ll commit it to the Code Place.



--Phil Sevetson



P.S. Hat tip to Suresh Sane for the original looping code which I built up from, and to David Bretz (http://www.idug.org/p/do/sd/sid=3780), who created the first recursive query against SYSRELS in the Codeplace. And to whoever wrote Rudiger’s example code.



WITH RAW_HIERARCHY (LEVEL, PARENT, LAST_LEV_FOUND, LOOP_FOUND, CHILDCHAIN)

AS

(SELECT 1, REFTBNAME, TBNAME, ' '

, CHAR(RTRIM(TBNAME)

CONCAT ','''

CONCAT DELETERULE

CONCAT ''' '

,255

)

FROM SYSIBM.SYSRELS WHERE REFTBCREATOR = '[your schema]'

UNION ALL

SELECT LEVEL + 1, C.PARENT, R.TBNAME

, CASE

WHEN C.PARENT = R.TBNAME THEN 'LOOP'

ELSE 'OK'

END AS LOOP_FOUND

, CHAR(RTRIM(C.CHILDCHAIN)

CONCAT ', '

CONCAT R.TBNAME

CONCAT ','''

CONCAT R.DELETERULE

CONCAT ''''

,255

)

AS CHILDCHAIN

FROM RAW_HIERARCHY C

,SYSIBM.SYSRELS R

WHERE LEVEL < 100

AND R.REFTBNAME = C.LAST_LEV_FOUND

AND R.REFTBCREATOR = '[your schema]'

)

SELECT *

FROM RAW_HIERARCHY

;





From: Kurtz, Rüdiger [mailto:[login to unmask email]
Sent: Tuesday, January 09, 2018 9:57 AM
To: '[login to unmask email]'
Subject: [DB2-L] - AW: [DB2 z/OS 12]



Phil,

we use Catalog Manager and we have come up with an sql to guide us through RI’s mist:



WITH PUMP (LEVEL, STARTTAB, ENDTAB, PATH, histdelrule,

relname,deleterule,ENFORCED

,cols

) AS

(SELECT 1, REFTBNAME, TBNAME ,

STRIP(REFTBNAME)!!'_'!!deleterule!!'_'!!STRIP(TBNAME)

,deleterule

,relname ,deleterule,ENFORCED

,colcount COLS

FROM SYSIBM.SYSRELS

where

REFTBNAME='<parent-table>'

and REFTBCREATOR = '<creator>'

UNION ALL

SELECT PUMP.LEVEL+1, PUMP.STARTTAB,

CHILD.TBNAME, PUMP.PATH!!'_'!!child.deleterule!!'_'!!CHILD.TBNAME,

pump.deleterule!!'_'!!child.deleterule,

child.relname ,child.deleterule

,child.ENFORCED

,colcount cols

FROM PUMP, SYSIBM.SYSRELS CHILD

WHERE PUMP.ENDTAB = CHILD.REFTBNAME

AND CHILD.TBNAME <> CHILD.REFTBNAME

AND PUMP.STARTTAB <> PUMP.ENDTAB

and child.REFTBCREATOR = '<creator>'

-- and pump.DELETERULE != 'C'

AND PUMP.LEVEL < 30

)

SELECT distinct

LEVEL, STARTTAB, ENDTAB, PATH,histdelrule,

relname,deleterule,ENFORCED

,cols

from pump



When I said we are using quite a bit of RI I really mean quite a bit ... just to give you some hint of what I’m talking about, we do have tables with more than 200 relations.
Call us crazy, but this is what we have. Neither Catalog Manager or our sql could provide an explanation so far.



Regards

Ruediger







Rüdiger Kurtz
Abteilung Informatik – Betrieb

HUK-COBURG
Bahnhofsplatz
96444 Coburg

Telefon:


09561 96-44148


Telefax:


09561 96-44104


E-Mail:


[login to unmask email]<mailto:[login to unmask email]>


Internet:


www.huk.de http://www.huk.de


________________________________

HUK-COBURG Haftpflicht-Unterstützungs-Kasse kraftfahrender Beamter Deutschlands a. G. in Coburg
Reg.-Gericht Coburg HRB 100; St.-Nr. 9212/101/00021
Sitz der Gesellschaft: Bahnhofsplatz, 96444 Coburg
Vorsitzender des Aufsichtsrats: Prof. Dr. Heinrich R. Schradin.
Vorstand: Klaus-Jürgen Heitmann (Sprecher), Stefan Gronbach, Dr. Hans Olav Herøy, Dr. Jörg Rheinländer (stv.), Sarah Rössler, Daniel Thomas (stv.).

________________________________

Diese Nachricht enthält vertrauliche und/oder rechtlich geschützte Informationen.
Wenn Sie nicht der richtige Adressat sind oder diese Nachricht irrtümlich erhalten haben,
informieren Sie bitte sofort den Absender und vernichten Sie diese Nachricht.
Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Nachricht ist nicht gestattet.

This information may contain confidential and/or privileged information.
If you are not the intended recipient (or have received this information in error) please notify the
sender immediately and destroy this information.
Any unauthorized copying, disclosure or distribution of the material in this information is strictly forbidden.

________________________________

Von: Sevetson, Phil [mailto:[login to unmask email]
Gesendet: Dienstag, 9. Januar 2018 15:40
An: '[login to unmask email]' <[login to unmask email]<mailto:[login to unmask email]>>
Betreff: [DB2-L] - RE: [DB2 z/OS 12]



Rudiger,

Do you have a catalog navigation tool? Also… do you suspect the RI loop of being so long (or complex) that walking the SYSRELS table won’t be enough to find it? You’d need a recursion processor to concatenate table names, I think, and set it to loop at least as many times as your worst-case imagination about number of levels.



From: Kurtz, Rüdiger [mailto:[login to unmask email]
Sent: Tuesday, January 09, 2018 9:35 AM
To: '[login to unmask email]'
Subject: [DB2-L] - [DB2 z/OS 12]



Folks,

we’re using quite a bit of RI in our shop. Due to legal requirements we are forced to change some delete-rules from restrict to cascade.

All changes but one have successfully been implemented so far, but this one drives us nuts.

We are receiving an -634 and have spent the last two days looking for an explanation but haven’t come up with any so far.
For those who are not familiar with this code:



<snip>



-634



THE DELETE RULE MUST NOT BE CASCADE



Explanation

~~~~~~~~~~~



The code is used to report that the CASCADE delete rule specified in the

FOREIGN KEY clause of an ALTER TABLE statement is invalid because:



The relationship would form a cycle that would cause a table to be

delete-connected to itself.



The relationship would cause another table to be delete-connected to the

same table through multiple paths with different delete rules or with a

delete rule equal to SET NULL.



</snip>

We can rule out the SET NULL rule.
We changed delete-rules forth and back, set them to ENFORCE NO, but the -634 won’t go away.

Now the question to you SQL gurus out there: Do any of you have an sql handy that detects any such circle?



Thanks in advance

Rüdiger

Rüdiger Kurtz
Abteilung Informatik – Betrieb

HUK-COBURG
Bahnhofsplatz
96444 Coburg

Telefon:


09561 96-44148


Telefax:


09561 96-44104


E-Mail:


[login to unmask email]<mailto:[login to unmask email]>


Internet:


www.huk.de http://www.huk.de


________________________________

HUK-COBURG Haftpflicht-Unterstützungs-Kasse kraftfahrender Beamter Deutschlands a. G. in Coburg
Reg.-Gericht Coburg HRB 100; St.-Nr. 9212/101/00021
Sitz der Gesellschaft: Bahnhofsplatz, 96444 Coburg
Vorsitzender des Aufsichtsrats: Prof. Dr. Heinrich R. Schradin.
Vorstand: Klaus-Jürgen Heitmann (Sprecher), Stefan Gronbach, Dr. Hans Olav Herøy, Dr. Jörg Rheinländer (stv.), Sarah Rössler, Daniel Thomas (stv.).

________________________________

Diese Nachricht enthält vertrauliche und/oder rechtlich geschützte Informationen.
Wenn Sie nicht der richtige Adressat sind oder diese Nachricht irrtümlich erhalten haben,
informieren Sie bitte sofort den Absender und vernichten Sie diese Nachricht.
Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Nachricht ist nicht gestattet.

This information may contain confidential and/or privileged information.
If you are not the intended recipient (or have received this information in error) please notify the
sender immediately and destroy this information.
Any unauthorized copying, disclosure or distribution of the material in this information is strictly forbidden.

________________________________



-----End Original Message-----

**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

-----End Original Message-----


-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
-----End Original Message-----

Michael Hannan

RE: AW: [DB2 z/OS 12]
(in response to Ruediger Kurtz)

Ruediger,

I can't say that all is now clear to me, from what you wrote below. Perhaps I did recognise an SQL you provided but was not certain. "PUMP" was not a term I would use. Great if others think of recursion like a pump though.

I was also puzzled that you were "Legally Required" to use Cascade since many sites do not use Referential Integrity at all. Without Cascade, but using Restrict, it merely forces the cleanup of the children, first before deleting the parent, or at least removal of FK references. I could understand that "SET NULL" might well be unacceptable. 
 
In Reply to Ruediger Kurtz:

In this particular case, we found that we do not encounter an infinite loop, but we have restrictions on tables that prevent us from altering the foreign key; thus we had to alter two different foreign keys’ deleterule and all’s well.

One minor thought, though: we haven’t got the thumbs-up from our application developers since these two foreign keys are not on the list of the ones we had to change, thus they have to check the underlying business-rules. But this an SOP (Someone Else’s Problem).

Thanks for your invaluable help all the same.

@Phil: I ran your sql, it works but doesn’t detect any loops; I had to limit the search, though, as the original version blew up our work-db. I also had to change the “CHAR(<whatever), 255” syntax to “VARCHAR() , 4096), but this is just a minor alteration.

@Sam: I haven’t applied your sql to our needs yet, hope I’ll get round to it this week.

@Michael: I wonder if you hadn’t recognized the sql I used in my original mail ☺

best regards

Ruediger

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Ruediger Kurtz

AW: AW: [DB2 z/OS 12]
(in response to Michael Hannan)
Michael,

okay, something didn’t convey correctly. We are *not* legally required to use CASCADE, but using CASCADE is the way we chose to get in accordance with legal requirements.
Legal requirement is to delete data after a certain period of time, basically.

We can have a chat about it next time you’re here.

Regards

Ruediger




Rüdiger Kurtz
Abteilung Informatik – Betrieb

HUK-COBURG
Bahnhofsplatz
96444 Coburg
Telefon: 09561 96-44148
Telefax: 09561 96-44104
E-Mail: [login to unmask email]
Internet: www.huk.de
________________________________
HUK-COBURG Haftpflicht-Unterstützungs-Kasse kraftfahrender Beamter Deutschlands a. G. in Coburg
Reg.-Gericht Coburg HRB 100; St.-Nr. 9212/101/00021
Sitz der Gesellschaft: Bahnhofsplatz, 96444 Coburg
Vorsitzender des Aufsichtsrats: Prof. Dr. Heinrich R. Schradin.
Vorstand: Klaus-Jürgen Heitmann (Sprecher), Stefan Gronbach, Dr. Hans Olav Herøy, Dr. Jörg Rheinländer (stv.), Sarah Rössler, Daniel Thomas (stv.).
________________________________
Diese Nachricht enthält vertrauliche und/oder rechtlich geschützte Informationen.
Wenn Sie nicht der richtige Adressat sind oder diese Nachricht irrtümlich erhalten haben,
informieren Sie bitte sofort den Absender und vernichten Sie diese Nachricht.
Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Nachricht ist nicht gestattet.

This information may contain confidential and/or privileged information.
If you are not the intended recipient (or have received this information in error) please notify the
sender immediately and destroy this information.
Any unauthorized copying, disclosure or distribution of the material in this information is strictly forbidden.
________________________________
Von: Michael Hannan [mailto:[login to unmask email]
Gesendet: Mittwoch, 10. Januar 2018 13:27
An: [login to unmask email]
Betreff: [DB2-L] - RE: AW: [DB2 z/OS 12]


Ruediger,

I can't say that all is now clear to me, from what you wrote below. Perhaps I did recognise an SQL you provided but was not certain. "PUMP" was not a term I would use. Great if others think of recursion like a pump though.

I was also puzzled that you were "Legally Required" to use Cascade since many sites do not use Referential Integrity at all. Without Cascade, but using Restrict, it merely forces the cleanup of the children, first before deleting the parent, or at least removal of FK references. I could understand that "SET NULL" might well be unacceptable.

In Reply to Ruediger Kurtz:
In this particular case, we found that we do not encounter an infinite loop, but we have restrictions on tables that prevent us from altering the foreign key; thus we had to alter two different foreign keys’ deleterule and all’s well.

One minor thought, though: we haven’t got the thumbs-up from our application developers since these two foreign keys are not on the list of the ones we had to change, thus they have to check the underlying business-rules. But this an SOP (Someone Else’s Problem).

Thanks for your invaluable help all the same.

@Phil: I ran your sql, it works but doesn’t detect any loops; I had to limit the search, though, as the original version blew up our work-db. I also had to change the “CHAR(

@Sam: I haven’t applied your sql to our needs yet, hope I’ll get round to it this week.

@Michael: I wonder if you hadn’t recognized the sql I used in my original mail ☺

best regards

Ruediger

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

-----End Original Message-----