[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Determine table load order with many foreign keys in to DB2 LUW 9.5

Posted on 2010-09-16
7
Medium Priority
?
1,426 Views
Last Modified: 2012-05-10
Objective: Loading many tables in the correct order to prevent foreign key and any other violations.

We have 60-90 tables that need to be loaded into a DB2 LUW 9.5 instance.  There are a considerable number of foreign key relationships on many tables.

I "think" an SQL script that looks at various system tables, and produces a list of the "load order of tables" is needed. The list can include the type of table (table, view, nickname, etc), and any other information that you believe is useful (number of rows in the tables would be a plus, but certainly not necessary).

I have searched for hints or scripts that might provide this capability.  
 
Notwithstanding RISKS, what about an sql script to disable keys, indices, foreign keys, et cetera prior to loading data?  And a second SQL script to enable all of these subsequent load completion?  Is that possible?

Finally, the only software tools we have available are Control Center and Data Studio 2.2.  A third party ETL tool will be used to select and load the data (not DBLoad).

I may be asking for a lot, so I will make this a 500 point question;  depending on responses I may award points to "best-assisted" answers too.

Please let me know if additional information is needed.
0
Comment
Question by:JdanielII
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 46

Accepted Solution

by:
Kent Olsen earned 2000 total points
ID: 33696819
Hi Daniel,

If it were me, I'd disable the foreign keys and load the tables in any order that makes sense to you.

ALTER TABLE mytable ALTER FOREIGN KEY keyname NOT ENFORCED;

Then load the table and reenable the constraint.

ALTER TABLE mytable ALTER FOREIGN KEY keyname ENFORCED;


Not only will this eliminate the need to load the tables in a specific order, it usually results in better performance in the entire load process.  DB2 will check all of the constraints in one pass instead of reading the foreign table with each loaded row.

Dropping the indexes before you load the data will also generally improve performance.

Of course, those rules must be flexible.  If the table already has 1,000,000 rows and you're adding 100, it's probably not a good idea to disable the constraint and drop the indexes.


Kent
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 33697043
so what will it be?
do you need the script or you go with kent's advice?
0
 

Author Comment

by:JdanielII
ID: 33697194
1st choice is a script; my colleagues can also use this info daily.  We may not be able to alter, but I know we can select from sysibm.

Thanks for inquiring.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 46

Expert Comment

by:Kent Olsen
ID: 33697295
Hi Daniel,

Are you loading the tables fully or adding to existing data?

0
 

Author Comment

by:JdanielII
ID: 33697345
Target tables will initially be empty. Only full table loads will be performed.
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 33697371
That's certainly the easiest to navigate.  :)

I strongly suggest that you load the tables without indexes or constraints and add them after the loads are complete.  You'll see a huge performance benefit from this approach.

It might take someone with DBA permissions, but that should be easy to arrange.  If you're loading to your own schema, you should have those permissions.


Kent
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 33697380
I have to agree with Kent
0

Featured Post

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question