I want to generate a list of table names into a file in the order of FK dependencies. The purpose is to truncate all tables in a given database/schema by using the generated file in a "for loop". I use "Load from /dev/null..." or "Import.." command to truncate the tables. The database is IBM DB2 V 9 on an AIX platform. The below query gave me a list of tables that aren't parents, in other words list of Children that aren't parents. I need help in taking this further up the hierarchy. Thanks in advance for the help.
db2 "WITH tmp
(
tabname
) AS
(SELECT DISTINCT rtrim(reftabschema) || '.' || SUBSTR(reftabname,1,50)
FROM syscat.references
ORDER BY 1
)
SELECT distinct rtrim(tabschema) || '.' || SUBSTR(tabname,1,50)
FROM syscat.references
WHERE rtrim(tabschema) || '.' || SUBSTR(tabname,1,50)
NOT IN (select * FROM tmp)"|awk '{print $1}'