Sort table names by foreign key dependencies in a schema/Database

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}'

re: Sort table names by foreign key dependencies in a schema/Database

I use below commands for the same purpose. Load/truncate works great.

Disable Constraints:
db2 -x "select 'alter table ' || ltrim(rtrim(tabschema)) || '.'|| ltrim(rtrim(tabname)) || ' alter foreign key ' || ltrim(rtrim(constname)) || ' NOT ENFORCED ;' from syscat.references where tabschema not in('EXPLAIN','QUEST','SYSCAT','SYSCATV82','SYSIBM' ,'SYSSTAT','SYSTOOLS')"

Enable Constraints:

db2 -x "select 'alter table ' || ltrim(rtrim(tabschema)) || '.'|| ltrim(rtrim(tabname)) || ' alter foreign key ' || ltrim(rtrim(constname)) || ' ENFORCED ;' from syscat.references where tabschema not in('EXPLAIN','QUEST','SYSCAT','SYSCATV82','SYSIBM' ,'SYSSTAT','SYSTOOLS')"

Ta

Vijay

http://www.orkut.com/Community?cmm=56387550&mt=7

re: Sort table names by foreign key dependencies in a schema/Database

I don't have alter privileges, anyway I got the required query. Thanks