Order of Tables DBUNLOAD

[Baron]

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?

[Breck_Carter · Accepted solution]

> 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 🙂

[Baron · Accepted solution]

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?

[VolkerBarth · Accepted solution]

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.

[Breck_Carter · Accepted solution]

@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 🙂

[johnsmirnios]

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.

[Baron]

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).

[johnsmirnios]

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.

[VolkerBarth]

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.

[Baron]

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...

[johnsmirnios]

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.