473,324 Members | 2,248 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

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}'
Sep 4 '09 #1
2 4162
vijay2082
112 100+
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
Sep 5 '09 #2
I don't have alter privileges, anyway I got the required query. Thanks
Sep 5 '09 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: ewu | last post by:
Hello all, I got a chance to peak into a database system. Part of its design is rather unfamiliar to me. When I look at the diagram generated by SQL Server, there are many floating tables. ...
1
by: php newbie | last post by:
I have a quick question on how to qualify table names as it relates to "dbo" vs. user names. Suppose that I am a user named "dwuser1", and that I need to create a table named "dw_stage_1". Do I...
1
by: Jens Riedel | last post by:
Hello, we are planning to port an application from one database to different others, including DB2. While reading the GettingStarted document for DB2 Personal Edition I got a little confused...
1
by: Gordon Keeler | last post by:
Hi all. I'm trying to develop an automated method of converting Access (97 or 2000) databases to SAS using DBMSCopy. I've discovered that there is not a rigid standard set to control the naming...
3
by: David C. Barber | last post by:
Using SQL Server 2000 and moving to a new computer. We did a full backup of the existing database to tape, brought up the new computer with a clean install using the same server name and IP...
1
by: stephen.anderson | last post by:
Can anyone point me to an existing utility or code template to write a utility? I want a command line utility, passing in the MDB filename, producing a file (text or whatever) of tablenames and...
5
by: blackjack2150 | last post by:
Hi guys! What query should I use to retrieve a list of all the table names in an Access database? The effect would be similar to that of a simple "SHOW TABLES;" in MySql. To clear thing up, "SHOW...
2
by: Deepa koruturu | last post by:
Hi How to Retrieve Table Names And Column Names in perticuler schema using oracle
4
by: noorg | last post by:
hi all can you tell me the db2 query for how to get all table names from db2 database schema
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.