Toad World® Forums

Am I too late for a suggestion of an enhancement for Toad?


#1

My suggestion is instead of ordering the tables in alphabetical order on exports order them from child to parent. That way if you are exporting from an active system you won’t get all of the errors when putting the foreign keys back on it. The following query, when run in the source subsystem will give you the list of tables in this order (it takes a little bit to run but it works well):

WITH children (creator, kkey, lvl) AS
(SELECT creator, REFTBNAME, 1
FROM SYSIBM.SYSRELS
where creator = ‘SCHEMANAME’
UNION ALL
SELECT h.creator, H.REFTBNAME, C.lvl + 1
FROM SYSIBM.SYSRELS H
,children C
WHERE H.TBNAME = C.kkey
and h.creator = ‘SCHEMANAME’
and C.lvl
)
,parents (creator, kkey, lvl) AS
(SELECT creator, TBNAME, -1
FROM SYSIBM.SYSRELS
where creator = ‘SCHEMANAME’
UNION ALL
SELECT h.creator, H.TBNAME, P.lvl - 1
FROM SYSIBM.SYSRELS H
,parents P
WHERE H.REFTBNAME = P.kkey
and h.creator = ‘SCHEMANAME’
and P.lvl > -50
)
,others (creator, kkey, lvl) AS
(SELECT creator, NAME, 1
FROM SYSIBM.SYStables
where creator = ‘SCHEMANAME’
and name not in
(select tbname from sysibm.sysrels)
and name not in
(select REFTBNAME from sysibm.sysrels)
and type = ‘T’
)
select tmp2.kkey
from
(select tmp1.creator, tmp1.kkey, min(tmp1.lvl) as lvlmin
from
(SELECT distinct creator, kkey ,lvl
FROM children
UNION ALL
SELECT distinct creator, kkey ,lvl
FROM parents
union all
SELECT distinct creator, kkey ,lvl
FROM others
order by lvl) as tmp1
group by tmp1.creator, tmp1.kkey
order by lvlmin) as tmp2;


#2

Jim -

I’ve entered request #73977 on your behalf.

Thank you
Jeff


#3

This is another test by the System’s Administrator. Please disregard.