I am trying to unload a DB into files using DBUNLOAD, and then load/input the resulted tables(.dat files) into an empty DB. I have problem in inputing these tables because of not respecting the order of relationships between tables. Example: a table containing a foreign key is trying to be loaded before the corresponding table with primary key being loaded!! How should I tell the DBUNLOAD to respect the relationships of tables? I didnt find any such option in the documentation. As a workaround I have used SET OPTION WAIT_FOR_COMMIT='ON', but this makes the load process much more slower. Anyone could help?

asked 19 Sep, 11:48

Sarkis's gravatar image

Sarkis
300192336
accept rate: 0%


> As a workaround I have used SET OPTION WAIT_FOR_COMMIT='ON', but this makes the load process much more slower.

If memory serves, running ALTER TABLE DROP FOREIGN KEY for all the foreign keys before loading the data, then ALTER TABLE ADD FOREIGN KEY again AFTER loading the data, runs pretty well.

...just the foreign key definitions, not the primary keys... that's a different discussion :)

permanent link

answered 19 Sep, 13:47

Breck%20Carter's gravatar image

Breck Carter
28.0k476634915
accept rate: 20%

Thanks for the reply, this could be the solution, but the question is, is there a way to read all the configurations of FOREIGN KEYS so that I can read them again after dropping? OR, I should drop all FOREIGN KEYS on a table and then add them (and repeat this for each table having FK)? Could I use the SYSCONSTRAINT for example?

(19 Sep, 14:13) Sarkis
2

I would simply let Dbunload -n create the reload.sql. This includes a section with all FK declarations (as John has mentioned), so you can just use that section.

(19 Sep, 14:26) Volker Barth
Replies hidden
1

@Sarkis: Yes, what @Volker said, for the ALTER TABLE ADD FOREIGN KEY statements.

As for the ALTER TABLE DROP FOREIGN KEY statements, it's easier to write SQL code to generate those because you don't need all the crap details about columns, hints, options, blah blah blah :)

(19 Sep, 16:04) Breck Carter

dbunload creates a reload script that defines the tables with their primary keys, then loads the data, then does ALTERs to add the foreign keys. It shouldn't be possible to get key violations during the data loads.

permanent link

answered 19 Sep, 12:09

John%20Smirnios's gravatar image

John Smirnios
9.4k378119
accept rate: 38%

But I have the case that I use DBUNLOAD only for data, without loading structure. The structure and the relationships between tables are already defined in my empty database, on which I inputing the resulted tables(.dat files).

(19 Sep, 12:13) Sarkis
Replies hidden

dbunload does not order the LOADs, just the creation of the keys. There are cases in which there is no ordering of LOADs that can be done in separate transactions without generating a key violation. For example, some schemas have circular PK/FK relationships.

(19 Sep, 12:19) John Smirnios

Another one would be CHECK constraints that relate to other tables whose data is not yet loaded. (Been there, done that...)

Note that this issue does not seem to apply here as Sarkis has to deal with FK violations.

(19 Sep, 12:25) Volker Barth

I am using the command 'dbunload -d .....' As a result I get a RELOAD.SQL file which contains only two groups of statements: (LOAD STATISTICS &&& INPUT INTO tablename FROM...) I dont see any statement in the RELOAD.SQL defining the constraints / creating PK/FK...

(19 Sep, 12:39) Sarkis
Replies hidden

A full unload without the -d would generate the other statements I'm talking about. In the general case, there is not always an ordering of data loads that will work so dbunload does work that way. If you happen to have a schema for which there is an ordering of LOADs which will work, you will need to order the data loads yourself according to your schema. Alternatively, if it is possible in your case, your 'empty' database should not have the schema either: let dbunload generate the schema definitions for you too.

(19 Sep, 12:44) John Smirnios
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×42

question asked: 19 Sep, 11:48

question was seen: 69 times

last updated: 19 Sep, 16:05