How to find parent child relationship for import script

[samuel ray]

Hello,

I am using DB2 10.5.0.5 on Linux x86. I need to write import script (deletes the data from child till the parent) and then import data (parent till child). The table hierarchy is quite complex where a table has self reference, a leaf child has a direct relationship with the top most parent etc. I found some sqls on this forum but none of those are giving correct results.

Can someone provide the query that wouldnt go in infinite loop?

I tried below but it repeats the parent-child name again for every level

WITH parentchild
(
parentname,
childname,
depth
) AS
(SELECT reftabname,
tabname,
1
FROM syscat.references
WHERE reftabname= 'MYTABLE'
UNION ALL
SELECT child.reftabname,
child.tabname,
depth+1
FROM parentchild parent,
syscat.references child
WHERE parent.childname = child.reftabname and depth <3
)
SELECT DISTINCT parentname,
childname,
depth
FROM parentchild
ORDER BY parentname;