Traversing Relationship Tree

[mikelbell2000]

I am attempting to write a query that will traverse the relationships
for tables in my database that will provide me the order in which I
need to perform delete operations to avoid running into foreign key
constraints. I have determined the delete order manually, but since the
database is complex and always having to structures added to it as the
scope of the application grows, I was looking for an automated
approach.

I tried modelling the query after some recursive examples from Graeme
Birchall's SQL Cookbook, but am missing something fundamental and the
query produces "infinite" output. I have been using SYSCAT.REFERENC ES
as my target table (view). The last incarnation of my query looks
something like this:

with temp1 (src_name, depends_on) as
((select root.reftabname , root.tabname
from syscat.referenc es root
where root.reftabname ='PX_CASE')
union all
( select child.reftabnam e, child.tabname
from temp1 parent, syscat.referenc es child
where parent.depends_ on = child.tabname)
)
select depends_on
from temp1
order by depends_on;

There are only 130 FK constraints on the collective tables, yet this
produces thousands of rows of output before I end up manually
terminating it. I have probably bitten off more than I can chew, but am
convinced that the solution exists to this problem. Can anyone make any
suggestions?

Thanks,
Mike

[Knut Stolze]

mikelbell2000 wrote:

I am attempting to write a query that will traverse the relationships
for tables in my database that will provide me the order in which I
need to perform delete operations to avoid running into foreign key
constraints. I have determined the delete order manually, but since the
database is complex and always having to structures added to it as the
scope of the application grows, I was looking for an automated
approach.

I tried modelling the query after some recursive examples from Graeme
Birchall's SQL Cookbook, but am missing something fundamental and the
query produces "infinite" output. I have been using SYSCAT.REFERENC ES
as my target table (view). The last incarnation of my query looks
something like this:

with temp1 (src_name, depends_on) as
((select root.reftabname , root.tabname
from syscat.referenc es root
where root.reftabname ='PX_CASE')
union all
( select child.reftabnam e, child.tabname
from temp1 parent, syscat.referenc es child
where parent.depends_ on = child.tabname)
)

Somehow this doesn't make a lot of sense to me. src_name is the name of a
referenced table A and "depends_on " contains the name of a referencing
table B (according to the start of the recursion). The part after the
UNION ALL should now try to find all tables that reference B, right? But
the sub-select there finds table B itself again. I would have expected
something like this:

WITH temp1 (src_name, depends_on) AS
( SELECT root.reftabname , root.tabname
FROM syscat.referenc es root
WHERE root.reftabname ='PX_CASE'
UNION ALL
SELECT child.reftabnam e, child.tabname
FROM temp1 parent, syscat.referenc es child
WHERE parent.depends_ on = child.reftabnam e )
SELECT depends_on
FROM temp1
ORDER BY depends_on

There are some other issues I see:
(1) You have table 'PX_CASE' as start of the recursion. You'll only find
tables that refer directly or indirectly to that one. If there are other
"start tables", you may miss things.
(2) You should add an explicit termination criteria (see examples in the SQL
Reference) to prevent endless recursions due to circular dependencies or
self-referencing tables.
(3) I would probably start at the leafs of the dependency-tree/graph, i.e.
with the tables that are not referenced by any other.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[mikelbell2000]

Thanks, Knut. This works much better. I usually shun recursion because
it's one of those things that makes my brain hurt when I think about it
too deeply :-)

The query I posted was probably the 10th incarnation of my attempt to
do this.

The first issue you raised is dead on. This is exactly what I want in
my particular case. I am actually looking to for all tables that are
descendants of one particular master table. My exact scenario is for
every record in PX_CASE that is older than a certain date, I want to
move it and all its dependents out of the current database into an
"archive" database. I build a list of all cases then use the table list
(which would be generated the recursive query) to export all related
data.

After loading data into archive database I need to delete from the
primary database. I need to delete from the bottom up.I will take your
version of the query and try to tweak it to include depth information
to be give me the delete order.

Cheers,
Mike

[Knut Stolze]

mikelbell2000 wrote:

Thanks, Knut. This works much better. I usually shun recursion because
it's one of those things that makes my brain hurt when I think about it
too deeply :-)

The query I posted was probably the 10th incarnation of my attempt to
do this.

The first issue you raised is dead on. This is exactly what I want in
my particular case. I am actually looking to for all tables that are
descendants of one particular master table. My exact scenario is for
every record in PX_CASE that is older than a certain date, I want to
move it and all its dependents out of the current database into an
"archive" database. I build a list of all cases then use the table list
(which would be generated the recursive query) to export all related
data.

After loading data into archive database I need to delete from the
primary database. I need to delete from the bottom up.I will take your
version of the query and try to tweak it to include depth information
to be give me the delete order.

Great, archiving. I did something along those lines in my master's
thesis. ;-)

--
Knut Stolze
DB2 Information Integration Development
IBM Germany